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:
@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:
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:
const tasks = await taskTable.findMany();
// tasks[0].project === undefinedThis applies to all query methods that accept controls: findMany, findOne, and findById:
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:
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:
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:
await projectTable.findMany({
controls: { $with: ["owner", { name: "tasks", filter: { done: false } }, "tasks.assignee"] },
});
// = owner, and tasks (done = false) with their assigneeThere 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:
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
$sortorders the related rows themselves; without$sortthey come in junction order. - TO — a parent row has at most one related row, so
$limit: 0or a$skipof 1 or more loadsnull. - 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:
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.
const tasks = await taskTable.findMany({
controls: { $select: ["title"], $with: [{ name: "project" }] },
});
// tasks[0] → { title: '…', project: { id: 1, name: 'Website Redesign', … } } — no projectIdFiltering Loaded Relations
Use filter on a $with entry to restrict which related records are returned:
const projects = await projectTable.findMany({
controls: {
$with: [
{
name: "tasks",
filter: { done: false },
},
],
},
});
// projects[0].tasks → only incomplete tasksThis 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).
Filtering Parents by Related Rows
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:
// 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:
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 want | Use |
|---|---|
| fewer related rows on each returned row | $with entry filter |
| fewer returned rows, by their related rows | { nav: { $some: … } } / { nav: { $none: … } } |
| both | both — 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":
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 type | No matches | Null FK | Return type |
|---|---|---|---|
@db.rel.to (required FK) | N/A — FK must exist | N/A | Object |
@db.rel.to (optional FK) | N/A | null | Object or null |
@db.rel.from (array) | [] | N/A | Array |
@db.rel.from (singular) | null | N/A | Object or null |
@db.rel.via | [] | N/A | Array |
Key behaviors to remember:
- Null FK produces null navigation: When an optional FK field is
null, the corresponding@db.rel.toproperty returnsnull— notundefined, not an error. - Empty collections, not null:
@db.rel.fromand@db.rel.viaalways return[]when no matching records exist, nevernull. - Without
$with: Navigation properties areundefined— they are not present on the returned object at all. This is distinct fromnull, which means "loaded but no match."
Nullable FK Lifecycle
Optional foreign keys follow a clear lifecycle through insert, query, update, and relation loading:
// 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
$withtriggers one additional query (two for VIA). No JOINs are emitted by the loader. - MongoDB — a single
$lookupaggregation pipeline per relation (the adapter overridessupportsNativeRelations()). VIA relations chain two$lookupstages.
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:
- The junction table is queried for all junction rows matching the local PKs
- 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:
# 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
- Navigation Properties — defining TO, FROM, VIA, and filter relations in the schema
- Referential Actions — cascade, restrict, and set-null behavior on delete/update
- CRUD Operations — the full programmatic API for inserts, reads, updates, and deletes