SurrealDB for SQL developers
SQL to SurrealDB mapping
Quickly learn how to map your SQL knowledge to corresponding SurrealDB concepts and syntax.
Introduction
As a multi-model database, SurrealDB offers a lot of flexibility. Our SQL-like query language SurrealQL is a good example of this, where we often have more than one way to achieve the same result, depending on developer preference. In this mapping guide, we will focus on the syntax that most closely resembles the ANSI Structured Query Language (SQL).
Concepts mapping
For more in-depth explanations of SurrealDB concepts, see the concepts page.
| Relational databases | SurrealDB |
|---|---|
| database | database |
| table | table |
| row | record |
| column | field |
| index | index |
| primary key | record id |
| transaction | transaction |
| join | record links, embedding and graph relations |
Syntax mapping
Let's get you up to speed with SurrealQL syntax with some CRUD examples.
Create
As relational databases are schemafull, only the SurrealQL schemafull approach is shown below. For a schemafull option see the define table page.
For more SurrealQL examples, see the create and insert pages.
| SQL | SurrealQL |
|---|---|
| CREATE TABLE person ( person_id SERIAL PRIMARY KEY, name varchar(255) ) // SERIAL is PosgresSQL syntax | DEFINE TABLE person SCHEMAFULL; DEFINE FIELD name ON TABLE person TYPE string; // record id field is defined by default |
| INSERT INTO person (name) VALUES ('John'), ('Jane') | INSERT INTO person (name) VALUES ('John'), ('Jane') |
| CREATE INDEX idx_name ON person (name) | DEFINE INDEX idx_name ON TABLE person COLUMNS name |
| column | field |
| index | index |
| primary key | record id |
| transaction | transaction |
| join | record links, embedding and graph relations |
Read
For more SurrealQL examples, see the select, live select and return pages.
| SQL | SurrealQL |
|---|---|
| SELECT * FROM person | SELECT * FROM person |
| SELECT name FROM person | SELECT name FROM person |
| SELECT name FROM person WHERE name = "Jane" | SELECT name FROM person WHERE name = "Jane" |
| EXPLAIN SELECT name FROM person WHERE name = "Jane" | SELECT name FROM person WHERE name = "Jane" EXPLAIN |
| SELECT count(*) AS person_count FROM person | SELECT count() AS person_count FROM person GROUP ALL |
| SELECT DISTINCT name FROM person | SELECT array::distinct(name) FROM person GROUP ALL |
| SELECT * FROM person LIMIT 10 | SELECT * FROM person LIMIT 10 |
| SELECT review.*, person.name as reviewer FROM review INNER JOIN review.person_id = person.id | SELECT *, person.name as reviewer FROM review |
Update
For more SurrealQL examples, see the update page.
| SQL | SurrealQL |
|---|---|
| ALTER TABLE person ADD last_name varchar(255) | DEFINE FIELD last_name ON TABLE person TYPE string |
| UPDATE person SET last_name = "Doe" WHERE name = "Jane" | UPDATE person SET last_name = "Doe" WHERE name = "Jane" |
| ALTER TABLE person DROP COLUMN last_name | REMOVE FIELD last_name ON TABLE person |
Delete
For more SurrealQL examples, see the delete and remove pages.
| SQL | SurrealQL |
|---|---|
| DELETE FROM person WHERE name = "Jane" | DELETE person WHERE name = "Jane" |
| DELETE FROM person | DELETE person |
| DROP TABLE person | REMOVE TABLE person |