Claude Code for Kysely: Type-Safe SQL Query Builder

Kysely is a type-safe SQL query builder for TypeScript — it knows the shape of every result based on your database schema type, not an ORM abstraction. db.selectFrom("orders").select(["id","status","total_cents"]).where("customer_id","=",customerId).execute() returns { id: string; status: string; total_cents: number }[] — TypeScript errors if you select a non-existent column. The Database interface defines all table schemas. Codegen derives this interface from an existing database. Joins produce correctly typed merged results. The transaction API provides the same fluent builder within a serialized transaction. Migrations use the Migrator class with up/down functions. sql template tag escapes raw SQL safely. Kysely is a peer to Drizzle — both avoid the ORM model but Kysely stays closer to SQL syntax. Claude Code generates Kysely database types, query builders, migration files, and the full CRUD patterns matching your PostgreSQL or MySQL schema.

CLAUDE.md for Kysely Projects

## Kysely Stack
- Version: kysely >= 0.27, kysely-codegen for schema-to-types
- Dialect: PostgresDialect (pg), MysqlDialect (mysql2), SqliteDialect (better-sqlite3)
- Types: Database interface with table types — runs Codegen to derive from existing DB
- Queries: db.selectFrom("table").select([...]).where(col, op, val).execute()
- Joins: innerJoin/leftJoin with typed on condition and merged result type
- Tx: db.transaction().execute(async (trx) => {...}) — same API as db
- Migrations: new Migrator({ db, provider }) — up(db) / down(db) functions
- Raw: sql`SELECT ${sql.raw(col)} FROM orders WHERE id = ${id}` — parameterized safe

Database Type and Setup

// lib/database.ts — Kysely setup with types
import { Kysely, PostgresDialect } from "kysely"
import { Pool } from "pg"

// Database interface: one type per table
// Kysely uses Insertable<T>, Selectable<T>, Updateable<T> wrappers
interface Orders {
  id: string
  customer_id: string
  status: "pending" | "processing" | "shipped" | "delivered" | "cancelled"
  total_cents: number
  items: unknown  // JSONB — type in application code
  tracking_number: string | null
  created_at: Date
  updated_at: Date
}

interface Customers {
  id: string
  email: string
  name: string
  plan: "free" | "pro" | "enterprise"
  stripe_customer_id: string | null
  created_at: Date
}

interface Products {
  id: string
  slug: string
  name: string
  description: string | null
  price_cents: number
  stock: number
  active: boolean
}

// Export Database interface for Kysely
export interface Database {
  orders: Orders
  customers: Customers
  products: Products
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 20,
  idleTimeoutMillis: 30_000,
})

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
})

Queries

// lib/queries/orders.ts — Kysely query examples
import { db, Database } from "../database"
import { sql } from "kysely"
import type { Insertable, Updateable } from "kysely"

interface OrderItem {
  productId: string
  name: string
  quantity: number
  priceCents: number
}

export async function listCustomerOrders(
  customerId: string,
  opts: { status?: string; limit?: number; offset?: number } = {}
) {
  const { status, limit = 20, offset = 0 } = opts

  let query = db
    .selectFrom("orders")
    .select([
      "id",
      "status",
      "total_cents",
      "created_at",
    ])
    .where("customer_id", "=", customerId)
    .orderBy("created_at", "desc")
    .limit(limit)
    .offset(offset)

  if (status) {
    query = query.where("status", "=", status as Orders["status"])
  }

  return query.execute()
}

export async function getOrderWithCustomer(orderId: string) {
  return db
    .selectFrom("orders")
    .innerJoin("customers", "customers.id", "orders.customer_id")
    .select([
      "orders.id",
      "orders.status",
      "orders.total_cents",
      "orders.created_at",
      "customers.email as customer_email",
      "customers.name as customer_name",
      "customers.plan as customer_plan",
    ])
    .where("orders.id", "=", orderId)
    .executeTakeFirst()
  // Returns: { id, status, total_cents, created_at, customer_email, customer_name, customer_plan } | undefined
}

export async function createOrder(
  customerId: string,
  items: OrderItem[],
  totalCents: number
) {
  const id = crypto.randomUUID()

  return db
    .insertInto("orders")
    .values({
      id,
      customer_id: customerId,
      status: "pending",
      total_cents: totalCents,
      items: JSON.stringify(items),
      created_at: new Date(),
      updated_at: new Date(),
    } satisfies Insertable<Database["orders"]>)
    .returningAll()
    .executeTakeFirstOrThrow()
}

export async function updateOrderStatus(
  orderId: string,
  status: Orders["status"]
) {
  return db
    .updateTable("orders")
    .set({
      status,
      updated_at: new Date(),
    } satisfies Updateable<Database["orders"]>)
    .where("id", "=", orderId)
    .returningAll()
    .executeTakeFirstOrThrow()
}

// Revenue stats with raw SQL for aggregates
export async function getRevenueStats(startDate: Date, endDate: Date) {
  const result = await db
    .selectFrom("orders")
    .select([
      db.fn.count<number>("id").as("order_count"),
      db.fn.sum<number>("total_cents").as("total_revenue_cents"),
      db.fn.avg<number>("total_cents").as("avg_order_cents"),
    ])
    .where("created_at", ">=", startDate)
    .where("created_at", "<=", endDate)
    .where("status", "!=", "cancelled")
    .executeTakeFirstOrThrow()

  return {
    orderCount: result.order_count,
    totalRevenueCents: result.total_revenue_cents ?? 0,
    avgOrderCents: result.avg_order_cents ?? 0,
  }
}

Transactions

// lib/queries/checkout.ts — Kysely transaction
export async function processCheckout(
  customerId: string,
  items: OrderItem[]
) {
  return db.transaction().execute(async (trx) => {
    // All operations within trx are atomic

    // 1. Check and reserve stock
    for (const item of items) {
      const product = await trx
        .selectFrom("products")
        .select(["id", "stock", "price_cents"])
        .where("id", "=", item.productId)
        .forUpdate()  // Row-level lock
        .executeTakeFirstOrThrow()

      if (product.stock < item.quantity) {
        throw new Error(`Insufficient stock for product ${item.productId}`)
      }

      await trx
        .updateTable("products")
        .set({ stock: product.stock - item.quantity })
        .where("id", "=", item.productId)
        .execute()
    }

    // 2. Create order
    const totalCents = items.reduce(
      (sum, item) => sum + item.priceCents * item.quantity,
      0
    )

    const order = await trx
      .insertInto("orders")
      .values({
        id: crypto.randomUUID(),
        customer_id: customerId,
        status: "pending",
        total_cents: totalCents,
        items: JSON.stringify(items),
        created_at: new Date(),
        updated_at: new Date(),
      })
      .returningAll()
      .executeTakeFirstOrThrow()

    return order
  })
}

Migrations

// migrations/001_create_orders.ts
import type { Kysely } from "kysely"

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("orders")
    .addColumn("id", "uuid", col => col.primaryKey().defaultTo(sql`gen_random_uuid()`))
    .addColumn("customer_id", "uuid", col => col.notNull().references("customers.id"))
    .addColumn("status", "text", col => col.notNull().defaultTo("pending"))
    .addColumn("total_cents", "integer", col => col.notNull())
    .addColumn("items", "jsonb", col => col.notNull())
    .addColumn("tracking_number", "text")
    .addColumn("created_at", "timestamptz", col =>
      col.notNull().defaultTo(sql`now()`)
    )
    .addColumn("updated_at", "timestamptz", col =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute()

  await db.schema
    .createIndex("orders_customer_id_idx")
    .on("orders")
    .column("customer_id")
    .execute()

  await db.schema
    .createIndex("orders_status_idx")
    .on("orders")
    .column("status")
    .execute()
}

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

// lib/migrate.ts — run migrations programmatically
import { FileMigrationProvider, Migrator } from "kysely"
import { db } from "./database"
import { promises as fs } from "fs"
import * as path from "path"

const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder: path.join(__dirname, "../../migrations"),
  }),
})

// Run all pending migrations
export async function migrateToLatest() {
  const { error, results } = await migrator.migrateToLatest()

  results?.forEach(r => {
    console.log(`${r.status}: ${r.migrationName}`)
  })

  if (error) {
    console.error("Migration failed", error)
    process.exit(1)
  }
}

For the Drizzle ORM alternative that also generates types from schema definitions but with a more ORM-like query syntax and schema-as-code approach instead of Kysely’s type-interface approach, see the Drizzle guide for schema definition and migrations. For the Prisma ORM alternative when automatic migrations, Prisma Studio, and the broader Prisma ecosystem (Accelerate, Pulse) are needed alongside stronger abstraction over raw SQL, see the Prisma guide for schema and query patterns. The Claude Skills 360 bundle includes Kysely skill sets covering type setup, query building, and migrations. Start with the free tier to try Kysely query generation.