Connect records with relations

A relation is a named link between records; a foreign key can check that the destination exists.

The illustration uses products and suppliers; the schema below uses the same idea for orders and customers.

Define the connectionCopy Anchor Link

1. Register the destination table first, such as shop.customers.
2. Add a column on the source table that holds the destination ID.
3. Add a [[relations]] block naming that connection.

namespace   = "shop"
table       = "customers"
primary_key = ["id"]

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "name"
ty   = "str"

[[columns]]
name = "country"
ty   = "str"

[[columns]]
name = "referred_by"
ty   = "str"        # another customer's id

# A self-relation: customers point at customers.

[[relations]]
name          = "referrer"
from_col      = "referred_by"
target        = { namespace = "shop", table = "customers", pk = "id" }
bidirectional = true

[[indexes]]
name    = "by_id"
columns = ["id"]

namespace   = "shop"
table       = "orders"
primary_key = ["id"]

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "customer"        # <- THIS column is the edge
ty   = "str"

[[columns]]
name = "amount_cents"
ty   = "i64"

[[columns]]
name = "status"
ty   = "str"

[[columns]]
name = "notes"
ty   = "str"

[[columns]]
name = "placed_ms"
ty   = "u64"

# The declaration that turns a plain column into a traversable edge.

[[relations]]
name          = "placed_by"   # the name you pass as ?rel=
from_col      = "customer"    # the column ON THIS TABLE
target        = { namespace = "shop", table = "customers", pk = "id" }
bidirectional = true          # default; enables reverse traversal

[[indexes]]
name    = "by_id"
columns = ["id"]

A separate table containing src and dst columns does not become traversable by itself. The relation declaration is what gives a connection its graph meaning.

Save the row to create its linksCopy Anchor Link

Saving an order maintains the declared links from its customer value. Changing that value moves the link; deleting the row removes its derived links.

# Write an ORDINARY row. There is no edge API and nothing else to call.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "id":           "01JTRX9KQ3YH8K2WMX0F5JZAB7",
    "customer":     "01JTRX1H4Q9P0N2WMX0F5JZ001",
    "amount_cents": 12950,
    "status":       "paid",
    "notes":        "rush delivery",
    "placed_ms":    1714478049000
  }'
# The edge order -> customer now exists. Both directions, because
# placed_by declares bidirectional = true.

If the relation's source value is a JSON array, it produces a link for each value. A missing or null value produces no link. A relation alone does not require the destination row to exist; add a foreign key for that rule.

Check the limitsCopy Anchor Link

• REST graph keys: use single-column string IDs. Integer and composite keys are not addressed by these graph endpoints.
• Cross-namespace paths: declaring a relation is separate from traversing it. Check Cypher or a supported plan for paths outside the REST endpoint's schema scope.
• Reverse links: changing bidirectional does not backfill old rows. Rewrite affected rows when changing this setting.
• Cypher starting points: declare the required by_id index when using an ID-based point match.

Try a traversalCopy Anchor Link

Graph query guide · Prepare for Cypher · Relation fields