Eliminating JOIN Boilerplate with SurrealDB Record IDs

The Cost of Relational Glue

In traditional relational databases, connecting two pieces of data requires a JOIN. While powerful, JOINs introduce significant boilerplate and cognitive load. You must manage foreign keys, ensure indexes are aligned, and write increasingly complex queries as your data depth grows. When you need a user's profile, their recent posts, and the authors of the comments on those posts, the SQL query quickly becomes a wall of text that is difficult to maintain.

SurrealDB solves this by treating Record IDs as first-class pointers. Instead of storing a numeric ID that requires a lookup table, SurrealDB uses a table:id format that acts as a direct reference to another object. This shifts the paradigm from "searching for related rows" to "following a pointer," effectively blending document-store flexibility with graph-database traversal.

Direct Referencing via Record IDs

A Record ID in SurrealDB is not just a primary key; it is a URI for the data. When you store a reference to another record, you aren't storing a value that needs to be matched against another column; you are storing the address of the target record.

This allows for dot-notation traversal. If a post record has a field author that contains the Record ID user:jane, you can access the author's name directly using SELECT author.name FROM post. The database engine resolves the pointer internally, removing the need for an explicit JOIN clause.

Graph Relations with the RELATE Statement

While direct pointers are useful for simple ownership, complex relationships (like "likes," "follows," or "purchased") often require their own metadata. SurrealDB handles this through graph edges using the RELATE statement.

An edge is a record that connects two other records. Unlike a join table in SQL, which is often a hidden implementation detail, a SurrealDB edge is a tangible entity that can hold its own properties, such as the timestamp of a relationship or the strength of a connection.

Practical Example: Modeling a Social Interaction

Consider a scenario where users follow each other and post content. We want to retrieve a post and the full details of the user who wrote it in one go.

1. Setup Data

Run these commands in the SurrealDB CLI or via an HTTP request (requires root or namespace permissions):

-- Create a user
CREATE user:jane SET name = 'Jane Doe', email = '[contact removed]';

-- Create a post linked to that user
CREATE post:first_post SET title = 'Hello SurrealDB', author = user:jane;

-- Create a graph relationship (Jane follows Bob)
CREATE user:bob SET name = 'Bob Smith';
RELATE user:jane -> follows -> user:bob SET since = '2023-01-01';

2. Retrieving Data without JOINs

To get the post and automatically expand the author pointer into a full object, use the FETCH clause:

SELECT * FROM post:first_post FETCH author;

Expected Result: Instead of returning "author": "user:jane", the response will contain the full user object "author": { "id": "user:jane", "name": "Jane Doe", ... }.

Trade-offs and Limitations

While Record IDs simplify queries, they introduce specific engineering considerations:

• Fetch Depth: Using FETCH on deeply nested relationships (e.g., FETCH author, author.profile, author.profile.settings) can increase memory consumption and latency. It is a form of eager loading; overusing it on large datasets can lead to "over-fetching."
• Schema Modes: In SCHEMALESS mode, you can put any value in a field. In SCHEMAFULL mode, you can enforce that a field must be a record type, ensuring that the pointer always leads to a valid table.
• Dangling Pointers: If you delete a record that is referenced by others, those references remain as IDs but will return NULL when fetched. You must manage cascading deletes manually or via application logic.

Verification Checklist

To verify this behavior in your own environment:

1. Deploy SurrealDB via Docker: docker run --rm -p 8000:8000 surrealdb/surrealdb start.
2. Create two records in different tables.
3. Assign the Record ID of the first to a field in the second.
4. Execute a SELECT with FETCH and confirm the nested object is returned rather than a string ID.