Skip to content

Loading Relations ​

Navigation properties are not populated by default. They are only loaded when you explicitly request them via $with in your query controls. This design is intentional — it avoids the N+1 problem common in ORMs with lazy loading and gives you full control over which relations are fetched and how.

Throughout this page, we use the following schema as a running example:

atscript
@db.table 'users'
export interface User {
    @meta.id
    @db.default.increment
    id: number
    name: string
}

@db.table 'projects'
export interface Project {
    @meta.id
    @db.default.increment
    id: number
    name: string

    @db.rel.FK
    ownerId: User.id

    @db.rel.to
    owner: User

    @db.rel.from
    tasks: Task[]
}

@db.table 'tasks'
export interface Task {
    @meta.id
    @db.default.increment
    id: number
    title: string
    done: boolean

    @db.rel.FK
    projectId: Project.id

    @db.rel.FK 'assignee'
    assigneeId?: User.id

    @db.rel.to
    project: Project

    @db.rel.to 'assignee'
    assignee?: User

    @db.rel.via TaskTag
    tags: Tag[]
}

(Plus the supporting Tag and TaskTag junction types from the Navigation Properties page.)

Explicit Loading with $with ​

To load a navigation property, include it in the $with array inside controls:

typescript
const tasks = await taskTable.findMany({
  controls: {
    $with: [{ name: "project" }],
  },
});
// tasks[0].project → { id: 1, name: 'Website Redesign', ownerId: 3 }

Without $with, navigation properties are undefined on returned objects — they simply don't exist:

typescript
const tasks = await taskTable.findMany();
// tasks[0].project === undefined

This applies to all query methods that accept controls: findMany, findOne, and findById:

typescript
const task = await taskTable.findOne({
  filter: { id: 42 },
  controls: { $with: [{ name: "project" }] },
});

const task = await taskTable.findById(42, {
  controls: { $with: [{ name: "project" }] },
});

Loading Multiple Relations ​

Pass multiple entries in the $with array to load several relations at once:

typescript
const tasks = await taskTable.findMany({
  controls: {
    $with: [{ name: "project" }, { name: "assignee" }, { name: "tags" }],
  },
});
// tasks[0].project   → Project object
// tasks[0].assignee  → User object or null (FK is optional)
// tasks[0].tags      → Tag[] (may be empty)

All relations in a single $with array are loaded in parallel — the queries for project, assignee, and tags run concurrently, not sequentially. The strategy is adapter-specific: SQL adapters issue a separate batched follow-up query per relation (no JOINs emitted by the loader); MongoDB executes a single $lookup aggregation pipeline per relation. See How It Works Internally below.

Nested (Deep) Loading ​

Load relations of relations by nesting $with controls inside a relation entry:

typescript
const projects = await projectTable.findMany({
  controls: {
    $with: [
      { name: "owner" },
      {
        name: "tasks",
        controls: {
          $with: [{ name: "assignee" }, { name: "tags" }],
        },
      },
    ],
  },
});
// projects[0].owner               → User
// projects[0].tasks[0].assignee   → User | null
// projects[0].tasks[0].tags       → Tag[]

Dotted paths ​

A dotted name is the same nesting written short (since 0.1.165; before, it was silently skipped): "tasks.assignee" reads as tasks with $with: [{ name: "assignee" }], at any depth. The entry's own filter / controls apply at the last level. Entries naming the same relation merge into one (the first entry's filter and controls win; nested $with lists are joined), and the string shorthand works for plain names too:

typescript
await projectTable.findMany({
  controls: { $with: ["owner", { name: "tasks", filter: { done: false } }, "tasks.assignee"] },
});
// = owner, and tasks (done = false) with their assignee

There is no hard limit on nesting depth for reads. Each level of nesting adds one or more batched queries, so keep depth reasonable for performance. (Nested writes are a separate concern — see Deep Operations § Depth Control.)

Per-Relation Controls ​

Each $with entry accepts its own filter and controls, next to name. You can sort, filter, paginate, and project on loaded relations independently of the parent query:

typescript
const projects = await projectTable.findMany({
  controls: {
    $with: [
      {
        name: "tasks",
        filter: { done: false },
        controls: {
          $sort: { done: 1, title: 1 },
          $limit: 10,
          $with: [{ name: "tags" }],
        },
      },
    ],
  },
});

The filter goes on the entry, not inside controls — there is no $filter control. Over HTTP the same entry is $with=tasks(done=false&$sort=done,title&$limit=10&$with=tags).

This loads each project's tasks sorted by done then title, limited to 10 incomplete tasks, each with their tags attached.

$sort, $skip and $limit apply to the related rows of each parent row (since 0.1.147 on every adapter): above, every project gets up to 10 of its own tasks, not 10 tasks shared among all projects.

  • FROM / VIA — each parent row's related rows are sorted, then paged. On a VIA relation $sort orders the related rows themselves; without $sort they come in junction order.
  • TO — a parent row has at most one related row, so $limit: 0 or a $skip of 1 or more loads null.
  • Engines — on SQLite, PostgreSQL and MySQL a paged TO / FROM relation is read with a ROW_NUMBER() window in one statement, which needs SQLite ≥ 3.25 and MySQL ≥ 8.0 (MariaDB ≥ 10.2). A paged VIA relation reads the related rows of the parent rows' junction entries and pages them in memory.

Before 0.1.147, on every adapter but MongoDB, $skip / $limit paged the related rows of all parent rows together ($limit: 1 gave one parent a row and the others none), and a VIA relation ignored $sort.

TIP

Per-relation controls are especially useful for FROM and VIA relations where you want to limit the number of loaded children. For example, loading only the 5 most recent comments on each post, or only active tags on a task.

Field Selection on Relations ​

Use $select within relation controls to load only specific fields from related records:

typescript
const tasks = await taskTable.findMany({
  controls: {
    $with: [
      {
        name: "project",
        controls: { $select: ["name"] },
      },
    ],
  },
});
// tasks[0].project → { id: 1, name: 'Website Redesign' }

INFO

Primary key and foreign key fields used for joining are always included in the query, even if not listed in $select. They are needed internally to match related records to their parents. In the example above, id appears in the result even though only name was selected.

The relation's $select takes every projection form — an array, an inclusion map ({ name: 1 }) or an exclusion map ({ secret: 0 }); an exclusion never drops a join key (since 0.1.143 on every adapter).

The parent's own $select does not have to list the key a relation joins on either (since 0.1.143): the key — a TO relation's foreign key, the primary key a FROM / VIA relation is looked up by — is read for the join and removed from the returned rows again.

typescript
const tasks = await taskTable.findMany({
  controls: { $select: ["title"], $with: [{ name: "project" }] },
});
// tasks[0] → { title: '…', project: { id: 1, name: 'Website Redesign', … } } — no projectId

Filtering Loaded Relations ​

Use filter on a $with entry to restrict which related records are returned:

typescript
const projects = await projectTable.findMany({
  controls: {
    $with: [
      {
        name: "tasks",
        filter: { done: false },
      },
    ],
  },
});
// projects[0].tasks → only incomplete tasks

This is a query-time filter applied when loading the relation. It is different from @db.rel.filter, which is a permanent filter baked into the schema definition. Both can be active at the same time — the schema filter and the query-time filter are combined with $and (the schema filter is applied on every adapter since 0.1.147).

A $with filter narrows the loaded children — every project above is still returned. To narrow the parents by their related rows, use a relational predicate (since 0.1.147) in the parent's own filter:

typescript
// Only projects that have an unfinished task — tasks are not loaded
const busy = await projectTable.findMany({
  filter: { tasks: { $some: { done: false } } },
});

The two are independent, so combine them to get the matching parents together with the matching children:

typescript
const busy = await projectTable.findMany({
  filter: { tasks: { $some: { done: false } } }, // which projects
  controls: { $with: [{ name: "tasks", filter: { done: false } }] }, // which tasks come back
});
// every project has at least one task in busy[i].tasks
You wantUse
fewer related rows on each returned row$with entry filter
fewer returned rows, by their related rows{ nav: { $some: … } } / { nav: { $none: … } }
bothboth — they do not affect each other

A $with filter may contain predicates too, on the related table's own relations — "load each project's tasks that have a bug tag":

typescript
controls: {
  $with: [{ name: "tasks", filter: { tags: { $some: { label: "bug" } } } }];
}

Nesting limits and errors are on the Queries page.

Behavior by Relation Type ​

Different relation types return different shapes when loaded:

Relation typeNo matchesNull FKReturn type
@db.rel.to (required FK)N/A — FK must existN/AObject
@db.rel.to (optional FK)N/AnullObject or null
@db.rel.from (array)[]N/AArray
@db.rel.from (singular)nullN/AObject or null
@db.rel.via[]N/AArray

Key behaviors to remember:

  • Null FK produces null navigation: When an optional FK field is null, the corresponding @db.rel.to property returns null — not undefined, not an error.
  • Empty collections, not null: @db.rel.from and @db.rel.via always return [] when no matching records exist, never null.
  • Without $with: Navigation properties are undefined — they are not present on the returned object at all. This is distinct from null, which means "loaded but no match."

Nullable FK Lifecycle ​

Optional foreign keys follow a clear lifecycle through insert, query, update, and relation loading:

typescript
// 1. Insert with null FK
await taskTable.insertOne({
  title: "Unassigned task",
  done: false,
  projectId: 1,
  assigneeId: null,
});

// 2. Query for null FKs
const unassigned = await taskTable.findMany({
  filter: { assigneeId: null },
});

// 3. Load relation on null FK — returns null, not an error
const task = await taskTable.findOne({
  filter: { assigneeId: null },
  controls: { $with: [{ name: "assignee" }] },
});
// task.assignee === null

// 4. Assign a user
await taskTable.updateOne({ id: task.id, assigneeId: 5 });

// 5. Unassign (set back to null)
await taskTable.updateOne({ id: task.id, assigneeId: null });

WARNING

Setting an FK to a non-existent ID results in a foreign key violation error. Via the HTTP controller, this surfaces as a 400 response with an FK_VIOLATION error code. Programmatically, the adapter throws an error.

How It Works Internally ​

Under the hood, relation loading is adapter-specific:

  • SQL adapters (SQLite / PostgreSQL / MySQL) — separate batched queries. Each relation in $with triggers one additional query (two for VIA). No JOINs are emitted by the loader.
  • MongoDB — a single $lookup aggregation pipeline per relation (the adapter overrides supportsNativeRelations()). VIA relations chain two $lookup stages.

The descriptions below cover the SQL batching strategy. MongoDB's $lookup follows the same logical shape — same FK direction semantics, same composite-key support — but executes server-side.

TO relations ​

FK values are collected from all result rows, deduplicated, and sent as a single $in query to the target table. Results are indexed by primary key and assigned back to each row. If 100 tasks all reference 5 distinct projects, only 1 query runs against the projects table with { id: { $in: [1, 2, 3, 4, 5] } }.

FROM relations ​

Primary key values are collected from the result set, and the target table is queried with { fkField: { $in: [pkValues] } }. Results are grouped by foreign key and assigned as arrays (or singular values for 1:1 FROM relations).

VIA relations ​

Two queries run in sequence:

  1. The junction table is queried for all junction rows matching the local PKs
  2. The target table is queried for the collected target FK values from step 1

Results are grouped through the junction mapping and assigned to each row.

Composite keys ​

When a relation involves composite primary or foreign keys, the loader uses $or filters with all unique key combinations instead of $in. The matching logic uses composite key indexing internally.

Performance

This batching strategy means loading a relation on 100 records requires exactly 1 extra query (or 2 for VIA), not 100. The N+1 problem is avoided by design. Relations within the same $with level are loaded in parallel.

HTTP Equivalent ​

When using the HTTP controller (AsDbController), relation loading maps to the $with query parameter:

bash
# Load a single relation
curl "http://localhost:3000/tasks/query?\$with=project"

# Load multiple relations
curl "http://localhost:3000/tasks/query?\$with=project,assignee,tags"

For nested loading and per-relation controls in URLs, see CRUD Endpoints. Relational predicates in URLs (tasks=$some(done=false)) are on URL Query Syntax. The controller rejects an unknown or hidden relation at any nesting level with 400 Unknown relation "<name>" — see Advanced Queries § Validation.

Next Steps ​

Released under the MIT License.