Skip to content

Queries ​

KickDbClient is a typed query builder — selectFrom, insertInto, updateTable, deleteFrom — plus the relational db.query layer.

All examples assume an injected client:

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

@Service()
export class UsersService {
  @Inject(DB_PRIMARY) private db!: KickDbClient
  // ...
}

The query builder ​

Select ​

ts
// One row, or undefined
await this.db.selectFrom('users').selectAll().where('email', '=', 'a@b.com').executeTakeFirst()

// One row, or throw
await this.db
  .selectFrom('users')
  .select(['id', 'email'])
  .where('id', '=', id)
  .executeTakeFirstOrThrow()

// Many rows
await this.db
  .selectFrom('posts')
  .selectAll()
  .where('authorId', '=', userId)
  .orderBy('createdAt', 'desc')
  .limit(20)
  .execute()

Column names, operators, and the returned row shape are all checked against your schema — row.email is string, selecting a column that doesn't exist is a compile error.

Insert ​

Generated columns (serial(), uuid().defaultRandom(), timestamp().defaultNow(), anything with .default(...)) are optional on insert:

ts
await this.db
  .insertInto('users')
  .values({ email: 'a@b.com', name: 'Ada' }) // id + createdAt omitted
  .returningAll()
  .executeTakeFirstOrThrow()

Update & delete ​

ts
await this.db
  .updateTable('users')
  .set({ name: 'Grace' })
  .where('id', '=', id)
  .returningAll()
  .executeTakeFirst()

await this.db.deleteFrom('posts').where('id', '=', id).execute()

The full query builder

For anything not on the client itself, this.db.qb is the full query builder. You rarely need it — selectFrom / insertInto / updateTable / deleteFrom cover the common surface.

Condition helpers ​

Besides where('email', '=', x) and the expression builder eb(…), conditions can be built with standalone helpers. Their operands are a table's columns (users.email) or plain values, and they return an expression, so they work in .where(), a join's .on(), having and db.query:

ts
import { and, eq, gt, inArray, isNull, like, or } from '@forinda/kickjs-db'

await db
  .selectFrom('users')
  .selectAll()
  .where(and(eq(users.role, 'admin'), or(isNull(users.deletedAt), gt(users.lastSeen, cutoff))))
  .execute()

// In db.query, use the row argument: it refers to the right table at every level.
await db.query.users.findMany({
  where: (u) => inArray(u.id, ids),
  with: { posts: { where: (p) => like(p.title, 'Draft%') } },
})
HelperSQL
eq, ne, gt, gte, lt, lte=, <>, >, >=, <, <=
like, notLike, ilike (Postgres)LIKE, NOT LIKE, ILIKE
isNull, isNotNullIS NULL, IS NOT NULL
inArray, notInArrayIN (…), NOT IN (…)
between(col, low, high)BETWEEN … AND …
and(…), or(…), not(…)grouped in parentheses
exists(subquery), notExists(subquery)EXISTS (…)
  • One side is a column or an expression. A string is a value, not a column name: eq('id', id) would compare the text 'id' with id, so it doesn't compile, and it throws if the types are bypassed. Name the column (eq(users.id, id)) or write .where('id', '=', id).
  • Operands are type-checked. eq(users.name, 1) doesn't compile. An expression (eb.ref('email'), sql`lower(email)`, sql.ref('users.id')) is accepted wherever a column is.
  • Edge cases stay valid SQL. and() and or() skip undefined, so a filter can be built conditionally: and(eq(t.a, a), b ? eq(t.b, b) : undefined). With nothing left, and() matches every row and or() matches none. An empty inArray matches nothing, and an empty notInArray matches everything.
  • Use the right column reference. Inside db.query, use the row argument rather than users.email. The relational reader aliases each table (users_0), so a reference to the bare table name wouldn't resolve.
  • Escape like patterns. Pass user input through escapeLike() first (Raw SQL).

A table twice: alias() ​

alias(table, name) gives a second name for a table, for self-joins. Its columns work with the helpers, and $from is what selectFrom and the joins take:

ts
import { alias, eq } from '@forinda/kickjs-db'

const manager = alias(users, 'manager')

await db
  .selectFrom('users')
  .innerJoin(manager.$from, (j) => j.on(eq(manager.id, users.managerId)))
  .select(['users.name', 'manager.name as managerName'])
  .execute()

Reusable CTEs are under Raw SQL & Recipes.

Upsert and find-or-create ​

upsert inserts a row, or updates the one whose key already exists — one statement, safe under concurrency:

ts
const user = await this.db.upsert('users', {
  values: { email, name },
  target: ['email'], // the unique key that decides insert vs update
})
// → the row as stored, inserted or updated
OptionWhat it does
valuesa row, or an array of rows — an array returns an array
targetthe unique (or primary) key columns whose conflict means "update instead"
updatecolumn names that take the incoming value (default: every inserted column outside target), or fixed { col: value }; false leaves a conflicting row as it is
wherethe predicate of a partial unique index target refers to — Postgres and SQLite
ts
// Count a visit: insert with 1, or add to what's there.
await this.db.upsert('page_views', {
  values: { path, views: 1 },
  target: ['path'],
  update: { views: sql`page_views.views + 1` },
})

With update: false a row whose key exists is skipped (ON CONFLICT DO NOTHING; on MySQL a no-op update rather than INSERT IGNORE, which would hide other errors), and every row comes back as stored — the new ones and the ones that were already there, read back by target, so every row has to give its target values. On MySQL the no-op update still runs the table's UPDATE triggers for a skipped row.

findOrCreate returns the row matching where, creating it from where + create when there's none:

ts
const { row, created } = await this.db.findOrCreate('tags', {
  where: { name: 'urgent' },
  create: { color: 'red' },
})

create must cover every required column where leaves out — a missing one is a type error, not a NOT NULL failure at run time.

It's race-safe: when two requests miss at the same moment, one inserts and the other hits the unique key, catches the UniqueViolationError and reads the winner's row — both get the same row, created is true for one. That holds outside a transaction and inside a READ COMMITTED one (Postgres's default). Inside a REPEATABLE READ or serializable transaction — MySQL's default — the re-read sees the transaction's snapshot, which can't contain the winner's row, so the UniqueViolationError is rethrown; catch it and run the whole transaction again. where should be a unique key; a conflict on a different key (the row can't be created and none matches where) is thrown. Inside a transaction, the insert runs in a savepoint, so a lost race doesn't abort it on Postgres.

Per dialect:

  • Postgres and SQLite compile to INSERT … ON CONFLICT (target) DO UPDATE … RETURNING *.
  • MySQL compiles to INSERT … ON DUPLICATE KEY UPDATE and reads the rows back by their target values — MySQL has no RETURNING. It also ignores target when deciding: a conflict on any unique key updates. where isn't supported.
  • A partial index's where must be written as the index's own predicate, with literals — where: () => sql`active` for … WHERE active — because the database matches it to the index, and a bound parameter can't be matched.

Relational queries ​

db.query.<table>.findMany / findFirst / findUnique load rows together with their related rows in one query, driven by the relations() in your schema:

ts
await this.db.query.users.findMany({
  where: (u, eb) => eb('isActive', '=', true),
  with: { posts: { limit: 5 } },
  signal: ctx.signal,
})
// → Array<{ id; email; …; posts: Post[] }>

The options, nesting, self-references, dialect notes and cancellation are on Relational Queries.

Timeouts and cancellation ​

db.timeout(ms) gives any query builder the execute options that cancel it on the server once it has run that long — pg_cancel_backend on Postgres, KILL QUERY on MySQL — instead of only giving up on the wait while it keeps running and holding its connection. It rejects with a TimeoutError. Pass a request's signal too, and whichever fires first cancels:

ts
const rows = await this.db
  .selectFrom('reports')
  .selectAll()
  .execute(this.db.timeout(5_000, { signal: ctx.signal }))

db.query reads take the same as options: timeoutMs and signal (on the top level; nested with levels run inside it).

  • A timeout rejects with a DOMException named TimeoutError, not an importable class — check err.name; an aborted signal rejects with its own reason, whichever fires first. From db.query it's RelationalQueryCancelledError with that as its cause.
  • Inside a transaction the query is abandoned, not cancelled: a server-side cancel would abort the whole transaction, and Postgres would turn its COMMIT into a ROLLBACK. The query finishes on the server and the transaction carries on after it.
  • A write may already have taken effect when it's cancelled — most databases can't stop one cleanly — so time out reads.
  • On SQLite there is nothing to cancel: better-sqlite3 runs each query synchronously, and on libsql or D1 a timeout only stops the wait while the query runs on.
  • The timer starts when db.timeout() is called, so call it per query.
  • Kysely logs one line to console.error for each query cancelled in flight (failed in the background after abortion).

Streaming large results ​

.stream() reads a select row by row instead of loading the whole result into memory. It works on any query-builder select and goes through the same plugins, tenancy and error translation as .execute():

ts
for await (const row of this.db.selectFrom('events').selectAll().orderBy('id').stream(500)) {
  await exporter.write(row)
}

Memory stays flat whether the table has ten thousand rows or ten million. Use it for exports, backfills and reports. For a page of rows an API returns, use limit and pagination instead.

How each database delivers rows ​

DialectRows arriveSetup
Postgresin chunks from a server-side cursor; the argument (500) is the chunk sizepnpm add pg-cursor, then pgDialect({ pool, cursor: Cursor }) with import Cursor from 'pg-cursor'. Without it, .stream() throws
MySQLone at a time, from the driver's row stream; the argument is ignorednone
SQLiteone at a time, from the prepared statement; the argument is ignorednone

Only the query builder streams. db.query.* builds each result as one JSON tree, so it has no .stream(). For a large relational read, page through it instead (Paginating nested rows).

A stream holds its connection ​

From the first row until the loop ends, the stream occupies one connection:

  • Breaking out of the loop or throwing closes the stream and gives the connection back. Errors are translated like any other query's.
  • On a pool (Postgres, MySQL), a query inside the loop takes a second connection. A slow loop over many streams at once can use up the pool.
  • On SQLite, or inside a transaction, there is only one connection. A query issued inside the loop waits until the stream ends, so awaiting it in the loop never returns. Inside db.transaction() that includes queries made through the plain client, since they join the transaction.
ts
// SQLite or a transaction: this loop never finishes.
for await (const row of trx.selectFrom('events').selectAll().stream()) {
  await trx.updateTable('events').set({ exported: true }).where('id', '=', row.id).execute()
}

So when you need to write as you read on one connection, don't stream. If the rows to change can be picked by a where, one set-based update does it without reading them at all:

ts
await trx.updateTable('events').set({ exported: true }).where('exported', '=', false).execute()

If each row needs work first, page by cursor instead. Each batch is read, which frees the connection, then written, so memory stays bounded and no query waits on another:

ts
let lastId = 0
for (;;) {
  const batch = await trx
    .selectFrom('events')
    .selectAll()
    .where('id', '>', lastId)
    .orderBy('id')
    .limit(500)
    .execute()
  if (batch.length === 0) break
  await exporter.write(batch)
  await trx
    .updateTable('events')
    .set({ exported: true })
    .where(
      'id',
      'in',
      batch.map((row) => row.id),
    )
    .execute()
  lastId = batch.at(-1)!.id
}

Streaming to an HTTP response ​

Turn the rows into a ReadableStream and send it with ctx.sendResponse(), which works on the Express, Fastify and h3 runtimes. The stream below pulls a row only when the client is ready for more, and cancelling it — the client disconnecting — closes the database stream:

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

@Controller()
export class ExportController {
  constructor(@Inject(DB_PRIMARY) private readonly db: KickDbClient) {}

  @Get('/events.ndjson')
  async events(ctx: RequestContext) {
    const rows = this.db.selectFrom('events').selectAll().orderBy('id').stream(500)
    await ctx.sendResponse(
      new Response(ndjson(rows), { headers: { 'content-type': 'application/x-ndjson' } }),
    )
  }
}

// Your own helper, defined here — KickJS doesn't export one. Copy it into your app.
/** One JSON line per row, read from `rows` only as fast as the client takes them. */
function ndjson(rows: AsyncIterable<unknown>): ReadableStream<Uint8Array> {
  const it = rows[Symbol.asyncIterator]()
  const encoder = new TextEncoder()
  return new ReadableStream({
    async pull(controller) {
      const { value, done } = await it.next()
      if (done) controller.close()
      else controller.enqueue(encoder.encode(`${JSON.stringify(value)}\n`))
    },
    // The client went away: close the database stream.
    async cancel() {
      await it.return?.()
    },
  })
}

ndjson() is defined in the example itself — a few lines over the web-standard ReadableStream, not a KickJS API. One JSON object per line (NDJSON) lets the client process rows as they arrive. For CSV, enqueue a header line first and then one line per row.

  • The status is sent with the first chunk. An error halfway through can't become a 500 anymore; the response just ends early. If the client must know the export is complete, end with a summary line ({"done":true,"rows":1200}) and have it check for that.
  • For a live feed of changes rather than an export, use server-sent events (ctx.sse()).

Pagination with ctx.paginate ​

KickJS's HTTP layer already parses page / limit / filters / sort off the query string. ctx.paginate() wraps a fetcher that returns { data, total } and emits a standardized paginated response. Use parsed.pagination.limit / parsed.pagination.offset to bound the query. findManyAndCount returns that shape, the page plus the total before paging:

ts
import { Controller, Get, type RequestContext } from '@forinda/kickjs'

@Controller()
export class UsersController {
  @Inject(DB_PRIMARY) private db!: KickDbClient

  @Get('/')
  list(ctx: RequestContext) {
    return ctx.paginate((parsed) =>
      this.db.query.users.findManyAndCount({
        limit: parsed.pagination.limit,
        offset: parsed.pagination.offset,
      }),
    )
  }
}

To honour ?filter= and ?sort= too, pass a field config as the second argument and map parsed.filters / parsed.sort into where / orderBy; the same where then also bounds total. With the query builder instead, run the page and a count(*) with the same where, and return { data, total: Number(count) }. The response includes meta: { page, limit, total, totalPages, hasNext, hasPrev }. See Query Parsing for the full ctx.qs / ctx.paginate surface, and Repositories for wrapping these queries behind a repository interface.

More ​

  • Transactions — commit/rollback, savepoints, call-chain joining, afterCommit, retry
  • Raw SQL & Recipes — the sql template, upsert, keyset pagination, aggregates, CTEs
  • Errors — typed errors and how to handle them
  • Events and Plugins — lifecycle events, safeNullComparison()
  • Extensions — $extends per-table methods

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