Agent skill
kysely
Kysely type-safe SQL query builder - End-to-end type safety from schema to queries, migrations, transactions, plugins
Install this agent skill to your Project
npx add-skill https://github.com/bobmatnyc/claude-mpm-skills/tree/main/toolchains/typescript/data/kysely
SKILL.md
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<number>;
email: string;
name: string | null;
created_at: Generated<Date>;
updated_at: Date;
}
interface PostTable {
id: Generated<number>;
user_id: number;
title: string;
content: string;
published: Generated<boolean>;
created_at: Generated<Date>;
}
// Database interface
interface Database {
users: UserTable;
posts: PostTable;
}
// Type-safe query result types
type User = Selectable<UserTable>;
type NewUser = Insertable<UserTable>;
type UserUpdate = Updateable<UserTable>;
2. Create Database Instance
import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
const db = new Kysely<Database>({
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,
}),
}),
});
3. 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<number>('id').as('post_count'),
db.fn.avg<number>('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',
sql<number>`COUNT(DISTINCT posts.id)`.as('total_posts'),
sql<Date>`MAX(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<number>('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', sql<number>`0`.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',
sql<number>`org_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<number>;
email: string;
name: string | null;
created_at: Generated<Date>;
}
export interface PostsTable {
id: Generated<number>;
user_id: number;
title: string;
content: string;
published: Generated<boolean>;
created_at: Generated<Date>;
}
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<string, string | undefined, string>; // SELECT, INSERT, UPDATE types
metadata: ColumnType<Record<string, unknown>, string, string>; // JSON column
price: ColumnType<number, number, number | undefined>; // 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<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(255)')
.addColumn('created_at', 'timestamp', (col) =>
col.defaultTo(sql`CURRENT_TIMESTAMP`).notNull()
)
.execute();
await db.schema
.createIndex('users_email_idx')
.on('users')
.column('email')
.execute();
}
export async function down(db: Kysely<any>): Promise<void> {
await db.schema.dropTable('users').execute();
}
Complex Migration Examples
// Add foreign key
export async function up(db: Kysely<any>): Promise<void> {
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<any>): Promise<void> {
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<any>): Promise<void> {
await sql`CREATE TYPE user_role AS ENUM ('admin', 'user', 'guest')`.execute(db);
await db.schema
.alterTable('users')
.addColumn('role', sql`user_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',
sql<string>`UPPER(name)`.as('uppercase_name'),
sql<number>`EXTRACT(YEAR FROM created_at)`.as('year_created'),
])
.execute();
// Raw SQL in WHERE
const filtered = await db
.selectFrom('posts')
.selectAll()
.where(sql`LOWER(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 sql`
WITH 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 sql<User>`
SELECT * 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<number>`(SELECT COUNT(*) FROM posts WHERE user_id = users.id)`,
}).as('user_data'),
])
.execute();
Pagination Plugin
import { SelectQueryBuilder } from 'kysely';
function paginate<DB, TB extends keyof DB, O>(
query: SelectQueryBuilder<DB, TB, O>,
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<DB, TB extends keyof DB, O>(
query: SelectQueryBuilder<DB, TB, O>,
page: number,
pageSize: number
) {
const [items, { count }] = await Promise.all([
query.limit(pageSize).offset((page - 1) * pageSize).execute(),
query.select(db.fn.count<number>('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<any>): Promise<void> {
await sql`
ALTER TABLE posts
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
) STORED
`.execute(db);
await sql`
CREATE INDEX posts_search_idx ON posts USING GIN (search_vector)
`.execute(db);
}
// Full-text search query
const searchResults = await db
.selectFrom('posts')
.selectAll()
.where(
sql`search_vector`,
'@@',
sql`to_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<number>; // 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<number>; // Auto-generated by database
email: string;
created_at: ColumnType<Date, string | undefined, never>;
// ColumnType<SelectType, InsertType, UpdateType>
};
}
// Type inference in queries
const db = new Kysely<Database>({ /* 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<T> = {
[K in keyof T]: T[K] extends ColumnType<infer S, any, any> ? 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<any>): Promise<void> {
// 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: sql`concat(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<any>): Promise<void> {
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: sql`email` })
.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: sql`cast(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
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
bad-example-skill
ANTI-PATTERN - Example showing violations of self-containment (DO NOT COPY)
example-framework-skill
Example of a properly self-contained skill following all best practices
systematic-debugging
Systematic debugging methodology emphasizing root cause analysis over quick fixes
verification-before-completion
Run verification commands and confirm output before claiming success
Root Cause Tracing
Systematically trace bugs backward through call stack to find original trigger
Brainstorming Ideas Into Designs
Interactive idea refinement using Socratic method to develop fully-formed designs
Didn't find tool you were looking for?