Kysely Query Builder skill for Codex

Done well means: the Database type matches the live schema (generated, not hand-guessed), each query compiles to the SQL you would have written by hand, results are typed without casts, and no user input ever reaches sql.raw.

Inspect first

1. Read AGENTS.md, then package.json for kysely, the dialect driver (pg, mysql2, better-sqlite3, or a community dialect) and kysely-codegen or prisma-kysely.
2. Find the Database interface and whether it is generated (header comment, codegen script) or hand-written. Never hand-edit a generated file; regenerate it.
3. Find the single new Kysely<Database>(...) instance and how it is shared.
4. Find migrations (migrations/ with up/down exports) and how they run.

Types and setup

import { Kysely, PostgresDialect, Generated, ColumnType, Selectable, Insertable, Updateable } from "kysely";
import { Pool } from "pg";

interface OrdersTable {
  id: Generated<string>;                                   // DB default, optional on insert
  tenant_id: string;
  status: "pending" | "paid" | "shipped";
  total_cents: number;
  created_at: ColumnType<Date, string | undefined, never>; // select Date, insert optional, never update
}
export interface Database { orders: OrdersTable; customers: CustomersTable; }

export type Order = Selectable<OrdersTable>;
export type NewOrder = Insertable<OrdersTable>;
export type OrderPatch = Updateable<OrdersTable>;

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool: new Pool({ connectionString: process.env.DATABASE_URL, max: 10 }) }),
});

Generate the types from a real database instead of writing them:

DATABASE_URL=postgres://... npx kysely-codegen --out-file src/db/types.ts

This needs a reachable database (local container or branch); in a sandbox without network, ask the user to run it or point it at a local database. Regenerate after every migration.

• pg returns bigint and numeric as strings by default; type them as string (or install a type parser) rather than lying with number.
• CamelCasePlugin maps created_at to createdAt; decide once, since codegen output must match (--camel-case).

Queries

import { sql } from "kysely";

const rows = await db
  .selectFrom("orders as o")
  .innerJoin("customers as c", "c.id", "o.customer_id")
  .select(["o.id", "o.total_cents", "c.name as customer_name"])
  .where("o.tenant_id", "=", tenantId)
  .where((eb) => eb.or([eb("o.status", "=", "pending"), eb("o.total_cents", ">", 100_00)]))
  .$if(Boolean(since), (qb) => qb.where("o.created_at", ">=", since!))
  .orderBy("o.created_at", "desc")
  .orderBy("o.id", "desc")
  .limit(50)
  .execute();

const one = await db.selectFrom("orders").selectAll().where("id", "=", id).executeTakeFirst();      // T | undefined
const must = await db.selectFrom("orders").selectAll().where("id", "=", id).executeTakeFirstOrThrow();

• Use executeTakeFirst when absence is normal, executeTakeFirstOrThrow when it is a bug.
• $if keeps optional filters typed; do not build queries by mutating a let qb with casts.
• Column references are checked: a typo in "o.totl_cents" is a compile error. If a query needs as any, the Database type is wrong; fix the type.
• Aggregates: .select((eb) => eb.fn.count<string>("o.id").as("n")) (Postgres count is bigint, returned as a string by pg); eb.fn.countAll() for count(*).

Nested results without N+1

import { jsonArrayFrom, jsonObjectFrom } from "kysely/helpers/postgres"; // also /mysql, /sqlite

const orders = await db
  .selectFrom("orders as o")
  .select((eb) => [
    "o.id",
    jsonObjectFrom(eb.selectFrom("customers as c").select(["c.id", "c.name"])
      .whereRef("c.id", "=", "o.customer_id")).as("customer"),
    jsonArrayFrom(eb.selectFrom("order_items as i").select(["i.sku", "i.qty"])
      .whereRef("i.order_id", "=", "o.id").orderBy("i.sku")).as("items"),
  ])
  .where("o.tenant_id", "=", tenantId)
  .limit(50)
  .execute();

One statement, typed nested output. JSON aggregation turns dates into strings; type or parse them accordingly (ParseJSONResultsPlugin handles nested JSON strings on some dialects).

Writes

const created = await db.insertInto("orders").values(newOrder).returningAll().executeTakeFirstOrThrow();

await db.insertInto("customers").values(rows)
  .onConflict((oc) => oc.columns(["tenant_id", "email"]).doUpdateSet((eb) => ({ name: eb.ref("excluded.name") })))
  .execute();

const res = await db.updateTable("orders").set({ status: "paid" }).where("id", "=", id).executeTakeFirst();
if (res.numUpdatedRows === 0n) throw new Error("order not found");

• updateTable/deleteFrom without .where affects every row; guard filters built from optional input.
• MySQL has no RETURNING: read insertId from the result.
• Chunk large values([...]) arrays (Postgres caps parameters at 65 535 per statement).

Transactions

await db.transaction().setIsolationLevel("serializable").execute(async (trx) => {
  await trx.updateTable("accounts").set((eb) => ({ balance: eb("balance", "-", amount) })).where("id", "=", from).execute();
  await trx.updateTable("accounts").set((eb) => ({ balance: eb("balance", "+", amount) })).where("id", "=", to).execute();
});

Use trx inside the callback; any db call escapes the transaction. Throwing rolls back. Retry serialization failures (Postgres 40001) with backoff at the caller.

Raw SQL

const stats = await sql<{ day: Date; n: string }>`
  select date_trunc('day', created_at) as day, count(*) as n
  from orders where tenant_id = ${tenantId} group by 1 order by 1`.execute(db);

${value} becomes a bound parameter. sql.ref("col") and sql.table("t") quote identifiers safely (validate them against an allow-list anyway). sql.raw() and sql.lit() inline text: never with user input.

Migrations

// migrations/2025_06_01_add_orders.ts
import { Kysely, sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema.createTable("orders")
    .addColumn("id", "uuid", (c) => c.primaryKey().defaultTo(sql`gen_random_uuid()`))
    .addColumn("tenant_id", "uuid", (c) => c.notNull())
    .addColumn("created_at", "timestamptz", (c) => c.notNull().defaultTo(sql`now()`))
    .execute();
  await db.schema.createIndex("orders_tenant_created_idx").on("orders").columns(["tenant_id", "created_at"]).execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("orders").execute();
}

• Migrations take Kysely<any>: never import the app’s Database type, which changes later.
• Run with Migrator and FileMigrationProvider, or kysely-ctl if the repo uses it (npx kysely --help). Migrations apply in name order; prefix with sortable timestamps.
• On Postgres the Migrator runs migrations inside a transaction, so CREATE INDEX CONCURRENTLY fails there. Run concurrent index builds as a separate deploy step, or use your version’s option to disable migration transactions if it has one.
• Running down migrations or dropping tables outside a local database requires explicit user confirmation.

Debugging

• query.compile() returns { sql, parameters } without executing; log it to compare with expectations.
• new Kysely({ dialect, log: ["query", "error"] }) or a log function with event.queryDurationMillis.
• For slow statements, run the compiled SQL with EXPLAIN (ANALYZE, BUFFERS) in psql.

Gotchas

• Community dialects (Neon, D1, PlanetScale, postgres.js) vary in transaction support; HTTP-based drivers often cannot run interactive transactions.
• Generated types drift silently if codegen is not part of the migration routine.
• selectAll() on wide tables in hot paths fetches columns nobody reads.
• Mixing CamelCasePlugin with raw sql strings: raw SQL is not rewritten; use snake_case there.

Verification

• npx tsc --noEmit passes with no casts added to queries.
• Compiled SQL for changed queries reviewed; one statement where nesting was expected.
• Migrations apply on a fresh database, codegen re-run, and the diff of the types file is expected.
• Tests hit a real database (container or branch), not mocks of Kysely.

Output

**Queries changed:** <file:function> -> compiled SQL summary
**Types:** regenerated with <command> | hand-edited (why)
**Migrations:** <files>, transactional: yes/no, separate steps: <concurrent indexes>
**Verified:** tsc, compiled SQL review, fresh-DB migrate, tests