FQL for SQL users

This guide outlines major differences between traditional relational databases (RDBs) and Fauna. It also:

Maps traditional RDB concepts to Fauna
Translates common SQL queries to Fauna Query Language (FQL).

Major differences

The following table outlines major differences between traditional RDBs and Fauna.

Difference Traditional RDB Fauna

Difference	Traditional RDB	Fauna
Data model	Stores data in tables with rows and columns.	Stores data as JSON documents in collections.
Schema	Requires upfront schema definition with a fixed data type for each column.	Flexible collection schemas with the option to add field constraints and checks. Collection schemas can’t directly require fields or enforce field data types.
Relationships	Uses foreign keys and joins to connect data across tables.	Uses references to create relationships between documents in different collections.
Query language	Uses SQL, which relies on various commands.	Uses FQL, which has a Typescript-like syntax and relies on methods.
Data definition	Uses SQL to create databases, create tables, and alter tables.	Uses Fauna Schema Language (FSL) to define, create, and update collections as schemas. You can manage schemas in the Fauna Dashboard or as `.fsl` files using the Fauna CLI.

Data model

Stores data in tables with rows and columns.

Stores data as JSON documents in collections.

Schema

Requires upfront schema definition with a fixed data type for each column.

Flexible collection schemas with the option to add field constraints and checks.

Collection schemas can’t directly require fields or enforce field data types.

Relationships

Uses foreign keys and joins to connect data across tables.

Uses references to create relationships between documents in different collections.

Query language

Uses SQL, which relies on various commands.

Uses FQL, which has a Typescript-like syntax and relies on methods.

Data definition

Uses SQL to create databases, create tables, and alter tables.

Uses Fauna Schema Language (FSL) to define, create, and update collections as schemas.

You can manage schemas in the Fauna Dashboard or as .fsl files using the Fauna CLI.

Concepts

The following table maps common concepts from traditional RDBs to their equivalent in Fauna.

SQL Fauna Notes

SQL	Fauna	Notes
Record / Row	Document
Column	Document fields
Table	Collection
Database	Database
Primary key	Document `id`	You can mimic some aspects of a primary key using a unique constraint and an index term. See Create a table.
Index / Materialized Views	Index	Fauna indexes must be named. This encourages better readability and more predictable behavior.
Foreign key	Reference
Stored procedure	User-defined function
Transactions	Transactions

Record / Row

Document

Column

Document fields

Table

Collection

Database

Primary key

Document id

You can mimic some aspects of a primary key using a unique constraint and an index term. See Create a table.

Index / Materialized Views

Index

Fauna indexes must be named. This encourages better readability and more predictable behavior.

Foreign key

Reference

Stored procedure

User-defined function

Transactions

Examples

The following examples compare basic operations in SQL and Fauna.

The SQL examples use the dept (departments) and emp (employees) tables:

SQL> DESC dept
 Name                         Null?         Type
 ----------------------------------------- --------
 DEPTNO                     NOT NULL       NUMBER(2)
 DNAME                                     VARCHAR2(14)
 LOC                                       VARCHAR2(13)
 ZIP                                       NUMBER

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)

The Fauna examples use the corresponding Dept and Emp collections. The collections use the following schemas:

collection Dept {
  unique [.deptno]
  unique [.dname]

  index byDeptNo {
    terms [.deptno]
  }

  index byDeptName {
    terms [.dname]
    values [.deptno]
  }

  index sortedByDeptNoLowToHigh {
    values [.deptno, .dname, .zip]
  }
}

collection Emp {

  index byDeptNo {
    terms [.deptno]
  }

  index sortedBySalaryLowToHigh {
    values [.sal, .deptno]
  }

  index sortedBySalaryHighToLow {
    values [desc(.sal), .deptno]
  }
}

Create and alter

This section covers common data definition operations in SQL and Fauna.

Create a database

CREATE DATABASE

CREATE DATABASE employees;

Create a database

Fauna is multi-tenant. You can create a parent database with one or more nested child databases.

You can create top-level databases in the Dashboard or with the Fauna CLI. You can then run a query to create a child database:

Database.create({ name: "employees" })

Create a table

CREATE TABLE

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

Create a collection

To create a collection, add a collection schema in the Dashboard or upload the schema using the Fauna CLI.

For example, the Dept collection schema contains a unique constraint and an index definition:

collection Dept {
  unique [.deptno]
  ...

  index byDeptNo {
    terms [.deptno]
  }
  ...
}

The collection’s deptno field mimics some aspects of a primary key:

Each document in the collection must have a unique deptno value.
You can use the byDeptNo index to fetch documents based on deptno.

Fauna can’t directly require fields in collections or enforce field data types.

Add a column

ALTER TABLE: ADD COLUMN

ALTER TABLE dept ADD (zip NUMBER);

No direct equivalent

Fauna can’t directly require fields or enforce field data types. There’s no direct equivalent to adding a field without a value to all documents in a collection.

Truncate a table

TRUNCATE TABLE

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

TRUNCATE TABLE dept;

Delete and recreate a collection

In FQL, the equivalent is to delete and recreate the collection with the same schema. See Drop a table and Create a table.

Drop a table

DROP TABLE

DROP TABLE dept;

Delete a collection

To delete a collection, delete its schema using the Dashboard or Fauna CLI. Deleting a collection deletes its documents and indexes.

Insert, update, and delete

This section covers common data manipulation operations in SQL and Fauna.

Insert a record

INSERT

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

Create a document

Dept.create({
  deptno: 10,
  dname: "ACCOUNTING",
  loc: "NEW YORK"
})

Update a record

UPDATE

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

Update a document

Dept.where(.deptno == 10).first()
  ?.update({
    loc: "AUSTIN"
  })

where() requires a scan of the entire collection and isn’t performant on large collections.

For better performance, use an index with the deptno term to run an exact match search:

Dept.byDeptNo(10).first()
  ?.update({
    loc: "AUSTIN"
  })

The query uses method chaining to:

Call the Dept collection’s byDeptNo index to get a Set of documents with a deptno of 10. depto is the only term for the index.

Because of the collection’s unique constraint on deptno, the set only contains one document.
Call first() to get the first (and only) document from the set.
Call update() to update the document.

Indexes store, or cover, their terms and values for quicker retrieval than a collection scan.

Delete a record

DELETE

DELETE FROM dept WHERE deptno = 10;

Delete a document

Dept.where(.deptno == 10).first()
  ?.delete()

For better performance, use an index with the deptno term instead:

Dept.byDeptNo(10).first()
  ?.delete()

Select

This section covers common read operations in SQL and Fauna.

Select all records

SELECT: ALL ROWS

SELECT * FROM dept;

Get all documents

Dept.all()

Like where(), all() requires a scan of the entire collection. It isn’t performant on large collections.

Instead, use an index and projection to only get the specific fields you need:

Dept.sortedByDeptNoLowToHigh() {
  dname, loc
}

dname and loc are values of the sortedByDeptNoLowToHigh index.

The above query is covered: It can fetch dname and loc values without scanning the entire Dept collection.

Select based on a single parameter

SELECT with WHERE

SELECT * FROM dept WHERE deptno = 10;

Exact match search with an index term

Dept.where(.deptno == 10)

For better performance, use an index with the deptno term to run an exact match search:

Dept.byDeptNo(10)

Select using inequality

SELECT with an inequality comparison

SELECT * FROM dept WHERE deptno != 10;
f

Covered query with .where()

Dept.where(.deptno != 10)

For better performance, use an index that includes deptno as an index value:

Dept.sortedByDeptNoLowToHigh()
  .where(.deptno != 10)

The query uses where() to filter the set returned by the index. The query is more performant because it:

Applies where() to a smaller set of documents
Filters off a covered index value, deptno

Select based on a list

SELECT with IN

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

Get documents using .map()

// Convert array to set
let deptNums = [10, 11, 12].toSet()

// Iterate through the set
deptNums.map((deptno) => {
  // Get a `Dept` document for each dept num
  Dept.byDeptNo(deptno).first()
})

Fauna has two methods for iterating through a set:

map() returns a new set of transformed values. It uses lazy loading. Sets may be large, and eagerly mapping the set may be prohibitively resource-intensive.

Because it’s lazy, you can’t use map() to perform write operations.
forEach() eagerly iterates through the set. It lets you perform writes but does not return a new set.

flatMap() is similar to map() except it flattens the results by one level.

If deptno values weren’t unique, you could use flatMap() to flatten the resulting nested set:

// Convert array to set
let deptNums = [10, 11, 12].toSet()

// Iterate through the set and
// flatten the resulting set by one level
deptNums.flatMap((deptno) => {
  Dept.byDeptNo(deptno)
})

Select by ID

SELECT: Based on a row id

SELECT * FROM emp WHERE id = 2349879823

Get a document with byId

Emp.byId("395238614905126976")

Use byId() to get a document by its id.

Range search

SELECT with a range condition

SELECT * FROM emp WHERE sal >= 20000

Ranged search with an index value

Emp.where(.sal >= 20000)

For better performance, use an index with the sal value to run a ranged search:

Emp.sortedBySalaryLowToHigh({ from: 20000 })

sal is the first value of the sortedBySalaryLowToHigh index.

Group by

SELECT with GROUP BY

Query to select the maximum salary by department

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

Get grouped documents

// Get set of department numbers
let deptNums = Dept.sortedByDeptNoLowToHigh() { deptno }

// Get the first employee for each department number.
// Employees are sorted by salary from high to low.
deptNums.map((deptNum) => {
  Emp.sortedBySalaryHighToLow().firstWhere(
    .deptno == deptNum.deptno
  ) { sal, deptno }
})

Joins

EQUI-JOIN two tables

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

Get documents based on data from another collection

// Get the `deptno` for the "Sales" department
let salesDeptNo = Dept.byDeptName("Sales").first() { deptno }

// Get employees with a matching `deptno`
Emp.byDeptNo(salesDeptNo?.deptno)

Instead of using a foreign key, such as deptno, you can directly reference documents in other collections.

For example, create an Emp document with a dept field. The field references a document in the Dept collection:

// Get "Sales" dept document
let salesDept = Dept.byDeptName("Sales").first()

Emp.create({
  enam: "John Doe",
  sal: 2000,
  // Create a reference to "Sales" dept document
  dept: salesDept
})

You can define an Emp index that uses the dept field as its term:

collection Emp {

  index byDept {
    terms [.dept]
  }
  ...
}

Use the index term to get Emp documents that reference a specific Dept document:

// Get "Sales" dept document
let salesDept = Dept.byDeptName("Sales").first()

// Get `Emp` documents that
// reference the "Sales" dept document
Emp.byDept(salesDept)  { ename, sal, dept }

Use projection to resolve the reference in results. This is similar to performing a join.

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!