Fauna Query Language 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 Fauna Query language, 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 not a declarative language as SQL. Hence the actual path of execution needs to be provided for each query — FQL requires the developer to specify an index in most queries.

DDL DML Query

Conceptual equivalents

Relational FaunaDB

Schema

Database

Table

Collection

Row

Document

Index/Materialized Views

Index

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"});

FaunaDB 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 FaunaDB, 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 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")))
)

Stay tuned for another set of examples in the near future. If you are looking for a specific example, feel free to email me at deba@fauna.com.

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve! documentation@fauna.com

Thank you for your feedback!