VoltroDocumentation

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:

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.

// 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 
// 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:

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:

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

// 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<User & {
//   profile: Profile | null,
//   posts: Post[],
//   organizations: Org[],
// }>

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?

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