Kysely: Type-Safe SQL Query Builder for Node.js (2026)

ORMs solve one problem and create three others: magic behavior you didn't ask for, performance you can't tune without fighting the abstraction, and SQL you can't read when something goes wrong. Kysely takes a different position — it models SQL directly in TypeScript. You write what looks like SQL, TypeScript checks every table name, column reference, and result type at compile time, and you're never surprised by what query gets generated because you wrote it yourself.

This guide covers Kysely from initial setup through real production patterns: typed queries, joins with inferred result types, transactions, migration management, and the singleton pattern for Next.js.

What Kysely Is (and Isn't)

Kysely is a query builder, not an ORM. The distinction matters:

• ORM (Prisma, Drizzle with relations): defines your schema once, generates queries, manages relations, abstracts SQL
• Query builder (Kysely): you write SQL-like code, TypeScript validates column and table names, no abstractions over the query itself

You won't find user.posts style relation loading. You write the JOIN. What you get in return: exact control over every query, no N+1 surprises, and a result type that reflects exactly the columns you selected.

Installation

npm install kysely pg
npm install --save-dev @types/pg

For other databases: npm install better-sqlite3 (SQLite) or npm install mysql2 (MySQL). Kysely supports all three with the same API.

Defining the Database Schema

The TypeScript interface you define becomes the single source of truth for all query types. Every column, every table, every nullable field:

// lib/database-types.ts
import type { Generated, ColumnType } from 'kysely'

// Generated<T> = the database provides this (auto-increment, defaults, timestamps)
// ColumnType<Select, Insert, Update> = different types for each operation

export interface UsersTable {
  id: Generated<string>         // uuid, auto-generated
  email: string
  name: string | null
  role: 'admin' | 'user' | 'viewer'
  avatar_url: string | null
  created_at: Generated<Date>
  updated_at: ColumnType<Date, Date | undefined, Date>
}

export interface PostsTable {
  id: Generated<string>
  title: string
  slug: string
  content: string
  author_id: string
  published: Generated<boolean>
  view_count: Generated<number>
  created_at: Generated<Date>
  updated_at: ColumnType<Date, Date | undefined, Date>
}

export interface CommentsTable {
  id: Generated<string>
  post_id: string
  author_id: string
  text: string
  created_at: Generated<Date>
}

// The root database interface — this is what you pass to Kysely<Database>
export interface Database {
  users: UsersTable
  posts: PostsTable
  comments: CommentsTable
}

Kysely also exports helper types for each operation:

import type { Selectable, Insertable, Updateable } from 'kysely'
import type { UsersTable } from './database-types'

type User = Selectable<UsersTable>      // what SELECT returns (Generated<T> → T)
type NewUser = Insertable<UsersTable>   // what INSERT accepts (Generated<T> optional)
type UserUpdate = Updateable<UsersTable> // what UPDATE accepts (all fields optional)

Creating the Connection

// lib/db.ts
import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import type { Database } from './database-types'

// Singleton pattern for Next.js — prevents connection pool exhaustion in dev
const globalForDb = global as unknown as { db: Kysely<Database> }

export const db =
  globalForDb.db ||
  new Kysely<Database>({
    dialect: new PostgresDialect({
      pool: new Pool({
        connectionString: process.env.DATABASE_URL,
        max: 10,           // max connections in pool
        idleTimeoutMillis: 30_000,
        connectionTimeoutMillis: 2_000,
      }),
    }),
  })

if (process.env.NODE_ENV !== 'production') globalForDb.db = db

The globalForDb singleton prevents Next.js hot reload from creating dozens of connection pools in development — the same pattern used for Prisma and Drizzle.

SELECT Queries

Basic select with full type inference:

import { db } from '@/lib/db'

// selectAll() → all columns, result typed as Selectable<UsersTable>[]
const allUsers = await db
  .selectFrom('users')
  .selectAll()
  .execute()

// select() with specific columns → result only has those columns
const userEmails = await db
  .selectFrom('users')
  .select(['id', 'email', 'name'])
  .where('role', '=', 'admin')
  .orderBy('created_at', 'desc')
  .execute()
// TypeScript: Array<{ id: string; email: string; name: string | null }>

// Single row
const user = await db
  .selectFrom('users')
  .selectAll()
  .where('email', '=', email)
  .executeTakeFirst()
// user: Selectable<UsersTable> | undefined

// Throw if not found
const user = await db
  .selectFrom('users')
  .selectAll()
  .where('id', '=', userId)
  .executeTakeFirstOrThrow()
// user: Selectable<UsersTable> — never undefined

WHERE with multiple conditions

const activeAdmins = await db
  .selectFrom('users')
  .selectAll()
  .where('role', '=', 'admin')
  .where('avatar_url', 'is not', null)
  .orderBy('created_at', 'desc')
  .limit(20)
  .offset((page - 1) * 20)
  .execute()

// OR condition
const results = await db
  .selectFrom('users')
  .selectAll()
  .where((eb) =>
    eb.or([
      eb('role', '=', 'admin'),
      eb('role', '=', 'viewer'),
    ])
  )
  .execute()

// Dynamic WHERE (only add clause if value is present)
let query = db.selectFrom('users').selectAll()

if (search) {
  query = query.where('email', 'like', `%${search}%`)
}
if (role && role !== 'all') {
  query = query.where('role', '=', role)
}

const users = await query.orderBy('created_at', 'desc').execute()

JOIN Queries

The result type is automatically inferred from which columns you select across joined tables:

const postsWithAuthors = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.author_id')
  .select([
    'posts.id',
    'posts.title',
    'posts.slug',
    'posts.created_at',
    'posts.view_count',
    'users.name as author_name',
    'users.email as author_email',
    'users.avatar_url as author_avatar',
  ])
  .where('posts.published', '=', true)
  .orderBy('posts.created_at', 'desc')
  .limit(10)
  .execute()

// Result type is fully inferred:
// Array<{
//   id: string
//   title: string
//   slug: string
//   created_at: Date
//   view_count: number
//   author_name: string | null
//   author_email: string
//   author_avatar: string | null
// }>

Left join with nullable result handling:

const postsWithCommentCount = await db
  .selectFrom('posts')
  .leftJoin('comments', 'comments.post_id', 'posts.id')
  .select([
    'posts.id',
    'posts.title',
    (eb) => eb.fn.count<number>('comments.id').as('comment_count'),
  ])
  .where('posts.published', '=', true)
  .groupBy(['posts.id', 'posts.title'])
  .orderBy('comment_count', 'desc')
  .execute()

INSERT

import type { Insertable } from 'kysely'
import type { UsersTable } from '@/lib/database-types'

type NewUser = Insertable<UsersTable>

async function createUser(data: NewUser) {
  return db
    .insertInto('users')
    .values(data)
    .returning(['id', 'email', 'name', 'role', 'created_at'])
    .executeTakeFirstOrThrow()
}

// Batch insert
async function importUsers(users: NewUser[]) {
  return db
    .insertInto('users')
    .values(users)
    .returning(['id', 'email'])
    .execute()
}

// Upsert (INSERT ... ON CONFLICT)
async function upsertUser(data: NewUser) {
  return db
    .insertInto('users')
    .values(data)
    .onConflict((oc) =>
      oc.column('email').doUpdateSet({
        name: data.name,
        avatar_url: data.avatar_url,
        updated_at: new Date(),
      })
    )
    .returning(['id', 'email'])
    .executeTakeFirstOrThrow()
}

UPDATE

import type { Updateable } from 'kysely'

async function updateUser(id: string, data: Updateable<UsersTable>) {
  return db
    .updateTable('users')
    .set({ ...data, updated_at: new Date() })
    .where('id', '=', id)
    .returning(['id', 'email', 'name', 'role', 'updated_at'])
    .executeTakeFirst()
}

// Increment a counter atomically
await db
  .updateTable('posts')
  .set((eb) => ({ view_count: eb('view_count', '+', 1) }))
  .where('id', '=', postId)
  .execute()

DELETE

async function deleteUser(id: string) {
  const deleted = await db
    .deleteFrom('users')
    .where('id', '=', id)
    .returning(['id', 'email'])
    .executeTakeFirst()

  return deleted ?? null
}

// Delete with JOIN condition
await db
  .deleteFrom('comments')
  .where('author_id', '=', userId)
  .execute()

Transactions

All operations on trx are inside the transaction — if any throw, the whole transaction rolls back:

async function createPostWithTags(
  authorId: string,
  postData: Omit<Insertable<PostsTable>, 'author_id'>,
  tagIds: string[]
) {
  return db.transaction().execute(async (trx) => {
    // Create the post
    const post = await trx
      .insertInto('posts')
      .values({ ...postData, author_id: authorId })
      .returning(['id', 'title', 'slug'])
      .executeTakeFirstOrThrow()

    // Link tags in bulk
    if (tagIds.length > 0) {
      await trx
        .insertInto('post_tags')
        .values(tagIds.map((tagId) => ({ post_id: post.id, tag_id: tagId })))
        .execute()
    }

    // Bump the author's post count
    await trx
      .updateTable('users')
      .set((eb) => ({ post_count: eb('post_count', '+', 1) }))
      .where('id', '=', authorId)
      .execute()

    return post
  })
}

Auto-Generating Types with kysely-codegen

Rather than writing the database interface by hand, kysely-codegen introspects your actual database and generates the TypeScript types:

npm install --save-dev kysely-codegen

# Generate types from your database
npx kysely-codegen --dialect postgres --out-file src/lib/database-types.ts

Point it at your database URL and it outputs a complete Database interface matching your actual schema. Regenerate after every migration to keep types in sync:

{
  "scripts": {
    "db:types": "kysely-codegen --dialect postgres --out-file src/lib/database-types.ts",
    "db:migrate": "tsx src/lib/migrations/run.ts && npm run db:types"
  }
}

Migrations

Kysely has a built-in migrator — no external CLI needed:

// lib/migrations/run.ts
import { Migrator, FileMigrationProvider } from 'kysely'
import { db } from '../db'
import * as path from 'path'
import { promises as fs } from 'fs'

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

const { error, results } = await migrator.migrateToLatest()

results?.forEach(({ migrationName, status }) => {
  if (status === 'Success') console.log(`✓ ${migrationName}`)
  if (status === 'Error') console.error(`✗ ${migrationName}`)
})

if (error) throw error
await db.destroy()

// lib/migrations/files/2026_001_create_users.ts
import type { Kysely } from 'kysely'

export async function up(db: Kysely<any>) {
  await db.schema
    .createTable('users')
    .addColumn('id', 'uuid', (col) => col.primaryKey().defaultTo(db.fn('gen_random_uuid')))
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('name', 'varchar(255)')
    .addColumn('role', 'varchar(20)', (col) => col.notNull().defaultTo('user'))
    .addColumn('avatar_url', 'text')
    .addColumn('created_at', 'timestamptz', (col) => col.notNull().defaultTo(db.fn('now')))
    .addColumn('updated_at', 'timestamptz', (col) => col.notNull().defaultTo(db.fn('now')))
    .execute()
}

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

Kysely vs Drizzle vs Prisma

	Kysely	Drizzle	Prisma
Philosophy	SQL-first	Schema-first	ORM
Type source	TypeScript interface	Schema definition	Prisma schema
Relations	Manual JOIN	Defined + queried	Magic .include
Bundle size	Tiny	Small	Large (generated client)
Edge runtime	✅	✅	❌ (requires Node.js)
Learning curve	Low if you know SQL	Medium	Low
Magic factor	None	Low	High

Choose Kysely when: you want full SQL control with TypeScript safety, you're targeting edge runtimes, or your team already thinks in SQL. Choose Drizzle when you prefer a schema-first workflow with relation helpers. Choose Prisma when you want maximum developer ergonomics and don't mind the abstraction layer.

Quick Reference

// Setup
const db = new Kysely<Database>({ dialect: new PostgresDialect({ pool }) })

// SELECT
db.selectFrom('users').selectAll().where('id', '=', id).executeTakeFirstOrThrow()
db.selectFrom('users').select(['id', 'email']).execute()

// JOIN
db.selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.author_id')
  .select(['posts.title', 'users.name as author'])
  .execute()

// INSERT
db.insertInto('users').values({ email, name }).returning(['id']).executeTakeFirstOrThrow()

// UPDATE
db.updateTable('users').set({ name }).where('id', '=', id).execute()

// DELETE
db.deleteFrom('users').where('id', '=', id).execute()

// TRANSACTION
db.transaction().execute(async (trx) => {
  await trx.insertInto('users').values({...}).execute()
  await trx.insertInto('posts').values({...}).execute()
})

// Helper types
type User = Selectable<UsersTable>
type NewUser = Insertable<UsersTable>
type UserUpdate = Updateable<UsersTable>

Kysely fits naturally alongside Drizzle in a stack where some services need full ORM conveniences and others need raw query control. For the data-critical paths — reporting queries, complex aggregations, or anything where the generated SQL needs to be exact — Kysely's explicit model earns its place.

---

Full article at stacknotice.com/blog/kysely-complete-guide-2026

Comments

[Mads Hansen]

Good distinction between query builder and ORM. Kysely hits a useful middle ground: you keep SQL-shaped thinking, but TypeScript catches a lot of the typo/refactor class of bugs before runtime.

The hidden maintenance cost is schema drift. If the table interfaces are hand-written and migrations move faster than the types, the safety story degrades quietly. In production I would usually want those types generated from the actual database schema or from the same migration source that creates it.

That keeps Kysely as “typed SQL” instead of “a second schema I hope is still true.”

[Carlos Oliva Pascual]

Totally agree—the real failure mode is maintaining "a second schema that I hope is still true." While kysely-codegen solves much of that problem, the implementation detail that makes it robust in production is integrating type generation directly into the migration workflow so schema drift becomes impossible by construction.

For example:

{
"scripts": {
"db:migrate": "tsx src/lib/migrations/run.ts && npm run db:types"
}
}

With this approach, types are regenerated every time migrations are applied, rather than through a separate manual step. If a developer adds a column in a migration but forgets about the generated types, the migration process itself keeps everything synchronized before the changes are merged.

There are a couple of other approaches worth knowing depending on your stack.

If your team already uses Prisma for schema management but prefers Kysely for querying, prisma-kysely can generate Kysely-compatible database interfaces directly from the Prisma schema. That lets you maintain a single schema definition while supporting both ecosystems.

Another pattern is to generate types in CI from a fresh database created entirely from migrations. Some teams spin up a temporary database, apply every migration, run kysely-codegen, and compare the generated output with the committed types. If there's any difference, the build fails. It's a stricter workflow, but it catches cases where generated files were forgotten or manually edited.

The common principle behind all of these approaches is that the database schema—not the TypeScript interfaces—is the source of truth. Generated types are an artifact of that schema, not something developers should maintain by hand. As soon as there are two independently maintained representations of the schema, they will eventually diverge. The most reliable systems eliminate that possibility by making type generation a deterministic consequence of the migration process, ensuring there's only one authoritative definition of the database structure.