Claude Code with Kysely: Type-Safe SQL Without an ORM

Why Kysely without CLAUDE.md drops back to raw SQL

Kysely is the most idiomatic type-safe SQL query builder available to TypeScript developers in 2026. The library generates a query API directly from your database schema, the types are exact down to nullability and JSON shapes, and the API reads close to SQL itself. The problem is that Claude Code does not understand Kysely's relationship with the schema codegen. Without explicit constraints, Claude tends to write raw SQL with the sql template tag for anything non-trivial, which loses the type safety advantage and produces queries that are hard to maintain.

The most common Claude defaults that hurt Kysely correctness: reaching for the sql template tag at the first sign of complexity (joins, subqueries, conditional WHERE clauses), using executeTakeFirst and then null-checking instead of executeTakeFirstOrThrow where the row is required, mismanaging transactions by calling commit or rollback manually instead of using the transaction().execute() callback, ignoring the codegen step so types drift from the live schema, and conflating Postgres-specific helpers with the dialect-agnostic core API. None of these surface as compile errors because Kysely accepts the patterns silently and just loses the type guarantees that justify using it over raw pg or mysql2.

This guide covers the CLAUDE.md configuration that locks Claude Code into Kysely's correct model: codegen as the source of types, the query builder for everything reasonable, executeTakeFirstOrThrow for required rows, transaction callbacks for atomicity, and the dialect helpers that give you Postgres-specific features without dropping to raw SQL. If you are choosing between Kysely and Drizzle, Claude Code with Drizzle covers the alternative; this guide assumes you have chosen Kysely for the SQL-shaped query builder reason.

The Kysely CLAUDE.md template

The CLAUDE.md at your project root is read at the start of every Claude Code session. For a Kysely integration it needs to declare: the package version, the database dialect, the codegen pipeline, the executor selection rules, the transaction pattern, and the hard rules that block the mistakes Claude makes most often.

# Kysely SQL builder rules

## Stack
- kysely ^0.27.x, TypeScript 5.x strict
- Dialect: pg ^8.x (Postgres) | mysql2 ^3.x (MySQL) | better-sqlite3 ^11.x (SQLite)
- kysely-codegen ^0.16.x for type generation from live DB
- DATABASE_URL in .env.local (libsql / pg / mysql connection string)

## Project structure
- src/db/db.ts            , Kysely instance singleton
- src/db/types.ts         , generated from kysely-codegen
- src/db/queries/         , one file per domain (users.ts, orders.ts, etc)
- migrations/             , numbered migration files (run with kysely-ctl)

## Codegen (MANDATORY)
- npm script: "db:codegen": "kysely-codegen --out-file src/db/types.ts"
- Run after every migration
- The Database type in types.ts is the single source of truth for table shapes
- NEVER hand-edit src/db/types.ts

## Query builder usage
- ALWAYS use the query builder for: select, insert, update, delete
- USE sql template tag ONLY for: dialect-specific features the builder lacks
- For complex WHERE: pass expression builder via .where((eb) => eb.and([...]))
- For joins: .innerJoin('table', 'foreign_key', 'primary_key')
- For aggregations: .select(eb => eb.fn.count('column').as('count'))

## Executor selection
- executeTakeFirstOrThrow(): row MUST exist (raises NoResultError)
- executeTakeFirst(): row MAY exist (returns T | undefined)
- execute(): zero or more rows (returns T[])
- NEVER use .execute() then [0] - use executeTakeFirst()

## Transactions
- ALWAYS use db.transaction().execute(async (trx) => { ... })
- Inside the callback, use trx instead of db for every query
- NEVER call commit() or rollback() manually
- Throw from the callback to roll back

## Hard rules
- NEVER hand-edit src/db/types.ts (it's regenerated by codegen)
- NEVER fall back to raw sql`...` for queries the builder supports
- NEVER use .execute() followed by array indexing - use the take helpers
- NEVER mix db and trx inside a transaction callback
- ALWAYS run codegen after schema changes (npm run db:codegen)
- ALWAYS handle the NoResultError from executeTakeFirstOrThrow at the route boundary

Three rules here prevent the majority of correctness issues Claude generates without them.

The codegen rule is the most impactful for type safety. Kysely's types come from the Database interface generated by kysely-codegen. If Claude hand-writes that interface or skips the codegen step after a migration, the types drift from the live schema and db.selectFrom('users').select('non_existent_column') compiles cleanly. The rule makes codegen non-negotiable and tags it as the single source of truth.

The executor selection rule prevents a class of subtle bugs. executeTakeFirst() returns T | undefined. Treating an undefined result as a "row not found" works, but Claude often writes const user = await db.selectFrom('users').where(...).execute(); return user[0] which silently returns the first row of an unbounded query. Using executeTakeFirstOrThrow() for required rows and executeTakeFirst() for optional rows makes the intent explicit and catches mistakes at runtime.

The transaction callback rule prevents leaked connections and inconsistent rollbacks. The Kysely transaction().execute(callback) pattern handles commit, rollback, and connection cleanup automatically based on whether the callback throws. Manual commit() and rollback() calls are error-prone and pool-leak-prone. The rule bans them.

Install and codegen setup

Install Kysely and the dialect driver:

npm i kysely pg
npm i -D kysely-codegen

Set up the codegen script:

// package.json
{
  "scripts": {
    "db:codegen": "kysely-codegen --out-file src/db/types.ts",
    "db:migrate": "npx kysely-ctl migrate latest"
  }
}

Create the Kysely instance:

// src/db/db.ts
import { Kysely, PostgresDialect } from "kysely";
import { Pool } from "pg";
import type { DB } from "./types";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

export const db = new Kysely<DB>({
  dialect: new PostgresDialect({ pool }),
  log: process.env.NODE_ENV === "development" ? ["query", "error"] : ["error"],
});

The Kysely<DB> generic ties the query builder to the generated types. Every selectFrom, insertInto, update, and deleteFrom call now has full column-level autocomplete and type checking.

Run codegen after the initial migration to populate src/db/types.ts:

npm run db:codegen

The generated file looks like:

// src/db/types.ts (generated, do not edit)
import type { ColumnType, Generated, Insertable, Selectable, Updateable } from "kysely";

export interface Users {
  id: Generated<string>;
  email: string;
  name: string;
  bio: string | null;
  created_at: Generated<Date>;
  updated_at: Generated<Date>;
}

export interface Orders {
  id: Generated<string>;
  user_id: string;
  total_cents: number;
  status: "pending" | "paid" | "shipped" | "cancelled";
  created_at: Generated<Date>;
}

export interface DB {
  users: Users;
  orders: Orders;
}

The Generated<T> marker indicates columns the database fills (defaults, identity columns). They are optional on insert and required on select.

Add the codegen flow to CLAUDE.md:

## Codegen flow

1. Write migration in migrations/NNN-name.ts
2. npm run db:migrate (applies migration to local DB)
3. npm run db:codegen (regenerates src/db/types.ts)
4. Use new tables/columns in queries with full type safety
5. Commit the migration AND the updated types.ts in the same PR

If src/db/types.ts is out of date, queries will compile against the old shape
and fail at runtime. ALWAYS run codegen after a schema change.

Basic queries

The query builder mirrors SQL closely. Selects:

// src/db/queries/users.ts
import { db } from "../db";

export async function getUserById(id: string) {
  return await db
    .selectFrom("users")
    .select(["id", "email", "name", "bio", "created_at"])
    .where("id", "=", id)
    .executeTakeFirstOrThrow();
}

export async function findUserByEmail(email: string) {
  return await db
    .selectFrom("users")
    .selectAll()
    .where("email", "=", email)
    .executeTakeFirst();
}

export async function listRecentUsers(limit: number) {
  return await db
    .selectFrom("users")
    .selectAll()
    .orderBy("created_at", "desc")
    .limit(limit)
    .execute();
}

Three patterns, three executors:

1. getUserById uses executeTakeFirstOrThrow() because a missing user is an error condition for this function. The caller does not need to null-check.

2. findUserByEmail uses executeTakeFirst() because a missing user is a valid case (user does not exist yet, sign them up). The return type is User | undefined.

3. listRecentUsers uses execute() because the result is a list. The return type is User[].

Inserts return the inserted row when you append .returning():

export async function createUser(input: { email: string; name: string }) {
  return await db
    .insertInto("users")
    .values({
      email: input.email,
      name: input.name,
    })
    .returning(["id", "email", "name", "created_at"])
    .executeTakeFirstOrThrow();
}

Generated columns (id, created_at) are omitted from the insert payload. The .returning(["id", "email", "name", "created_at"]) call brings them back in the result.

Updates and deletes follow the same pattern:

export async function updateUserName(id: string, name: string) {
  return await db
    .updateTable("users")
    .set({ name, updated_at: new Date() })
    .where("id", "=", id)
    .returning("id")
    .executeTakeFirstOrThrow();
}

export async function deleteUser(id: string) {
  await db.deleteFrom("users").where("id", "=", id).execute();
}

Complex WHERE with the expression builder

For multi-condition WHERE clauses (especially when conditions are conditional on input), use the expression builder. It composes cleanly and stays fully typed.

// src/db/queries/orders.ts
import { db } from "../db";
import { sql } from "kysely";

interface OrderFilters {
  userId?: string;
  status?: "pending" | "paid" | "shipped" | "cancelled";
  minTotal?: number;
  search?: string;
}

export async function listOrders(filters: OrderFilters) {
  return await db
    .selectFrom("orders")
    .selectAll()
    .where((eb) => {
      const conditions = [];
      if (filters.userId) {
        conditions.push(eb("user_id", "=", filters.userId));
      }
      if (filters.status) {
        conditions.push(eb("status", "=", filters.status));
      }
      if (filters.minTotal) {
        conditions.push(eb("total_cents", ">=", filters.minTotal));
      }
      if (filters.search) {
        conditions.push(eb("description", "ilike", `%${filters.search}%`));
      }
      return eb.and(conditions);
    })
    .orderBy("created_at", "desc")
    .execute();
}

The expression builder eb accepts eb(column, operator, value) for individual conditions and eb.and(...) / eb.or(...) for combinators. The types remain exact: passing an invalid column or a value of the wrong type fails to compile.

For joins:

export async function getOrderWithUser(orderId: string) {
  return await db
    .selectFrom("orders")
    .innerJoin("users", "users.id", "orders.user_id")
    .select([
      "orders.id as order_id",
      "orders.total_cents",
      "orders.status",
      "users.email as user_email",
      "users.name as user_name",
    ])
    .where("orders.id", "=", orderId)
    .executeTakeFirstOrThrow();
}

Column references in joined queries need the table.column form. Aliases (as) keep the result shape readable.

For aggregations:

export async function getOrderStats(userId: string) {
  return await db
    .selectFrom("orders")
    .select((eb) => [
      eb.fn.count<number>("id").as("order_count"),
      eb.fn.sum<number>("total_cents").as("total_spent_cents"),
      eb.fn.avg<number>("total_cents").as("average_order_cents"),
    ])
    .where("user_id", "=", userId)
    .executeTakeFirstOrThrow();
}

eb.fn.count, eb.fn.sum, and eb.fn.avg are typed aggregate helpers. The generic <number> annotation tells Kysely to coerce the result.

Add the expression builder pattern to CLAUDE.md:

## Expression builder patterns

- For complex WHERE: .where((eb) => eb.and([...]))
- For OR clauses: eb.or([eb('col', '=', a), eb('col', '=', b)])
- For aggregations: .select((eb) => [eb.fn.count('col').as('alias')])
- For joins: .innerJoin('table', 'foreign_key', 'primary_key')
- For subqueries: .selectFrom((eb) => eb.selectFrom('table').as('alias'))
- NEVER drop to raw sql`...` for these cases, the builder handles them

Transactions

The transaction pattern is non-negotiable. db.transaction().execute(callback) handles commit, rollback, and pool cleanup. The callback receives a trx handle that must be used for every query inside.

import { db } from "../db";

export async function transferFunds(fromUserId: string, toUserId: string, amountCents: number) {
  return await db.transaction().execute(async (trx) => {
    const sender = await trx
      .selectFrom("users")
      .select("balance_cents")
      .where("id", "=", fromUserId)
      .forUpdate()
      .executeTakeFirstOrThrow();

    if (sender.balance_cents < amountCents) {
      throw new Error("Insufficient balance");
    }

    await trx
      .updateTable("users")
      .set((eb) => ({ balance_cents: eb("balance_cents", "-", amountCents) }))
      .where("id", "=", fromUserId)
      .execute();

    await trx
      .updateTable("users")
      .set((eb) => ({ balance_cents: eb("balance_cents", "+", amountCents) }))
      .where("id", "=", toUserId)
      .execute();

    const transfer = await trx
      .insertInto("transfers")
      .values({
        from_user_id: fromUserId,
        to_user_id: toUserId,
        amount_cents: amountCents,
      })
      .returning("id")
      .executeTakeFirstOrThrow();

    return transfer.id;
  });
}

Three things make this transaction correct:

1. forUpdate() locks the sender row for the duration of the transaction. Without it, two concurrent transfers from the same user could both read the same balance and overdraw.

2. Every query uses trx, not db. Mixing the two breaks the atomicity guarantee: db queries run on a separate connection and bypass the transaction entirely.

3. The callback throws on insufficient balance, which automatically rolls back the transaction. No manual rollback() call needed.

Add a transaction section to CLAUDE.md:

## Transaction patterns

- db.transaction().execute(async (trx) => { ... }) for atomic operations
- Inside the callback: use trx, NEVER db
- Throw to roll back, return normally to commit
- forUpdate() on selects that need row-level locking for the transaction duration
- For isolation level: db.transaction().setIsolationLevel('serializable').execute(...)
- NEVER call .commit() or .rollback() manually

For a deeper look at how Kysely transactions pair with the framework request lifecycle, Claude Code with Next.js covers the route handler patterns that wrap transactions cleanly.

Looking to ship type-safe SQL without an ORM? Get Claudify. Pre-built CLAUDE.md templates for Kysely and every major TypeScript backend tool, ready to drop into your project.

JSON columns

Postgres JSONB columns get a typed Kysely interface via the JSONColumnType marker in your generated types. To work with them in queries, use the dialect-specific JSON helpers.

// src/db/types.ts (generated)
export interface UserPreferences {
  id: Generated<string>;
  user_id: string;
  preferences: JSONColumnType<{
    theme: "light" | "dark";
    notifications: { email: boolean; sms: boolean };
    language: string;
  }>;
}

Reading a JSON column:

export async function getUserTheme(userId: string) {
  const result = await db
    .selectFrom("user_preferences")
    .select((eb) => [
      eb.ref("preferences", "->").key("theme").as("theme"),
    ])
    .where("user_id", "=", userId)
    .executeTakeFirstOrThrow();

  return result.theme;
}

Updating a single JSON field:

import { sql } from "kysely";

export async function setUserTheme(userId: string, theme: "light" | "dark") {
  await db
    .updateTable("user_preferences")
    .set({
      preferences: sql`jsonb_set(preferences, '{theme}', to_jsonb(${theme}::text))`,
    })
    .where("user_id", "=", userId)
    .execute();
}

Replacing the entire JSON column:

export async function setUserPreferences(userId: string, prefs: UserPreferences["preferences"]) {
  await db
    .updateTable("user_preferences")
    .set({ preferences: JSON.stringify(prefs) })
    .where("user_id", "=", userId)
    .execute();
}

JSON.stringify for a full replacement, sql\jsonb_set(...)`for a partial update. TheJSONColumnType` marker keeps the read-side type accurate.

Migrations with kysely-ctl

Kysely's recommended migration tool is kysely-ctl. Migrations are TypeScript files in migrations/ numbered sequentially.

npm i -D kysely-ctl

Create a migration:

npx kysely-ctl migrate make add-bio-to-users

The generated file:

// migrations/2026-05-20T10-30-00-add-bio-to-users.ts
import { Kysely, sql } from "kysely";

export async function up(db: Kysely<unknown>): Promise<void> {
  await db.schema
    .alterTable("users")
    .addColumn("bio", "text")
    .execute();
}

export async function down(db: Kysely<unknown>): Promise<void> {
  await db.schema
    .alterTable("users")
    .dropColumn("bio")
    .execute();
}

Apply migrations:

npx kysely-ctl migrate latest

Roll back:

npx kysely-ctl migrate down

After every migration, run codegen to update types:

npm run db:codegen

Add migration rules to CLAUDE.md:

## Migrations

- migrations/ directory, one TS file per migration
- Naming: ISO timestamp + descriptive slug
- Always implement both up() and down()
- After running migrate, ALWAYS run db:codegen
- Migrations run via npx kysely-ctl migrate latest (production via CI)
- For destructive changes (DROP COLUMN, etc), include a data backup step in up()

Common Claude Code mistakes with Kysely

Six patterns Claude generates incorrectly without CLAUDE.md constraints, with the correct replacement for each.

1. Raw SQL for builder-supported queries

Claude generates: await sql<User>\SELECT * FROM users WHERE email = ${email}`.execute(db)`.

Correct pattern: await db.selectFrom("users").selectAll().where("email", "=", email).executeTakeFirst().

2. .execute()[0] instead of executeTakeFirst()

Claude generates: const users = await db.selectFrom("users").execute(); return users[0].

Correct pattern: return await db.selectFrom("users").executeTakeFirst().

3. Mixing db and trx in transactions

Claude generates: a transaction callback that uses db.selectFrom(...) inside.

Correct pattern: every query inside the callback uses trx.selectFrom(...).

4. Manual commit/rollback

Claude generates: const trx = await db.startTransaction().execute(); try { ... await trx.commit(); } catch { await trx.rollback(); }.

Correct pattern: await db.transaction().execute(async (trx) => { ... throw new Error to rollback }).

5. Hand-edited types.ts

Claude generates: a manual addition to src/db/types.ts to "add a missing column".

Correct pattern: add the column via a migration, run npm run db:codegen, let the file regenerate.

6. Missing forUpdate on race-prone selects

Claude generates: a transaction that reads a balance, checks it, then decrements it, all without forUpdate().

Correct pattern: trx.selectFrom("users").select("balance_cents").where("id", "=", userId).forUpdate().executeTakeFirstOrThrow().

Mistake	Symptom	Fix
Raw SQL for builder cases	Lost type safety	Query builder
.execute()[0]	Unbounded query	executeTakeFirst
db inside transaction	Atomicity broken	trx everywhere
Manual commit/rollback	Leaked connections	transaction().execute
Hand-edited types.ts	Drift from schema	Run codegen
No forUpdate	Race condition	Add forUpdate

Add a common mistakes section to CLAUDE.md with these six pairs. Concrete pairs are more reliable than abstract rules for Claude to apply consistently.

Building type-safe SQL that scales

The Kysely CLAUDE.md in this guide produces SQL code where the query builder handles selects, inserts, updates, and deletes; the sql template tag is reserved for dialect-specific features the builder lacks; executeTakeFirstOrThrow enforces row presence at the call site; transactions use the callback pattern for atomicity; codegen runs after every migration to keep types in sync; and JSON column types flow through queries via the JSONColumnType marker.

The underlying principle is the same as any library integration with Claude Code. Kysely without a CLAUDE.md produces code that runs and returns results, but produces queries that bypass the type safety advantage that justified choosing Kysely in the first place, leaked transactions, race conditions on critical paths, and a types file that drifts from the live schema. The CLAUDE.md template removes each failure mode by making the correct pattern the only pattern Claude can generate.

For the next layer up from queries, Kysely pairs well with Claude Code with Valibot for runtime validation of inputs and Claude Code with Postgres for database-specific tuning. Claudify includes a Kysely CLAUDE.md template with the codegen flow, query builder patterns, transaction conventions, JSON column helpers, and all six common-mistake pairs pre-configured.

Get Claudify. Ship production-ready Kysely integrations with Claude Code from the first session.