kysely

Type safe SQL query builder

weekly downloads2.5M

versionv0.28.11

licenseMIT

categorydatabase

querybuilderquery buildersqltypescriptpostgrespostgresqlmysqlsqlitemssqlms sql serversql servertype-safetype-safetyplugins

Overview

Kysely is a TypeScript-first SQL query builder that provides compile-time type safety and autocompletion for database queries. Unlike traditional ORMs that abstract away SQL, Kysely embraces SQL syntax while layering TypeScript's type system on top. It compiles directly to raw SQL with a 1:1 mapping, meaning developers write queries that look like SQL but get full IDE support and type checking.

The package was created to solve the friction between type-safe TypeScript development and traditional query builders like Knex.js that lack native type inference. With 2.4 million weekly downloads, Kysely has become the go-to solution for teams that want SQL control without sacrificing TypeScript benefits. It supports PostgreSQL, MySQL, SQLite, and MS SQL Server through official dialects, with zero runtime dependencies and compatibility across Node.js, Deno, Bun, Cloudflare Workers, and browsers.

Kysely infers result types based on selected columns, handles complex joins and subqueries with proper type narrowing, and catches schema mismatches at compile time. Developers typically use kysely-codegen to automatically generate TypeScript interfaces from existing database schemas, keeping type definitions synchronized with actual database structure. This approach eliminates entire classes of runtime errors related to column names, data types, and query structure.

The library targets teams that need explicit SQL control—those building high-performance backends, working with complex queries, or migrating from raw SQL to type-safe code. It's particularly valuable in large codebases where refactoring database schemas requires confidence that all affected queries are updated correctly.

Quick Start

typescript

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

// Define your database schema types
interface Database {
  users: {
    id: number;
    email: string;
    created_at: Date;
  };
  posts: {
    id: number;
    user_id: number;
    title: string;
    content: string;
    published: boolean;
  };
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'myapp',
    }),
  }),
});

// Type-safe query with join and filtering
const publishedPosts = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .select([
    'posts.id',
    'posts.title',
    'posts.content',
    'users.email as author_email',
  ])
  .where('posts.published', '=', true)
  .orderBy('posts.created_at', 'desc')
  .limit(10)
  .execute();

// Result type is inferred:
// Array<{ id: number; title: string; content: string; author_email: string }>

// Insert with type checking
await db
  .insertInto('posts')
  .values({
    user_id: 1,
    title: 'Getting Started with Kysely',
    content: 'Kysely makes SQL type-safe...',
    published: true,
  })
  .execute();

// Update with conditional logic
await db
  .updateTable('posts')
  .set({ published: true })
  .where('user_id', '=', 1)
  .where('created_at', '>', new Date('2024-01-01'))
  .execute();

Use Cases

Building REST APIs with complex joins: When constructing APIs that require multiple table joins with filtering and pagination, Kysely provides type-safe query composition where join conditions and selected columns are validated at compile time, preventing runtime errors from typos or schema changes.

Migrating legacy SQL codebases to TypeScript: Teams with existing SQL-heavy applications can adopt Kysely incrementally, wrapping raw SQL in type-safe builders without rewriting business logic or learning ORM conventions, while gaining immediate feedback on schema mismatches.

Multi-tenant SaaS applications: Applications requiring dynamic query construction based on tenant permissions or feature flags benefit from Kysely's composable query building, where conditional joins and filters maintain type safety even when query structure varies at runtime.

Data analytics and reporting dashboards: Complex aggregations, window functions, and CTEs (Common Table Expressions) needed for analytics queries are fully supported with proper type inference, allowing developers to build sophisticated reports while catching errors before deployment.

Microservices with shared database types: In monorepo architectures where multiple services access the same database, Kysely's generated types can be shared across services, ensuring consistent database interaction patterns and catching breaking changes during schema evolution.

Pros & Cons

Pros

+Full compile-time type safety with accurate inference for joins, subqueries, and CTEs—catches schema mismatches before runtime
+Zero dependencies and lightweight footprint; runs in any JavaScript environment including edge runtimes and browsers
+SQL-first approach with 1:1 query mapping preserves SQL knowledge and enables easy optimization without fighting ORM abstractions
+Excellent TypeScript autocompletion for column names, table references, and query methods reduces documentation lookups
+Flexible raw SQL escape hatches via sql template tags allow complex expressions while maintaining parameterization safety

Cons

−Requires manual schema type generation via kysely-codegen or hand-written interfaces; no automatic schema introspection at runtime
−Learning curve for developers accustomed to ORM patterns like active record or entity mapping—no model classes or automatic relations
−Migration tooling is minimal compared to full-featured ORMs; kysely-ctl provides basics but lacks advanced migration management features
−Complex queries with heavy use of raw SQL expressions can lose type safety, requiring explicit type casting
−No built-in connection pooling or transaction management beyond basic primitives—relies on underlying database drivers

Alternatives

drizzle-ormv0.45.1

Drizzle ORM package for SQL databases

databasedrizzleorm

4.6M / week

prismav7.4.0

Prisma is an open-source database toolkit. It includes a JavaScript/TypeScript ORM for Node.js, migrations and a modern GUI to view and edit the data in your database. You can use Prisma in new projects or add it to an existing one.

databaseCLIORM

7.8M / week

Comparisons

drizzle-orm vs kysely

database

Install

bash

npm install kysely

bash

pnpm add kysely

bash

bun add kysely

Overview

Quick Start

typescript

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

// Define your database schema types
interface Database {
  users: {
    id: number;
    email: string;
    created_at: Date;
  };
  posts: {
    id: number;
    user_id: number;
    title: string;
    content: string;
    published: boolean;
  };
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'myapp',
    }),
  }),
});

// Type-safe query with join and filtering
const publishedPosts = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .select([
    'posts.id',
    'posts.title',
    'posts.content',
    'users.email as author_email',
  ])
  .where('posts.published', '=', true)
  .orderBy('posts.created_at', 'desc')
  .limit(10)
  .execute();

// Result type is inferred:
// Array<{ id: number; title: string; content: string; author_email: string }>

// Insert with type checking
await db
  .insertInto('posts')
  .values({
    user_id: 1,
    title: 'Getting Started with Kysely',
    content: 'Kysely makes SQL type-safe...',
    published: true,
  })
  .execute();

// Update with conditional logic
await db
  .updateTable('posts')
  .set({ published: true })
  .where('user_id', '=', 1)
  .where('created_at', '>', new Date('2024-01-01'))
  .execute();

Use Cases

Pros & Cons

Pros

+Full compile-time type safety with accurate inference for joins, subqueries, and CTEs—catches schema mismatches before runtime
+Zero dependencies and lightweight footprint; runs in any JavaScript environment including edge runtimes and browsers
+SQL-first approach with 1:1 query mapping preserves SQL knowledge and enables easy optimization without fighting ORM abstractions
+Excellent TypeScript autocompletion for column names, table references, and query methods reduces documentation lookups
+Flexible raw SQL escape hatches via sql template tags allow complex expressions while maintaining parameterization safety

Cons

−Requires manual schema type generation via kysely-codegen or hand-written interfaces; no automatic schema introspection at runtime
−Learning curve for developers accustomed to ORM patterns like active record or entity mapping—no model classes or automatic relations
−Migration tooling is minimal compared to full-featured ORMs; kysely-ctl provides basics but lacks advanced migration management features
−Complex queries with heavy use of raw SQL expressions can lose type safety, requiring explicit type casting
−No built-in connection pooling or transaction management beyond basic primitives—relies on underlying database drivers