Kysely orm

Kysely ORM

Type-safe TypeScript SQL query builder. This skill covers Kysely-specific patterns—for database-specific syntax (PostgreSQL, SQL Server, MySQL), see the corresponding database skills.

When to Use Kysely vs Drizzle

Aspect	Kysely	Drizzle
Philosophy	Pure query builder with typing	Full ORM with schema-first approach
Migration tooling	Manual up/down functions	Auto-generated with drizzle-kit
Best for	Knex.js migrations, complex queries	New projects, integrated tooling
Nested queries	Built-in helpers (jsonArrayFrom)	Manual grouping required
Learning curve	Familiar if you know SQL/Knex	ORM concepts required

Choose Kysely when:

• Migrating from Knex.js
• You want SQL-like syntax with full type safety
• You need complex nested queries with JSON aggregation
• You prefer writing migrations manually

---

Project Setup

Installation

Use the project's package manager (detect from lock file: bun.lockb, pnpm-lock.yaml, yarn.lock, or package-lock.json).

# Core
<pkg> add kysely

# Database driver (choose one)
<pkg> add pg                  # PostgreSQL
<pkg> add mysql2              # MySQL
<pkg> add better-sqlite3      # SQLite
<pkg> add tedious             # SQL Server

Database Instance

// 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,
    }),
  }),
});

// IMPORTANT: Call db.destroy() on app shutdown
process.on('SIGTERM', async () => {
  await db.destroy();
});

SQL Server Setup

import { Kysely, MssqlDialect } from 'kysely';
import * as Tedious from 'tedious';
import * as Tarn from 'tarn';

export const db = new Kysely<Database>({
  dialect: new MssqlDialect({
    tarn: {
      ...Tarn,
      options: {
        min: 0,
        max: 10,
      },
    },
    tedious: {
      ...Tedious,
      connectionFactory: () =>
        new Tedious.Connection({
          server: process.env.DB_HOST,
          authentication: {
            type: 'default',
            options: {
              userName: process.env.DB_USER,
              password: process.env.DB_PASSWORD,
            },
          },
          options: {
            database: process.env.DB_NAME,
            encrypt: true,
            trustServerCertificate: true,
          },
        }),
    },
  }),
});

---

Database Types

Type Generation (Recommended)

For production apps, generate types from your database:

# Using kysely-codegen
<pkg> add -D kysely-codegen

# Generate types (use bunx for bun, npx for others)
npx kysely-codegen --out-file ./db/types.ts

Manual Type Definition

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

// Database interface - maps table names to table types
export interface Database {
  users: UsersTable;
  posts: PostsTable;
  comments: CommentsTable;
}

// Table interface
export interface UsersTable {
  id: Generated<string>;           // UUID, generated by DB
  email: string;
  name: string;
  password_hash: string;
  is_active: boolean;
  metadata: Generated<unknown>;    // JSONB with default
  created_at: Generated<Date>;     // Has DEFAULT
  updated_at: Generated<Date>;
  deleted_at: Date | null;         // Nullable, no default
}

// Convenience types for each table
export type User = Selectable<UsersTable>;
export type NewUser = Insertable<UsersTable>;
export type UserUpdate = Updateable<UsersTable>;

Type Wrappers Explained

Wrapper	Use For	Generated Columns?	Nullable?
Generated<T>	Columns with DEFAULT or auto-generated	Yes	Based on T
Selectable<Table>	Query results	Unwraps Generated	Preserves
Insertable<Table>	INSERT operations	Makes optional	Preserves
Updateable<Table>	UPDATE operations	Makes optional	Makes optional

// Example: PostsTable with all patterns
export interface PostsTable {
  // Generated: optional on insert, required on select
  id: Generated<string>;
  created_at: Generated<Date>;

  // Required: must provide on insert
  user_id: string;
  title: string;

  // Nullable: can be null
  content: string | null;
  published_at: Date | null;

  // Generated + Nullable: optional on insert, nullable on select
  view_count: Generated<number | null>;
}

// Resulting types:
// Selectable<PostsTable> = { id: string, user_id: string, title: string, ... }
// Insertable<PostsTable> = { id?: string, user_id: string, title: string, ... }
// Updateable<PostsTable> = { id?: string, user_id?: string, title?: string, ... }

---

Query Building

CRITICAL: Queries Are Immutable

// ❌ WRONG - mutation doesn't work
let query = db.selectFrom('users');
query.where('id', '=', userId);  // Returns new query, doesn't mutate!

// ✅ CORRECT - reassign the result
let query = db.selectFrom('users');
query = query.where('id', '=', userId);

// ✅ BEST - chain methods
const user = await db
  .selectFrom('users')
  .where('id', '=', userId)
  .selectAll()
  .executeTakeFirst();

SELECT Queries

// Select all columns
const users = await db
  .selectFrom('users')
  .selectAll()
  .execute();

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

// With conditions
const activeUsers = await db
  .selectFrom('users')
  .where('is_active', '=', true)
  .where('created_at', '>', new Date('2024-01-01'))
  .selectAll()
  .execute();

// Single result (returns undefined if not found)
const user = await db
  .selectFrom('users')
  .where('id', '=', userId)
  .selectAll()
  .executeTakeFirst();

// Single result (throws if not found)
const user = await db
  .selectFrom('users')
  .where('id', '=', userId)
  .selectAll()
  .executeTakeFirstOrThrow();

INSERT Queries

// Insert single row
const result = await db
  .insertInto('users')
  .values({
    email: '[email protected]',
    name: 'John Doe',
    password_hash: hashedPassword,
    is_active: true,
  })
  .returningAll()
  .executeTakeFirstOrThrow();

// Insert multiple rows
await db
  .insertInto('tags')
  .values([
    { name: 'typescript' },
    { name: 'kysely' },
    { name: 'database' },
  ])
  .execute();

// Insert with conflict handling (upsert)
await db
  .insertInto('users')
  .values({ email, name, password_hash })
  .onConflict((oc) =>
    oc.column('email').doUpdateSet({ name, updated_at: new Date() })
  )
  .execute();

UPDATE Queries

// Update with returning
const updated = await db
  .updateTable('users')
  .set({ is_active: false, updated_at: new Date() })
  .where('id', '=', userId)
  .returningAll()
  .executeTakeFirst();

// Conditional update
await db
  .updateTable('posts')
  .set({ status: 'published', published_at: new Date() })
  .where('id', '=', postId)
  .where('user_id', '=', userId)  // Authorization check
  .execute();

DELETE Queries

// Delete with condition
await db
  .deleteFrom('sessions')
  .where('expires_at', '<', new Date())
  .execute();

// Delete with returning
const deleted = await db
  .deleteFrom('users')
  .where('id', '=', userId)
  .returningAll()
  .executeTakeFirst();

---

Joins

// Inner join
const postsWithAuthors = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .select([
    'posts.id',
    'posts.title',
    'users.name as author_name',
  ])
  .execute();

// Left join
const usersWithPosts = await db
  .selectFrom('users')
  .leftJoin('posts', 'posts.user_id', 'users.id')
  .select([
    'users.id',
    'users.name',
    'posts.title',
  ])
  .execute();

// Multiple joins
const result = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .leftJoin('categories', 'categories.id', 'posts.category_id')
  .select([
    'posts.id',
    'posts.title',
    'users.name as author',
    'categories.name as category',
  ])
  .execute();

---

JSON Aggregation (Nested Queries)

Kysely's killer feature for complex nested data:

import { jsonArrayFrom, jsonObjectFrom } from 'kysely/helpers/postgres';
// Or: from 'kysely/helpers/mysql', 'kysely/helpers/sqlite'

// Get posts with nested comments array
const postsWithComments = await db
  .selectFrom('posts')
  .select((eb) => [
    'posts.id',
    'posts.title',
    jsonArrayFrom(
      eb.selectFrom('comments')
        .select(['comments.id', 'comments.content', 'comments.created_at'])
        .whereRef('comments.post_id', '=', 'posts.id')
        .orderBy('comments.created_at', 'desc')
    ).as('comments'),
  ])
  .execute();

// Get post with nested author object
const postWithAuthor = await db
  .selectFrom('posts')
  .select((eb) => [
    'posts.id',
    'posts.title',
    'posts.content',
    jsonObjectFrom(
      eb.selectFrom('users')
        .select(['users.id', 'users.name', 'users.email'])
        .whereRef('users.id', '=', 'posts.user_id')
    ).as('author'),
  ])
  .where('posts.id', '=', postId)
  .executeTakeFirst();

// Result type is automatically inferred:
// {
//   id: string;
//   title: string;
//   content: string;
//   author: { id: string; name: string; email: string } | null;
// }

---

Transactions

// Basic transaction
const result = await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto('users')
    .values({ email, name, password_hash })
    .returningAll()
    .executeTakeFirstOrThrow();

  await trx
    .insertInto('user_settings')
    .values({ user_id: user.id, theme: 'light' })
    .execute();

  return user;
});

// Transaction with isolation level (PostgreSQL)
import { sql } from 'kysely';

await db.transaction().execute(async (trx) => {
  await sql`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE`.execute(trx);
  // ... operations
});

---

Raw SQL

import { sql } from 'kysely';

// Raw SQL in select
const result = await db
  .selectFrom('users')
  .select([
    'id',
    'name',
    sql<number>`extract(year from created_at)`.as('join_year'),
  ])
  .execute();

// Raw SQL in where
const users = await db
  .selectFrom('users')
  .where(sql`lower(email)`, '=', email.toLowerCase())
  .selectAll()
  .execute();

// Full raw query with type
const result = await sql<{ count: number }>`
  SELECT count(*) as count FROM users WHERE is_active = true
`.execute(db);

// Parameterized raw SQL (safe from injection)
const searchTerm = 'john';
const users = await sql<User>`
  SELECT * FROM users
  WHERE name ILIKE ${`%${searchTerm}%`}
`.execute(db);

---

Migrations

Migration File Structure

migrations/
├── 001_create_users.ts
├── 002_create_posts.ts
├── 003_add_email_index.ts
└── 004_add_user_settings.ts

Migration Template

// 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', 'uuid', (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`)
    )
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('name', 'varchar(100)', (col) => col.notNull())
    .addColumn('password_hash', 'varchar(255)', (col) => col.notNull())
    .addColumn('is_active', 'boolean', (col) => col.notNull().defaultTo(true))
    .addColumn('created_at', 'timestamptz', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .addColumn('updated_at', 'timestamptz', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute();

  // Create index
  await db.schema
    .createIndex('idx_users_email')
    .on('users')
    .column('email')
    .execute();
}

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

Running Migrations

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

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

  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');
    console.error(error);
    process.exit(1);
  }

  await db.destroy();
}

migrate();

Schema Builder Reference

// Create table with all column types
await db.schema
  .createTable('posts')
  .addColumn('id', 'uuid', (col) => col.primaryKey().defaultTo(sql`gen_random_uuid()`))
  .addColumn('user_id', 'uuid', (col) =>
    col.notNull().references('users.id').onDelete('cascade')
  )
  .addColumn('title', 'varchar(200)', (col) => col.notNull())
  .addColumn('content', 'text')
  .addColumn('status', sql`post_status`, (col) => col.notNull().defaultTo('draft'))
  .addColumn('view_count', 'integer', (col) => col.notNull().defaultTo(0))
  .addColumn('metadata', 'jsonb', (col) => col.defaultTo(sql`'{}'::jsonb`))
  .addColumn('created_at', 'timestamptz', (col) => col.notNull().defaultTo(sql`now()`))
  .execute();

// Add column to existing table
await db.schema
  .alterTable('users')
  .addColumn('phone', 'varchar(20)')
  .execute();

// Drop column
await db.schema
  .alterTable('users')
  .dropColumn('phone')
  .execute();

// Rename column
await db.schema
  .alterTable('users')
  .renameColumn('name', 'full_name')
  .execute();

// Create index
await db.schema
  .createIndex('idx_posts_user_id')
  .on('posts')
  .column('user_id')
  .execute();

// Create unique index
await db.schema
  .createIndex('idx_users_email')
  .on('users')
  .column('email')
  .unique()
  .execute();

// Drop index
await db.schema.dropIndex('idx_posts_user_id').execute();

// Raw SQL for complex operations
await sql`
  CREATE INDEX idx_posts_published
  ON posts(published_at)
  WHERE status = 'published'
`.execute(db);

---

Plugins

CamelCase Plugin

Automatically convert between snake_case (DB) and camelCase (TypeScript):

import { Kysely, PostgresDialect, CamelCasePlugin } from 'kysely';

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
  plugins: [new CamelCasePlugin()],
});

// Now you can use camelCase in queries
const user = await db
  .selectFrom('users')
  .select(['id', 'createdAt', 'isActive'])  // Maps to created_at, is_active
  .executeTakeFirst();

Dedupe Join Plugin

Prevents duplicate joins in complex queries:

import { DeduplicateJoinsPlugin } from 'kysely';

const db = new Kysely<Database>({
  dialect,
  plugins: [new DeduplicateJoinsPlugin()],
});

---

Common Patterns

Repository Pattern

// repositories/user.repository.ts
import { db } from '../db';
import { NewUser, User, UserUpdate } from '../db/types';

export const userRepository = {
  async findById(id: string): Promise<User | undefined> {
    return db
      .selectFrom('users')
      .where('id', '=', id)
      .selectAll()
      .executeTakeFirst();
  },

  async findByEmail(email: string): Promise<User | undefined> {
    return db
      .selectFrom('users')
      .where('email', '=', email)
      .selectAll()
      .executeTakeFirst();
  },

  async create(user: NewUser): Promise<User> {
    return db
      .insertInto('users')
      .values(user)
      .returningAll()
      .executeTakeFirstOrThrow();
  },

  async update(id: string, data: UserUpdate): Promise<User | undefined> {
    return db
      .updateTable('users')
      .set({ ...data, updated_at: new Date() })
      .where('id', '=', id)
      .returningAll()
      .executeTakeFirst();
  },

  async delete(id: string): Promise<boolean> {
    const result = await db
      .deleteFrom('users')
      .where('id', '=', id)
      .executeTakeFirst();
    return result.numDeletedRows > 0n;
  },
};

Dynamic Query Building

interface UserFilters {
  email?: string;
  isActive?: boolean;
  createdAfter?: Date;
  search?: string;
}

async function findUsers(filters: UserFilters): Promise<User[]> {
  let query = db.selectFrom('users').selectAll();

  if (filters.email) {
    query = query.where('email', '=', filters.email);
  }

  if (filters.isActive !== undefined) {
    query = query.where('is_active', '=', filters.isActive);
  }

  if (filters.createdAfter) {
    query = query.where('created_at', '>', filters.createdAfter);
  }

  if (filters.search) {
    query = query.where((eb) =>
      eb.or([
        eb('name', 'ilike', `%${filters.search}%`),
        eb('email', 'ilike', `%${filters.search}%`),
      ])
    );
  }

  return query.execute();
}

---

Anti-Patterns

Anti-Pattern	Problem	Solution
Mutating queries	query.where() returns new query	Always reassign: query = query.where()
Using table types directly	Breaks on Generated columns	Use Selectable<T>, Insertable<T>
Missing Generated<>	Required fields become optional	Mark DB-generated columns
Not destroying db	Connection pool leaks	Call db.destroy() on shutdown
any in migrations	Loses type safety benefits	Accept it—migration types are dynamic
Raw SQL without params	SQL injection risk	Use sql template: sql\...${param}``

---

Quick Reference

TYPES:
  Database         Interface mapping table names to types
  Generated<T>     Column with DEFAULT or auto-generated
  Selectable<T>    Type for SELECT results
  Insertable<T>    Type for INSERT values
  Updateable<T>    Type for UPDATE values

QUERIES:
  selectFrom()     Start SELECT query
  insertInto()     Start INSERT query
  updateTable()    Start UPDATE query
  deleteFrom()     Start DELETE query

EXECUTION:
  execute()              Returns array
  executeTakeFirst()     Returns single or undefined
  executeTakeFirstOrThrow()  Returns single or throws

JOINS:
  innerJoin()      INNER JOIN
  leftJoin()       LEFT JOIN
  rightJoin()      RIGHT JOIN
  fullJoin()       FULL OUTER JOIN

HELPERS:
  jsonArrayFrom()   Nested array aggregation
  jsonObjectFrom()  Nested object
  sql``            Raw SQL template

SCHEMA:
  createTable()    CREATE TABLE
  alterTable()     ALTER TABLE
  dropTable()      DROP TABLE
  createIndex()    CREATE INDEX

---

References

• Kysely Documentation
• Kysely GitHub
• kysely-codegen