Check out v4 of the Fauna CLI

v4 of the Fauna CLI is now in beta.

The new version introduces enhancements to the developer experience, including an improved authentication workflow. To get started, check out the CLI v4 quick start.

Fauna 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).

Moving from PostgreSQL to Fauna? Check out the Modernizing from PostgreSQL to Serverless blog series.

Major differences

The following table outlines major differences between traditional RDBs and 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 schema model with optional field definitions and constraints. Migrations require zero downtime.

Relationships

Uses foreign keys and joins to connect data across tables.

Uses document 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 schema.

You can create and manage schema using any of the following:

Concepts

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

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

Document reference

Stored procedure

User-defined function

Transactions

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 schema:

collection Dept {
  deptno: Number
  dname: String?
  loc: String?
  zip: Number?

  unique [.deptno]
  unique [.dname]

  index byDeptNo {
    terms [.deptno]
  }

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

  index sortedByDeptNoLowToHigh {
    values [.deptno, .dname, .zip]
  }
}
collection Emp {
  empno: Number
  ename: String?
  job: String?
  mgr: Number?
  hiredate: Date?
  sal: Number?
  comm: Number?
  deptno: Number?

  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 Fauna 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, create a collection schema in the database.

You can create and manage schema using any of the following:

For example, the Dept collection has the following schema:

collection Dept {
  deptno: Number
  dname: String?
  loc: String?
  ...

  unique [.deptno]
  ...

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

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

  • The deptno field is required in incoming documents.

  • 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);

Add a field definition

Fauna collections are schemaless by default. Documents can contain any field of any type. Documents in the same collection aren’t required to have the same fields.

You can enforce a document structure by adding field definitions to the collection schema:

collection Dept {
  zip: Number?
  ...
}

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 the Fauna CLI's fauna schema push command. 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"
  })

collection.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 set.first() to get the first (and only) document from the Set.

  • Call document.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 collection.where(), collection.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 set.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()
})

FQL provides several methods for iterating over a Set. set.forEach(), set.map(), set.flatMap() are similar but used for different purposes:

Method Primary use Notes

set.forEach()

Perform in-place writes on Set elements.

Doesn’t return a value.

set.map()

Returns a new Set.

Can’t perform writes.

set.flatMap()

Similar to set.map(), but flattens the resulting Set by one level.

Can’t perform writes.

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 collection.byId() to get a document by its id.

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

You can also use the built-in FQL array.fold() and set.fold() methods to create a groupBy() FQL function or a user-defined function (UDF) that outputs the same results as GROUP BY. See Group By: Aggregate data in Fauna.

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, update the Emp collection schema to include:

  • A nullable dept field definition that accepts Dept collection documents

  • A migration statement for the new dept field

  • A byDept() index definition that lets you get Emp collection documents by dept value

collection Emp {
  ...
  // Field definition for the `dept` field. Accepts a reference
  // to a `Dept` collection document or `null` (not present).
  dept: Ref<Dept>?

  // Migrations block. Contains an `add` migration statement
  // for the new `dept` field.
  migrations {
    add .dept
  }

  // Defines the `byDept()` index. Use the index to get
  // `Emp` documents by their `dept` field value.
  index byDept {
    terms [.dept]
  }
  ...
}

Then 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({
  empno: 123456,
  ename: "John Doe",
  sal: 2000,
  // Create a reference to "Sales" dept document
  dept: salesDept
})

Use the byDept() index to get Emp documents with a dept field that references a specific Dept collection 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.

Set operations

You can use set instance methods to perform SQL-like set operations, such as unions, joins, and intersections, in FQL. For examples, see Work with multiple Sets.

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!