many() — 1:N

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

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

const result = await store.query(
  database.users.with({ posts: true }).descriptor,
)
// result: Array<User & { posts: Post[] }>

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.

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:

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

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:

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

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

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:

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

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:

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

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)