Relations

A relation links two entities by a key pair. You declare relations in a schema-level references: list; the loader turns each entry into a named relation that drives two things — the foreign-key DDL the migrator can emit, and the name you pass to include to expand the relation at query time.

Where a relation is declared

references: is a top-level list, a sibling of entities:. It is the only form that produces a relation. There is no relations: key, and there is no per-entity relation list.

entities:
  - name: customer
    fields:
      - name: customer_name
        type: string

  - name: sales_order
    fields:
      - name: order_no
        type: string
        modifiers: [required, unique]
      - name: customer_id
        type: string
        nullable: true
      - name: status
        type: string

  - name: sales_order_line
    fields:
      - name: order_id
        type: string
        modifiers: [required]
      - name: product_id
        type: string
      - name: qty
        type: int

references:
  - name: customer_orders
    parent: customer.id
    child: sales_order.customer_id
    type: onetomany

  - name: order_lines
    parent: sales_order.id
    child: sales_order_line.order_id
    type: onetomany

references[] keys

Key	Type	Meaning
name	string	Relation name. Also the FK constraint name and the include key.
description	string	Free text.
parent	entity.field	The referenced (“one”) side. A bare entity defaults the field to id.
child	entity.field	The FK-holding (“many”) side. Same bare-entity rule.
from	entity.field	Alias for parent, used only when parent is empty.
to	entity.field	Alias for child, used only when child is empty.
fromentity / fromfield / toentity / tofield	string	Explicit quadruple. Setting fromentity or toentity overrides parent/child entirely and is passed through with no direction flip.
type	string	Cardinality. See below.
join	string	Junction entity for manytomany. Stored and never read.
onDelete	string	Referential action, emitted as ON DELETE <value> upper-cased.
onUpdate	string	Parsed and discarded. No ON UPDATE clause is ever emitted.

Omitting name synthesises <from-entity>_<to-entity> after the multi-file merge. Unnamed references are never collapsed onto each other during merge, so two unnamed entries both survive.

Cardinality and direction

type: is normalised by trimming, lower-casing and stripping hyphens, so one-to-many, OneToMany and onetomany are the same token.

type:	Aliases	Declaring side (from)	Target side (to)	Query-time shape
onetomany	hasmany	parent	child	has-many
onetoone	—	parent	child	has-one when from field is the declaring entity’s primary key, otherwise belongs-to
manytoone	belongsto	child (flipped)	parent	belongs-to
manytomany	—	parent	child	belongs-to key mapping, attached as an array
omitted / unrecognised	—	parent	child	has-many

Caution

Normalisation strips hyphens only — not underscores, not stray characters — and an unrecognised type: silently becomes onetomany instead of failing the load. belongs_to and many_to_one therefore load as one-to-many, and include returns an array where a single object was intended. With the fromentity: / toentity: spelling the same typo also flips which table receives the foreign key, because the FK generator swaps from and to for onetomany and onetoone only.

Modelling patterns

One-to-one

Split a wide entity, or hold sensitive columns in a separate table. Put the key on the dependent side and point parent at the owner’s primary key.

references:
  - name: employee_salary
    parent: employee.id
    child: salary_breakup.employee_id
    type: onetoone

Nothing enforces the “one” part. A one-to-one relation emits the same single-column foreign key as a one-to-many; add a unique index on the child key to make the cardinality real.

entities:
  - name: salary_breakup
    indexes:
      - name: salary_breakup_employee_unique
        fields: [employee_id]
        unique: true

One-to-many

The common case. The child holds the key; the parent side is the primary key.

references:
  - name: customer_orders
    parent: customer.id
    child: sales_order.customer_id
    type: onetomany
    onDelete: RESTRICT

Many-to-many

Model it explicitly with a bridge entity and two one-to-many references. The bridge is a normal entity, so it can carry its own columns, indexes, access rules and history.

entities:
  - name: student
    fields:
      - name: full_name
        type: string

  - name: course
    fields:
      - name: title
        type: string

  - name: enrolment
    fields:
      - name: student_id
        type: string
        modifiers: [required]
      - name: course_id
        type: string
        modifiers: [required]
      - name: enrolled_on
        type: date
    indexes:
      - name: enrolment_pair_unique
        fields: [student_id, course_id]
        unique: true

references:
  - name: student_enrolments
    parent: student.id
    child: enrolment.student_id
    type: onetomany
  - name: course_enrolments
    parent: course.id
    child: enrolment.course_id
    type: onetomany

Caution

type: manytomany with join: does not work. The join entity is stored and read by nothing: no junction table is created, the include executor handles only belongs-to, has-one and has-many, and the Postgres FK generator applies no direction swap for manytomany — it emits a direct foreign key between the two named columns. Use the bridge-entity form above.

Self-referencing

An entity may reference itself; the key column is just another field on the same table.

entities:
  - name: employee
    fields:
      - name: manager_id
        type: string
        nullable: true

references:
  - name: employee_manager
    parent: employee.id
    child: employee.manager_id
    type: onetomany

include expands one level; it does not recurse down a self-reference. Walking the tree uses the query layer’s traversal form, which compiles to WITH RECURSIVE and takes the self-referencing column as via (direction ancestors or descendants, with $depth and $path selectable out of the CTE). Traversal is a library-level query form — the REST query string has no parameter for it. Recursive CTEs are available on PostgreSQL, DuckDB and SQLite; ClickHouse does not support them.

The field-level references: shorthand

A field may carry an inline foreign-key block. This is the loader’s spelling — an object, not a string:

fields:
  - name: customer_id
    type: string
    references:
      entity: customer
      field: id
      onDelete: CASCADE
      onUpdate: NO ACTION

Key	Meaning
entity	Target entity name.
field	Target column.
onDelete	One of CASCADE, SET NULL (or SETNULL), RESTRICT; anything else parses as no action.
onUpdate	Same vocabulary.

Caution

The field-level block records the intent and nothing more. In the shipping loader it is never promoted into a relation, no dialect emits an inline REFERENCES clause from it, and it is invisible to include. A relation exists only if you declare it under the top-level references: list. Treat the field-level form as documentation.

Divergence

A second, older YAML front-end still exists — the one the bundled example schemas are written for — and it spells this key as a string: references: "customer.id", alongside default:, enumref:, entity-level traits: (the loader spells trait inheritance inherits:), terse type strings such as "uuid -> users.id", and a top-level relations: list. None of that binds in the deployable service. The decoder does not reject unknown keys, so a copied snippet loads cleanly with the relation simply absent. Key matching is exact and case-sensitive — onDelete and onUpdate are camelCase (as are the beforeCreate / afterCreate hook keys), and ondelete: binds to nothing.

Referential integrity per backend

Foreign-key DDL is produced by the backend dialect, and only one backend produces any.

Backend	ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY	Notes
PostgreSQL	Yes	Schema-qualified on both sides; ON DELETE appended when set.
DuckDB	No	The dialect refuses; adding a constraint to an existing table is not supported.
SQLite	No	The engine would need the FK inline in CREATE TABLE; it emits none, and the dialect refuses at migrate time.
ClickHouse	No	No foreign-key constraints in the engine.

data.svc wires PostgreSQL for tenant datastores, so PostgreSQL is the case that matters in a deployment.

The emitted statement for customer_orders above:

ALTER TABLE "acme"."sales_order"
  ADD CONSTRAINT "customer_orders"
  FOREIGN KEY ("customer_id") REFERENCES "acme"."customer" ("id")
  ON DELETE RESTRICT;

The constraint takes the relation’s name. Unnamed relations fall back to fk_<table>_<column>_<target>.

Caution

The service’s migration pipeline never creates foreign keys. FK DDL is emitted only by the library’s direct migrate sweep, used for embedded and test databases. The diff-based pipeline that provisions every service-managed tenant — data bootstrap, and data plan generate plus data plan apply — has no foreign-key operation, and CREATE TABLE carries no inline REFERENCES. A tenant database provisioned by data.svc therefore has no declared FK constraints: referential integrity is not enforced by the database, and onDelete has no effect there. Relations still resolve for include, because traversal reads the schema, not the catalog.

Ownership and cascade behaviour

Declaration	Effect
onDelete: CASCADE / RESTRICT / SET NULL / NO ACTION	Appended verbatim, upper-cased, to the FK DDL — on the path that emits FK DDL.
onUpdate: …	Discarded. No ON UPDATE clause is generated on any backend.
retention.onfkconstraint	fail (default), cascade or anonymize_refs — what the retention runner does when a purge would violate a foreign key. Any other value is a load error.

onDelete is not validated: whatever you write is upper-cased and pasted into the DDL, so onDelete: casacde produces a statement the database rejects and the FK step fails. Deleting a parent row through the API does not run a cascade in application code — cascade means the database clause, on the backend and migration path that emit it.

Soft delete changes what a cascade would even see. Every entity carries deletedon/deletedby by default, so a delete is an UPDATE, not a DELETE, and no FK action fires at all.

Traversing a relation at query time

include expands a relation by name. Resolution runs in this order for entity E and name R:

1. Entity-level relations named R.
2. A schema relation named R whose from entity is E.
3. A schema relation keyed E.R.
4. Any schema relation with from entity E and to entity R — the target-entity shorthand, so include=customer works without knowing the relation name.
5. Reverse direction: any relation whose to entity is E and whose name — or whose from entity name — is R. A belongs-to is synthesised with the keys swapped, so a child can ask for its parent.

An unresolved name fails the query with unknown relation "<name>" on "<entity>".

Over HTTP

The REST list and get endpoints take ?include= as a comma-separated list of relation names. The query string carries names only — per-relation projection, filter, ordering and limit are available to in-process callers, not over HTTP. The full endpoint reference is at /api/data/.

GET /rest/sales_order?status=approved&include=order_lines&orderby=-createdon&limit=25
GET /rest/sales_order/id/01J8Z0F5K7QN3W?include=order_lines
GET /rest/sales_order_line?include=order_lines

The third request is the reverse direction: sales_order_line resolves order_lines through rule 5 and gets its parent order back.

Result shape

Included rows attach to each parent row under the relation name. A has-many relation yields an array — empty, never absent. A belongs-to or has-one relation yields a single object, or null when the key does not match.

{
  "id": "01J8Z0F5K7QN3W",
  "order_no": "SO-1042",
  "customer_id": "01J8Z0EW0000AA",
  "status": "approved",
  "order_lines": [
    { "id": "01J8Z0FA0001", "order_id": "01J8Z0F5K7QN3W", "product_id": "01J8Z0P1", "qty": 3 },
    { "id": "01J8Z0FA0002", "order_id": "01J8Z0F5K7QN3W", "product_id": "01J8Z0P7", "qty": 1 }
  ]
}

How the expansion executes

An include is not a join. Each relation at each level compiles to its own statement, run after the parent query and filtered by child_key IN (<parent key values>), then grouped back onto the parent rows in memory. Nested includes recurse over the child rows the same way.

Direction determines the key pair:

Query-time shape	Parent key	Child key
has-many, has-one	the parent’s local key (its primary key)	the child’s foreign key
belongs-to	the foreign key on the queried row	the target’s key

Caution

Three consequences of the separate-query model:

• Fan-out is unbounded. There is no ceiling on include nesting or on the number of relations per level. n relations across d levels is n × d extra statements.
• A per-include limit is global. It caps the whole child result set, not rows per parent, so a limit of 5 across 100 parents leaves most parents empty.
• Depth controls are inert. Per-include depth and the request-level depth option parse and are then ignored; only explicit nesting limits how deep the expansion goes.

Note

Asking for an include widens your projection. When you supply an explicit select alongside an include, the parent primary key is prepended to the parent projection and the child foreign key to each child projection, so the rows can be stitched. Consumers pinning an exact key set will see extra fields.

Filtering across a relation

Dot paths in a filter do not join. user.country = "US" compiles to a single quoted identifier, "user.country", which is not a column. Filter on the local foreign-key column, or expand the relation and filter the child query in process.

Relations across layers and planes

Situation	Behaviour
Same relation name in two schema files	Last file wins; the walk is lexical and recursive, so a file rename can change which definition survives.
Two unnamed references	Both survive — unnamed entries are never merged.
A product or tenant layer adds a relation	Additive. A name collision keeps the base layer’s relation and logs a diagnostic; folding never fails the load.
The two entities sit in different scope: planes (tenant, shared, superadmin)	The relation is dropped. A relation survives plane subsetting only when both of its entities are in the same plane.
An entity marked final: true	Sealed against any layer contribution, relations included.