Skip to content

Relational Queries ​

db.query.<table>.findMany({ with }) (plus findFirst / findUnique) is the relational read API on @forinda/kickjs-db. It compiles to a single round trip per call — one query that aggregates parents and every nested level of children into a JSON tree the client decodes.

The runtime ships for PostgreSQL, SQLite, and MySQL 8+ as of v5.4.0.

When to reach for it ​

PatternUse thisUse the query builder
"Get task X plus its comments, assignees, labels"db.query.tasks.findUnique({ with: { … } })—
"Get the 20 newest projects with their tasks and lead user"db.query.projects.findMany({ with: { … } })—
"Count tasks by status across a workspace"—db.selectFrom('tasks').select(...)
"Update + return"—db.updateTable(...).returningAll()

The relational layer is read-only. Inserts, updates, deletes, and any non-trivial aggregation continue to flow through the query builder — db.selectFrom / insertInto / updateTable etc. The two coexist on the same KickDbClient.

First findMany ​

ts
import { Service, Inject } from '@forinda/kickjs'
import { DB_PRIMARY, desc, type KickDbClient } from '@forinda/kickjs-db'

@Service()
export class WorkspacesRepository {
  constructor(@Inject(DB_PRIMARY) private readonly db: KickDbClient) {}

  recentWorkspaces() {
    return this.db.query.workspaces.findMany({
      orderBy: (_w, eb) => desc(eb.ref('createdAt')),
      limit: 20,
    })
  }
}

The return type narrows from the inferred schema — column types from the DSL flow through. No as casts.

Choosing a relation ​

with reads the relations declared with relations(): one for at most one row (many-to-one, one-to-one), many for any number (one-to-many), many(…, { through }) for many-to-many, and morphMany / morphOne / morphTo for the same three over a type + id column pair. Relations walks through each one on a single schema, with the query and what it returns.

Adding with ​

with walks the relations declared in your relations() blocks. Once kick typegen has run, the keys are checked — so misspelling a relation key is a compile error, not a runtime surprise.

ts
this.db.query.workspaces.findUnique({
  where: (_w, eb) => eb('id', '=', id),
  with: {
    owner: true, // one-relation → workspace.owner: User
    members: true, // many-relation → workspace.members: Member[]
    projects: true, // many-relation → workspace.projects: Project[]
  },
})

One round trip. Every nested column is included in the inner JSON.

Nested with ​

Pass an object instead of true to keep walking:

ts
this.db.query.tasks.findUnique({
  where: (_t, eb) => eb('id', '=', id),
  with: {
    comments: {
      with: { author: true }, // each comment carries its author
    },
    assignees: {
      with: { user: true },
    },
    labels: true,
  },
})

Each level pushes another nested LATERAL (PG) / correlated subquery (SQLite/MySQL). Default depth limit is 5; raise it with maxDepth on the call options when you need deeper nesting.

Per-relation where, orderBy, limit ​

Inside a with value you can constrain the inner subquery the same way as the outer:

ts
this.db.query.users.findUnique({
  where: (_u, eb) => eb('id', '=', userId),
  with: {
    posts: {
      where: (_p, eb) => eb('publishedAt', 'is not', null),
      orderBy: (_p, eb) => desc(eb.ref('publishedAt')),
      limit: 5,
    },
  },
})

Each clause scopes only to the inner relation — outer filters keep working independently.

orderBy returns an expression, ascending by default. Wrap it in desc() (or asc(), both from @forinda/kickjs-db) for a direction, and return an array to sort by several: [desc(eb.ref('priority')), asc(eb.ref('title'))].

Paginating nested rows ​

A post with thousands of comments shouldn't load them all. limit and offset inside a with apply per parent, so every row gets its own page:

ts
const rows = await db.query.posts.findMany({
  extras: {
    // The total, for "show all 50 comments".
    commentCount: (_p, eb) =>
      eb
        .selectFrom('comments')
        .select(eb.fn.countAll<number>().as('n'))
        .where('comments.postId', '=', sql.ref<number>('posts_0.id')),
  },
  with: {
    comments: { orderBy: (_c, eb) => desc(eb.ref('id')), limit: 5 },
  },
})
// [
//   { title: 'Busy', commentCount: 50, comments: [/* ids 50, 49, 48, 47, 46 */] },
//   { title: 'Quiet', commentCount: 3, comments: [/* ids 53, 52, 51 */] },
// ]

offset skips within each parent's rows too: { limit: 5, offset: 5 } gives the busy post comments 45 to 41.

That covers the first page, read together with the parent. For the pages after it, query the child table directly. Its own where is cheaper than walking through the parent again, and it can return the total:

ts
// Page 3 of a post's comments, 20 per page, with the total.
const { data, total } = await db.query.comments.findManyAndCount({
  where: (_c, eb) => eb('postId', '=', postId),
  orderBy: (_c, eb) => desc(eb.ref('id')),
  limit: 20,
  offset: 40,
})

{ data, total } is the shape ctx.paginate takes. offset reads and discards every row before the page, so deep pages slow down. For infinite scroll or a "load more" button, page by cursor instead: the client sends the last id it has, and the query continues after it.

ts
const next = await db.query.comments.findMany({
  where: (_c, eb) => eb.and([eb('postId', '=', postId), eb('id', '<', lastSeenId)]),
  orderBy: (_c, eb) => desc(eb.ref('id')),
  limit: 20,
})

Either way, index the foreign key together with the sort column, so each page is read in order from the index rather than sorted: index('comments_post_id_idx').on(t.postId, t.id).

  • Polymorphic children (morphMany) page the same way. In the direct query, filter on the pair: eb('subjectType', '=', 'post') and eb('subjectId', '=', postId). Index (subjectType, subjectId, id).
  • On Postgres the top-level count comes back as a string (a bigint). Cast it with count(*)::int or Number(); see Choosing fields.
  • A morphTo subject takes no limit or offset: it is one row.

Choosing fields ​

columns picks which columns come back, and extras adds computed fields. Both work at every level of with:

ts
import { sql } from '@forinda/kickjs-db'

const user = await this.db.query.users.findFirst({
  where: (_u, eb) => eb('id', '=', id),
  columns: { passwordHash: false }, // everything but this
  extras: {
    postCount: (_u, eb) =>
      eb
        .selectFrom('posts')
        .select(eb.fn.countAll<number>().as('n'))
        .where('posts.authorId', '=', sql.ref<number>('users_0.id')),
  },
  with: {
    posts: {
      columns: { id: true, title: true }, // only these
      extras: { titleLength: () => sql<number>`length(title)` },
    },
  },
})
// { id, email, postCount, posts: { id, title, titleLength }[] }
  • columns: either name the columns you want (true) or the ones you don't (false); a mix of both is refused. Leaving it out returns every column.
  • extras: each one is an SQL expression over the row, and its type comes from the expression (sql<number>, eb.fn.countAll<number>()). To refer to the row in a correlated subquery, use its alias through sql.ref(), which type-checks where a string reference wouldn't: the table name, then _ and the nesting depth (users_0 at the top, posts_1 one level down). A scalar subquery types as its one column's value, or null.
  • The row type follows both. Excluded columns aren't on it, and each extra is.
  • Counts on Postgres: at the top level, count(*) comes back as a string (a bigint). Cast it in SQL (count(*)::int) or with Number(). Inside with it comes back as JSON, so it's a number.

Many-to-many ​

Declare the junction table as a table, and point a many through it:

ts
export const posts = table('posts', { id: serial().primaryKey(), title: text().notNull() })
export const tags = table('tags', { id: serial().primaryKey(), name: text().notNull() })
export const postTags = table(
  'post_tags',
  {
    postId: integer()
      .notNull()
      .references(() => posts.id),
    tagId: integer()
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({ pk: primaryKey().on(t.postId, t.tagId) }),
)

export const postRelations = relations(posts, ({ many }) => ({
  tags: many(tags, { through: postTags }),
}))
export const tagRelations = relations(tags, ({ many }) => ({
  posts: many(posts, { through: postTags }),
}))
ts
const post = await db.query.posts.findFirst({
  where: (_p, eb) => eb('id', '=', id),
  with: { tags: { orderBy: (_t, eb) => eb.ref('name') } },
})
// post.tags: Tag[] — the junction rows themselves aren't returned

The junction's foreign keys decide the join — one to each side. A through relation takes where, orderBy, limit and nested with like any many, and still compiles to one query.

When the junction has more than one key to a side — a table joined to itself, like follows between users — name the junction columns:

ts
export const userRelations = relations(users, ({ many }) => ({
  following: many(users, {
    through: { table: follows, from: [follows.followerId], to: [follows.followeeId] },
  }),
  followers: many(users, {
    through: { table: follows, from: [follows.followeeId], to: [follows.followerId] },
  }),
}))

from holds the source's key, to the target's. Reading a junction's own columns (a role on a membership, a createdAt on a follow) needs the junction as a table in between: with: { memberships: { with: { project: true } } }.

Polymorphic associations ​

One kind of child row belonging to several kinds of parent — comments on posts, publications and podcasts — can be modelled three ways. Pick by how much the database should guarantee.

The morph* helpers are the type-and-id versions of many and one — see Relations for which goes where.

PatternSchemaDatabase integrityRead with
Exclusive arcA nullable foreign key per parent, plus a CHECKFull: FKs, cascades, exactly-one checkone / many
Junction table per parentpost_comments, podcast_comments, …Full: FKs, cascadesmany(…, { through })
Type + id columnssubjectType + subjectId on the childNone — no foreign key is possiblemorphMany / morphOne / morphTo

Exclusive arc ​

Give the child one nullable foreign key per parent, and a check() that exactly one is set. Every key is a real foreign key, so deletes cascade and a dangling reference is impossible; adding a parent kind is a migration that adds a column.

ts
export const comments = table(
  'comments',
  {
    id: serial().primaryKey(),
    postId: integer().references(() => posts.id, { onDelete: 'cascade' }),
    podcastId: integer().references(() => podcasts.id, { onDelete: 'cascade' }),
    body: text().notNull(),
  },
  // SQL as written, for your dialect and casing — here Postgres with casing: 'snake_case'.
  () => ({ oneSubject: check('comments_one_subject', 'num_nonnulls(post_id, podcast_id) = 1') }),
)

export const postRelations = relations(posts, ({ many }) => ({ comments: many(comments) }))
export const commentRelations = relations(comments, ({ one }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  podcast: one(podcasts, { fields: [comments.podcastId], references: [podcasts.id] }),
}))

On SQLite and MySQL, where num_nonnulls doesn't exist, add up the set keys instead: (post_id IS NOT NULL) + (podcast_id IS NOT NULL) = 1.

A junction table per parent ​

Keep comments free of parent columns and join each parent through its own table — post_comments (post_id, comment_id) and so on, read with many(comments, { through: postComments }). Integrity holds on both sides; the price is a table per parent kind, and a unique key on comment_id if a comment may only have one parent.

Type and id columns: morphMany, morphOne, morphTo ​

The child stores which kind of parent it has and that parent's key. A new parent kind needs no migration. The database can't check the pair, though — there's no foreign key, so no cascade, and nothing stops a subjectId that points nowhere.

ts
import { desc, index, integer, relations, serial, table, text, varchar } from '@forinda/kickjs-db'

export const comments = table(
  'comments',
  {
    id: serial().primaryKey(),
    subjectType: varchar(50).notNull(), // MySQL can't index a text() column without a length
    subjectId: integer().notNull(),
    body: text().notNull(),
  },
  // The lookup every morphMany read does — add it.
  (t) => ({ subject: index('comments_subject_idx').on(t.subjectType, t.subjectId) }),
)

export const postRelations = relations(posts, ({ morphMany }) => ({
  comments: morphMany(comments, { type: comments.subjectType, id: comments.subjectId, as: 'post' }),
}))
export const podcastRelations = relations(podcasts, ({ morphMany }) => ({
  comments: morphMany(comments, {
    type: comments.subjectType,
    id: comments.subjectId,
    as: 'podcast',
  }),
}))

morphMany(target, { type, id, as, references? }) reads the target rows whose type column holds as and whose id column holds the source row's key. references names that key; it defaults to the source's primary key, and a table with a composite primary key has to name one. as is a value, bound as a query parameter. Casing, .colName(), tenancy and soft-delete apply as on any relation.

It reads like any many — typed as an array of target rows, with where, orderBy, limit, columns and nested with:

ts
const post = await db.query.posts.findFirst({
  where: (_p, eb) => eb('id', '=', id),
  with: { comments: { orderBy: (_c, eb) => eb.ref('id'), limit: 20 } },
})
// post.comments: Comment[] — only rows with subjectType 'post' and subjectId = post.id

The relational layer is read-only, so a comment is written with the pair set by hand:

ts
await db.insertInto('comments').values({ subjectType: 'post', subjectId: postId, body }).execute()

With no foreign key, deleting a post leaves its comments behind. Delete them in the same transaction:

ts
await db.transaction(async (tx) => {
  await tx
    .deleteFrom('comments')
    .where('subjectType', '=', 'post')
    .where('subjectId', '=', id)
    .execute()
  await tx.deleteFrom('posts').where('id', '=', id).execute()
})

morphOne is the same pair read as a single row, for a parent with at most one child of the shared table, like a post's cover image. It's typed like one (the row, or null); give it an orderBy when there can be several and you want a particular one:

ts
export const postRelations = relations(posts, ({ morphMany, morphOne }) => ({
  comments: morphMany(comments, { type: comments.subjectType, id: comments.subjectId, as: 'post' }),
  latestComment: morphOne(comments, {
    type: comments.subjectType,
    id: comments.subjectId,
    as: 'post',
  }),
}))

const rows = await db.query.posts.findMany({
  with: { latestComment: { orderBy: (_c, eb) => desc(eb.ref('id')) } },
})
// rows[0].latestComment: Comment | null

morphTo goes the other way: from a comment to its subject, in whichever table subjectType names. Declare it on the child, mapping each type value to its table:

ts
export const commentRelations = relations(comments, ({ morphTo }) => ({
  subject: morphTo({
    type: comments.subjectType,
    id: comments.subjectId,
    targets: { post: posts, podcast: podcasts },
  }),
}))

const rows = await db.query.comments.findMany({ with: { subject: true } })
// rows[0].subject: (Post & { __type: 'post' }) | (Podcast & { __type: 'podcast' }) | null

Each target's primary key is matched against id, only for rows whose type holds that target's value (bound as a parameter). The subject is null when the type has no entry in targets, the row is gone, or it's soft-deleted. The subject comes back as its table's row plus __type, the type value it was found by, so if (subject?.__type === 'post') narrows it to a post — even when columns leaves the comment's subjectType out.

The subject comes from one of several tables, so options that need a table go per target, under its type value. withDeleted beside targets applies to all of them:

ts
await db.query.comments.findMany({
  with: {
    subject: {
      withDeleted: true,
      targets: {
        post: { columns: { title: true }, with: { author: true } },
        podcast: { where: (_p, eb) => eb('published', '=', true) },
      },
    },
  },
})
// subject: ({ title; author } & { __type: 'post' }) | (Podcast & { __type: 'podcast' }) | null

A target takes columns, extras, where, nested with and its own withDeleted — not orderBy, limit or offset, since it is one row; a target's where that doesn't match gives null. A target without options loads its whole row. On SQLite a bytea column is left out of a target unless columns names it, which is refused (SQLite's JSON can't hold one). Computed fields on the subject find its table by __type. Every target's key has to compare with the id column — integers with integers, strings with strings, a uuid with a uuid — since Postgres refuses to compare an integer column with a uuid key even for rows of another type. A target that doesn't is refused when the client is created.

Self-references and cycles ​

Aliases are depth-suffixed under the hood (tasks_0, tasks_1, tasks_2…) so self-referencing relations don't collide:

ts
// Schema:
// tasks.parentTaskId references tasks.id
// relations: parentTask: one(tasks, { … }), subtasks: many(tasks)

this.db.query.tasks.findUnique({
  where: (_t, eb) => eb('id', '=', id),
  with: {
    parentTask: true,
    subtasks: {
      with: { subtasks: true }, // 2-deep self-ref
    },
  },
})

Two-FK cycles where the same target table is referenced by two distinct columns (e.g. messages.senderId + messages.recipientId both → users) need a relationName tag on both sides, pairing each one with its inverse many:

ts
relations(messages, ({ one }) => ({
  sender: one(users, { fields: [messages.senderId], references: [users.id], relationName: 'sent' }),
  recipient: one(users, {
    fields: [messages.recipientId],
    references: [users.id],
    relationName: 'received',
  }),
}))

relations(users, ({ many }) => ({
  sentMessages: many(messages, { relationName: 'sent' }),
  receivedMessages: many(messages, { relationName: 'received' }),
}))

Without the tags, users.sentMessages can't tell which of the two messages → users relations is its inverse.

Options ​

  • findMany(options?) → Row[]
  • findFirst(options?) → Row | null
  • findUnique(options) → Row | null
  • findManyAndCount(options?) → { data: Row[]; total: number }: one page, plus how many rows where matches before limit and offset. It runs a second, count(*) query with the same where and soft-delete filter, and returns the shape ctx.paginate takes.

The options bag:

FieldTypeNotes
where(table, eb) => Expressioneb is the expression builder — eb('col', '=', v)
orderBy(table, eb) => Expression | Expression[]eb.ref('col'), wrapped in desc() / asc() for direction
limitnumber
offsetnumber
with{ [relation]: true | FindManyOptions }true eager-loads; an object form filters the relation
columns{ [column]: boolean }the columns to return: all true (only these) or all false (all but these) — Choosing fields
extras{ [name]: (table, eb) => Expression }computed fields, typed from each expression
withDeletedbooleaninclude rows a softDelete() column marks deleted — this level only
maxDepthnumberdepth guard (default 5); throws RelationalQueryDepthError
signalAbortSignalcancels the in-flight query on the server (Postgres, MySQL) — bind to ctx.signal
timeoutMsnumbercancels it on the server once it has run this long; with signal, whichever fires first

The with keys are constrained to the relations declared for that table; a relation slot resolves to Related | null for one and Related[] for many.

db.query is read-only — use insertInto / updateTable / deleteFrom for writes.

Cancellation ​

Bind the query to the request's AbortSignal so it is cancelled at the dialect level when the client disconnects or the request times out:

ts
@Get('/')
list(ctx: RequestContext) {
  return this.db.query.users.findMany({
    with: { posts: true },
    signal: ctx.signal,
  })
}

A cancelled query rejects with RelationalQueryCancelledError.

Dialect notes ​

PG uses json_agg / to_json (single LATERAL per with key, empty arrays return []).

SQLite uses json_group_array(json_object(...)) for many and json_object(...) for one. Empty many returns []; empty one returns null.

MySQL 8+ uses JSON_ARRAYAGG(JSON_OBJECT(...)) for many. MySQL's JSON_ARRAYAGG returns NULL over zero rows; the runtime wraps it in COALESCE(..., JSON_ARRAY()) so the client always sees []. MySQL 5.x is not supported — JSON_ARRAYAGG doesn't exist there. Adopters get a runtime version assertion when mysqlAdapter() boots.

Errors you might hit ​

  • RelationalQueryUnknownRelationError — the with key isn't in the relations registry. Run kick typegen (or kick dev); this almost always means the typegen output is stale.
  • RelationalQueryDepthError — the request exceeds maxDepth (default 5). Raise the limit on the call or restructure the query.
  • RelationalQueryAmbiguousRelationNameError — two relations share the same name in the registry. Disambiguate via relationName: 'foo' on both sides.
  • RelationalQueryMissingInverseError — a many relation can't find its inverse one. Either add the inverse in the relations file or tag both sides with relationName: 'foo'.
  • RelationalQueryThroughError — a through junction doesn't have exactly one foreign key to each side, or the from / to columns given aren't a foreign key to that side. Name the columns, or fix the junction's references().

Reference: task-kickdb-api ​

The example app uses the relational layer in three places:

FileMethodReturns
src/modules/tasks/tasks.repository.tsfindFullById(id)task + comments + assignees + labels
src/modules/workspaces/workspaces.repository.tsfindFullById(id)workspace + owner + members (with users) + projects
src/modules/workspaces/workspaces.repository.tslistOwnedByUser(userId)owned workspaces + members + projects

Each method replaced an N+1 in a controller. The with keys are typed against KickDbRelationsRegister — try renaming members to member in any call to see the compile-time guard fire.

Migrating from N+1 ​

Typical pattern before:

ts
// Three round trips, awkward to type, easy to forget one
async function workspaceOverview(id: string) {
  const ws = await this.workspaces.findById(id)
  if (!ws) return null
  const members = await this.workspaceMembers.listByWorkspace(id)
  const projects = await this.projects.listByWorkspace(id)
  return { ...ws, members, projects }
}

After:

ts
// One round trip, types flow from the schema, no manual assembly
async function workspaceOverview(id: string) {
  return this.workspaces.findFullById(id) // see workspaces.repository.ts
}

The with shape is enforced by the typegen registry — the registry stays in sync with the schema as long as kick typegen runs (it's wired into kick dev and pretypecheck).

Narrowing the client ​

Three compile-time narrowing helpers limit what a repository can touch: $pickTables<...>(), $omitTables<...>(), and the ReadonlyKysely<DB> type exported from @forinda/kickjs-db.

Reach the table-set narrowers through the full query builder (db.qb):

ts
import { Service, Inject } from '@forinda/kickjs'
import { DB_PRIMARY, type KickDbClient } from '@forinda/kickjs-db'

@Service()
export class WorkspacesAuditRepository {
  constructor(@Inject(DB_PRIMARY) private readonly db: KickDbClient) {}

  // Limit this repo to two tables at compile time. `selectFrom('users')` is a
  // type error here, even though the runtime client can reach every table.
  private get reader() {
    return this.db.qb.$pickTables<'workspaces' | 'workspace_members'>()
  }

  list() {
    return this.reader.selectFrom('workspaces').selectAll().execute()
  }
}

For a fully read-only handle, annotate db.qb as ReadonlyKysely<KickDb>. The runtime is the same client — the type just strips insertInto / updateTable / deleteFrom / mergeInto:

ts
import { Service, Inject } from '@forinda/kickjs'
import { DB_PRIMARY, type KickDbClient, type ReadonlyKysely } from '@forinda/kickjs-db'
import type { KickDb } from '../db/schema' // your SchemaToTypes alias

@Service()
export class WorkspacesQueryRepository {
  private readonly reader: ReadonlyKysely<KickDb>

  constructor(@Inject(DB_PRIMARY) db: KickDbClient<KickDb>) {
    this.reader = db.qb as unknown as ReadonlyKysely<KickDb>
  }

  list() {
    return this.reader.selectFrom('workspaces').selectAll().execute()
  }

  // this.reader.insertInto('workspaces') → compile error:
  //   Argument of type ... is not assignable to parameter of type
  //   'KyselyTypeError<"not allowed with a read-only Kysely instance.">'
}

The four write entrypoints (insertInto / updateTable / deleteFrom / mergeInto) stay visible in autocomplete on ReadonlyKysely<DB>, but every call site is typed to return a poisoned KyselyTypeError sentinel — so any actual write attempt fails to compile. The IDE shows the method names; the call fails the build.

See Schema Types for how KickDb is derived from your schema via SchemaToTypes.

Released under the MIT License. Built with TypeScript — runs on Express, Fastify, or h3.