Relations

In this guide, we'll learn how to define relationships between tables. You'll master one-to-one, one-to-many, and many-to-many relations, self-references, and eager loading with with:.

Overview

Relations in the ORM use the same API as Drizzle for defining and loading relationships. The with: option is type-safe, with a few nested constraints noted below.

Relation loading via with: works end-to-end. Nested with has a depth limit, and per-relation filters for many() relations are applied post-fetch.

Relation loading requires indexes on relation fields (e.g. posts.userId). Missing indexes throw unless you opt in with allowFullScan. You can use defineSchema(..., { strict: false }) to downgrade throws to warnings when allowFullScan is set.

Relations are about loading related rows. They do not enforce referential integrity. If you want runtime checks / cascades, define foreign keys separately with .references() / foreignKey() (see /docs/orm/indexes-constraints).

Loading many() relations requires explicit sizing: provide with.<relation>.limit, a schema defaults.defaultLimit, or allowFullScan on the parent query.

See Schema Options for strict policy details.

Relation Config Options

The callback shape mirrors Drizzle's relation builder, but in kitcn you attach relations directly to the default schema export with .relations(...).

`one()`

Here's the full set of options for a one() relation:

r.one.users({
  from: r.posts.authorId,
  to: r.users.id,
  optional: false,
  alias: 'author',
  where: { isActive: true },
})

from / to: describe the relationship using columns
optional: type-level nullability (true by default)
alias: disambiguate multiple relations between the same tables
where: conditional relation loading (filter which related rows are loaded)

`many()`

And the options for a many() relation:

r.many.posts({
  from: r.users.id,
  to: r.posts.authorId,
  alias: 'posts',
  where: { published: true },
})

from / to: optional when the inverse relation is unambiguous
alias: disambiguate multiple relations between the same tables
where: filter which related rows are loaded

Disambiguating Relations With `alias`

If you have multiple relations between the same two tables, you must use alias to tell the ORM which relation is which.

Classic example: a post has both an author and a reviewer, pointing at the same users table.

convex/schema.ts

import { convexTable, defineSchema, id, text } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const posts = convexTable('posts', {
  title: text().notNull(),
  authorId: id('users').notNull(),
  reviewerId: id('users'),
});

export default defineSchema({ users, posts }).relations((r) => ({
  users: {
    // No need for from/to here: these infer from the reverse one() relations.
    authoredPosts: r.many.posts({ alias: 'author' }),
    reviewedPosts: r.many.posts({ alias: 'reviewer' }),
  },
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
      alias: 'author',
    }),
    reviewer: r.one.users({
      from: r.posts.reviewerId,
      to: r.users.id,
      alias: 'reviewer',
    }),
  },
}));

alias does not change your output keys (author, reviewer, authoredPosts, reviewedPosts). It only disambiguates the underlying edge metadata.

Relation Types

One-to-One Relations

A one-to-one relation connects exactly one record in each table. Here's a user with a single profile:

convex/schema.ts

import { convexTable, defineSchema, text, id } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const profiles = convexTable('profiles', {
  bio: text().notNull(),
  userId: id('users'),
});

export default defineSchema({ users, profiles }).relations((r) => ({
  profiles: {
    user: r.one.users({
      from: r.profiles.userId,
      to: r.users.id,
    }),
  },
  users: {
    profile: r.one.profiles(),
  },
}));

Note: Use from and to to describe the foreign key relationship. In Convex, primary keys are id.

Important: Enforce a unique constraint on the foreign key column (.unique() / uniqueIndex()) for true 1:1 semantics. Runtime uniqueness is best-effort; concurrent mutations can still race.

One-to-Many Relations

The most common relation type - one user has many posts. Let's define both sides:

convex/schema.ts

import { convexTable, defineSchema, text, boolean, id } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const posts = convexTable('posts', {
  title: text().notNull(),
  content: text().notNull(),
  published: boolean(),
  userId: id('users'),
});

export default defineSchema({ users, posts }).relations((r) => ({
  users: {
    posts: r.many.posts(),
  },
  posts: {
    author: r.one.users({
      from: r.posts.userId,
      to: r.users.id,
    }),
  },
}));

Key Points:

Foreign key is on the "many" side: posts.userId references users.id
Define both sides: Define relations on both users and posts
Export both: Both table and relations object must be in schema export

Many-to-Many Relations (Join Table)

The ORM does not auto-create join tables. You model many-to-many with an explicit join table:

convex/schema.ts

import { convexTable, defineSchema, text, id } from 'kitcn/orm';

const posts = convexTable('posts', {
  title: text().notNull(),
});

const tags = convexTable('tags', {
  name: text().notNull(),
});

const postsTags = convexTable('postsTags', {
  postId: id('posts'),
  tagId: id('tags'),
});

export default defineSchema({ posts, tags, postsTags }).relations((r) => ({
  posts: {
    tags: r.many.postsTags(),
  },
  tags: {
    posts: r.many.postsTags(),
  },
  postsTags: {
    post: r.one.posts({ from: r.postsTags.postId, to: r.posts.id }),
    tag: r.one.tags({ from: r.postsTags.tagId, to: r.tags.id }),
  },
}));

Many-to-Many With Data (Join Table + Payload)

If you need extra data on the relationship (role, timestamps, etc.), put it on the join table. No special API required:

convex/schema.ts

import { convexTable, defineSchema, id, integer, text } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const groups = convexTable('groups', {
  name: text().notNull(),
});

const memberships = convexTable('memberships', {
  userId: id('users').notNull(),
  groupId: id('groups').notNull(),
  role: text().notNull(),
  joinedAt: integer().notNull(),
});

export default defineSchema({ users, groups, memberships }).relations((r) => ({
  users: {
    memberships: r.many.memberships(),
  },
  groups: {
    memberships: r.many.memberships(),
  },
  memberships: {
    user: r.one.users({ from: r.memberships.userId, to: r.users.id }),
    group: r.one.groups({ from: r.memberships.groupId, to: r.groups.id }),
  },
}));

You can then load the join table payload alongside nested relations:

convex/queries.ts

const usersWithGroups = await db.query.users.findMany({
  with: {
    memberships: {
      columns: { role: true, joinedAt: true },
      with: { group: true },
    },
  },
});

Many-to-Many Relations with `.through()`

You can also define direct many-to-many relations with .through() to point at junction table columns:

convex/schema.ts

import { boolean, convexTable, defineSchema, id, text } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const groups = convexTable('groups', {
  name: text().notNull(),
});

const usersToGroups = convexTable('usersToGroups', {
  userId: id('users').notNull(),
  groupId: id('groups').notNull(),
});

export default defineSchema({ users, groups, usersToGroups }).relations((r) => ({
  users: {
    groups: r.many.groups({
      from: r.users.id.through(r.usersToGroups.userId),
      to: r.groups.id.through(r.usersToGroups.groupId),
      alias: 'users-groups-direct',
    }),
  },
  groups: {
    users: r.many.users({
      from: r.groups.id.through(r.usersToGroups.groupId),
      to: r.users.id.through(r.usersToGroups.userId),
      alias: 'users-groups-direct',
    }),
  },
}));

Self-Referencing Relations

You can define relations where a table references itself. Here's a manager/reports hierarchy:

convex/schema.ts

import { convexTable, defineSchema, text, id } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
  username: text().notNull(),
  managerId: id('users'),
});

export default defineSchema({ users }).relations((r) => ({
  users: {
    manager: r.one.users({
      from: r.users.managerId,
      to: r.users.id,
      alias: 'manager',
    }),
    reports: r.many.users({
      from: r.users.id,
      to: r.users.managerId,
      alias: 'manager',
    }),
  },
}));

Important: Use alias to distinguish self-referencing relations. Unique names are required to disambiguate.

Optional `one()` Relations

By default, one() relations are optional and return null when no match is found. Use optional: false when the relation is required:

convex/schema.ts

import { convexTable, defineSchema, id, text } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const posts = convexTable('posts', {
  title: text().notNull(),
  authorId: id('users').notNull(),
});

export default defineSchema({ users, posts }).relations((r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
      optional: false,
    }),
  },
}));

Relation Definition Rules

Required Fields

Define the one() side with from and to. The many() side can be inferred from the inverse relation:

export default defineSchema({ users, posts }).relations((r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.userId,
      to: r.users.id,
    }),
  },
  users: {
    posts: r.many.posts(),
  },
}));

Export Tables and Schema

Make sure to export both tables and relations from your schema:

export default defineSchema({ users, posts }).relations((r) => ({
  users: { posts: r.many.posts() },
  posts: { author: r.one.users({ from: r.posts.userId, to: r.users.id }) },
}));

Split Relations with `defineRelationsPart`

Use defineRelationsPart to split large relation graphs across modules and merge them later:

convex/relations.ts

import { defineSchema, defineRelationsPart } from 'kitcn/orm';
import { users, posts, comments } from './schema';

const userRelations = defineRelationsPart({ users, posts }, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),
  },
}));

const postRelations = defineRelationsPart({ posts, comments }, (r) => ({
  posts: {
    comments: r.many.comments({
      from: r.posts.id,
      to: r.comments.postId,
    }),
  },
}));

export default defineSchema({ users, posts, comments }).relations((r) => ({
  users: userRelations.users.relations,
  posts: {
    ...userRelations.posts.relations,
    ...postRelations.posts.relations,
  },
  comments: postRelations.comments.relations,
}));

Tip: If a relation relies on inference (no from/to), define both sides in the same part or use explicit from/to.

Bidirectional Definitions

Define both sides when you want with: loading in both directions.

One-Way Definitions

You can define relations only on the side you need. When you do, provide explicit from/to (and alias if ambiguous). The inverse side is optional and won't be available for with: unless you define it.

Predefined Filters

You can attach a predefined where filter to a relation. It will always be applied when loading:

convex/schema.ts

import { boolean, convexTable, defineSchema, id, text } from 'kitcn/orm';

const users = convexTable('users', {
  name: text().notNull(),
});

const posts = convexTable('posts', {
  title: text().notNull(),
  authorId: id('users'),
  published: boolean().notNull(),
});

export default defineSchema({ users, posts }).relations((r) => ({
  users: {
    publishedPosts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
      where: { published: true },
      alias: 'published-posts',
    }),
  },
}));

where in a relation definition can only reference columns on the target (to) table. In the ORM, it's applied after fetching candidate rows, so keep many() relations intentionally sized.

Polymorphic Associations

Polymorphism is schema-first via a discriminator column builder. Instead of manually wiring nullable FKs, a discriminator, and check constraints, you declare variants directly in the table definition with discriminator().

Defining a Polymorphic Table

Use one nullable FK per target plus a discriminator. This gives polymorphic-style relation loading and filtered aliases, without strict one-of enforcement.

convex/schema.ts

import { convexTable, defineSchema, id, text } from 'kitcn/orm';

const posts = convexTable('posts', {
  title: text().notNull(),
});

const videos = convexTable('videos', {
  title: text().notNull(),
});

const comments = convexTable('comments', {
  body: text().notNull(),
  targetType: text().notNull(), // 'post' | 'video'
  postId: id('posts'),
  videoId: id('videos'),
});

export default defineSchema({ posts, videos, comments }).relations((r) => ({
  posts: {
    comments: r.many.comments({
      from: r.posts.id,
      to: r.comments.postId,
      where: { targetType: 'post' },
      alias: 'post-comments',
    }),
  },
  videos: {
    comments: r.many.comments({
      from: r.videos.id,
      to: r.comments.videoId,
      where: { targetType: 'video' },
      alias: 'video-comments',
    }),
  },
}));

Step 2: Strict (Schema-Enforced)

Add FK references, indexes, and check(...) constraints when a row must point to exactly one target and match the discriminator.

We'll use discriminator() to define an audit log where each row has an actionType discriminator and variant-specific fields:

convex/schema.ts

import {
  boolean,
  convexTable,
  discriminator,
  id,
  index,
  integer,
  text,
} from 'kitcn/orm';

const auditLogs = convexTable(
  'audit_logs',
  {
    timestamp: integer().notNull(),
    actionType: discriminator({
      as: 'details', // optional alias, defaults to "details"
      variants: {
        role_change: {
          targetUserId: id('users'),
          oldRole: text().notNull(),
          newRole: text().notNull(),
        },
        document_update: {
          documentId: id('documents'),
          version: integer().notNull(),
          changes: text().notNull(),
        },
        security_alert: {
          severity: text().notNull(),
          errorCode: text().notNull(),
          isResolved: boolean().notNull(),
        },
      },
    }),
  },
  (t) => [
    index('by_action_ts').on(t.actionType, t.timestamp),
    index('by_role_target').on(t.actionType, t.targetUserId),
    index('by_doc').on(t.actionType, t.documentId),
  ]
);
export default defineSchema({ posts, videos, comments }).relations((r) => ({
  posts: {
    comments: r.many.comments({
      from: r.posts.id,
      to: r.comments.postId,
      where: { targetType: 'post' }, // optional filtered alias
      alias: 'post-comments',
    }),
  },
  videos: {
    comments: r.many.comments({
      from: r.videos.id,
      to: r.comments.videoId,
      where: { targetType: 'video' }, // optional filtered alias
      alias: 'video-comments',
    }),
  },
  comments: {
    post: r.one.posts({
      from: r.comments.postId,
      to: r.posts.id,
    }),
    video: r.one.videos({
      from: r.comments.videoId,
      to: r.videos.id,
    }),
  },
}));

How it works:

Storage is flat. The discriminator (actionType) and all variant fields (targetUserId, documentId, etc.) are stored as top-level document fields. Variant-specific fields are nullable at the storage level.
Reads synthesize nested data. When you query, the ORM groups variant fields into a nested details object (or your custom as alias) as a typed discriminated union.
Variant fields are available for indexes and filters. Generated variant columns like t.targetUserId and t.documentId are normal top-level refs you can use in index(), check(), and where.

Querying Polymorphic Tables

Polymorphism is resolved automatically from the schema. Use withVariants: true when you also want automatic variant relation loading:

const rows = await ctx.orm.query.audit_logs.findMany({
  limit: 20,
  withVariants: true,
});

for (const row of rows) {
  if (row.actionType === 'role_change') {
    row.details.targetUserId;
    row.details.oldRole;
  } else if (row.actionType === 'document_update') {
    row.details.documentId;
    row.details.version;
  }
}

Writing to Polymorphic Tables

Writes are flat — you set the discriminator and the fields for that variant:

await ctx.orm.insert(auditLogs).values({
  timestamp: Date.now(),
  actionType: 'role_change',
  targetUserId: someUserId,
  oldRole: 'member',
  newRole: 'admin',
});

The ORM validates writes at runtime: required fields for the active variant must be present, and fields from other variants must not be set.

Rules

One discriminator() discriminator column per table (current limit)
Variant keys become the discriminator's literal values
Variant fields are generated as nullable physical columns
.notNull() on a variant field means required in that branch only
Duplicate field names across variants must have identical builder signatures
The alias (as) cannot collide with column names, relation names, with, or extras

Note: Query config does not include a polymorphic option. Polymorphism is defined entirely in schema columns.

For the discriminator() builder API, see Column Types -- discriminator().

For index requirements per relation type, see API Reference below.

Relation Loading with `with:`

The ORM supports Drizzle-style relation loading. Here's how to eagerly load posts for each user:

const usersWithPosts = await ctx.orm.query.users.findMany({
  with: {
    posts: {
      limit: 5,
      orderBy: { createdAt: 'desc' },
    },
  },
});

Note: limit, orderBy, and where apply to many() relations. If limit is omitted, configure defineSchema(..., { defaults: { defaultLimit } }) or use allowFullScan on the parent query. Relation loading also fail-fasts when unique lookup keys exceed defineSchema(..., { defaults: { relationFanOutMaxKeys } }) (default 1000) unless allowFullScan is set. Nested with: works with a depth limit to prevent infinite recursion.

Column Selection Semantics

columns follows Drizzle selection rules. You can include specific columns, exclude them, or select none:

convex/queries.ts

const users = await db.query.users.findMany({
  columns: { name: true, email: true }, // include-only
});

const usersNoEmail = await db.query.users.findMany({
  columns: { email: false }, // exclude-only
});

const usersOnlyPosts = await db.query.users.findMany({
  columns: {}, // no table columns
  with: { posts: true },
});

Note: columns only affects table columns. Relation data and extras are preserved.

API Reference

Indexing For Relation Loading

The ORM will use indexes when loading relations and will throw (unless allowFullScan) if the needed index is missing.

For compound relations, your index must start with the same columns in the same order as to / .through(...).
many() (one-to-many): index the child foreign key (example: posts.userId)
.through() (many-to-many): index the junction table foreign keys you query by (usually both directions; add a compound index if you also query by both)
one(): to: ...id uses db.get() (no extra index); non-id targets require an index on the target fields

convex/schema.ts

import { convexTable, id, index, text } from 'kitcn/orm';

export const posts = convexTable(
  'posts',
  {
    title: text().notNull(),
    userId: id('users').notNull(),
  },
  (t) => [index('by_user').on(t.userId)]
);

export const usersToGroups = convexTable(
  'usersToGroups',
  {
    userId: id('users').notNull(),
    groupId: id('groups').notNull(),
  },
  (t) => [
    index('by_user').on(t.userId),
    index('by_group').on(t.groupId),
    index('by_user_group').on(t.userId, t.groupId),
  ]
);

Common Gotchas

Here's a quick reference for the most frequent issues:

Issue	Solution
Relations not working	Define relations in `defineSchema(...).relations(...)` and verify both sides (or explicit `from`/`to`)
Foreign key field missing	Define the FK column yourself (e.g., `userId: id('users')`)
Type errors in `from`/`to`	Ensure `from` columns belong to the source table and `to` columns belong to the target table
Self-referencing conflicts	Use unique `alias` for each self-referencing relation
`with:` not working	Ensure relations are defined on both tables (or explicit `from`/`to`)
`many()` relation throws sizing error	Add `with.<relation>.limit`, configure `defaults.defaultLimit`, or set `allowFullScan`
Relation fan-out guardrail throws	Reduce source cardinality, raise `defaults.relationFanOutMaxKeys`, or set `allowFullScan`

You now know how to define every kind of relation and load them eagerly with with:.

Relations

Overview

Relation Config Options

`one()`

`many()`

Disambiguating Relations With `alias`

Relation Types

One-to-One Relations

One-to-Many Relations

Many-to-Many Relations (Join Table)

Many-to-Many With Data (Join Table + Payload)

Many-to-Many Relations with `.through()`

Self-Referencing Relations

Optional `one()` Relations

Relation Definition Rules

Required Fields

Export Tables and Schema

Split Relations with `defineRelationsPart`

Bidirectional Definitions

One-Way Definitions

Predefined Filters

Polymorphic Associations

Defining a Polymorphic Table

Step 2: Strict (Schema-Enforced)

Querying Polymorphic Tables

Writing to Polymorphic Tables

Rules

Relation Loading with `with:`

Column Selection Semantics

API Reference

Indexing For Relation Loading

Common Gotchas

Next Steps

Querying Data

Schema Definition

Limitations

Drizzle Comparison

On this page