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 |
||
Column |
||
Table |
||
Database |
||
Primary key |
You can mimic some aspects of a primary key using a unique constraint and an index term. See Create a table. |
|
Index / Materialized Views |
Fauna indexes must be named. This encourages better readability and more predictable behavior. |
|
Foreign key |
||
Stored procedure |
||
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]
}
}
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!