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