Kysely - Type-Safe SQL Query Builder Overview

Kysely is a type-safe TypeScript SQL query builder that provides end-to-end type safety from database schema to query results. Unlike ORMs, it generates plain SQL and gives you full control while maintaining perfect TypeScript inference.

Key Features:

Complete type inference (schema → queries → results) Zero runtime overhead (compiles to SQL) Database-agnostic (PostgreSQL, MySQL, SQLite, MSSQL) Migration system included Plugin ecosystem (CTEs, JSON, geospatial) Raw SQL integration when needed

Installation:

npm install kysely

Database driver (choose one)

npm install pg # PostgreSQL npm install mysql2 # MySQL npm install better-sqlite3 # SQLite

Quick Start 1. Define Database Schema Types import { Generated, Selectable, Insertable, Updateable } from 'kysely';

// Table interface (all columns) interface UserTable { id: Generated; email: string; name: string | null; created_at: Generated; updated_at: Date; }

interface PostTable { id: Generated; user_id: number; title: string; content: string; published: Generated; created_at: Generated; }

// Database interface interface Database { users: UserTable; posts: PostTable; }

// Type-safe query result types type User = Selectable; type NewUser = Insertable; type UserUpdate = Updateable;

Create Database Instance import { Kysely, PostgresDialect } from 'kysely'; import { Pool } from 'pg';

const db = new Kysely({ dialect: new PostgresDialect({ pool: new Pool({ host: process.env.DB_HOST, database: process.env.DB_NAME, user: process.env.DB_USER, password: process.env.DB_PASSWORD, max: 10, }), }), });

Type-Safe Queries // SELECT with full type inference const users = await db .selectFrom('users') .select(['id', 'email', 'name']) .where('created_at', '>', new Date('2024-01-01')) .execute(); // Type: Array<{ id: number; email: string; name: string | null }>

// INSERT with type checking const newUser: NewUser = { email: 'alice@example.com', name: 'Alice', updated_at: new Date(), };

const inserted = await db .insertInto('users') .values(newUser) .returningAll() .executeTakeFirstOrThrow(); // Type: User

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

// DELETE await db .deleteFrom('users') .where('email', 'like', '%@spam.com') .execute();

Advanced Query Patterns Joins with Type Safety // INNER JOIN const usersWithPosts = await db .selectFrom('users') .innerJoin('posts', 'posts.user_id', 'users.id') .select([ 'users.id', 'users.name', 'posts.title', 'posts.content', ]) .execute(); // Type: Array<{ id: number; name: string | null; title: string; content: string }>

// LEFT JOIN with null handling const usersWithOptionalPosts = await db .selectFrom('users') .leftJoin('posts', 'posts.user_id', 'users.id') .select([ 'users.id', 'users.email', 'posts.title', // Type: string | null (from LEFT JOIN) ]) .execute();

// Multiple joins const complexQuery = await db .selectFrom('posts') .innerJoin('users', 'users.id', 'posts.user_id') .leftJoin('comments', 'comments.post_id', 'posts.id') .select([ 'posts.id as postId', 'posts.title', 'users.name as authorName', 'comments.id as commentId', ]) .execute();

Aggregations and Grouping import { sql } from 'kysely';

// COUNT, AVG, SUM const stats = await db .selectFrom('posts') .select([ 'user_id', db.fn.count('id').as('post_count'), db.fn.avg('views').as('avg_views'), ]) .groupBy('user_id') .having(db.fn.count('id'), '>', 5) .execute(); // Type: Array<{ user_id: number; post_count: number; avg_views: number }>

// Complex aggregations with raw SQL const advanced = await db .selectFrom('users') .select([ 'users.id', sqlCOUNT(DISTINCT posts.id).as('total_posts'), sqlMAX(posts.created_at).as('latest_post'), ]) .leftJoin('posts', 'posts.user_id', 'users.id') .groupBy('users.id') .execute();

Subqueries // Scalar subquery const usersWithPostCount = await db .selectFrom('users') .select([ 'users.id', 'users.name', (eb) => eb .selectFrom('posts') .select(eb.fn.count('id').as('count')) .whereRef('posts.user_id', '=', 'users.id') .as('post_count'), ]) .execute();

// EXISTS subquery const activeUsers = await db .selectFrom('users') .selectAll() .where((eb) => eb.exists( eb .selectFrom('posts') .select('id') .whereRef('posts.user_id', '=', 'users.id') .where('created_at', '>', new Date('2024-01-01')) ) ) .execute();

// IN subquery const usersInTopTier = await db .selectFrom('users') .selectAll() .where( 'id', 'in', db.selectFrom('posts') .select('user_id') .groupBy('user_id') .having(db.fn.count('id'), '>', 100) ) .execute();

Common Table Expressions (CTEs) // WITH clause const result = await db .with('popular_posts', (db) => db .selectFrom('posts') .select(['id', 'user_id', 'title']) .where('views', '>', 1000) ) .with('active_users', (db) => db .selectFrom('users') .select(['id', 'email']) .where('last_login', '>', new Date('2024-01-01')) ) .selectFrom('popular_posts') .innerJoin('active_users', 'active_users.id', 'popular_posts.user_id') .selectAll() .execute();

// Recursive CTE (organizational hierarchy) interface OrgNode { id: number; name: string; parent_id: number | null; level: number; }

const hierarchy = await db .withRecursive('org_tree', (db) => db .selectFrom('departments') .select(['id', 'name', 'parent_id', sql0.as('level')]) .where('parent_id', 'is', null) .unionAll( db .selectFrom('departments') .innerJoin('org_tree', 'org_tree.id', 'departments.parent_id') .select([ 'departments.id', 'departments.name', 'departments.parent_id', sqlorg_tree.level + 1.as('level'), ]) ) ) .selectFrom('org_tree') .selectAll() .execute();

Schema Generation from Database Using kysely-codegen

Install

npm install --save-dev kysely-codegen

Generate types from existing database

npx kysely-codegen --url "postgresql://user:pass@localhost:5432/mydb"

Generated output:

// Generated by kysely-codegen import type { ColumnType, Generated } from 'kysely';

export interface Database { users: UsersTable; posts: PostsTable; comments: CommentsTable; }

export interface UsersTable { id: Generated; email: string; name: string | null; created_at: Generated; }

export interface PostsTable { id: Generated; user_id: number; title: string; content: string; published: Generated; created_at: Generated; }

Custom Type Mapping // Map database types to TypeScript types interface CustomTypes { timestamp: Date; jsonb: unknown; numeric: string; // Preserve precision uuid: string; }

interface ProductTable { id: ColumnType; // SELECT, INSERT, UPDATE types metadata: ColumnType, string, string>; // JSON column price: ColumnType; // Numeric }

Migrations Migration Setup import { Kysely, Migrator, FileMigrationProvider } from 'kysely'; 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 async function migrateToLatest() { const { error, results } = await migrator.migrateToLatest();

results?.forEach((it) => { if (it.status === 'Success') { console.log(Migration "${it.migrationName}" executed successfully); } else if (it.status === 'Error') { console.error(Migration "${it.migrationName}" failed); } });

if (error) { console.error('Migration failed:', error); process.exit(1); } }

// Rollback last migration async function migrateDown() { const { error, results } = await migrator.migrateDown(); // Handle results... }

Migration Files // migrations/001_create_users.ts import { Kysely, sql } from 'kysely';

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

await db.schema .createIndex('users_email_idx') .on('users') .column('email') .execute(); }

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

Complex Migration Examples // Add foreign key export async function up(db: Kysely): Promise { await db.schema .createTable('posts') .addColumn('id', 'serial', (col) => col.primaryKey()) .addColumn('user_id', 'integer', (col) => col.references('users.id').onDelete('cascade').notNull() ) .addColumn('title', 'varchar(500)', (col) => col.notNull()) .addColumn('content', 'text') .execute(); }

// Alter table export async function up(db: Kysely): Promise { await db.schema .alterTable('users') .addColumn('bio', 'text') .execute();

await db.schema .alterTable('users') .modifyColumn('email', 'varchar(320)') .execute(); }

// Add enum column (PostgreSQL) export async function up(db: Kysely): Promise { await sqlCREATE TYPE user_role AS ENUM ('admin', 'user', 'guest').execute(db);

await db.schema .alterTable('users') .addColumn('role', sqluser_role, (col) => col.defaultTo('user')) .execute(); }

Transactions Basic Transactions // Automatic rollback on error await db.transaction().execute(async (trx) => { await trx .insertInto('users') .values({ email: 'alice@example.com', name: 'Alice', updated_at: new Date() }) .execute();

await trx .insertInto('posts') .values({ user_id: 1, title: 'First Post', content: 'Hello' }) .execute(); });

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

const post = await trx .insertInto('posts') .values({ user_id: user.id, title: 'Bob\'s Post', content: 'Content', }) .returningAll() .executeTakeFirstOrThrow();

return { user, post }; });

Isolation Levels import { IsolationLevel } from 'kysely';

// Read committed (default) await db.transaction() .setIsolationLevel('read committed') .execute(async (trx) => { // Transaction logic });

// Serializable (strongest isolation) await db.transaction() .setIsolationLevel('serializable') .execute(async (trx) => { const balance = await trx .selectFrom('accounts') .select('balance') .where('id', '=', accountId) .executeTakeFirstOrThrow();

await trx
  .updateTable('accounts')
  .set({ balance: balance.balance - amount })
  .where('id', '=', accountId)
  .execute();

});

Raw SQL Integration Using sql Template Tag import { sql } from 'kysely';

// Raw SQL in SELECT const result = await db .selectFrom('users') .select([ 'id', sqlUPPER(name).as('uppercase_name'), sqlEXTRACT(YEAR FROM created_at).as('year_created'), ]) .execute();

// Raw SQL in WHERE const filtered = await db .selectFrom('posts') .selectAll() .where(sqlLOWER(title), 'like', '%typescript%') .execute();

// Complex raw queries const custom = await sql<{ total: number; avg_age: number }>SELECT COUNT(*) as total, AVG(EXTRACT(YEAR FROM age(birth_date))) as avg_age FROM users WHERE active = true.execute(db);

Full Raw Queries // Execute arbitrary SQL const result = await sqlWITH ranked_posts AS ( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY views DESC) as rank FROM posts p ) SELECT * FROM ranked_posts WHERE rank <= 3.execute(db);

// Parameterized raw queries const email = 'alice@example.com'; const user = await sqlSELECT * FROM users WHERE email = ${email}.execute(db);

Plugin Ecosystem JSON Operations (PostgreSQL) import { jsonBuildObject, jsonArrayFrom } from 'kysely/helpers/postgres';

// Build JSON objects const usersWithPosts = await db .selectFrom('users') .select([ 'users.id', 'users.name', jsonArrayFrom( db .selectFrom('posts') .select(['posts.id', 'posts.title', 'posts.content']) .whereRef('posts.user_id', '=', 'users.id') ).as('posts'), ]) .execute(); // Result: { id: 1, name: "Alice", posts: [{ id: 1, title: "..." }] }

// JSON aggregation const nested = await db .selectFrom('users') .select([ 'users.id', jsonBuildObject({ name: 'users.name', email: 'users.email', postCount: sql(SELECT COUNT(*) FROM posts WHERE user_id = users.id), }).as('user_data'), ]) .execute();

Pagination Plugin import { SelectQueryBuilder } from 'kysely';

function paginate( query: SelectQueryBuilder, page: number, pageSize: number ) { return query.limit(pageSize).offset((page - 1) * pageSize); }

// Usage const page = 2; const pageSize = 20;

const users = await paginate( db.selectFrom('users').selectAll(), page, pageSize ).execute();

// With total count async function paginateWithCount( query: SelectQueryBuilder, page: number, pageSize: number ) { const [items, { count }] = await Promise.all([ query.limit(pageSize).offset((page - 1) * pageSize).execute(), query.select(db.fn.count('id').as('count')).executeTakeFirstOrThrow(), ]);

return { items, total: count, page, pageSize, totalPages: Math.ceil(count / pageSize), }; }

Full-Text Search (PostgreSQL) // GIN index for full-text search export async function up(db: Kysely): Promise { await sqlALTER TABLE posts ADD COLUMN search_vector tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, '')) ) STORED.execute(db);

await sqlCREATE INDEX posts_search_idx ON posts USING GIN (search_vector).execute(db); }

// Full-text search query const searchResults = await db .selectFrom('posts') .selectAll() .where( sqlsearch_vector, '@@', sqlto_tsquery('english', ${query}) ) .execute();

Kysely vs Drizzle vs Prisma Feature Comparison Feature Kysely Drizzle Prisma Type Safety Full (schema → queries) Full (schema → queries) Full (generated client) SQL Control ✅ Raw SQL friendly ✅ Raw SQL friendly ❌ Limited Bundle Size ~50kB ~30kB ~500kB+ Migration System ✅ Built-in ✅ Built-in ✅ Powerful CLI Query Performance ✅ Plain SQL ✅ Plain SQL ❌ Slower (abstraction) Schema Definition TypeScript types TypeScript schema Prisma schema Codegen Required Optional No ✅ Required ORM Features ❌ Query builder only Partial (relational) ✅ Full ORM Learning Curve Medium (SQL knowledge) Medium Easy (abstracts SQL) Best For SQL-first, complex queries Type-safe schemas Rapid prototyping When to Choose Kysely

✅ Choose Kysely when:

You know SQL and want full control Complex queries (CTEs, window functions, subqueries) Performance is critical (no ORM overhead) Migrating from raw SQL Need raw SQL escape hatch frequently Working with existing databases Bundle size matters (edge functions)

❌ Choose Drizzle when:

Want declarative TypeScript schemas Need relational query capabilities Prefer ORM-like ergonomics with SQL control Working with new greenfield projects

❌ Choose Prisma when:

Team unfamiliar with SQL Rapid prototyping and iteration Need powerful migration tooling Want automatic relation handling Prefer declarative schema language Migration from Prisma // Prisma const users = await prisma.user.findMany({ where: { createdAt: { gte: new Date('2024-01-01') } }, include: { posts: true }, });

// Kysely equivalent const users = await db .selectFrom('users') .select([ 'users.id', 'users.email', jsonArrayFrom( db.selectFrom('posts') .selectAll() .whereRef('posts.user_id', '=', 'users.id') ).as('posts'), ]) .where('created_at', '>=', new Date('2024-01-01')) .execute();

Best Practices Define schema types first - Use Generated, Selectable, Insertable, Updateable Use kysely-codegen - Generate types from existing databases Leverage type inference - Let TypeScript infer result types Use transactions - For multi-step operations Raw SQL when needed - Don't fight the query builder Paginate large results - Use LIMIT/OFFSET or cursor-based Index frequently queried columns - Performance is your responsibility Test migrations - Both up and down Use CTEs for readability - Complex queries become maintainable Connection pooling - Configure database pool appropriately Common Pitfalls

❌ Forgetting to execute queries:

// WRONG - returns query builder, not results const users = db.selectFrom('users').selectAll();

// CORRECT const users = await db.selectFrom('users').selectAll().execute();

❌ Not handling null from LEFT JOIN:

// TypeScript knows posts.title can be null from LEFT JOIN const result = await db .selectFrom('users') .leftJoin('posts', 'posts.user_id', 'users.id') .select(['users.name', 'posts.title']) .execute(); // posts.title type: string | null

❌ Missing Generated for auto-increment columns:

// WRONG - TypeScript will require 'id' in INSERT interface UserTable { id: number; // Bad! }

// CORRECT interface UserTable { id: Generated; // INSERT doesn't require id }

Resources Documentation: https://kysely.dev GitHub: https://github.com/kysely-org/kysely Discord: https://discord.gg/kysely kysely-codegen: https://github.com/RobinBlomberg/kysely-codegen Playground: https://kysely-org.github.io/kysely-playground/ Related Skills

When using Kysely, consider these complementary skills:

typescript-core: TypeScript type system, advanced patterns, and tsconfig optimization database-migration: Safe schema evolution patterns for production databases Node.js backend: Server setup, connection pooling, and database configuration Quick TypeScript Type System Reference (Inlined for Standalone Use) // Kysely leverages advanced TypeScript features import { Kysely, Generated, ColumnType } from 'kysely';

// Database interface with Generated types interface Database { users: { id: Generated; // Auto-generated by database email: string; created_at: ColumnType; // ColumnType }; }

// Type inference in queries const db = new Kysely({ / config / });

// Full type safety - TypeScript knows return type const users = await db .selectFrom('users') .select(['id', 'email']) .where('created_at', '>', new Date('2025-01-01')) .execute(); // Type: Array<{ id: number; email: string }>

// Conditional types for dynamic queries type SelectFields = { [K in keyof T]: T[K] extends ColumnType ? S : T[K]; };

Quick Database Migration Patterns (Inlined for Standalone Use)

Safe Migration Principles:

Backward compatible - New code works with old schema Reversible - Can rollback migrations if needed Zero downtime - No service interruption Incremental - Small changes, not big-bang rewrites

Kysely Migration Example:

// migrations/001_add_full_name.ts import { Kysely, sql } from 'kysely';

export async function up(db: Kysely): Promise { // Phase 1: Add new column (nullable initially) await db.schema .alterTable('users') .addColumn('full_name', 'varchar(255)') .execute();

// Phase 2: Backfill data await db .updateTable('users') .set({ full_name: sqlconcat(first_name, ' ', last_name) }) .execute();

// Phase 3: Make required (separate migration recommended) // await db.schema // .alterTable('users') // .alterColumn('full_name', (col) => col.setNotNull()) // .execute(); }

export async function down(db: Kysely): Promise { await db.schema .alterTable('users') .dropColumn('full_name') .execute(); }

Common Safe Migrations:

// Add index (concurrently for PostgreSQL) await db.schema .createIndex('idx_users_email') .on('users') .column('email') .execute();

// Rename column (multi-phase approach) // Phase 1: Add new column await db.schema .alterTable('users') .addColumn('email_address', 'varchar(255)') .execute();

// Phase 2: Copy data await db .updateTable('users') .set({ email_address: sqlemail }) .execute();

// Phase 3: Drop old column (after deploy) // await db.schema // .alterTable('users') // .dropColumn('email') // .execute();

// Change column type (add new, migrate, drop old) await db.schema .alterTable('products') .addColumn('price_cents', 'integer') .execute();

await db .updateTable('products') .set({ price_cents: sqlcast(price * 100 as integer) }) .execute();

Running Migrations:

// migrate.ts import { Kysely, Migrator, FileMigrationProvider } from 'kysely'; import { promises as fs } from 'fs'; import path from 'path';

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

// Migrate to latest const { error, results } = await migrator.migrateToLatest();

// Migrate up/down await migrator.migrateUp(); await migrator.migrateDown();

// List pending migrations const migrations = await migrator.getMigrations();

[Full TypeScript patterns and migration workflows available in respective skills if deployed together]

Summary Kysely is a type-safe SQL query builder, not an ORM Full type inference from schema definitions to query results Zero runtime overhead - compiles to plain SQL Migration system included with up/down support Raw SQL integration when query builder isn't enough Plugin ecosystem for JSON, pagination, full-text search Best for developers who know SQL and want type safety Alternative to Prisma (full ORM) and Drizzle (schema-first) Perfect for complex queries, existing databases, performance-critical apps

安装

Database driver (choose one)

Install

Generate types from existing database