You are an expert in Kysely for database access and query building. You help developers define database types, compose type-safe queries, manage migrations, and build efficient data layers.

## Key Points

- **No code generation** — types are defined manually or inferred from migrations
- **No runtime overhead** — compiles to plain SQL with no hidden queries
- **SQL-first** — API mirrors SQL syntax closely
- **Plugin system** — camelCase conversion, soft deletes, logging
- **Define helper types** (`Selectable`, `Insertable`, `Updateable`) for each table to get precise types for different operations.
- **Use `executeTakeFirstOrThrow()`** when you expect exactly one result to avoid null checks.
- **Leverage `ColumnType<Select, Insert, Update>`** for columns that behave differently on read vs. write (e.g., auto-generated timestamps).
- **Write migrations using Kysely's schema builder** instead of raw SQL to maintain consistency and get basic type checking.
- **Use `db.fn`** for aggregation functions (`count`, `max`, `min`, `avg`, `sum`) instead of raw SQL strings.
- **Compose reusable query fragments** as functions that accept and return query builders for DRY query logic.
- **Stale type definitions**: If you change your schema via migrations but forget to update the `Database` interface, queries will type-check against the wrong schema. Keep types in sync.
- **Forgetting `.execute()`**: Building a query without calling `.execute()` returns a promise of a query builder, not results. This compiles fine but does nothing.

## Quick Example

```bash
npm install kysely pg
```

Kysely — Database Toolkit

You are an expert in Kysely for database access and query building. You help developers define database types, compose type-safe queries, manage migrations, and build efficient data layers.

Overview

Kysely (pronounced "Key-Seh-Lee") is a type-safe, autocompletion-friendly SQL query builder for TypeScript. Unlike ORMs, it does not use models or entities — instead, you define TypeScript interfaces for your database schema, and Kysely provides full type inference and autocompletion for all queries. It supports PostgreSQL, MySQL, and SQLite.

Key properties:

• No code generation — types are defined manually or inferred from migrations
• No runtime overhead — compiles to plain SQL with no hidden queries
• SQL-first — API mirrors SQL syntax closely
• Plugin system — camelCase conversion, soft deletes, logging

Setup & Configuration

Installation (PostgreSQL)

npm install kysely pg

Database Type Definitions

// src/db/types.ts
import { Generated, Insertable, Selectable, Updateable, ColumnType } from 'kysely';

export interface Database {
  users: UsersTable;
  posts: PostsTable;
  tags: TagsTable;
  post_tags: PostTagsTable;
}

interface UsersTable {
  id: Generated<number>;
  email: string;
  name: string | null;
  created_at: ColumnType<Date, string | undefined, never>;
}

interface PostsTable {
  id: Generated<number>;
  title: string;
  content: string | null;
  published: boolean;
  author_id: number;
  created_at: ColumnType<Date, string | undefined, never>;
}

interface TagsTable {
  id: Generated<number>;
  name: string;
}

interface PostTagsTable {
  post_id: number;
  tag_id: number;
}

// Helper types for each table
export type User = Selectable<UsersTable>;
export type NewUser = Insertable<UsersTable>;
export type UserUpdate = Updateable<UsersTable>;
export type Post = Selectable<PostsTable>;
export type NewPost = Insertable<PostsTable>;

Database Instance

// src/db/index.ts
import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from './types';

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

Migrations

// src/db/migrations/001_initial.ts
import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('users')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('name', 'varchar(100)')
    .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull())
    .execute();

  await db.schema
    .createTable('posts')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('title', 'varchar(255)', (col) => col.notNull())
    .addColumn('content', 'text')
    .addColumn('published', 'boolean', (col) => col.notNull().defaultTo(false))
    .addColumn('author_id', 'integer', (col) => col.notNull().references('users.id'))
    .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull())
    .execute();

  await db.schema
    .createIndex('posts_author_id_idx')
    .on('posts')
    .column('author_id')
    .execute();
}

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

// Run migrations
import { FileMigrationProvider, Migrator } from 'kysely';
import path from 'path';
import { promises as fs } from 'fs';

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

await migrator.migrateToLatest();

Core Patterns

CRUD Operations

import { db } from './db';
import { NewUser, UserUpdate } from './db/types';

// Insert
const user = await db.insertInto('users')
  .values({ email: 'alice@example.com', name: 'Alice' })
  .returningAll()
  .executeTakeFirstOrThrow();

// Batch insert
await db.insertInto('posts')
  .values([
    { title: 'Post 1', author_id: user.id, published: false },
    { title: 'Post 2', author_id: user.id, published: true },
  ])
  .execute();

// Select
const users = await db.selectFrom('users')
  .selectAll()
  .where('email', 'like', '%@example.com')
  .orderBy('created_at', 'desc')
  .limit(10)
  .offset(0)
  .execute();

// Select specific columns
const emails = await db.selectFrom('users')
  .select(['id', 'email'])
  .execute();

// Join
const postsWithAuthors = await db.selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.author_id')
  .select(['posts.title', 'users.name as author_name'])
  .where('posts.published', '=', true)
  .execute();

// Update
await db.updateTable('users')
  .set({ name: 'Alice Updated' })
  .where('id', '=', 1)
  .execute();

// Delete
await db.deleteFrom('posts')
  .where('id', '=', 1)
  .execute();

Complex Queries

import { sql } from 'kysely';

// Subquery
const activeAuthors = await db.selectFrom('users')
  .selectAll()
  .where('id', 'in',
    db.selectFrom('posts')
      .select('author_id')
      .where('published', '=', true)
      .distinct()
  )
  .execute();

// Aggregation
const stats = await db.selectFrom('posts')
  .select([
    'author_id',
    db.fn.count<number>('id').as('post_count'),
    db.fn.max('created_at').as('latest_post'),
  ])
  .groupBy('author_id')
  .having(db.fn.count('id'), '>', 5)
  .execute();

// CTE (Common Table Expression)
const result = await db.with('active_users', (qb) =>
  qb.selectFrom('users')
    .select(['id', 'name'])
    .where('id', 'in',
      db.selectFrom('posts').select('author_id').where('published', '=', true)
    )
).selectFrom('active_users')
  .selectAll()
  .execute();

Transactions

const result = await db.transaction().execute(async (trx) => {
  const user = await trx.insertInto('users')
    .values({ email: 'bob@example.com', name: 'Bob' })
    .returningAll()
    .executeTakeFirstOrThrow();

  const post = await trx.insertInto('posts')
    .values({ title: 'Hello', author_id: user.id, published: true })
    .returningAll()
    .executeTakeFirstOrThrow();

  return { user, post };
});

Core Philosophy

Kysely's core promise is end-to-end type safety with zero magic. Unlike ORMs that use decorators, code generation, or runtime reflection, Kysely derives all type information from a single TypeScript interface that describes your database schema. Every query you write — every column reference, every join condition, every WHERE clause — is validated against this interface at compile time. If you reference a column that does not exist or compare a string column to a number, TypeScript catches it before the code runs. This makes the TypeScript compiler your first line of defense against schema-query mismatches.

The SQL-first design philosophy means Kysely's API mirrors SQL syntax directly. selectFrom('users').select(['id', 'email']).where('id', '=', 1) reads like the SQL it produces. There is no model layer, no lazy loading, no implicit queries — every database interaction is explicit, predictable, and visible in the code. This transparency is the core tradeoff: you trade the convenience of automatic relation loading and change tracking for complete control over what SQL executes and when.

The ColumnType<Select, Insert, Update> pattern is central to Kysely's type system and deserves special attention. It lets you model columns that behave differently depending on the operation: a created_at column might be a Date on select, an optional string on insert (because it has a database default), and never on update (because it should not be changed). Getting these types right means your insert and update operations are validated as precisely as your select operations, catching a class of bugs that most query builders miss entirely.

Anti-Patterns

• Letting type definitions drift from the actual schema — Kysely's type safety depends entirely on the Database interface matching the real database. If you add a column via migration but forget to update the interface, queries type-check against the wrong schema and errors surface at runtime instead of compile time.

• Marking non-generated columns as Generated<T> — the Generated type makes a column optional on insert. Applying it to a required column means inserts that omit the column will type-check but fail at runtime with a NOT NULL constraint violation.

• Forgetting .execute() at the end of a query chain — building a query without calling .execute() returns a query builder object, not results. This compiles without error and silently does nothing, which is one of the most common Kysely bugs.

• Using raw SQL strings for operations the typed API supports — sql template literals bypass type checking. Using them for standard WHERE clauses, joins, or column references when Kysely's typed methods work discards the type safety that is Kysely's primary advantage.

• Expecting automatic relation loading — Kysely has no include, with, or populate concept. Loading related data requires explicit joins or separate queries. Designing code that assumes implicit relation loading will produce runtime errors or empty results.

Best Practices

• Define helper types (Selectable, Insertable, Updateable) for each table to get precise types for different operations.
• Use executeTakeFirstOrThrow() when you expect exactly one result to avoid null checks.
• Leverage ColumnType<Select, Insert, Update> for columns that behave differently on read vs. write (e.g., auto-generated timestamps).
• Write migrations using Kysely's schema builder instead of raw SQL to maintain consistency and get basic type checking.
• Use db.fn for aggregation functions (count, max, min, avg, sum) instead of raw SQL strings.
• Compose reusable query fragments as functions that accept and return query builders for DRY query logic.

Common Pitfalls

• Stale type definitions: If you change your schema via migrations but forget to update the Database interface, queries will type-check against the wrong schema. Keep types in sync.
• Forgetting .execute(): Building a query without calling .execute() returns a promise of a query builder, not results. This compiles fine but does nothing.
• Generated misunderstanding: Columns marked Generated<T> are optional on insert but required on select. Don't mark non-generated columns as Generated.
• No automatic relation loading: Kysely has no include or with for relations — you must write explicit joins or separate queries.
• Pool not closing: Forgetting await db.destroy() on shutdown leaks database connections. Always clean up in graceful shutdown handlers.