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