Relations

A relation links two entities by a key pair. You declare relations in a schema-level references: list, or as a shorthand on the referencing field; the loader turns each into a named reference that drives three things — the integrity rule the service enforces on every write and delete, the foreign-key constraint the migrator creates when the reference asks for one, 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:. A field may also carry the shorthand, which the loader promotes into the same list. There is no relations: key.

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
    onDelete: restrict

  - name: order_lines
    parent: sales_order.id
    child: sales_order_line.order_id
    type: onetomany
    onDelete: cascade
    create: true

references[] keys

Key	Type	Meaning
name	string	Reference name. Also the constraint name and the include key. Must be unique across the schema.
description	string	Free text.
parent	entity.field or {entity, fields: [...]}	The referenced (“one”) side. A bare entity defaults the field to id. Must be the parent’s primary key, a unique field, or — for the map form with several fields — a unique index over exactly those columns.
child	entity.field or {entity, fields: [...]}	The side that holds the key (“many”). Same bare-entity rule; the map form pairs its fields with the parent’s, in order.
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.
type	string	Cardinality. See below.
join	string or object	Junction entity for manytomany: a bare entity name, or {entity, parentfield, childfield}.
onDelete	string	What happens to child rows when a parent is deleted: noaction (default), restrict, cascade, setnull. Any spelling — SET NULL, set_null, SetNull — is accepted; anything else fails the load.
onUpdate	string	Same vocabulary. Carried onto the database constraint when one is created.
create	bool	false (default): the reference is virtual — the service enforces it, no database constraint exists. true: the migration also creates a foreign-key constraint, or fails where the backend cannot.
deferrable	bool	With create: true: the constraint is DEFERRABLE INITIALLY DEFERRED, checked at commit rather than per statement, so a transaction may write a child before its parent. Needs create: true; PostgreSQL and SQLite honour it, DuckDB refuses the migration.

Omitting name synthesises <declaring entity>_<target entity> after the multi-file merge. The shorthand on a field is named <entity>_<field>_<target entity>, and the two references a manytomany expands to are named <join entity>_<join field>_<target entity>. All are built from entity names, so a table: rename never changes a constraint name.

Cardinality and direction

type: is normalised by trimming, lower-casing and stripping hyphens, so one-to-many, OneToMany and onetomany are the same token. Which end you declare is not a semantic: every reference is normalised to child column → parent column and enforced as that.

type:	Aliases	Meaning	Query-time shape
onetomany	hasmany	Many children point at one parent; declared from the parent’s side	has-many
manytoone	belongsto	The same reference, declared from the child’s side	belongs-to
onetoone	—	One child per parent. Enforced as the same child → parent reference; add a unique index on the child column to make the “one” real	has-one when the declaring field is its entity’s primary key, otherwise belongs-to
manytomany	—	Through a join entity. Expanded at load into two manytoone references on the join entity	belongs-to key mapping, attached as an array
omitted / unrecognised	—	Treated as onetomany	has-many

Composite references

A reference may span several columns when the parent side is a unique index over exactly those columns. Both sides use the map form and list their fields in the same order:

entities:
  - name: sales_order
    fields:
      - { name: id, type: ulid }
      - { name: company, type: string }
      - { name: order_no, type: int }
    indexes:
      - { name: ux_order, fields: [company, order_no], unique: true }
  - name: order_line
    fields:
      - { name: id, type: ulid }
      - { name: company, type: string, nullable: true }
      - { name: order_no, type: int, nullable: true }
references:
  - name: order_lines
    parent: { entity: sales_order, fields: [company, order_no] }
    child: { entity: order_line, fields: [company, order_no] }
    type: onetomany
    onDelete: setnull

The service enforces it as a whole: a child row with every column set must match one parent on all of them, and a row with any column null holds no reference (the database’s MATCH SIMPLE). A missing parent reports field: "company,order_no" and the tuple in the message; setnull clears every column and requires each to be nullable; cascade and restrict match children on the tuple. With create: true the constraint lists every column on both sides. The loader refuses a column count that differs between the sides and a parent column set that is not a unique index.

include does not traverse a composite reference: asking for one is refused with the columns named, rather than joined on the first column alone.

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. For those two the declared sides are the same columns either way, so the enforced reference is unaffected; what changes is the include shape, which returns an array where a single object was intended.

What the service enforces

Every write and delete goes through the engine, so a reference is a rule, not a hint. This holds whether or not a database constraint exists; the constraint, when asked for, is the backup for writes that bypass the service.

Writes (create, update, upsert, bulk). For each reference whose child column the payload sets to a non-null value, the parent row must exist. A null child value passes — a nullable reference is optional by definition. A missing parent is a validation failure:

{
  "errors": [{
    "code": "create_failed",
    "message": "customer.id 01J8Z0EW0000AA does not exist",
    "details": {
      "code": "reference_missing", "field": "customer_id",
      "failures": [{ "field": "customer_id", "code": "reference_missing", "message": "customer.id 01J8Z0EW0000AA does not exist" }]
    }
  }]
}

422, with one entry under details.failures[] per bad reference in the payload. The existence check runs in the same transaction as the write and reads the parent as the service, not as the caller: integrity is a property of the data, so an order line for an order the caller cannot read is still a line for a real order. The only thing the check can reveal is that a key the caller already supplied exists.

Deletes of a parent, soft or hard. Before the delete runs, the service resolves the parent rows it matches and applies each inbound reference’s onDelete, in the delete’s own transaction:

onDelete	Effect
noaction, restrict	If any child row references a matched parent, the delete is refused: 409, details.code: reference_restrict, with details.reference, details.parent_entity, details.child_entity and details.count. The database distinction between the two — deferred versus immediate — does not exist at this level; both refuse.
cascade	The child rows are deleted the same way the parent is — a soft delete cascades as soft deletes, ?purge=true as purges — recursively through the children’s own inbound references.
setnull	The child column is set to null on the child rows. The loader refuses setnull on a column that is not nullable.

There is no per-request switch to skip the checks (the old deletechildren flag is gone). A caller that needs to load data with dangling references is loading it in the wrong order.

Load-time validation. The loader refuses a schema whose references cannot be honoured: an unknown parent or child entity or field; a parent field that is neither the primary key nor unique; a child column whose type cannot hold the parent’s (string kinds with string kinds, integer kinds with integer kinds); setnull on a non-nullable column; manytomany without a join, or a join entity missing its columns; create: true towards a remote entity or across datastores; the same child column declared twice; and a cascade cycle through two or more entities. A self-referencing cascade — a tree — is allowed.

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. The service enforces the reference; the “one” part is a unique index on the child column.

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

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

Declare the join entity as a normal entity — it can carry its own columns, indexes, access rules and history — and name it in join:. The loader expands the reference into two manytoone references on the join entity, so a join row must point at existing rows on both sides, and deleting either end applies the reference’s onDelete to the join rows.

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_courses
    parent: student.id
    child: course.id
    type: manytomany
    onDelete: cascade
    join: enrolment            # join columns default to student_id and course_id

Name the join columns when they do not follow <entity>_id:

    join:
      entity: enrolment
      parentfield: student_ref
      childfield: course_ref

Two explicit onetomany references on the bridge entity are the same thing spelled out, and a join column that already has its own reference (explicit, or the field shorthand) is kept as declared.

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
    onDelete: setnull

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 the reference inline — an object, not a string:

fields:
  - name: customer_id
    type: string
    nullable: true
    references:
      entity: customer
      field: id
      onDelete: restrict
      create: true

Key	Meaning
entity	Target entity name.
field	Target column; defaults to the target’s primary key.
onDelete, onUpdate	Same vocabulary as the schema-level form.
create	Ask for a database constraint.
deferrable	With create: DEFERRABLE INITIALLY DEFERRED.

The loader promotes the shorthand into a schema-level manytoone reference named <entity>_<field>_<target entity>, so there is one model downstream: it is enforced, it can carry a constraint, and include resolves it. Declaring the same child column both on the field and under references: is a load error.

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.

The database constraint

A reference is virtual by default: no constraint on any backend, and the service enforces it. create: true asks for a constraint as well. The migration then creates it where the backend can, and fails — an error, not a tolerated partial step — where it cannot:

Backend	With create: true	Notes
PostgreSQL	ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY … REFERENCES … ON DELETE … [ON UPDATE …] [DEFERRABLE INITIALLY DEFERRED]	Schema-qualified on both sides; idempotent on re-run. deferrable: true is honoured.
SQLite	FOREIGN KEY (…) REFERENCES … ON DELETE … [DEFERRABLE INITIALLY DEFERRED] inline in CREATE TABLE	A table that already exists without it cannot gain one: the migration refuses, naming the table. Recreate it, or set create: false. deferrable: true is honoured.
DuckDB	Inline in CREATE TABLE, as SQLite	Same limit on existing tables. deferrable: true fails the table’s DDL with the reference named.
ClickHouse	—	No foreign keys in the engine; the migration refuses.

The emitted statement for order_lines above, on PostgreSQL:

ALTER TABLE "acme"."sales_order_line"
  ADD CONSTRAINT "order_lines"
  FOREIGN KEY ("order_id") REFERENCES "acme"."sales_order" ("id")
  ON DELETE CASCADE;

The constraint takes the reference’s name. data bootstrap and data plan generate / data plan apply emit an add_foreign_key step for every create: true reference, after every table step. Constraints already present on a provisioned database are left alone — migration never drops a constraint — and a live constraint with no create: true behind it is reported as a warning in the plan so a schema author can decide. A live constraint whose deferrability differs from the declaration is likewise a warning, not a change: drop and recreate it to switch.

A constraint violation that reaches the service anyway — a write that bypassed it, or a race the transaction did not cover — is the database’s own refusal: 409 with the constraint name in details.constraint.

Ownership and cascade behaviour

Declaration	Effect
onDelete: restrict / noaction / cascade / setnull	Enforced by the service on every delete through the API, as described above; mirrored onto the constraint when create: true.
onUpdate: …	Carried onto the constraint. At the application level a parent key does not change through the API — a primary key is final — so there is nothing further to enforce.
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.

Soft delete is the default, and a soft delete is an UPDATE the database’s constraint never sees. That is why the service applies onDelete itself: deleting a customer with orders is refused with reference_restrict, and a cascade soft-deletes the children with the parent.

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>". A virtual reference includes exactly as one with a constraint does.

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 /blocks/baas/data-api/api/.

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 folded schema is validated as a whole, so a layer’s reference may point at an entity another layer declares.
The two entities sit in different scope: planes (tenant, product, customer)	The relation is dropped. A relation survives plane subsetting only when both of its entities are in the same plane.
The two entities sit on different datastores	Enforced as a check — the lookup crosses stores, the transaction cannot — and create: true is refused.
An entity marked final: true	Sealed against any layer contribution, relations included.