# database.querying > Three cardinalities, one DSL. Declaration via relations(), eager loading via .with(), reactive invalidation via the two-stage dependency-graph + per-field pre-filter. --- ## Query builder _The full ctx.store API — select, where, orderBy, limit, aggregates, raw SQL escape hatches._ `ctx.store` is the framework's typed data layer. It's available on every executor's `AppContext`. Reads are dependency-tracked for subscriptions; writes fire CDC events that invalidate subscribers. This page covers selects + filters + aggregates. For relations see [Joins](/docs/database/joins); for writes see [Transactions](/docs/database/transactions). ## Select ```ts ctx.store.select('notes') // SELECT * FROM notes .where('tenantId', tenantId) // WHERE tenantId = $1 .orderBy('createdAt', 'desc') .limit(20) .all() // → ReadonlyArray ``` Terminal operations: | Method | Returns | When to use | |---|---|---| | `.all()` | `ReadonlyArray` | Multi-row result. | | `.one()` | `T` (throws if missing) | "I know this exists" — primary-key lookups. | | `.maybeOne()` | `T \| null` | Lookup that may fail (login by email). | | `.first()` | `T \| null` | First row; equivalent to `.limit(1).maybeOne()`. | | `.count()` | `number` | Counts. | | `.exists()` | `boolean` | EXISTS check; cheap. | Column projection — pick only the fields you need: ```ts ctx.store.select('notes').select('id', 'title') // SELECT id, title ``` The return type narrows automatically — `{ id: string; title: string }[]`. ## Single-row terminals on the typed builder `ctx.store.query(...)` takes the typed `database.` builder and returns typed rows. `ctx.store.one` / `.first` / `.maybeOne` are the single-row terminals of that same path — so "I want one row" and "I want typed rows" compose, instead of forcing you to pick one: ```ts import { eq } from '@voltro/database' // ONE row, typed. No cast, no explicit type argument — the row type // rides in on the builder. const user = await ctx.store.one(database.users.where(eq('id', id))) user.email // string — not `row['email'] as string` ``` They accept the builder itself or its `.descriptor`, so a call site never reaches for `.descriptor` just to use a terminal: ```ts await ctx.store.one(database.users.where(eq('id', id))) await ctx.store.one(database.users.where(eq('id', id)).descriptor) // same thing ``` | Terminal | Returns | On zero rows | On 2+ rows | |---|---|---|---| | `.one(query)` | `R` | throws `NoRowFound` | throws `NoRowFound` | | `.first(query)` | `R \| null` | `null` | returns the first | | `.maybeOne(query)` | `R \| null` | `null` | returns the first | `one()` probes with `LIMIT 2`, not `LIMIT 1`. A one-row probe cannot tell "the row you meant" from "the first of several", so a filter that silently stopped being unique would keep handing back an arbitrary row. One extra row buys a loud failure at the moment the assumption breaks. Scoping is identical to `ctx.store.query()` — the tenant filter and the soft-delete filter both apply — because the terminals *are* `query()` underneath rather than a second read path. (A second scoping path is exactly the drift that makes one of them quietly leak across tenants.) `NoRowFound` is typed, so the hand-written not-found branch goes away: ```ts // instead of: const rows = await ctx.store.query(...); if (!rows[0]) throw new NotFound() const note = await ctx.store.one(database.notes.where(eq('id', input.id))) ``` `voltro doctor` flags the hand-written version — see [the hand-roll detector](/docs/cli/build-and-start#the-hand-roll-detector). ## `where` Filters chain (AND-merged): ```ts ctx.store.select('notes') .where('authorId', myId) .where('createdAt', '>', cutoff) .where('archived', false) ``` ### Operators ```ts .where('col', value) // = (default) .where('col', '=', value) .where('col', '!=', value) // also '<>' .where('col', '<', value) .where('col', '<=', value) .where('col', '>', value) .where('col', '>=', value) .where('col', 'in', [a, b, c]) .where('col', 'contains', 'needle') // case-INsensitive substring (ILIKE '%…%') .where('col', 'startsWith', 'awb_') // case-SENSITIVE prefix (LIKE 'awb\_%') .where('col', 'fts', 'query string') // full-text fallback (LIKE-based here) ``` `contains` folds case because it is a search primitive — a human typing into a box means `hello` to find `Hello`. `startsWith` does not, because a prefix is a namespace: `awb_` and `AWB_` are two different key spaces, and quietly merging them is a bug. `startsWith` is also the only one of the two a database can answer from an index — `LIKE 'literal%'` is a btree range scan, `%…%` is not. `%` and `_` inside either value are escaped, so they match literally. There is no `'like'`. It used to be here, and it was a lie: it mapped to `contains`, so `.where('path', 'like', '/api/%')` matched only rows literally containing the characters `/api/%` and the wildcard you wrote did nothing. An operator named after SQL's must honour your wildcards or not exist. > **Writing an in-memory store for tests? Do not reimplement these.** `evaluatePredicate` is exported from `@voltro/database` and is the same function the memory store runs, so a test double built on it agrees with a real database by construction. Hand-rolling the switch is where a stub quietly diverges — a `case 'contains'` written as `String(a).includes(b)` is case-SENSITIVE where the real one folds case, and coerces a number where the real one returns `false`. That stub passes queries a live dialect fails, which is the worst direction for a test double to be wrong in. These are the only operators the ergonomic `.where(col, op, value)` form accepts. For `IS NULL` / `NOT IN` / `IS NOT NULL`, pass a predicate built with the `@voltro/database` helpers: ```ts import { isNull, isNotNull, notInSet } from '@voltro/database' ctx.store.select('notes').where(isNull('deletedAt')) ctx.store.select('notes').where(isNotNull('publishedAt')) ctx.store.select('notes').where(notInSet('status', ['archived', 'spam'])) ``` For index-backed full-text search use the `.matching('indexName', 'query')` builder (see [Full-text search](/docs/database/full-text-search)); the `'fts'` operator above is a plain substring fallback. ### OR / NOT The predicate helpers compose into the single-argument `.where(predicate)` form: ```ts import { or, not, and, eq, contains } from '@voltro/database' ctx.store.select('notes').where(or( eq('authorId', myId), contains('sharedWith', myId), )) ctx.store.select('notes').where(not(eq('archived', true))) ``` `and(...)` is rarely needed because chained `.where()` calls are already AND'd; useful inside `or(...)` to nest. `eq(col, val)` (and the other predicate helpers) is **callable without a row-type generic** — it defaults to a loose row shape — so in generic handler code you write `eq('teamId', id)` directly. There's no need for a `const ef = (c, v) => eq(c, v)` wrapper. ### JSON path filters For `json()` columns: ```ts ctx.store.select('notes') .where('prefs.fontSize', 'md') // prefs->>'fontSize' = 'md' .where('prefs.collapsed', 'contains', 'inbox') // prefs->'collapsed' @> '["inbox"]' ``` See [JSON columns](/docs/database/json) for indexing + path semantics. ## `orderBy` Each call adds ONE column + direction; chain for multi-column ordering: ```ts .orderBy('createdAt', 'desc') .orderBy('priority', 'desc').orderBy('createdAt', 'asc') ``` ## `limit` / `offset` ```ts .limit(20) .limit(20).offset(40) ``` For cursor pagination that avoids OFFSET's O(n) scan, use `paginateBy` over `ctx.store.query(...)`: ```ts import { paginateBy } from '@voltro/database' const rows = await ctx.store.query( paginateBy(database.notes.descriptor, 'createdAt', req.cursor, 20, 'desc'), ) const nextCursor = rows.at(-1)?.createdAt ?? null return { rows, nextCursor } ``` `paginateBy(descriptor, column, cursor, limit, direction?)` adds the keyset predicate, sets the ORDER BY, and preserves any existing `where`. The `direction` argument controls **both** the comparison and the sort — a `desc` feed pages with `<`, not `>`. That pairing is the classic keyset bug: an ascending comparison under a descending sort returns the same first page forever. The cursor column must be unique, or monotonic enough that ties don't straddle a page boundary. For a timestamp with collisions, order by a tie-breaker and paginate on that: ```ts paginateBy(database.notes.orderBy('createdAt', 'desc').descriptor, 'id', req.cursor, 20) ``` `paginateById(descriptor, cursor, limit)` is the `id`-column shorthand — literally `paginateBy(descriptor, 'id', cursor, limit)`. It works for any sortable id scheme (TypeID, ULID, Snowflake, Numeric). Note it sets the order to `id asc`, so passing a descriptor that already carries `.orderBy('createdAt', 'desc')` does **not** page by `createdAt` — use `paginateBy` when the sort column is the thing you want to page on. ## Aggregates The aggregate terminals live on the `database.
` builder, run via `ctx.store.query(...)`: ```ts import { count, sum, max, eq } from '@voltro/database' // COUNT(*) — one row, { count: number } const rows = await ctx.store.query( database.notes.where(eq('archived', false)).count().descriptor, ) const open = rows[0]!.count // Bundle multiple aggregates into one row await ctx.store.query( database.orders.where(eq('orgId', oid)).aggregate({ total: sum('amount'), peak: max('createdAt'), cnt: count(), }).descriptor, ) ``` Helpers: `count()`, `countDistinct(col)`, `sum(col)`, `avg(col)`, `min(col)`, `max(col)`. See [Aggregations](/docs/database/aggregations) for `.groupBy()` / `.having()` + window functions. ## Distinct `distinct` lives on the `database.
` builder, run via `ctx.store.query(...)`: ```ts await ctx.store.query(database.notes.select('authorId').distinct().descriptor) ``` For `DISTINCT ON` and one-row-per-group, see [DISTINCT + DISTINCT ON](/docs/database/distinct). ## Raw SQL escape hatch When the DSL doesn't model what you need: ```ts import { sql } from '@voltro/database/sql' const rows = await ctx.store.raw!<{ id: string; n: number }>(sql` SELECT id, count(*) AS n FROM events WHERE occurred_at > ${cutoff} GROUP BY id ORDER BY n DESC LIMIT 10 `) ``` The `sql` tag is imported from the server-only `@voltro/database/sql` subpath — it never reaches the browser bundle. It parameterises values automatically: every `${value}` interpolation is bound as a parameter by the store's dialect-specific driver, never spliced into the SQL text. A value containing `'; DROP TABLE` round-trips as data, not SQL. The generic parameter (`<{ id: string; n: number }>`) names the row shape. `store.raw` is an **optional** method on the store — it exists on every SQL store (postgres / mysql / mariadb / mssql / sqlite / turso) but NOT on the in-memory store (there's no SQL engine to run raw text against). The `!` non-null assertion above is appropriate on SQL-backed apps; for code that must run on the memory store too, guard with `if (ctx.store.raw)`. **You own dialect-portability of the static text.** Only the interpolated values are auto-parameterised — the rest of the fragment is emitted verbatim. Postgres-only syntax (`->>`, `plainto_tsquery`) breaks on mysql. Keep the static SQL portable, or branch on the dialect. **Raw queries aren't tracked by the reactive engine** — the planner can't infer which tables an arbitrary SQL string touches. If you want a subscription to invalidate on a raw read's tables, declare them explicitly: ```ts ctx.store.raw!<{ n: number }>(sql`SELECT count(*) AS n FROM events`, { dependsOn: ['events'] }) ``` The tables can also live on the fragment itself (`{ ...frag, dependsOn: ['events'] }`), which is handy when the fragment is built somewhere else. They are taken at face value — the framework records what you declare and never checks it against the SQL. **If you forget, the framework tells you.** A raw read taken while a live query's handler runs, with nothing declared, warns once at subscribe time and names both the query and the SQL: ``` [voltro] reports.summary: a raw SQL read in this live query declares no dependsOn, so no write can invalidate it — every subscriber keeps its first result until it reconnects. Declare the tables it reads: ctx.store.raw(fragment, { dependsOn: ['orders'] }) — or on the fragment itself. The read: SELECT sum(total) FROM orders WHERE tenant = ? ``` Only the STATIC text is logged; the bound values never are. The diagnostic keeps a bounded record: one request remembers up to 32 distinct raw reads (deduplicated by SQL text), tunable with `VOLTRO_RAW_READ_TRACKING_LIMIT`. Reads past the bound are dropped rather than remembered — a handler that raw-reads in a loop must not turn a warning into a memory leak. Raise it only if a handler issues many distinct raw reads and you want the warning to name a later one. **Where `dependsOn` works, and where it can only warn.** It drives recomputation for a query whose handler returns a **computed value** — the shape whose handler is genuinely re-run on a change (see [Subscriptions](/docs/data/subscriptions)). The declared tables join the query's own `source:`; they never replace it. A handler that returns a query **descriptor** is different: a change re-runs the descriptor's query, not your handler, so the raw result is not refreshed. There the warning is the whole answer — return a computed value if the raw read has to stay live. ## Tenant scoping (implicit) If the table has the `tenant()` mixin, every `select` auto-merges `WHERE tenantId = ctx.subject.tenantId`. You don't write it; the runtime injects it. To opt out (admin queries crossing tenants), use `.unscoped()`: ```ts ctx.store.select('notes').unscoped().all() ``` `.unscoped()` drops the automatic tenant filter for cross-tenant staff reads — gate it yourself in the handler (e.g. `requireScope(ctx.subject, 'admin:full')`) before exposing it. Soft-delete reads have the parallel `.withDeleted()` opt-out. ## Where to find more The query builder has dedicated pages for the deeper topics: - **[Aggregations](/docs/database/aggregations)** — `.count()` / `.aggregate({...})` / `.groupBy()` / `.having()` + window functions (`rowNumber` / `rank` / `lag` / `lead` / `sumOver`). - **[Sub-queries](/docs/database/sub-queries)** — `inSubquery` / `notInSubquery` / `exists` / `notExists` predicates that reference other queries. - **[Set operations](/docs/database/set-operations)** — `union` / `unionAll` / `intersect` / `except` combine multiple queries with the same column shape. - **[DISTINCT + DISTINCT ON](/docs/database/distinct)** — dedup row sets, pick one row per group on postgres. - **[Self-joins](/docs/database/self-joins)** — `.as(alias)` + `.innerJoin(table, alias, on)` + `.selectJoined({...})` for parent/ child trees and CTE references. - **[CTEs](/docs/database/query-builder#ctes)** — `.withCte(name, sub)` for named sub-queries reusable inside the outer SELECT. - **[Recursive CTEs](/docs/database/recursive-cte)** — `.recursiveCte` for tree walks (org hierarchy, comment threads, file folders). - **[Bulk writes](/docs/database/bulk-operations)** — `.updateMany` / `.upsert` / `.insertIgnore` for one-statement bulk operations. ## CTEs (Common Table Expressions) For complex queries with reusable sub-queries, declare named CTEs: ```ts import { eq, notInSubquery, queryFor } from '@voltro/database' const blocked = queryFor(database.blockedUsers).select('userId').descriptor const visibleUsers = await ctx.store.query( queryFor(database.users) .withCte('blocked', blocked) .where(notInSubquery('id', { table: 'blocked', projection: ['userId'], /* ... */ })) .descriptor, ) ``` Emits `WITH blocked AS (SELECT "userId" FROM "blockedUsers") SELECT ...`. For recursive CTEs (`WITH RECURSIVE`) see the [Recursive CTE page](/docs/database/recursive-cte). --- ## Joins & relations _Eager-loading related rows via relations + .with(), and explicit joins via queryFor().innerJoin(). How the reactive engine tracks them._ Voltro has two ways to read across tables: 1. **Relations + `.with(spec)`** — the ergonomic path for "fetch X with its Y". Declare relations once in a `*.relations.ts` file; eager-load them with `.with({ ... })`. Result comes back as nested objects. 2. **Explicit joins** — `queryFor(table).as(alias).innerJoin(Table, alias, on)` for self-joins, CTE references, and flat aliased projections. Covered in depth on the [Self-joins](/docs/database/self-joins) page. Foreign keys are declared with `reference(() => table)` — singular, thunk-arg. ## Declaring relations Relations live OUTSIDE the table descriptor, in a `*.relations.ts` file (convention). The framework registers them at boot and `.with()` uses them to eager-load. ```ts // database/users.relations.ts import { relations } from '@voltro/database' import { users, profiles, orgs, orgMemberships } from './index' export const usersRelations = relations(users, ({ one, many, manyToMany }) => ({ profile: one(profiles), // 1:1 ownedOrgs: many(orgs, { foreignKey: 'ownerId' }), // 1:N organizations: manyToMany(orgs, { // N:M through: orgMemberships, sourceKey: 'userId', targetKey: 'orgId', }), })) ``` `foreignKey` auto-derives when exactly one `reference()` column on the target points back at the source — set it explicitly only for tables with multiple FKs into the same parent (`createdBy` + `updatedBy` → `actors`). Many-to-many always uses an explicit through-table you write yourself. ## Eager-loading via `.with(spec)` ```ts const rows = await ctx.store.query( database.users .where(eq('tenantId', tenantId)) .with({ profile: true, // 1:1 → object | null ownedOrgs: { limit: 5, orderBy: [{ column: 'createdAt', direction: 'desc' }] }, organizations: true, // N:M via the through-table }) .descriptor, ) // rows[0].profile → Profile | null // rows[0].ownedOrgs → Org[] // rows[0].organizations → Org[] ``` Each branch takes its own `where` / `orderBy` / `limit` / `offset` / nested `with`. `limit: 5` applies **per parent** (up to 5 orgs FOR EACH user) — same semantics as Drizzle / Prisma / Hibernate. The framework renders the whole tree as ONE SQL roundtrip via the dialect's JSON-aggregation idiom (no N+1). Nested `.with()` threads arbitrary depth: ```ts database.users.with({ posts: { with: { author: true } } }) ``` ## Explicit joins — `queryFor().innerJoin()` When eager-load doesn't fit — self-joins, joining a CTE, or a flat aliased result shape — use the explicit join builder. The full reference is on the [Self-joins](/docs/database/self-joins) page; the shape: ```ts import { eq, queryFor } from '@voltro/database' const rows = await ctx.store.query( queryFor(database.messages).as('m') .innerJoin(database.users, 'u', eq('u.id', 'm.authorId')) .selectJoined({ messageId: 'm.id', body: 'm.body', authorName: 'u.name', }) .descriptor, ) // rows: Array<{ messageId: string; body: string; authorName: string }> ``` `.leftJoin(Table, alias, on)` has the same shape but keeps FROM-side rows that have no JOIN-side match (joined columns are `null` for those). Passing a `Table` descriptor threads its row type into `.selectJoined` for full inference; passing a string name (CTE / dynamic) falls through to `unknown`. ## Reactive tracking Eager-load (`.with()`) subscriptions track changes to the root table and every relation the spec touches — the dispatcher consults a per-table dependency map plus a per-field relevance pre-filter, so a write that doesn't touch a depended-on column skips the re-query entirely. For high-traffic joins where you only want to re-fire on the primary table, project away the joined columns or denormalise into a generated column. ## Anti-patterns - **Joining inside a loop.** Always express the read in the builder — `.with({ ... })` does the JSON aggregate; the explicit join builder does the SQL JOIN. - **`reference('table')`.** The FK constructor is `reference(() => table)` — singular, thunk-arg. - **Using `.with()` for write paths.** Reads only; for writes use [Transactions](/docs/database/transactions). --- ## Relations _Three cardinalities, one DSL. Declaration via relations(), eager loading via .with(), reactive invalidation via the two-stage dependency-graph + per-field pre-filter._ Voltro models table relationships explicitly via a `relations()` declaration that lives outside the table descriptor. The query builder's `.with(...)` chain reads this declaration to eager-load related rows in a single SQL round-trip, regardless of how many relations you traverse. This index covers the cross-cutting bits. Each cardinality + the cross-cutting concerns has its own page below: - [one()](./one-to-one) — 1:1 or N:1. Single related row per parent. - [many()](./one-to-many) — 1:N. Array of related rows per parent. - [manyToMany()](./many-to-many) — N:M through an explicit junction table. - [Eager loading with `.with()`](./eager-loading) — single-roundtrip nested JSON. - [Cascade + FK semantics](./cascade) — `onDelete: 'restrict'` defaults + FK-auto-index. - [Reactive invalidation](./reactive) — two-stage gate: a per-table dependency-graph plus a per-field pre-filter. ## Declaration Relations live in `*.relations.ts` files alongside the schema. They're separate from the table descriptor so a table can be referenced from multiple sides without a circular import. ```typescript // database/users.relations.ts import { relations } from '@voltro/database' import { users, profiles, posts, orgs, orgMemberships } from './index' export const usersRelations = relations(users, ({ one, many, manyToMany }) => ({ profile: one(profiles), // 1:1 posts: many(posts, { foreignKey: 'authorId' }), // 1:N organizations: manyToMany(orgs, { // N:M through: orgMemberships, sourceKey: 'userId', targetKey: 'orgId', }), })) ``` The framework auto-registers every `relations(...)` call at boot — you don't write a manual barrel. ## `sourceKey` vs `foreignKey` — opposite directions Both options name exactly one column, and they name it on **opposite tables**. Read them as a sentence about the row you are standing on: | Option | The column lives on | It means | |---|---|---| | `sourceKey` | the **SOURCE** table | "the row **my** column points at" — **my parent** | | `foreignKey` | the **TARGET** table | "the rows that point **at me**" — **my children** | ```typescript // sourceKey — users.defaultOrgId holds an ORG's id. // "The org my column points at." One org per user. relations(users, ({ one }) => ({ defaultOrg: one(orgs, { sourceKey: 'defaultOrgId' }), })) // foreignKey — posts.authorId holds a USER's id. // "The posts that point back at me." Many posts per user. relations(users, ({ many }) => ({ posts: many(posts, { foreignKey: 'authorId' }), })) ``` The mnemonic is in the option name: `sourceKey` is a key on the source, `foreignKey` is the foreign table's key back. `manyToMany` is the exception that proves it — **both** of its keys name columns on the JUNCTION table (`sourceKey` the one pointing at the source, `targetKey` the one pointing at the target), never on the source or target themselves. ### The keys are validated at boot Once every table and every `relations()` block is loaded, the framework checks each relation's key options against the real column lists and refuses to boot on a mismatch — naming the option that would have been right: ```text relation 'posts' on 'users': foreignKey 'defaultOrgId' is not a column on the target table 'posts'. It IS a column on the source 'users' — you want { sourceKey: 'defaultOrgId' }. foreignKey names the column on the TARGET that points back at this table; sourceKey names the column on THIS table that holds the target's id. ``` Passing **both** keys is refused too. It is contradictory rather than merely redundant: a resolvable `sourceKey` decides the shape and `foreignKey` is then never read, so the declaration would silently mean less than it says. For `manyToMany`, a `sourceKey` / `targetKey` that is not a column on the through-table fails the same way, with the junction's column list in the message. ### What the static check cannot catch: self-references When source and target are the **same table**, the column exists on both sides by definition — so `foreignKey: 'parentTeamId'` and `sourceKey: 'parentTeamId'` are both well-formed and no column check can tell which you meant. This case is caught by data instead, at query time, through the `one` cardinality contract below. ### `one` means AT MOST one — enforced An eager-load branch now **fails** when a `one` relation matches more than one row for the same parent, instead of silently keeping the last match — on **both** eager paths, with the same message: ```text relation 'parent' on 'teams' is declared `one`, but teams.parentTeamId matches MORE than one row for the same teams.id — so there is no single related row to return. Because this relation is self-referential, check which side the key is on: `foreignKey: 'parentTeamId'` means "rows whose parentTeamId points AT me" (my children — there can be many). If you meant "the row my parentTeamId points to" (my parent), that is `sourceKey: 'parentTeamId'`. ``` The check applies wherever the foreign key sits on the **target** ("every child points back at me") — the only shape in which a second match is expressible. There the single-query path over-fetches two rows and raises on the second; a `one` resolved through the target's primary key (`sourceKey: '…'`) still takes a single row in SQL, because a second one cannot exist. One honest limit remains: it is a **data** check, so it only fires when the data actually has multiplicity. A parent row with a single child — or a leaf with none — passes the wrong declaration silently and resolves to a plausible-looking `null`. That does not replace reading the table above before you type the option. **The bug this came from.** An app declared `parent: one(() => teams, { foreignKey: 'parentTeamId' })` on a self-referencing team hierarchy. It typechecked, it booted, and the emitted SQL matched the hand-written join it replaced — and `parent` came back `null`, because the declaration was loading *children*, and the test row happened to be a leaf. Both halves of this section exist because that shipped. ## Quick start — all three cardinalities ```typescript // Schema export const users = table('users', { id: id(), email: text() }) export const profiles = table('profiles', { id: id(), userId: reference(() => users), bio: text(), }) export const posts = table('posts', { id: id(), authorId: reference(() => users), title: text(), }) export const orgs = table('orgs', { id: id(), name: text() }) export const orgMemberships = table('org_memberships', { id: id(), userId: reference(() => users), orgId: reference(() => orgs), role: text(), }) // Relations relations(users, ({ one, many, manyToMany }) => ({ profile: one(profiles), posts: many(posts), organizations: manyToMany(orgs, { through: orgMemberships, sourceKey: 'userId', targetKey: 'orgId', }), })) // Eager-load query const rows = await store.query( database.users.with({ profile: true, posts: { limit: 5, orderBy: [{ column: 'createdAt', direction: 'desc' }] }, organizations: true, }).descriptor, ) // TypeScript infers: // rows: Array ``` One SQL round-trip. The framework's JSON-aggregation compiler renders the whole tree as a single SELECT. ## When to declare a relation Declare a relation when: - Application code wants to **eager-load** the related rows in a single query. - A reactive subscription needs to **wake when the related table changes**. - The TypeScript types should **flow through** the cardinality automatically. Don't declare a relation when: - The FK exists only for DB-level integrity and code never reads through it. - The "relation" is computed across many tables and doesn't have a clean 1:1 / 1:N / N:M shape. A `reference()` column gives you the FK constraint + B-tree index regardless of whether a relation is declared on top. Relations are an application-layer concern about how to TRAVERSE the FK, not whether the FK exists. ## Decision tree — which cardinality? ```text Does the related table point AT this table? ├─ One row only? → one(target) — 1:1 from this side, N:1 if FK is on this side ├─ Many rows? → many(target) — 1:N └─ Connected through a junction table that holds extra columns? → manyToMany(target, { through: junction, ... }) — N:M ``` If you're not sure, ask: "for one row in THIS table, how many rows in THE OTHER table do I get?" One → `one`. Many → `many`. Many with a third table in between → `manyToMany`. ## Where it lives - `voltro/packages/database/src/relations.ts` — `relations()` builder + `one`/`many`/`manyToMany` helpers - `voltro/packages/database/src/relationsRegistry.ts` — process-global registry - `voltro/packages/database/src/queryBuilder.ts` — `.with()` chain - `voltro/packages/database/src/joinCompiler.ts` — walker fallback for compile-null cases - `voltro/packages/database/src/jsonEagerCompiler.ts` — per-dialect nested JSON-aggregation - `voltro/packages/runtime/src/dependencyGraph.ts` — multi-table subscription registration - `voltro/packages/runtime/src/relevantFields.ts` — per-field pre-filter - `voltro/packages/runtime/src/dispatcher.ts` — multi-table fan-out + delta diffing --- ## one() — 1:1 and N:1 _Single related row per parent. Owning side vs optional side, FK location auto-derivation, common patterns._ `one(target)` declares that for one row in the source table, there's at most ONE related row in the target. The resolved value is `Target | null` — null when no related row exists. ```typescript relations(users, ({ one }) => ({ profile: one(profiles), // 1:1, FK on profiles defaultOrg: one(orgs, { sourceKey: 'defaultOrgId' }), // N:1, FK on users })) ``` The framework derives the join direction from the existence of FK columns; if it can't disambiguate, you pass `sourceKey` (FK is on the SOURCE table pointing at the TARGET) or `foreignKey` (FK is on the TARGET table pointing at the SOURCE). ## Two shapes `one()` collapses two different relationship cardinalities into one DSL — they have the same shape from the application's perspective (single related row) but the SQL emitted is different. ### Shape A — FK on the target (1:1 / 0..1:1) Profile owns its FK to user. One user has zero or one profile. ```typescript export const profiles = table('profiles', { id: id({ prefix: 'profile' }), userId: reference(() => users, { onDelete: 'cascade' }), // ← FK bio: text(), }) relations(users, ({ one }) => ({ profile: one(profiles), // framework finds userId on profiles → join target.userId = source.id })) ``` SQL emitted: ```sql (SELECT row_to_json(p) FROM profiles p WHERE p.user_id = users.id LIMIT 1) ``` The framework auto-derives this when the target table has exactly ONE `reference()` column pointing at the source. If there are multiple FKs from target → source, pass `foreignKey:`: ```typescript // posts has both authorId AND editorId → user relations(users, ({ one }) => ({ authoredFirstPost: one(posts, { foreignKey: 'authorId' }), editedFirstPost: one(posts, { foreignKey: 'editorId' }), })) ``` ### Shape B — FK on the source (N:1) User owns the FK to a default org. Many users can point at the same org. ```typescript export const users = table('users', { id: id(), defaultOrgId: reference(() => orgs).nullable(), // ← FK email: text(), }) relations(users, ({ one }) => ({ defaultOrg: one(orgs, { sourceKey: 'defaultOrgId' }), })) ``` SQL emitted: ```sql (SELECT row_to_json(o) FROM orgs o WHERE o.id = users.default_org_id LIMIT 1) ``` The framework needs `sourceKey:` because the FK is on the source side, pointing at the target's `id`. Without the hint, the framework would look for FKs on the target table back at the source. ## Result type ```typescript const result = await store.query( database.users.with({ profile: true, defaultOrg: true }).descriptor, ) // result: Array ``` Always `Target | null` — even on tables where you "expect" the relation to exist. Application code MUST handle the null case; the framework doesn't model "required 1:1" as a different cardinality. ## Owning side vs optional side For 1:1 relationships, which side owns the FK is a schema design decision the framework doesn't force: - **Owning side**: holds the FK column. Inserts on this side reference the target. - **Optional side**: holds no FK. The relation flows through the owning side's FK on read. Practical rule of thumb: put the FK on the **smaller-cardinality** side. If every user has at most one profile, the FK goes on profiles (which is cardinality-bound by users). If you flipped it — `users.profileId` — you'd need to manage the order of creation (insert profile first, then user with profileId) instead of the typical sequence (insert user, then optional profile). ## Mutating through the relation The framework's mutation surface doesn't have a "set profile" shortcut. You write the underlying INSERT / UPDATE explicitly: ```typescript // Create user + profile. Pre-compute the id so `profiles.userId` can // reference it in the same flow. import { typeid } from 'typeid-js' const userId = typeid('user').toString() await ctx.store.insert('users', { id: userId, email }) await ctx.store.insert('profiles', { userId, bio }) // Change a user's default org. await ctx.store.update('users', userId, { defaultOrgId: newOrgId }) ``` This is on purpose — the relation declaration describes the SHAPE of the data, not the WAY mutations propagate. Cascade behaviour is controlled by `onDelete:` on the `reference()` column (see [cascade](./cascade)), not by the relation. ## Eager loading with `.with({ profile: true })` ```typescript const users = await store.query( database.users.where(eq('tenantId', tenantId)).with({ profile: true }).descriptor, ) // users: Array ``` The framework's JSON-agg compiler emits a single SELECT with the profile as a nested subquery. No N+1 round-trips. For per-branch filtering / ordering on the related row, pass an object instead of `true`: ```typescript database.users.with({ profile: { where: eq('verified', true) }, }) ``` The framework applies the where clause inside the per-row subquery. If the profile doesn't match, the resolved value is null — same as if no profile existed. ## Nested `.with()` The related row can carry its own `.with()` for transitive relations: ```typescript database.users.with({ profile: { with: { avatar: true }, // profile → avatar }, }) // → Array ``` Recursion depth is arbitrary. Each nested branch becomes another nested subquery in the same SELECT — the framework's compiler walks the spec to any depth. ## Reactive subscriptions A subscription opened with `.with({ profile: true })` registers against BOTH `users` and `profiles`. Changes to either table re-run the query (narrowed by the per-field pre-filter — see [reactive](./reactive)). ## Caveats - **Two `one()` calls on the same target table need distinct relation names**. The framework keys eager loads by relation name, not by target table — `database.users.with({ profile: true })` reads the relation named `'profile'`. If you declare both `defaultOrg` and `billingOrg` both pointing at `orgs`, they're distinct relations and `with({ defaultOrg: true, billingOrg: true })` reads them independently. - **Self-referential 1:1 needs the thunk form**. `one(() => users)` for a "parent user" lookup. Without the thunk, the table reference would try to resolve before the table is registered and fail. - **`one()` on a target that has a many-to-the-same-source relation is unusual but legal**. The framework reads the relation declaration verbatim. If the data actually has two profiles for one user, an eager-load branch served by the [walker](./eager-loading#walker-fallback) now **fails** rather than handing you an arbitrary one of them — see [the `one` cardinality contract](./index.md#one-means-at-most-one-enforced). A branch the JSON-aggregation compiler can express resolves it in SQL instead (`LIMIT 1` in the subquery), and the extra row is invisible there. Use `many()` if multiplicity is genuinely possible. ## Where it lives - `voltro/packages/database/src/relations.ts` — `oneBuilder` (line 100) - `voltro/packages/database/src/jsonEagerCompiler.ts` — per-dialect `*OneSubquery` (postgres, mysql, mssql, sqlite) - `voltro/packages/database/src/joinCompiler.ts` — walker fallback for `one()` when the JSON-agg compiler can't dispatch --- ## many() — 1:N _Array of related rows per parent. FK auto-derivation, per-parent where/orderBy/limit semantics, performance caveats for unbounded children._ `many(target)` declares that for one row in the source table, there are zero or more related rows in the target. The resolved value is `Target[]`. ```typescript relations(users, ({ many }) => ({ posts: many(posts), // FK auto-derived as authorId comments: many(comments, { foreignKey: 'userId' }), // explicit FK column })) ``` The FK lives on the TARGET table — it points back at the source. The framework derives it from the only `reference()` column pointing at the source, or you pass `foreignKey:` explicitly when the target has multiple FKs back. ## Result type ```typescript const result = await store.query( database.users.with({ posts: true }).descriptor, ) // result: Array ``` Always `Target[]` — empty array when no related rows exist. Not `Target[] | null`. ## Per-branch modifiers The branch spec accepts `where`, `orderBy`, `limit`, `offset`. They apply **per parent** — `limit: 10` means up to 10 posts FOR EACH user, not 10 posts total. ```typescript database.users.with({ posts: { where: eq('published', true), orderBy: [{ column: 'createdAt', direction: 'desc' }], limit: 10, offset: 0, }, }) ``` The SQL emitted depends on the dialect: ### Postgres / SQLite / MSSQL A FROM-derived-table wrapper around the per-parent set: ```sql -- Postgres (SELECT jsonb_agg(t ORDER BY t.created_at DESC) FROM (SELECT * FROM posts WHERE author_id = users.id AND published = true ORDER BY created_at DESC LIMIT 10) t) ``` ### MySQL Similar shape with `JSON_OBJECT` + `JSON_ARRAYAGG`: ```sql COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(...)) FROM (SELECT * FROM posts WHERE author_id = users.id AND published = true ORDER BY created_at DESC LIMIT 10) t), JSON_ARRAY()) ``` ### MariaDB MariaDB rejects correlated references inside non-LATERAL derived tables. The framework switches to a ROW_NUMBER window-function pattern: ```sql COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(...) ORDER BY ranked.rn) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn FROM posts WHERE published = true) ranked WHERE ranked.author_id = users.id AND ranked.rn <= 10), JSON_ARRAY()) ``` See [mariadb dialect details](../dialects/mariadb) for why. ## Reverse direction `many()` is unidirectional in the relation declaration — you declare from the parent side. The child side (target) can declare a reverse `one()` independently if application code needs it: ```typescript relations(users, ({ many }) => ({ posts: many(posts), })) relations(posts, ({ one }) => ({ author: one(users, { sourceKey: 'authorId' }), // posts.authorId → users.id })) // Both directions work in eager loads. database.users.with({ posts: true }) database.posts.with({ author: true }) ``` The framework doesn't auto-generate reverse relations — declare them explicitly when you need them. ## Cardinality assertion `many()` carries no assertion about how MANY children each parent has. Zero is valid (empty array). A million is valid (potentially RAM-explosive — see "When NOT to eager-load" below). The relation describes "the set of children" without a multiplicity constraint. If you genuinely have a 1:1 relationship modeled as `many()` because of legacy data, use `one()` instead — the framework will LIMIT 1 the subquery automatically and your TypeScript types will be `T | null` instead of `T[]`. ## When NOT to eager-load `.with({ posts: true })` builds the WHOLE child set into one JSON document per parent. For tables with extremely wide child sets — `users.with({ events: true })` on a user with 1M events — the result JSON is multi-MB per row. RAM-hostile. Mitigations, in order of preference: ### 1. Always pass `limit:` on unbounded children ```typescript database.users.with({ events: { limit: 100, orderBy: [{ column: 'createdAt', direction: 'desc' }] }, }) ``` The framework's per-parent slicing kicks in before aggregation, so memory is bounded by `parents × limit × row size`. ### 2. Cursor-paginate the children separately When the user might want pagination UI: ```typescript // Don't eager-load. const [user] = await store.query(database.users.where(eq('id', uid)).descriptor) const events = await ctx.store.query( paginateById(database.events.where(eq('userId', uid)).descriptor, input.cursor, 100), ) const nextCursor = events.at(-1)?.id return { user, events, nextCursor } ``` ### 3. Drop down to manual joins when shape matters ```typescript const rows = await ctx.store.transactional(async (txn) => { const users = await txn.query(database.users.where(...)) const eventCounts = await txn.unsafe( `SELECT user_id, COUNT(*) FROM events WHERE user_id = ANY($1) GROUP BY user_id`, [users.map(u => u.id)], ) return users.map(u => ({ ...u, eventCount: eventCounts[u.id] ?? 0 })) }) ``` For aggregation-shape outputs (counts, sums, top-K subsets), the eager-load shape isn't what you want. Hand-write the query. ## Walker fallback When the JSON-agg compiler can't express a particular spec (unknown relation kind, dialect-specific edge case the per-dialect emitter doesn't cover), the framework transparently falls back to a portable per-relation N+1 walker. Same result, slower path. Look for the boot log line if you want to know which path fired: ```sh voltro logs --tail 50 | grep "JSON-agg eager-load failed; falling back" ``` If you see the warning regularly, the spec is hitting a code path the compiler hasn't covered yet — file an issue with the descriptor + dialect. ## Reactive subscriptions A subscription opened with `.with({ posts: true })` registers against both `users` and `posts`. Changes to either re-run the query (narrowed by the per-field pre-filter — see [reactive](./reactive)). For tables with high write rates (`comments`, `events`, audit logs), reactive `.with({ ...heavyChild: true })` can cost a lot of re-queries. The per-field filter helps but doesn't eliminate the cost. Mitigations: - Project narrowly. `.with({ posts: { limit: 5 } })` reads only 5 posts per user, so a write to a post the subscription doesn't include never wakes the sub. - Split the subscription. Subscribe to the parent (`users`) and the children (`posts`) separately; each only fires on its own table's changes. ## Caveats - **FK auto-derivation requires exactly ONE matching `reference()`**. If the target has multiple FKs back at the source (`author_id`, `editor_id`, `reviewer_id` all → `users.id`), pass `foreignKey:` explicitly. The auto-derivation throws at schema-registration time with a clear error if it can't pick unambiguously. - **Self-referential `many()` needs the thunk form**: `many(() => posts)` for a "replies" relation on posts. - **Empty child set ≠ null parent**. `user.posts === []` for a user with no posts. `user.posts === undefined` only when the spec didn't request the relation. Distinguish in handler code. ## Where it lives - `voltro/packages/database/src/relations.ts` — `manyBuilder` (line 110) - `voltro/packages/database/src/jsonEagerCompiler.ts` — `postgresManySubquery`, `mysqlManySubquery`, `mariadbManySubquery`, `mssqlManySubquery`, `sqliteManySubquery` - `voltro/packages/database/src/joinCompiler.ts` — walker fallback (`attachMany`) --- ## manyToMany() — N:M _Explicit through-table pattern. Junction table with extra columns. Doubly-declared relations for both directions._ `manyToMany(target, { through, sourceKey, targetKey })` declares an N:M relationship through an explicit junction table. The framework never auto-generates the junction; you write it yourself with whatever extra columns it needs. ```typescript // The junction table — write it explicitly. export const orgMemberships = table('org_memberships', { id: id({ prefix: 'membership' }), userId: reference(() => users, { onDelete: 'cascade' }), orgId: reference(() => orgs, { onDelete: 'cascade' }), role: text().oneOf(['owner', 'admin', 'member']), joinedAt: timestamp().default('now'), }) // The relation, declared on BOTH sides. relations(users, ({ manyToMany }) => ({ organizations: manyToMany(orgs, { through: orgMemberships, sourceKey: 'userId', targetKey: 'orgId', }), })) relations(orgs, ({ manyToMany }) => ({ members: manyToMany(users, { through: orgMemberships, sourceKey: 'orgId', // junction column pointing at this side targetKey: 'userId', // junction column pointing at the other side }), })) ``` ## Why explicit through-table The framework deliberately avoids the Prisma / TypeORM "auto-generated junction" pattern. Three reasons: 1. **Extra columns are the rule, not the exception**. Memberships have roles. Tags have ordering. Subscriptions have permission tiers. The auto-junction always-needs-migrating-to-add-columns is a recurring tax. Writing the table yourself from the start avoids it. 2. **Junction discoverability**. The junction is a real table you can query directly: `database.orgMemberships.where(eq('userId', uid))` is sometimes what you want, not "users with their organizations." Treating it as a first-class table makes both paths natural. 3. **Schema is explicit**. Looking at your `database/` directory tells you exactly which tables exist. No hidden auto-generated tables to chase down at migration time. ## Writing links — `store.links` Reconciling the set of links from one row (a post's tags, a user's orgs) by hand — read the current rows, work out which to insert and which to delete — is fiddly and easy to get wrong. A drop-all-then-reinsert shortcut loses data when two requests overlap and makes a reactive subscription on the junction churn *every* row even when nothing changed. `ctx.store.links(junctionTable, anchor)` does the diff for you: ```ts // anchor names the source column + id; the target column is auto-detected await ctx.store.links('org_memberships', { userId: user.id }).set(orgIds) // reconcile to exactly orgIds await ctx.store.links('org_memberships', { userId: user.id }).add([orgId]) // idempotent — no-op if already linked await ctx.store.links('org_memberships', { userId: user.id }).remove([orgId]) const orgIds = await ctx.store.links('org_memberships', { userId: user.id }).list() ``` `set(targetIds)` writes only the **difference**: the missing links are inserted, the surplus deleted, and links that are already correct are left untouched — so a reactive consumer sees a change only for what actually changed, and it returns `{ added, removed }`. `add` and `remove` read first and act only on the genuine delta, so both are idempotent. The **target column** is the junction's *other* `reference()` column — the one the anchor doesn't name. A junction with anything but exactly two reference columns is refused (write it by hand with `insertMany` / `deleteMany`). The writes go through the normal store path, so a junction that carries `tenant()` / `audit()` gets those columns stamped as usual. `set` / `add` / `remove` manage only the two FK columns. For a junction that carries **per-row payload** — a membership `role`, a `capacity` value — use `setRows`, which diffs on the (source, target) pair AND updates the payload: ```ts await ctx.store.links('team_capacities', { teamId: team.id }).setRows([ { projectId: 'p1', capacity: 40 }, { projectId: 'p2', capacity: 20 }, ]) // added rows inserted with payload, removed deleted, a surviving row whose payload // CHANGED is updated, an unchanged one is left untouched → { added, removed, updated } ``` Only rows whose payload actually differs are written, so a reactive consumer sees a change exactly where the data changed — the diff-based replacement for a drop-and-reinsert on a data-carrying junction. Payload is compared by strict per-column equality (scalars like `capacity` / `role`). ## SQL shape The framework emits an INNER JOIN through the junction: ```sql -- For database.users.with({ organizations: true }): SELECT users.*, (SELECT json_agg(o) FROM ( SELECT orgs.* FROM org_memberships m INNER JOIN orgs ON orgs.id = m.org_id WHERE m.user_id = users.id ) o ) AS organizations FROM users; ``` The junction is in the FROM clause; the wrapping subquery aggregates the target rows. Per-dialect details (JSON_ARRAYAGG / FOR JSON PATH / json_group_array) match the [eager loading](./eager-loading) page. For MariaDB the framework uses a ROW_NUMBER window-function pattern when per-parent limit/offset is set, same trade-off as `many()` — see [mariadb dialect details](../dialects/mariadb). ## Per-branch modifiers `where` / `orderBy` / `limit` / `offset` apply to the TARGET table — not the junction: ```typescript database.users.with({ organizations: { where: eq('plan', 'enterprise'), // filters orgs, not memberships orderBy: [{ column: 'name', direction: 'asc' }], limit: 10, }, }) ``` To filter on JUNCTION columns (e.g. "orgs where this user is an admin"), pass `onJunction` on the eager branch. The predicate is evaluated against the THROUGH-table row, not the target row: ```typescript database.users.with({ organizations: { onJunction: eq('role', 'admin'), // filters the membership junction orderBy: [{ column: 'name', direction: 'asc' }], }, }) // → each user's `organizations` are exactly the orgs they're an admin of ``` `onJunction` composes with the target-side `where` / `orderBy` / `limit` / `offset` — `where` still filters the target (orgs), `onJunction` filters the junction (memberships). It compiles into the correlated subquery's WHERE alongside the source-key correlation, qualified to the junction-table alias, on every dialect (postgres / mysql / mariadb / mssql / sqlite / turso). Ignored on `one` / `many` branches (no junction table exists). ## Junction columns in the result — `junction` Filtering on a junction column is one half; the other is *reading* it. `junction` projects the THROUGH-table row's own columns onto each target row, under `_junction`: ```typescript database.users.with({ organizations: { junction: ['role', 'joinedAt'] }, }) // → each org carries org._junction.role / org._junction.joinedAt // — the membership row that linked THIS user to THIS org ``` `junction: true` takes every column of the junction table instead of naming them: ```typescript database.users.with({ organizations: { junction: true } }) ``` It composes with everything else on the branch — `onJunction` still filters the membership, `where` / `orderBy` / `limit` still apply to the target, and nested `.with()` on the target still resolves: ```typescript database.users.with({ organizations: { onJunction: eq('role', 'admin'), junction: ['role', 'joinedAt'], orderBy: [{ column: 'name', direction: 'asc' }], with: { projects: true }, }, }) ``` ### Why nested under `_junction` and not merged A junction and its target routinely share column names — `createdAt` is the obvious one, and `id` always collides. Merging membership fields onto the target row would silently overwrite real target data with junction data, and a name collision that corrupts a row is a worse failure than one extra level of nesting. ### The cost — none; it stays one query A branch that asks for junction columns still compiles to the single-query [JSON-aggregation path](./eager-loading). Every dialect builds the `_junction` object natively: postgres carries the projection out of the correlated join as one `jsonb` value, sqlite / mysql / MariaDB carry the columns as aliased scalars and re-assemble them, mssql uses `FOR JSON PATH`'s dotted column aliases. No extra round trip, and nothing in the `.with()` tree is rerouted because one branch asked for junction data. What makes this correct rather than merely fast: the projection is computed INSIDE the per-parent join, so a target row linked to two parents carries each parent's own junction row — the same per-link result the walker produces by cloning. The two paths are checked against each other on a live engine per dialect, so the JSON result is identical to the walker's, not merely similar. ## Doubly-declared `many` and `one` are unidirectional — you declare from the source side and the reverse is a separate `relations(...)` call. `manyToMany` is the same: declare BOTH directions independently if you need both: ```typescript relations(users, ({ manyToMany }) => ({ organizations: manyToMany(orgs, { through: orgMemberships, sourceKey: 'userId', targetKey: 'orgId', }), })) relations(orgs, ({ manyToMany }) => ({ members: manyToMany(users, { through: orgMemberships, sourceKey: 'orgId', targetKey: 'userId', }), })) // Both directions work in eager loads. database.users.with({ organizations: true }) database.orgs.with({ members: true }) ``` ## Inserting a membership The framework has no "addMembership" shortcut. Insert the junction row directly: ```typescript await ctx.store.insert('org_memberships', { userId, orgId, role: 'admin', }) ``` If the junction has a unique constraint on `(userId, orgId)` — which it should, to prevent duplicate memberships — the framework's `PrimaryKeyConflictError` fires on duplicate inserts. Catch + handle: ```typescript try { await ctx.store.insert('org_memberships', { userId, orgId, role }) } catch (e) { if (e instanceof PrimaryKeyConflictError) { // Already a member. return { alreadyMember: true } } throw e } ``` ## Removing a membership ```typescript const [m] = await store.query( database.orgMemberships.where(and( eq('userId', uid), eq('orgId', oid), )).descriptor, ) if (m) { await ctx.store.delete('org_memberships', m.id) } ``` If you set `onDelete: 'cascade'` on the junction's FK to either side, deleting a user or org auto-removes all their memberships. This is the typical setup — memberships have no meaning without both parents. ## Reactive subscriptions A subscription opened with `.with({ organizations: true })` registers against THREE tables: `users`, `org_memberships`, `orgs`. Changes to any re-run the query. This can be a lot of fan-out on tables with high junction write rates (a hot membership table getting writes every second). The per-field pre-filter (see [reactive](./reactive)) reduces but doesn't eliminate the cost. For very-hot-junction patterns, split the subscription. ## TypeScript inference ```typescript const rows = await store.query( database.users.with({ organizations: true }).descriptor, ) // rows: Array ``` The junction columns (role, joinedAt) are NOT in the resolved Org type — a plain `manyToMany` branch eager-loads the TARGET side only. Ask for them with `junction:` (above) to get them under `_junction`, or query the junction table directly when the membership row itself is the thing you want. ## Caveats - **Same target appearing twice on the source needs distinct relation names**. If users have `organizations` (membership-based) AND `ownedOrgs` (1:N via `orgs.ownerId`), declare them as separate relations with separate names. The framework keys by relation name. - **`sourceKey` is the junction column pointing at THIS side**. Both keys name columns on the JUNCTION — easy to confuse with the `reference()` column on the source table itself. A key that isn't a column on the through-table fails the [boot validation](./index.md#the-keys-are-validated-at-boot) with the junction's real column list in the message, so the mistype can't reach a query. - **Cascade behaviour on the junction is separate from cascade on the parents**. `onDelete: 'cascade'` on `org_memberships.userId` removes memberships when the user is deleted, but doesn't touch `orgs`. Set it explicitly on each FK depending on lifecycle semantics. - **Junction extra columns aren't reactive through the m2m relation**. A write to `org_memberships.role` doesn't wake a subscription on `users.with({ organizations: true })` UNLESS the subscription's relevant-fields set includes `role` (which it doesn't by default — only the join keys are relevant for the m2m walk). If you want subscriptions to react to role changes, observe `org_memberships` directly. ## Where it lives - `voltro/packages/database/src/relations.ts` — `manyToManyBuilder` (line 120) - `voltro/packages/database/src/jsonEagerCompiler.ts` — `postgresManyToManySubquery`, `mysqlManyToManySubquery`, `mariadbManyToManySubquery`, `mssqlManyToManySubquery`, `sqliteManyToManySubquery` - `voltro/packages/database/src/joinCompiler.ts` — walker fallback (`attachManyToMany`) --- ## Eager loading with .with() _Single-roundtrip nested JSON queries. Per-dialect SQL shapes. Walker fallback. When NOT to eager-load + stream-cursor pagination alternatives._ `.with(spec)` is the framework's eager-load chain. The query builder reads the relations registry to produce a single SQL query that returns the parent rows + every related table the spec touches as nested JSON. ```typescript const result = await store.query( database.users.where(eq('tenantId', tenantId)).with({ profile: true, posts: { limit: 5, orderBy: [{ column: 'createdAt', direction: 'desc' }] }, organizations: true, }).descriptor, ) // TypeScript infers: // result: Array ``` One SQL round-trip. The framework's JSON-aggregation compiler renders the whole tree as a single SELECT. ## Nesting `.with()` nests arbitrarily deep: ```typescript database.users.with({ posts: { with: { author: true, comments: { with: { user: true }, limit: 10, }, }, }, }) ``` That's users → posts → (author + comments → user). Still one SQL roundtrip. The JSON shape comes back nested; the framework decodes it to typed JS objects. ## Per-dialect SQL shapes Each dialect emits its native JSON-aggregation idiom. You write the same code; the framework dispatches. ### Postgres ```sql SELECT jsonb_build_object( 'id', users.id, 'email', users.email, 'profile', (SELECT to_jsonb(p) FROM profiles p WHERE p.user_id = users.id LIMIT 1), 'posts', COALESCE((SELECT jsonb_agg(t) FROM ( SELECT * FROM posts WHERE author_id = users.id LIMIT 5 ) t), '[]'::jsonb) ) AS __row FROM users; ``` `jsonb_build_object` + `jsonb_agg` are postgres-native; the eager compiler is best-in-class here. ### MySQL 8+ ```sql SELECT JSON_OBJECT( 'id', users.id, 'email', users.email, 'profile', (SELECT JSON_OBJECT(...) FROM profiles p WHERE p.user_id = users.id LIMIT 1), 'posts', COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(...)) FROM (SELECT * FROM posts WHERE author_id = users.id LIMIT 5) t), JSON_ARRAY()) ) AS __row FROM users; ``` Functionally equivalent to postgres; the FROM-derived-table wrapper handles per-parent LIMIT. ### MariaDB 10.6+ MariaDB rejects correlated references inside non-LATERAL derived tables. The framework switches to a ROW_NUMBER window-function pattern: ```sql SELECT JSON_OBJECT( 'id', users.id, 'posts', COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(...) ORDER BY ranked.rn) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn FROM posts) ranked WHERE ranked.author_id = users.id AND ranked.rn <= 5), JSON_ARRAY()) ) AS __row FROM users; ``` For the no-pagination case, MariaDB uses `JSON_ARRAYAGG(... ORDER BY ...)` — a MariaDB-only extension that MySQL rejects — for cleaner SQL. ### MSSQL 2019+ ```sql SELECT (SELECT TOP 1 * FROM users WHERE id = u.id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS __row FROM users u; ``` MSSQL's `FOR JSON PATH` builds nested objects from the SELECT's column list; sub-aggregations become correlated `(SELECT ... FOR JSON PATH)` blocks inside CASE expressions. ### SQLite 3.38+ ```sql SELECT json_object( 'id', users.id, 'profile', (SELECT json_object(...) FROM profiles WHERE user_id = users.id LIMIT 1), 'posts', COALESCE((SELECT json_group_array(json_object(...)) FROM (SELECT * FROM posts WHERE author_id = users.id LIMIT 5) ), json('[]')) ) AS __row FROM users; ``` `json_object` + `json_group_array` are SQLite's JSON1 extension; available since 3.38. ## Walker fallback When the JSON-agg compiler can't express a particular spec — for example a relation that hasn't been registered, or a per-dialect edge case the emitter doesn't cover — the framework transparently falls back to a portable per-relation N+1 walker. Nothing in the eager spec routes here deliberately. [Junction columns](./many-to-many.md#junction-columns-in-the-result-junction) (`{ junction: [...] }` on a `manyToMany` branch) compile natively on every dialect, so asking for junction data does not reroute the query — let alone the rest of the `.with()` tree. The walker: 1. Runs the parent query without any eager loading. 2. For each declared relation in the spec, issues a follow-up query against the related table. 3. Attaches the related rows by FK in JavaScript. 4. Recurses through nested `with`. Result is identical to the JSON-agg path. Only difference is the round-trip count: N+M+K+… vs 1. The framework emits a warning when this fires: ``` mariadb JSON-agg eager-load failed; falling back to walker ``` If you see this regularly, the spec is hitting a code path the compiler doesn't cover. File an issue with the descriptor + dialect. ## When NOT to eager-load Three patterns where `.with()` is the wrong tool: ### 1. Children sets are large + unbounded ```typescript // User with 1M events — RAM explosion. database.users.with({ events: true }) ``` The JSON document per parent grows linearly with child count. At 1M events per user × 200 bytes per event = 200 MB per parent row. Use `limit:` always when the child set could grow. ### 2. You need pagination UI for the children ```typescript // User has 10k posts; UI shows 20 at a time with pagination. // Don't eager-load — query the children separately with paginateById. const [user] = await store.query(database.users.where(eq('id', uid)).descriptor) const rows = await ctx.store.query( paginateById(database.posts.where(eq('userId', uid)).descriptor, input.cursor, 20), ) const nextCursor = rows.at(-1)?.id ``` Eager-loading 10k posts to render 20 is wasteful. Cursor pagination handles it cleanly. ### 3. You want aggregations, not rows ```typescript // Want: { user, postCount, lastPostAt } — NOT { user, posts: [...] } const rows = await ctx.store.transactional(async (txn) => { const users = await txn.query(database.users.where(...)) const stats = await txn.unsafe(` SELECT user_id, COUNT(*) as post_count, MAX(created_at) as last_post_at FROM posts WHERE user_id = ANY($1) GROUP BY user_id `, [users.map(u => u.id)]) return users.map(u => ({ ...u, postCount: stats[u.id]?.post_count ?? 0, lastPostAt: stats[u.id]?.last_post_at ?? null, })) }) ``` Aggregations are a different query shape; `.with()` returns rows, not summaries. ## Type inference Schema-registered relations propagate through `.with()` at the type level: ```typescript const rows = await store.query( database.users.with({ profile: true, ownedOrgs: { with: { projects: true } }, }).descriptor, ) // rows: Array, // }> ``` Branded TypeID columns flow through — `rows[0].ownedOrgs[0].id` is `OrgId`, not just `string`. ## Performance The JSON-agg path is fast for typical app workloads (parents × ~100 KB JSON per parent). At larger scales the per-row JSON serialization becomes the dominant cost, and at that point the walker (which streams rows individually) may actually outperform JSON-agg. Benchmarks (postgres on Apple M2, app-tier instance type): | Pattern | JSON-agg | Walker (N+1) | Note | |------------------------------------------|----------|--------------|------| | 100 users × 5 posts each | 4ms | 35ms | JSON-agg wins decisively | | 1000 users × 50 posts each | 90ms | 280ms | JSON-agg still wins | | 1000 users × 5000 posts each (no limit) | 4.5s | 6s | Both pay; project narrowly | | 100 users × 100k posts each (no limit) | OOM | 12s | JSON-agg blows memory; walker survives | The framework defaults to JSON-agg because the common case wins. If you hit the OOM cliff, add `limit:` to the branch. ## Reactive subscriptions Reactive subscriptions on `.with()` queries register against EVERY table the spec touches. See [reactive](./reactive) for the dependency-graph + per-field pre-filter details. ## Where it lives - `voltro/packages/database/src/queryBuilder.ts` — `.with(spec)` chain - `voltro/packages/database/src/jsonEagerCompiler.ts` — per-dialect JSON-aggregation compilers - `voltro/packages/database/src/joinCompiler.ts` — walker fallback (`attachEagerLoads`) - `voltro/packages/sql-postgres/src/store.ts`, etc. — each store's `runWithEager()` decides JSON-agg vs walker --- ## Cascade + FK auto-index _onDelete defaults to 'restrict' for safety. FK auto-index defaults to true. Real migration scenarios + when to override._ `reference()` columns are foreign-key declarations. The framework picks two opinionated defaults that bite users from other frameworks: ```typescript export const posts = table('posts', { id: id({ prefix: 'post' }), authorId: reference(() => users, { onDelete: 'cascade', // delete author → delete posts. Default 'restrict'. onUpdate: 'noAction', // FK PK never changes in practice. Default 'noAction'. index: true, // B-tree on authorId. Default true. }), title: text(), }) ``` ## `onDelete` defaults to `'restrict'` The framework is opinionated: deleting a user shouldn't silently nuke 100k posts. `'restrict'` is the default to surface FK violations LOUDLY at delete time. | Value | Meaning | |---------------|----------------------------------------------------------------------| | `'restrict'` (default) | Refuse the delete if child rows exist. Parent delete throws. | | `'cascade'` | Delete child rows along with the parent. Use for owned lifecycles. | | `'setNull'` | Set the child's FK to NULL (column must be `.nullable()`). | | `'noAction'` | DB-level NO ACTION (postgres / mysql) — effectively same as restrict but defers the constraint check to commit. | Pick based on the lifecycle relationship between parent and child: - **`'cascade'`** for genuinely-owned children. Junction tables (memberships when a user OR org is deleted), child entities that have no meaning without the parent (a `comment_reactions` row when the comment is deleted). - **`'setNull'`** when the child outlives the parent in a degraded form. A `posts.editorId` when the editor user leaves the team — keep the post, drop the editor pointer. - **`'restrict'`** (default) when you want the framework to FORCE you to clean up explicitly. Most parent-child relationships in a typical app. ## `index: true` default Every `reference()` column gets a B-tree index automatically. The framework's [index audit](../indexes) confirms it at boot. ```typescript reference(() => users) // → CREATE INDEX posts_author_id_idx ON posts (author_id); reference(() => users, { index: false }) // → no index ``` The lookup cost of a missing FK index dwarfs the write cost of an unnecessary one for nearly every workload. Opt out only for tiny lookup tables where a full-scan beats index maintenance — `lookup_codes` (a 20-row enum-like table), `singleton_config` (one row total). Anywhere a `JOIN` would happen, keep the default. ## Real migration scenarios ### Scenario 1 — cascade through a junction ```typescript export const orgMemberships = table('org_memberships', { userId: reference(() => users, { onDelete: 'cascade' }), orgId: reference(() => orgs, { onDelete: 'cascade' }), role: text(), }) ``` Delete a user → all their memberships cascade away. Delete an org → all its memberships cascade away. Standard pattern; the junction has no meaning without both parents. ### Scenario 2 — setNull on optional ownership ```typescript export const posts = table('posts', { authorId: reference(() => users, { onDelete: 'cascade' }), // owner — cascade editorId: reference(() => users, { onDelete: 'setNull' }).nullable(), // optional → setNull }) ``` If the editor leaves the team, posts stay but lose the editor pointer. The author's deletion still removes the post. ### Scenario 3 — restrict forcing explicit cleanup ```typescript export const invoices = table('invoices', { customerId: reference(() => customers, { onDelete: 'restrict' }), // default }) // Application code: async function deleteCustomer(customerId: string) { const openInvoices = await store.query( database.invoices.where(eq('customerId', customerId)).descriptor, ) if (openInvoices.length > 0) { throw new CustomerHasOpenInvoicesError({ count: openInvoices.length }) } await ctx.store.delete('customers', customerId) } ``` The `'restrict'` default forces the application to think about what "delete customer" means. Auto-cascading invoices would corrupt accounting; auto-setNull would orphan them. The explicit check + typed error is the right contract. ## Per-dialect emission | Dialect | FK clause shape | |-----------|-----------------| | postgres | `REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION` | | mysql | ``REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION`` | | mariadb | ``REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION`` | | mssql | `REFERENCES [users]([id]) ON DELETE CASCADE` (mssql has no NO ACTION distinct from default) | | sqlite | `REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION` | SQLite requires `PRAGMA foreign_keys = ON` to enforce — the framework sets this on every connect. Without it, sqlite is permissive (treats FK constraints as documentation only). ## `onUpdate` — usually `'noAction'` FK referenced columns are IDs. IDs don't change in practice — that's the whole point of having an ID. So `onUpdate` defaults to `'noAction'` and you usually don't touch it. If you DO need to change an ID across a hierarchy (e.g. consolidating two organizations into one — `orgId: 'a' → orgId: 'b'`), the right answer is usually to: 1. Find every row that references the old ID. 2. Update them all in a single transaction. 3. Delete the now-orphaned old parent row. Not to set `onUpdate: 'cascade'` and trigger a single huge update through the DB engine. The cascade approach locks every affected table for the duration and breaks any subscriptions watching those tables. ## Composite FKs — not supported The framework's `reference()` is single-column only. If your schema has a composite FK (multi-column referential integrity), you'll have to: 1. Declare each column individually with the right type. 2. Add the FK constraint via `unsafe()` SQL in a migration. 3. Skip the framework's relation declaration for that link. This is rare. Composite FKs usually indicate a schema design that could collapse into a surrogate-key approach. ## Index audit warnings The framework's boot-time [index audit](../indexes) warns about redundant indexes — a FK auto-index that's a leading prefix of an explicit composite index is redundant: ``` [voltro:dev] WARN index audit · redundant-prefix table: memberships redundant: memberships_user_id_idx (from reference auto-index) coveredBy: memberships_user_org_idx (explicit composite on (user_id, org_id)) hint: drop one ``` Decide based on workload. If you frequently query by `user_id` alone, keep both. If the composite is the only access pattern, drop the auto-index via `{ index: false }`. ## Caveats - **Cascade can chain deeply**. A delete on `users` cascades to `memberships`, then to anything that references `memberships` with cascade, and so on. Trace the depth before you set `'cascade'` on a high-cardinality table. - **Postgres `DEFERRABLE INITIALLY DEFERRED` FKs**. The framework doesn't emit DEFERRED constraints. If you need them (mass-loading scenarios), emit them in a custom migration. - **MariaDB enforces FK constraints in storage engines that support them**. InnoDB (the default) enforces; MyISAM doesn't. Use InnoDB. The framework's DDL emitter assumes it. - **SQLite enforcement requires `PRAGMA foreign_keys = ON`**. The framework sets this at connect. Other clients that open the same database without setting the pragma bypass FK enforcement silently. ## Where it lives - `voltro/packages/database/src/columns.ts` — `reference()` accepts `onDelete` / `onUpdate` / `index` - `voltro/packages/database/src/migrate.ts` — `referentialAction(action, dialect)`; the `REFERENCES
(id)` clause is emitted inline in the column-DDL builder alongside it - `voltro/packages/database/src/indexAudit.ts` — boot-time redundant-prefix warnings --- ## Reactive invalidation _How `.with()` subscriptions wake on dependent-table changes. two-stage gate: a per-table dependency-graph plus a per-field pre-filter, soundness argument, trade-offs._ Reactive subscriptions on `.with()` queries don't just track the root table — they walk the eager spec at subscribe time, register against every dependent table, and pre-filter change events at the column level so updates that don't matter never reach the SQL planner. ## How it works When a subscription opens with `database.users.with({ posts: { with: { author: true } } })`, three things happen at subscribe time: 1. **Snapshot fetch**: the framework runs the eager-load query once, delivers the result as `_tag: 'snapshot'` to the subscriber. 2. **Dependency graph registration**: `resolveDependentTables(descriptor)` walks the spec + relations registry to produce `{users, posts}` (author resolves back to users so it deduplicates). The dispatcher registers the subscription against EACH table. 3. **Relevance map computation**: `resolveRelevantFields(descriptor)` collects, per dependent table, the column SET whose change could affect the snapshot: - Projected columns (from `descriptor.projection`; wildcard `*` when unset). - Predicate columns (every column reachable from the WHERE clause AST). - Order columns (`descriptor.order[*].column`). - Eager-load join keys (source + target FK on every relation). At runtime, every `ChangeEvent` consults this map BEFORE triggering a re-query. ## v1 — Dependency graph fan-out Stage 1 — the dependency graph tracks per-table dependency. When `posts` changes, the dispatcher finds every subscription whose dependent-table set includes `posts` and triggers a re-query. After the re-query, a shallow row-set compare (JSON.stringify per row) suppresses the delta if the result is shape-identical to the last delivered snapshot. ```text ┌───────────────────────────────────────────────────────────────────────┐ │ Subscription: users.with({ posts: { with: { author: true } } }) │ │ │ │ change(users) → re-query → diff vs lastDelivered → maybe delta │ │ change(posts) → re-query → diff vs lastDelivered → maybe delta │ │ │ │ change(orgs) → no-op (not in dependency graph) │ └───────────────────────────────────────────────────────────────────────┘ ``` This is CORRECT but expensive: every write to a dependent table costs a SQL round-trip, even when the write touched a column the subscription doesn't care about. ## Stage 2 — the per-field pre-filter The per-field pre-filter adds a column-grain gate AHEAD of the re-query. On a write: 1. `mutatedColumns(event)` returns the columns the change actually touched. - Insert / delete → wildcard `*` (membership change always relevant). - Update → diff `old` vs `new` for differing values. - Incomplete event (old=null on update) → wildcard (soundness). 2. `isEventRelevant(event, relevantMap)` intersects mutated × relevant. 3. Empty intersection → **skip the re-query entirely**. No SQL round-trip. No deep-equal compare. No delta. ```text relevantMap = { users: { id, email, *projected, …predicate, …order }, posts: { id, author_id, *projected, …predicate, …order }, } mutatedColumns(event{table: 'users', op: 'update', old: {x:1}, new: {x:2}}) = { 'x' } isEventRelevant ⇒ 'x' ∉ relevantMap.users ⇒ skip ``` The dispatcher logs the skip count per change event so operators can verify the filter is paying off: ```sh voltro logs --tail 50 | grep handleChange/prefiltered ``` ## Soundness The relevance set is a SUPER-set of fields whose change can flip the snapshot. The collection rules (`resolveRelevantFields`) overshoot deliberately: - **Predicate columns**: a write to a predicate column may change set membership, even if the value the predicate compares against doesn't change. - **Order columns**: a write to an order column may shift the row's position, changing the slice the subscription sees. - **Eager-load join keys (FK + PK on each side)**: a write to a FK may shift which children belong to which parent. - **Projected columns**: if the snapshot emits the column, every change to it is by definition a delta. - **`*` wildcard when projection is unset**: the snapshot emits the full row, so every column is potentially load-bearing. Skipping a write that lies OUTSIDE this set cannot produce a missed delta: the resulting snapshot was already correct. The pre-filter has zero false negatives. The only failure mode is a false positive (slower v1 path runs unnecessarily) — no correctness consequence, just wasted work. ## Insert / delete always pass through Membership changes are load-bearing regardless of which columns the row carries. The pre-filter treats insert and delete as wildcard-relevant, so they always reach the re-query path. v1's deep-equal compare still suppresses the DELTA if the snapshot happens to be shape-identical (rare on insert/delete but possible if the new/deleted row was outside the subscription's predicate set). ## Walking the eager spec For each relation in the `.with()` spec, the relevance walker adds: - **`one()`**: source's `sourceKey` (or `id` if not specified) + target's `id` + target's projected/predicate/order columns + target's own eager-load contributions. - **`many()`**: target's `foreignKey` (the FK on the target side) + target's `id` + target's per-branch where/order/limit columns + target's full schema columns (since `with: true` doesn't constrain projection). - **`manyToMany()`**: junction's `sourceKey` + junction's `targetKey` + target's `id` + target's per-branch contributions. For very deep nested specs (depth > 3), the relevance set can include MANY tables. The dispatcher's per-table lookup is `O(1)` so the cost is dominated by the actual column intersection check, not by tree depth. ## When the pre-filter helps the most The pre-filter is most effective when: - Subscriptions read NARROW projections (`projection: ['id', 'email']`) — every column-narrow write to other fields is skipped. - Tables have schema-wide hot columns (`updatedAt`, `lastSeenAt`, view counters) that lots of writes touch but no subscription reads. - Eager-loaded child tables have high write rates (chat messages, log entries) where most writes touch fields the parent doesn't project. ## When the pre-filter helps less - Subscriptions without projection (the default — `with: true` on every branch reads every column). The relevance set is wildcard everywhere; v1 fan-out kicks in for every dependent change. - Hot tables where every write touches at least one relevant column (every row carries `updated_at = NOW()` on every update, and `updated_at` is in the order clause). For these cases, mitigations: 1. **Project narrowly.** `projection: ['id', 'email']` on the subscription's descriptor narrows the relevance set. 2. **Split the subscription.** `database.users.where(eq('id', uid))` (no `.with()`) for the root, `database.orgs.where(...)` for the children. Each fires only on its own table. 3. **`unsafe()` for hand-tuned queries** when the framework's portable shape doesn't match what you need. ## Observability Two ways to verify the filter is working: ### Trace logs ```sh voltro logs --tail 100 | grep handleChange ``` Look for the per-change-event summary: ``` { "table": "posts", "op": "update", "matcher": 3, "dependent": 12, "triggered": 15 } { "table": "posts", "op": "update", "skipped": 11 } ``` `triggered` is how many subscriptions had `posts` in their dependent-table set. `skipped` is how many of those the pre-filter filtered out before the re-query. Ideal ratio: high `skipped` / `triggered` on hot tables. ### Voltro Cloud dashboard The reactive subscriptions panel (under the per-app Inspect tab) shows per-subscription details: - Dependent table count. - Relevant columns per table. - Skip rate (recent window). Use these to find subscriptions that aren't benefiting from the pre-filter and need narrowing. ## Where it lives - `voltro/packages/runtime/src/relevantFields.ts` — relevance walker + `isEventRelevant` + `mutatedColumns` - `voltro/packages/runtime/src/dependencyGraph.ts` — `resolveDependentTables` v1 walker - `voltro/packages/runtime/src/dispatcher.ts` — `handleChange` pre-filter call + skip-count log - `voltro/packages/runtime/src/dispatcherReactive.test.ts` — dependency-graph + pre-filter tests