Claude Code for Kysely: Type-Safe SQL Query Builder

Kysely is a type-safe SQL query builder for TypeScript — queries are fully typed from a database schema interface you define. db.selectFrom("orders").select(["id", "status", "totalCents"]).where("status", "=", "pending").execute() returns { id: string; status: string; totalCents: number }[] inferred from your schema. db.insertInto("orders").values({ ... }).returningAll().executeTakeFirstOrThrow() inserts and returns the new row. db.transaction().execute(async trx => { ... }) runs queries in a transaction. sql\NOW()`injects raw SQL.CamelCasePluginconverts snake_case to camelCase transparently.FileMigrationProviderwithdb.migrate.latest()runs migration files. Kysely works with PostgreSQL (viapg), SQLite (via better-sqlite3`), and MySQL. Claude Code generates Kysely schema types, query builders, join queries, transactions, and migration files for type-safe database access without an ORM.

CLAUDE.md for Kysely

## Kysely Stack
- Version: kysely >= 0.27
- Schema: interface Database { tableName: TableInterface } — column types exactly match DB
- Dialect: PostgresDialect({ pool: new Pool({ connectionString }) })
- Select: db.selectFrom("orders").select(["id", "status"]).where("id", "=", id).executeTakeFirst()
- Insert: db.insertInto("users").values({ email, name }).returningAll().executeTakeFirstOrThrow()
- Update: db.updateTable("orders").set({ status: "shipped" }).where("id", "=", id).execute()
- Join: .innerJoin("customers", "orders.customerId", "customers.id")
- Transaction: db.transaction().execute(async trx => { ... trx.insertInto(...) })
- Plugin: CamelCasePlugin() — auto snake_case ↔ camelCase
- Migrations: FileMigrationProvider from fs/path — db.migrate.latest()

Database Schema Types

// lib/db/types.ts — database interface matching your schema exactly
import type { Generated, Insertable, Selectable, Updateable, ColumnType } from "kysely"

// orders table
export interface OrderTable {
  id: Generated<string>          // AUTO-GENERATED — don't insert
  customer_id: string
  status: "pending" | "processing" | "shipped" | "delivered" | "cancelled"
  total_cents: number
  notes: string | null
  created_at: Generated<Date>    // AUTO-GENERATED
  updated_at: ColumnType<Date, never, Date>  // Never insert, update only
  deleted_at: Date | null
}

// customers table
export interface CustomerTable {
  id: Generated<string>
  email: string
  name: string
  stripe_customer_id: string | null
  created_at: Generated<Date>
}

// order_items table
export interface OrderItemTable {
  id: Generated<string>
  order_id: string
  product_id: string
  quantity: number
  unit_price_cents: number
}

// products table
export interface ProductTable {
  id: Generated<string>
  name: string
  slug: string
  price_cents: number
  stock_quantity: number
  active: boolean
  created_at: Generated<Date>
}

// Database interface — maps table names to row types
export interface Database {
  orders: OrderTable
  customers: CustomerTable
  order_items: OrderItemTable
  products: ProductTable
}

// Convenience types for CRUD operations
export type Order = Selectable<OrderTable>
export type NewOrder = Insertable<OrderTable>
export type OrderUpdate = Updateable<OrderTable>

export type Customer = Selectable<CustomerTable>
export type NewCustomer = Insertable<CustomerTable>

Kysely Client Setup

// lib/db/client.ts — Kysely with PostgreSQL
import { Kysely, PostgresDialect, CamelCasePlugin } from "kysely"
import { Pool } from "pg"
import type { Database } from "./types"

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

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
  plugins: [
    new CamelCasePlugin(),  // snake_case columns → camelCase in TypeScript
  ],
  log(event) {
    if (process.env.NODE_ENV === "development" && event.level === "query") {
      console.log("[Kysely]", event.query.sql, event.query.parameters)
    }
  },
})

export type DB = typeof db

Typed Queries

// lib/db/order-queries.ts — type-safe queries
import { db } from "./client"
import { sql } from "kysely"

// SELECT with joins — return type inferred from .select()
export async function getOrderWithCustomer(orderId: string) {
  return db
    .selectFrom("orders")
    .innerJoin("customers", "orders.customerId", "customers.id")
    .select([
      "orders.id",
      "orders.status",
      "orders.totalCents",
      "orders.createdAt",
      "customers.name as customerName",
      "customers.email as customerEmail",
    ])
    .where("orders.id", "=", orderId)
    .where("orders.deletedAt", "is", null)
    .executeTakeFirst()
  // Returns: { id, status, totalCents, createdAt, customerName, customerEmail } | undefined
}

// Complex query with subquery and aggregation
export async function getOrdersWithItemCount(filters: {
  status?: string
  customerId?: string
  limit?: number
  offset?: number
}) {
  let query = db
    .selectFrom("orders")
    .leftJoin("orderItems", "orders.id", "orderItems.orderId")
    .select([
      "orders.id",
      "orders.status",
      "orders.totalCents",
      "orders.createdAt",
      db.fn.count<number>("orderItems.id").as("itemCount"),
    ])
    .where("orders.deletedAt", "is", null)
    .groupBy("orders.id")
    .orderBy("orders.createdAt", "desc")
    .limit(filters.limit ?? 20)
    .offset(filters.offset ?? 0)

  if (filters.status) {
    query = query.where("orders.status", "=", filters.status)
  }
  if (filters.customerId) {
    query = query.where("orders.customerId", "=", filters.customerId)
  }

  return query.execute()
}

// INSERT with returning
export async function createOrder(data: {
  customerId: string
  status?: string
  totalCents: number
  notes?: string
}) {
  return db
    .insertInto("orders")
    .values({
      customerId: data.customerId,
      status: data.status ?? "pending",
      totalCents: data.totalCents,
      notes: data.notes ?? null,
    })
    .returningAll()
    .executeTakeFirstOrThrow()
  // Throws if insert fails — returns full row with generated id and timestamps
}

// UPDATE with conditional fields
export async function updateOrderStatus(
  orderId: string,
  status: string,
  notes?: string
) {
  const result = await db
    .updateTable("orders")
    .set({
      status,
      ...(notes !== undefined && { notes }),
      updatedAt: new Date(),
    })
    .where("id", "=", orderId)
    .where("deletedAt", "is", null)
    .returning(["id", "status", "updatedAt"])
    .executeTakeFirst()

  return result ?? null
}

// Raw SQL for complex queries
export async function getRevenueByMonth(year: number) {
  return db
    .selectFrom("orders")
    .select([
      sql<string>`TO_CHAR(created_at, 'YYYY-MM')`.as("month"),
      db.fn.sum<number>("totalCents").as("totalRevenue"),
      db.fn.count<number>("id").as("orderCount"),
    ])
    .where(sql`EXTRACT(YEAR FROM created_at)`, "=", year)
    .where("status", "!=", "cancelled")
    .where("deletedAt", "is", null)
    .groupBy(sql`TO_CHAR(created_at, 'YYYY-MM')`)
    .orderBy(sql`TO_CHAR(created_at, 'YYYY-MM')`, "asc")
    .execute()
}

Transactions

// lib/db/transaction-examples.ts — multi-table transactions
import { db } from "./client"

export async function placeOrder(params: {
  customerId: string
  items: { productId: string; quantity: number }[]
}) {
  return db.transaction().execute(async trx => {
    // 1. Fetch products and check stock
    const products = await trx
      .selectFrom("products")
      .select(["id", "name", "priceCents", "stockQuantity"])
      .where("id", "in", params.items.map(i => i.productId))
      .execute()

    for (const item of params.items) {
      const product = products.find(p => p.id === item.productId)
      if (!product) throw new Error(`Product ${item.productId} not found`)
      if (product.stockQuantity < item.quantity) {
        throw new Error(`Insufficient stock for ${product.name}`)
      }
    }

    // 2. Calculate total
    const totalCents = params.items.reduce((sum, item) => {
      const product = products.find(p => p.id === item.productId)!
      return sum + product.priceCents * item.quantity
    }, 0)

    // 3. Create order
    const order = await trx
      .insertInto("orders")
      .values({ customerId: params.customerId, totalCents, status: "pending" })
      .returningAll()
      .executeTakeFirstOrThrow()

    // 4. Create order items
    await trx
      .insertInto("orderItems")
      .values(
        params.items.map(item => ({
          orderId: order.id,
          productId: item.productId,
          quantity: item.quantity,
          unitPriceCents: products.find(p => p.id === item.productId)!.priceCents,
        }))
      )
      .execute()

    // 5. Decrement stock
    for (const item of params.items) {
      await trx
        .updateTable("products")
        .set(eb => ({ stockQuantity: eb("stockQuantity", "-", item.quantity) }))
        .where("id", "=", item.productId)
        .execute()
    }

    return order
  })
}

For the Drizzle ORM alternative when a more complete ORM with relations API, schema-as-code migrations, and a higher-level db.query API for nested eager loading are preferred — Drizzle has the schema definition and migration tooling built-in while Kysely leaves migrations to SQL files, see the Drizzle Advanced guide. For the raw pg alternative when Kysely’s builder abstraction is overkill and tagged template SQL strings with full type control using @pgtyped/runtime are preferred — @pgtyped/runtime generates TypeScript types directly from .sql files without any query builder layer, see the type-safe SQL guide. The Claude Skills 360 bundle includes Kysely skill sets covering schema types, joins, transactions, and migrations. Start with the free tier to try type-safe SQL generation.