one() — 1:1 and N:1

one(target) declares that for one row in the source table, there's at most ONE related row in the target. The resolved value is Target | null — null when no related row exists.

relations(users, ({ one }) => ({
  profile:    one(profiles),                                     // 1:1, FK on profiles
  defaultOrg: one(orgs, { sourceKey: 'defaultOrgId' }),          // N:1, FK on users
}))

The framework derives the join direction from the existence of FK columns; if it can't disambiguate, you pass sourceKey (FK is on the SOURCE table pointing at the TARGET) or foreignKey (FK is on the TARGET table pointing at the SOURCE).

#Two shapes

one() collapses two different relationship cardinalities into one DSL — they have the same shape from the application's perspective (single related row) but the SQL emitted is different.

#Shape A — FK on the target (1:1 / 0..1:1)

Profile owns its FK to user. One user has zero or one profile.

export const profiles = table('profiles', {
  id:     id({ prefix: 'profile' }),
  userId: reference(() => users, { onDelete: 'cascade' }),  // ← FK
  bio:    text(),
})

relations(users, ({ one }) => ({
  profile: one(profiles),    // framework finds userId on profiles → join target.userId = source.id
}))

SQL emitted:

(SELECT row_to_json(p) FROM profiles p WHERE p.user_id = users.id LIMIT 1)

The framework auto-derives this when the target table has exactly ONE reference() column pointing at the source. If there are multiple FKs from target → source, pass foreignKey::

// posts has both authorId AND editorId → user
relations(users, ({ one }) => ({
  authoredFirstPost: one(posts, { foreignKey: 'authorId' }),
  editedFirstPost:   one(posts, { foreignKey: 'editorId' }),
}))

#Shape B — FK on the source (N:1)

User owns the FK to a default org. Many users can point at the same org.

export const users = table('users', {
  id:           id(),
  defaultOrgId: reference(() => orgs).nullable(),  // ← FK
  email:        text(),
})

relations(users, ({ one }) => ({
  defaultOrg: one(orgs, { sourceKey: 'defaultOrgId' }),
}))

SQL emitted:

(SELECT row_to_json(o) FROM orgs o WHERE o.id = users.default_org_id LIMIT 1)

The framework needs sourceKey: because the FK is on the source side, pointing at the target's id. Without the hint, the framework would look for FKs on the target table back at the source.

#Result type

const result = await store.query(
  database.users.with({ profile: true, defaultOrg: true }).descriptor,
)
// result: Array<User & {
//   profile: Profile | null,
//   defaultOrg: Org | null,
// }>

Always Target | null — even on tables where you "expect" the relation to exist. Application code MUST handle the null case; the framework doesn't model "required 1:1" as a different cardinality.

#Owning side vs optional side

For 1:1 relationships, which side owns the FK is a schema design decision the framework doesn't force:

• Owning side: holds the FK column. Inserts on this side reference the target.
• Optional side: holds no FK. The relation flows through the owning side's FK on read.

Practical rule of thumb: put the FK on the smaller-cardinality side. If every user has at most one profile, the FK goes on profiles (which is cardinality-bound by users). If you flipped it — users.profileId — you'd need to manage the order of creation (insert profile first, then user with profileId) instead of the typical sequence (insert user, then optional profile).

#Mutating through the relation

The framework's mutation surface doesn't have a "set profile" shortcut. You write the underlying INSERT / UPDATE explicitly:

// Create user + profile. Pre-compute the id so `profiles.userId` can
// reference it in the same flow.
import { typeid } from 'typeid-js'
const userId = typeid('user').toString()
await ctx.store.insert('users', { id: userId, email })
await ctx.store.insert('profiles', { userId, bio })

// Change a user's default org.
await ctx.store.update('users', userId, { defaultOrgId: newOrgId })

This is on purpose — the relation declaration describes the SHAPE of the data, not the WAY mutations propagate. Cascade behaviour is controlled by onDelete: on the reference() column (see cascade), not by the relation.

#Eager loading with .with({ profile: true })

const users = await store.query(
  database.users.where(eq('tenantId', tenantId)).with({ profile: true }).descriptor,
)
// users: Array<User & { profile: Profile | null }>

The framework's JSON-agg compiler emits a single SELECT with the profile as a nested subquery. No N+1 round-trips.

For per-branch filtering / ordering on the related row, pass an object instead of true:

database.users.with({
  profile: { where: eq('verified', true) },
})

The framework applies the where clause inside the per-row subquery. If the profile doesn't match, the resolved value is null — same as if no profile existed.

#Nested .with()

The related row can carry its own .with() for transitive relations:

database.users.with({
  profile: {
    with: { avatar: true },   // profile → avatar
  },
})
// → Array<User & {
//     profile: (Profile & { avatar: Avatar | null }) | null,
//   }>

Recursion depth is arbitrary. Each nested branch becomes another nested subquery in the same SELECT — the framework's compiler walks the spec to any depth.

#Reactive subscriptions

A subscription opened with .with({ profile: true }) registers against BOTH users and profiles. Changes to either table re-run the query (narrowed by the per-field pre-filter — see reactive).

#Caveats

• Two one() calls on the same target table need distinct relation names. The framework keys eager loads by relation name, not by target table — database.users.with({ profile: true }) reads the relation named 'profile'. If you declare both defaultOrg and billingOrg both pointing at orgs, they're distinct relations and with({ defaultOrg: true, billingOrg: true }) reads them independently.
• Self-referential 1:1 needs the thunk form. one(() => users) for a "parent user" lookup. Without the thunk, the table reference would try to resolve before the table is registered and fail.
• one() on a target that has a many-to-the-same-source relation is unusual but legal. The framework reads the relation declaration verbatim. If the data actually has two profiles for one user, an eager-load branch served by the walker now fails rather than handing you an arbitrary one of them — see the one cardinality contract. A branch the JSON-aggregation compiler can express resolves it in SQL instead (LIMIT 1 in the subquery), and the extra row is invisible there. Use many() if multiplicity is genuinely possible.

#Where it lives

• voltro/packages/database/src/relations.ts — oneBuilder (line 100)
• voltro/packages/database/src/jsonEagerCompiler.ts — per-dialect *OneSubquery (postgres, mysql, mssql, sqlite)
• voltro/packages/database/src/joinCompiler.ts — walker fallback for one() when the JSON-agg compiler can't dispatch