FQL v4 will be decommissioned on June 30, 2025. Ensure that you complete your migration from FQL v4 to FQL v10 by that date.

Fauna accounts created after August 21, 2024 must use FQL v10. These accounts will not be able to run FQL v4 queries or access the v4 Dashboard.

For more details, see the v4 EOL announcement and migration guide. Contact support@fauna.com with any questions.

FQL for SQL users

This section describes a number of common structured query language (SQL) queries and their Fauna Query Language (FQL) equivalents.

While it is almost impossible to provide an exhaustive comparison of every variation of all SQL commands, we provide a very basic comparison of the most used DDL (Data Definition Language) and DML (Data Manipulation Language) queries. As you gain more experience with the FQL, the ease and power of its syntax should become evident. Complex queries that are difficult, or even impossible, in SQL can be composed very easily in FQL. One very important difference between the two is that FQL is a functional language, while SQL is declarative. Hence, the actual path of execution must be provided for each FQL query. FQL requires the developer to specify an index in most queries.

SQL to FQL transpiler

If you’re familiar with SQL and want to ease into FQL, try out the SQL to FQL transpiler, created by Fauna Labs to help developers execute simple SQL statements on a Fauna database by translating them to FQL queries. This is not meant to be an exhaustive translation system between SQL and FQL, but it is a useful tool for you and your teams as you get started with FQL.

SQL statements with FQL equivalents

DDL DML Query

Conceptual equivalents

Relational Fauna Description

Table

Collection

Tables store records, collections store documents.

In a relational database, a table’s column definition specifies the structure for all records in the table. The column definition specifies the names and types of values that can be stored in a column. When visualized, the columns extend horizontally, and records are organized as rows that extend vertically, comprising a grid of values.

Collections are merely a container for documents, imposing no specific structure on those documents. As such, there is no common way to visualize Fauna documents.

Record / Row

Document

In a relational database, a record (or row) represents a distinct database entry. Every row must conform to the containing table’s column definition.

Each Fauna document represents a nested structure of fields and their values, with no specified structure or types: each document, even in the same collection, can have its own independent structure. This makes Fauna documents schemaless.

Additionally, each Fauna document is versioned, storing the history of a document’s mutations from creation to deletion.

Schema

Database

For a relational database, a schema refers to the set of table definitions and constraints defined in that database. The schema is enforced so that no row violates its table definition or constraints.

For Fauna, a database can contain Collections, Documents, User-defined functions, Indexes, security configuration such as Keys, Tokens, Credentials, Access Providers, and child databases (with arbitrarily deep nesting).

A Fauna database contains schemaless documents with no schema enforcement available at the document level.

Index / Materialized Views

Index

In a traditional relational database, an index is used as a performance optimization to help locate rows within tables without incurring the cost of a full table scan. A view is a virtual table whose contents are defined by a stored SQL query.

In Fauna, an index provides both the search terms required to locate documents in a collection, and as a view where specific document values are returned for matching documents.

Foreign Key

Reference

A foreign key is an identifier for a row in another table, which is (most often) protected by a constraint (an optional part of schema enforcement). The constraint causes errors if record modifications would damage the relation that a foreign key represents.

Fauna has References, which can be used to uniquely identify a document anywhere in a particular database, but there are no built-in constraints protecting the relations. Due to Fauna’s schemaless nature, a Reference is not guaranteed to point at an existing document.

In these examples below, we use two tables, dept (departments) and emp (Employees) to compare basic operations in both query languages. These tables have been extensively used in Oracle documentation to explain various SQL basics.

SQL> DESC emp
 Name                         Null?         Type
 ----------------------------------------- --------
 EMPNO                      NOT NULL       NUMBER(4)
 ENAME                                     VARCHAR2(10)
 JOB                                       VARCHAR2(9)
 MGR                                       NUMBER(4)
 HIREDATE                                  DATE
 SAL                                       NUMBER(7,2)
 COMM                                      NUMBER(7,2)
 DEPTNO                                    NUMBER(2)
SQL> DESC dept
 Name                         Null?         Type
 ----------------------------------------- --------
 DEPTNO                     NOT NULL       NUMBER(2)
 DNAME                                     VARCHAR2(14)
 LOC                                       VARCHAR2(13)
 ZIP                                       NUMBER

Data definition language

CREATE DATABASE

In some relational databases, like MySQL, a database can be created with:

CREATE DATABASE employees;

CREATE DATABASE

Fauna is a multi-tenant database and databases can be created like a nested tree.

CreateDatabase({name: "employees"})

CREATE TABLE

CREATE TABLE dept(
  deptno   NUMBER(2,0),
  dname   VARCHAR2(14),
  loc     VARCHAR2(13),
  CONSTRAINT pk_dept PRIMARY KEY (deptno)
)

CREATE COLLECTION

CreateCollection({name: "dept"});

Fauna doesn’t enforce the structure of a collection at the time of creation. However, if we know that every document in this collection should have a deptno field, we can create a unique index on the deptno field which emulates a relational database’s primary key.

CreateIndex({
  name:   "dept_by_deptno",
  source: Collection("dept"),
  terms:  [{ field: [ "data", "deptno" ] }],
  unique: true
})

ALTER TABLE ADD COLUMN

ALTER TABLE dept ADD (zip NUMBER);

No direct correlation

As documents do not have a predefined schema, there is no straightforward equivalent to adding a term (equivalent to a column) to all documents without any values. The Fauna equivalent would be to run Update on the document.

Update(
  Ref(Collection("dept"), "224507299921658368"),
  { data: { zip: 10001 } }
)

TRUNCATE TABLE

In SQL, truncate removes all records, but preserves the structure of the table.

TRUNCATE TABLE dept;

DELETE DOCUMENTS

In FQL, the equivalent would be to delete all records from the table.

Map(
  Paginate(
    Match(Index("all_depts"))
  ),
  Lambda("X", Delete(Var("X")))
)

The all_depts index is a collection index that indexes the entire collection.

CreateIndex({
  name: "all_depts",
  source: Collection("dept")
})

DROP TABLE

DROP TABLE dept;

DELETE COLLECTIONS and INDEXES

The Delete command can be used to remove tables and indexes. Unlike in SQL, dropping a table doesn’t remove the underlying indexes automatically. The indexes need to be removed manually.

Delete a Collection
Delete(Collection("dept"))
Delete an Index
Delete(Index("all_depts"))

Data manipulation language

INSERT RECORD

INSERT INTO dept (deptno, dname, loc)
  VALUES (10, "ACCOUNTING", "NEW YORK");

CREATE DOCUMENT

Create(
  Collection("dept"),
  {
    data: {
      "deptno": 10,
      "dname": "ACCOUNTING",
      "loc": "NEW YORK"
    }
  }
)

UPDATE

UPDATE dept SET loc = "AUSTIN"
  WHERE deptno = 10;

UPDATE

Update(
  Select("ref",
    Get(
      Match(Index("dept_by_deptno"), 10)
    )
  ),
  {
    data: { loc: "AUSTIN" }
  }
)

Running the Replace command on an entire document is also a form of Update. This is similar to a Delete followed by an Insert.

Replace(
  Ref(Collection("dept"), "224572974137606656"),
  {
    data: {
      "deptno": 10,
      "dname": "ACCOUNTING",
      "loc":   "AUSTIN"
    }
  }
)

DELETE

DELETE FROM dept WHERE deptno = 10;

DELETE

You can use the reference as the key to delete a specific record.

Delete(
  Ref(Collection("dept"), "224532222499095041")
)

Alternatively, you can delete a record based on a specific index column.

Delete(
  Select(
    "ref",
    Get(
      Match(Index("dept_by_deptno"), 10)
    )
  )
)

Query

SELECT: ALL ROWS

SELECT * FROM dept;

GET ALL DOCUMENTS

Just like in relational databases, selecting all documents from a collection results in a full scan. In SQL, the server automatically selects the appropriate indexes based on the specified columns. In Fauna, indexes must be specified explicitly.

You need a collection index to run a full scan:

CreateIndex({
  name: "all_depts",
  source: Collection("dept")
})

Once the index is in place, run the query below.

Map(
  Paginate(
    Match(Index("all_depts"))
  ),
  Lambda("X", Get(Var("X")))
)

SELECT: Based on a single Parameter

SELECT * FROM dept WHERE deptno = 10;

GET: Based on a single Parameter

We can use the unique index we created earlier to enforce the primary key.

Map(
  Paginate(
    Match(Index("dept_by_deptno"), 10)
  ),
  Lambda("X", Get(Var("X")))
)

SELECT: Based on a single Parameter with a NOT

SELECT * FROM dept WHERE deptno != 10;

GET: Based on a single Parameter with a NOT

Unlike SQL, we create this list as a difference between two indexes, the collection index and the unique index on the deptno.

Map(
  Paginate(
    Difference(
      Match(Index("all_depts")),
      Match(Index("dept_by_deptno"), 10)
    )
  ),
  Lambda("x", Get(Var("x")))
)

SELECT: Based on a list

SELECT * FROM dept WHERE deptno IN (10,11,12)

GET: Based on a list

We need an index on deptno so let’s reuse our earlier dept_by_deptno index:

Map(
  Paginate(
    Union(
      Map(
        [10, 11, 12],
        Lambda(
          "empno",
          Match(Index("dept_by_deptno"), Var("empno"))
        )
      )
    )
  ),
  Lambda("X", Get(Var("X")))
)

Match returns a Set Reference, so when we Map over it, the result is an Array of set references. Then, the Union function combines the array back into a single set so it can be paginated.

SELECT: Based on a row id

SELECT * FROM users WHERE id = 2349879823

GET: Based on a document reference

In Fauna, every document has a reference: a compound value that combines a reference to the containing collection and a document ID (a string-encoded integer). You can fetch a document based on its Reference with the Get function.

Get(Ref(Collection('users'), '334638638369342034'))

SELECT: Based on a condition

SELECT * FROM emp WHERE sal >= 2000

GET: Based on a condition

In order to accomplish this, we need an index on the sal term along with the Refs that point to the location of each document.

CreateIndex({
  name: "emp_by_sal",
  source: Collection("emp"),
  values: [
    {field: ["data", "sal"]},
    {field: ["ref"]}
  ],
})

After the index has built, we can get the results with:

Map(
  Paginate(
    Match(Index("emp_by_sal")),
    { after: 2000 }
  ),
  Lambda("x", Get(Select(1, Var("x"))))
)

Observe the Lambda function. The Select command gets the Ref fields, and then passes them to the Get command. Alternatively, we could have used Lambda(["sal", "ref"], Get(Var("ref"))) as we know the index returns two different values.

The after parameter is inclusive.

SELECT: GROUP BY

Query to select the maximum salary by department

SELECT MAX(sal), deptno FROM emp GROUP BY deptno;

GET: Grouped documents

FQL can accomplish such queries using two indexes, the first on deptno and the second on deptno and salary.

CreateIndex({
  name: "emp_by_deptno",
  source: Collection("emp"),
  values: [{ field: ["data","deptno"] }]
})
CreateIndex({
  name: "deptno_by_sal",
  source: Collection("emp"),
  terms: [{ field: ["data","deptno"] }],
  values: [{ field: ["data","sal"] }]
})

The second index deptno_by_sal stores the values sorted by sal within each deptno group. Since Get() returns the first element that matches the index, it returns the maximum value of sal.

 
Map(
  Paginate(
    Distinct(
      Match(Index("emp_by_deptno"))
    )
  ),
  gid => Get(
    Match(Index("deptno_by_sal"), Gid)
  )
)

EQUI-JOIN two tables

SELECT e.* FROM emp e, dept d
 WHERE e.deptno = d.deptno
   AND d.dname = "SALES";

GET documents joined by two collections

We need two indexes to accomplish this join in FQL. When we JOIN two collections, the value of one index is joined to the term of another index.

Index #1 (Collection: Dept, Term: dname, Value: deptno)
CreateIndex({
  name: "dept_by_name",
  source: Collection("dept"),
  terms: [{ field: ["data", "dname"] }],
  values: [{ field: ["data","deptno"] }]
})
Index #2 (Collection: emp, Term: deptno)
CreateIndex({
  name: "emp_by_deptno",
  source: Collection("emp"),
  terms: [{ field: ["data","deptno"] }]
})
Query
Map(
  Paginate(
    Join(
      Match(Index("dept_by_name"), "SALES"),
      Index("emp_by_deptno")
    )
  ),
  Lambda("X", Get(Var("X")))
)

Is this article helpful? 

Tell Fauna how the article can be improved:
 Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!