SurrealDB: Multi-Model Database – SQL, Graph & Documents Combined

In the modern application landscape, data rarely fits neatly into a single paradigm. A social media app might need relational tables for user accounts, document stores for flexible post content, and graph queries for friend recommendations. Traditionally, developers stitch together multiple databases (e.g., PostgreSQL + MongoDB + Neo4j), leading to complex data synchronization, increased latency, and higher operational costs.

SurrealDB emerges as a solution: a multi-model database that natively supports relational, document, and graph data models within a single engine. It uses SurrealQL, a query language that extends SQL with constructs for nested objects and graph traversals. This means you can define a table with a flexible schema, embed documents, and traverse relationships—all in one query.

In this tutorial, you'll learn how to set up SurrealDB, model data using its multi-model capabilities, write queries that combine SQL, document, and graph operations, and apply best practices for production. We'll also explore a real-world production incident and provide debugging guides to help you avoid common pitfalls.

1. Setting Up SurrealDB

SurrealDB can run as a single-node or in a distributed cluster. For development, the easiest way is to use the Docker image. First, pull the image and start a container:

``bash docker run --rm -p 8000:8000 surrealdb/surrealdb:latest start ``

This starts SurrealDB with an in-memory store. For persistent storage, mount a volume. Once running, connect using the SurrealQL CLI or any HTTP client. The default endpoint is http://localhost:8000.

To interact via CLI, install the SurrealDB binary and run:

``bash surreal sql --endpoint http://localhost:8000 --namespace test --database test ``

You can also use the REST API. For example, to create a record:

``bash curl -X POST http://localhost:8000/sql -H "Content-Type: text/plain" -d "CREATE user SET name = 'Alice', age = 30" ``

Now you're ready to explore multi-model features.

docker run --rm -p 8000:8000 surrealdb/surrealdb:latest start
surreal sql --endpoint http://localhost:8000 --namespace test --database test

2. Understanding SurrealQL: SQL with Superpowers

SurrealQL is the query language for SurrealDB. It looks like SQL but adds constructs for nested objects and graph traversals. For example, you can create a record with embedded data:

``sql CREATE user SET name = 'Bob', address = { street: '123 Main', city: 'NYC' }; ``

This creates a document with a nested object. You can also define relationships using the RELATE keyword:

``sql RELATE $user->friends->$friend SET since = '2024-01-01'; ``

This creates an edge between two records. The edge itself can have properties (like since).

Querying across relationships is done with arrow syntax:

``sql SELECT ->friends->name FROM user WHERE id = $user; ``

This traverses the graph from a user to their friends and returns the friends' names.

SurrealQL also supports standard SQL operations like WHERE, ORDER BY, GROUP BY, and JOIN (though joins are less common due to graph traversal).

-- Create a user with nested address
CREATE user SET name = 'Bob', address = { street: '123 Main', city: 'NYC' };

-- Create another user
CREATE user SET name = 'Alice';

-- Relate them as friends
LET $bob = (SELECT id FROM user WHERE name = 'Bob');
LET $alice = (SELECT id FROM user WHERE name = 'Alice');
RELATE $bob->friends->$alice SET since = '2024-01-01';

-- Query friends of Bob
SELECT ->friends->name FROM user WHERE name = 'Bob';

3. Multi-Model Data Modeling

SurrealDB allows you to mix models in a single table. For example, a post table can have a flexible schema: some posts are text, others are images with metadata. You can define fields with types, but also allow extra fields:

``sql DEFINE TABLE post SCHEMAFULL; DEFINE FIELD title ON post TYPE string; DEFINE FIELD content ON post TYPE string; DEFINE FIELD metadata ON post TYPE object; ``

With SCHEMAFULL, only defined fields are allowed. Use SCHEMALESS for flexibility. You can also define indexes on fields.

For graph relationships, define edges as separate tables or use the RELATE syntax directly. Edges are stored in a special table named after the relation (e.g., friends). You can query edges like any table:

``sql SELECT * FROM friends WHERE in = $user_id; ``

This gives you all edges where the user is the source.

To model a many-to-many relationship, use edges with properties. For example, a user can have many friends, and each friendship has a since date.

-- Define a schemafull table for posts
DEFINE TABLE post SCHEMAFULL;
DEFINE FIELD title ON post TYPE string;
DEFINE FIELD content ON post TYPE string;
DEFINE FIELD metadata ON post TYPE object;

-- Create a post with metadata
CREATE post SET title = 'My First Post', content = 'Hello', metadata = { image: 'url', tags: ['intro'] };

-- Query posts with specific metadata
SELECT * FROM post WHERE metadata.tags CONTAINS 'intro';

4. Advanced Queries: Combining SQL, Documents, and Graphs

One of SurrealDB's strengths is combining paradigms in a single query. For example, you can fetch a user, their posts (documents), and their friends' posts (graph) in one statement:

``sql SELECT name, (SELECT title, content FROM post WHERE author = $parent.id) AS posts, (SELECT ->friends->post.title FROM user WHERE id = $parent.id) AS friends_posts FROM user WHERE id = $user_id; ``

This uses subqueries with $parent to reference the outer record. The result is a nested JSON structure.

You can also use graph traversals with filters:

``sql SELECT ->friends->(post WHERE created_at > '2024-01-01')->title FROM user WHERE name = 'Bob'; ``

This returns titles of posts created after a date by Bob's friends.

SurrealDB also supports full-text search on string fields:

``sql SELECT * FROM post WHERE content @@ 'search term'; ``

This uses the built-in search index.

-- Get user with their posts and friends' posts
SELECT 
    name,
    (SELECT title, content FROM post WHERE author = $parent.id) AS posts,
    (SELECT ->friends->post.title FROM user WHERE id = $parent.id) AS friends_posts
FROM user WHERE id = 'user:1';

5. Real-Time Subscriptions and Security

SurrealDB supports real-time subscriptions via WebSocket. You can listen for changes on a table or query:

``sql LIVE SELECT * FROM user; ``

This returns a live query ID. The client receives updates when records are created, updated, or deleted. This is useful for building reactive applications.

For security, SurrealDB has a built-in row-level security system. You can define permissions on tables and fields:

``sql DEFINE TABLE user PERMISSIONS FOR select WHERE id = $auth.id; ``

This ensures users can only see their own record. The $auth variable contains the authenticated user's details.

You can also define scopes for authentication and token generation.

-- Define a live query
LIVE SELECT * FROM user;

-- Define row-level security
DEFINE TABLE user PERMISSIONS FOR select WHERE id = $auth.id;

6. Performance Tuning and Indexing

Like any database, indexing is crucial for performance. SurrealDB supports several index types:

• UNIQUE index for unique values
• SEARCH index for full-text search
• BTREE index for range queries

Create an index:

``sql DEFINE INDEX idx_user_name ON user COLUMNS name UNIQUE; ``

For graph traversals, index the fields used in edge queries. For example, if you often query edges by in or out, index those:

``sql DEFINE INDEX idx_friends_in ON friends COLUMNS in; ``

Use EXPLAIN to see the query plan:

``sql EXPLAIN SELECT * FROM user WHERE name = 'Bob'; ``

This shows whether an index is used.

Also consider using FETCH to eagerly load related records and avoid N+1 queries:

``sql SELECT * FROM user FETCH friends; ``

This loads the friends' records in one go.

-- Create a unique index on user name
DEFINE INDEX idx_user_name ON user COLUMNS name UNIQUE;

-- Create an index on friends.in
DEFINE INDEX idx_friends_in ON friends COLUMNS in;

-- Explain a query
EXPLAIN SELECT * FROM user WHERE name = 'Bob';

7. Production Best Practices

Running SurrealDB in production requires careful planning. Here are key practices:

1. Backup regularly: Use BACKUP command or snapshot the data directory.
2. Monitor metrics: SurrealDB exposes Prometheus metrics; integrate with monitoring tools.
3. Use connection pooling: For high concurrency, use a connection pooler like PgBouncer (though SurrealDB uses HTTP/WebSocket, so manage connections wisely).
4. Schema migrations: Use version-controlled migration scripts. Test on staging first.
5. Security: Enable authentication, use HTTPS, and define strict permissions.
6. Scaling: SurrealDB supports clustering; distribute data across nodes for horizontal scaling.

Example backup command:

``sql BACKUP INTO 's3://bucket/backup'; ``

For disaster recovery, have a replica in a different region.

-- Backup to S3
BACKUP INTO 's3://my-bucket/surrealdb-backup';