Replacing JOINs with SurrealDB Record Links: A Practical Guide

Why SurrealDB’s Record Links Matter for Modern Apps

In many relational systems, a JOIN is the go-to tool for pulling related data from two tables. SurrealDB flips that paradigm by treating relationships as first‑class citizens. Instead of a foreign key column that the engine must cross‑reference, you store a record link that points directly to another record. When you query, the database can follow that pointer in a single pass, eliminating the need for expensive join operations.

Core Concepts in a Nutshell

• Record Link: A value that references another record’s ID (e.g., user:123).
• FETCH Clause: Tells SurrealDB to dereference links and return the full record instead of just the ID.
• Graph Operators (-> and <-): Enable traversal of links in a direction‑aware way.
• Multi‑model Flexibility: The same record can act as a document, a graph edge, or a relational row.

Concrete Example: Building a Social Graph

Suppose you need a simple social network where users can follow each other. In SQL you would create a followers table and join it to users on every query. With SurrealDB you can model this as a single table and use links.

1. Create the user records:

   CREATE user SET name = "Alice", age = 30;
   CREATE user SET name = "Bob",   age = 25;
   CREATE user SET name = "Carol", age = 28;
   

   Assume SurrealDB returns IDs user:1, user:2, and user:3 respectively.

2. Add follow relationships using RELATE:

   RELATE user:1 -> follows -> user:2;
   RELATE user:2 -> follows -> user:3;
   

   The follows link is a record link stored on the source record.

3. Query with FETCH to avoid JOINs:

   SELECT name, follows FETCH follows FROM user:1;
   

   The result looks like:

   {
     "name": "Alice",
     "follows": [{
       "id": "user:2",
       "name": "Bob",
       "age": 25
     }]
   }
   

4. Deep traversal – friend‑of‑a‑friend:

   SELECT name, follows -> follows FETCH follows -> follows FROM user:1 LIMIT 1;
   

   This pulls two levels down the follow graph in one request. The -> operator walks the link; the second -> continues the traversal.

Trade‑offs and Limitations

• Over‑fetching Risk: Fetching many levels or entire subgraphs can consume large amounts of memory and increase latency. Use LIMIT or field selection to constrain the result.
• Orphaned Links: Unlike SQL foreign keys, SurrealDB does not automatically cascade deletes. If a target record is removed, its incoming links become dangling unless you clean them manually or use the ON DELETE CASCADE option (if supported in your version).
• Performance Variability: In‑memory storage offers lightning‑fast traversal, while disk‑backed engines may see higher latency for deep traversals. Benchmark your workload to choose the right persistence layer.

How to Verify the Behavior Yourself

Below is a minimal test script you can run in a local SurrealDB instance. Replace $HOST with your server address and $NS / $DB with your namespace/database names.

# 1. Connect via the REPL
surreal start --log debug

# 2. Create namespace and database
CREATE namespace $NS;
USE $NS:$DB;

# 3. Insert sample data and relate as shown above
...

# 4. Measure performance of a FETCH query vs. two separate SELECTs
SELECT name, follows FETCH follows FROM user:1;
SELECT name FROM user:1;
SELECT name FROM user:2;

Use the --log flag to capture execution time. Compare the timestamps to see the benefit of a single FETCH call.

Actionable Takeaway

If your application frequently needs to pull related documents, consider modeling those relationships as record links and leveraging FETCH and graph operators. Start by refactoring one simple join into a link, run the test script above, and measure the difference. Keep an eye on over‑fetching and orphaned links, and adjust your queries accordingly. In the long run, SurrealDB’s link‑based model can reduce round‑trips, simplify schema evolution, and enable richer graph queries without the overhead of traditional JOINs.