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.

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

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

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

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:

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

Per-branch modifiers

where / orderBy / limit / offset apply to the TARGET table — not the junction:

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:

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:

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:

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:

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

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:

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:

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

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) reduces but doesn't eliminate the cost. For very-hot-junction patterns, split the subscription.

TypeScript inference

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

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

manyToMany() — N:M

#Why explicit through-table

#Writing links — store.links

#SQL shape

#Per-branch modifiers

#Junction columns in the result — junction

#Why nested under _junction and not merged

#The cost — none; it stays one query

#Doubly-declared

#Inserting a membership

#Removing a membership

#Reactive subscriptions

#TypeScript inference

#Caveats

#Where it lives