Joins & relations

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 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.

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

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:

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 page; the shape:

import { columnRef, eq, queryFor } from '@voltro/database'

const rows = await ctx.store.query(
  queryFor(database.messages).as('m')
    .innerJoin(database.users, 'u', eq('u.id', columnRef('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.

#Exact bigint projections

queryFor(table) keeps the declared column metadata, including when the table has not been registered globally. Explicit joins that receive a table descriptor retain that metadata too. On SQLite, declared bigint() columns are projected as strings before the driver can round them to JavaScript numbers. This also applies to selectJoined() output aliases; ordering still compares the stored integers numerically. Unmatched left-join values remain null.

Name-only query descriptors and joins still require registered table definitions to identify bigint columns. Pass the table descriptor when it is available. This does not change decimal storage or aggregate precision.