Point, Don't Join: Modeling Purchases with SurrealDB Record Links and Graph Edges

Every data model hits the same moment: an order needs to point at a customer, a comment needs to point at its author, and in a SQL database that means a foreign key column plus a JOIN you write for the ten-thousandth time. SurrealDB takes a different route. A record can hold a pointer to another record as a plain value, the database resolves those pointers on demand, and relationships that carry their own data become graph edges you can walk in either direction. The takeaway up front: for most join-shaped problems you can store the relationship where the data lives and resolve it in one query — but the model has failure modes worth knowing before you commit.

Record links: the foreign key that lives inside the document

SurrealDB identifies every record with a record ID — a table name plus a key, like person:justina. Any field can store one of these IDs, or an array of them. That field is a record link: a first-class pointer from one document to another, not a string you parse yourself.

Two pieces of SurrealQL do the work of a JOIN:

• TYPE record<person> in a field definition constrains the value to point at the person table — the closest thing to a foreign key constraint.
• FETCH in a SELECT tells the database to replace each record ID with the full record it points to, server-side, in one round trip.

So an order document carries its customer and line items inline, and you never reassemble the object in application code from three separate queries.

Graph edges when the relationship has its own story

Record links are directional and dumb — they just point. When the relationship itself has attributes (a purchase timestamp, a quantity) or you need multi-hop traversal, graph edges fit better. The RELATE statement creates a directed edge record between two records, and the -> and <- operators walk those edges in queries.

A useful rule of thumb: use a record link when the relationship is structural and belongs to the owning document (an order's customer), and an edge when the relationship is a fact in its own right or needs reverse lookups like "who bought this?". Both techniques coexist fine in the same schema.

A worked example: one purchase, two ways to read it

Here is a small model using both techniques. The statements assume SurrealDB 2.x-era SurrealQL; record links and FETCH have been stable since the 1.x line, but the project moves quickly, so check current docs if a flag or clause differs.

Where to run it: install the SurrealDB CLI, start a throwaway in-memory instance from your terminal, and connect with the SQL shell. A root user is required because a fresh instance demands authentication:

# Terminal 1: start a local, in-memory server (default port 8000).
# Data is discarded when the process stops — nothing to clean up.
surreal start -u root -p root memory

# Terminal 2: open a SQL shell against a namespace and database.
# Flags shown for the 2.x CLI; run `surreal sql --help` to confirm.
surreal sql -u root -p root -e ws://localhost:8000 --ns demo --db demo

For anything persistent, pass a storage path to surreal start instead of memory — check surreal start --help for the backends your build supports. In production you would replace the root user with a scoped user limited to this namespace and database.

Define the tables and just enough schema to enforce link targets:

DEFINE TABLE person;
DEFINE TABLE product;
DEFINE TABLE purchase;

DEFINE FIELD customer ON TABLE purchase TYPE record<person>;
DEFINE FIELD items    ON TABLE purchase TYPE array;
DEFINE FIELD items.*  ON TABLE purchase TYPE record<product>;

Create the records, then a purchase that links to them:

CREATE person:justina SET name = 'Justina';
CREATE product:laptop SET name = 'Laptop', price = 1200;
CREATE product:stand  SET name = 'Laptop stand', price = 60;

CREATE purchase:one SET
    customer = person:justina,
    items = [product:laptop, product:stand];

Run SELECT customer, items FROM purchase:one; and you should get record IDs back as values — person:justina and the two product IDs — not full objects. That is the link in its raw form. Now resolve it:

SELECT customer, items FROM purchase:one
    FETCH customer, items;

The same query with FETCH returns the purchase with the customer and each product expanded into full objects, resolved by the server in a single round trip. If IDs still come back unresolved, the usual cause is a typo in the target record — the link points wherever you told it to, whether or not the target exists.

The same domain as a graph, where the purchase event carries its own data:

RELATE person:justina->bought->product:laptop
    SET at = time::now(), quantity = 1;

-- What has Justina bought?
SELECT ->bought->product.{ name, price } FROM person:justina;

-- Who bought the laptop? (the reverse direction)
SELECT <-bought<-person.{ name } FROM product:laptop;

The traversal reads left to right — from the person, across the bought edge, into the product — and the .{ } projection keeps the payload to the fields you asked for. No JOIN keyword, no join table.

Where the model bites

• Payload bloat. FETCH returns whole documents, and chained fetches across several linked tables can ship far more data than the query needed. Project the fields you actually want in the SELECT and keep fetch depth shallow.
• Dangling links. TYPE record<person> validates the target table, but deleting the target record does not cascade to anything pointing at it — historically the engine checks the table, not ongoing existence. If hard referential integrity and cascade deletes are requirements, that is territory where a traditional RDBMS still earns its keep; verify the enforcement behavior documented for your version.
• Traversal cost. Unbounded multi-hop traversals get expensive fast, especially on graphs with cycles. Limit depth and anchor traversals at a specific record where you can.
• Backend matters. Latency for link resolution and traversals differs between in-memory and disk-backed storage, so benchmark on the backend you plan to run, not the one you demoed with.

Start with one relationship

Pick your noisiest JOIN — the one your application reassembles by hand — and model it twice in a throwaway in-memory instance like the one above: once with a record link plus FETCH, once with RELATE and a traversal. Verify both directions: the raw SELECT should show record IDs, the FETCH version should expand them, and the traversal should reach the target through the edge. If the link version reads better for your domain, keep it; if you find yourself wanting attributes on the relationship or reverse queries, the edge is already there. And before anything reaches production, re-check schema enforcement and delete behavior against the docs for the version you are running — that is the part of this model still evolving.