Kysely Has a Free API — The Type-Safe SQL Query Builder for TypeScript

Kysely is a type-safe SQL query builder for TypeScript. Unlike ORMs that abstract away SQL, Kysely lets you write SQL — with full autocompletion and compile-time type checking.

Why Kysely?

• SQL, not ORM — write SQL you understand, get types you trust
• Full autocompletion — table names, column names, joins — all typed
• Zero runtime overhead — just builds SQL strings
• Any database — PostgreSQL, MySQL, SQLite, D1, PlanetScale

Quick Start

npm install kysely pg

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

interface Database {
  users: {
    id: number;
    name: string;
    email: string;
    created_at: Date;
  };
  posts: {
    id: number;
    title: string;
    content: string;
    author_id: number;
  };
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  }),
});

CRUD Operations

// Select
const users = await db
  .selectFrom('users')
  .selectAll()
  .where('email', 'like', '[e-mail]')
  .orderBy('created_at', 'desc')
  .limit(10)
  .execute();

// Insert
const newUser = await db
  .insertInto('users')
  .values({ name: 'Alice', email: '[e-mail]' })
  .returningAll()
  .executeTakeFirstOrThrow();

// Update
await db
  .updateTable('users')
  .set({ name: 'Bob' })
  .where('id', '=', 1)
  .execute();

// Delete
await db
  .deleteFrom('users')
  .where('id', '=', 1)
  .execute();

Joins

const usersWithPosts = await db
  .selectFrom('users')
  .innerJoin('posts', 'posts.author_id', 'users.id')
  .select(['users.name', 'posts.title', 'posts.content'])
  .where('users.id', '=', userId)
  .execute();
// TypeScript knows: { name: string; title: string; content: string }[]

Complex Queries

// Subqueries
const activeAuthors = await db
  .selectFrom('users')
  .selectAll()
  .where('id', 'in',
    db.selectFrom('posts')
      .select('author_id')
      .where('created_at', '>', new Date('2024-01-01'))
  )
  .execute();

// Aggregations
const stats = await db
  .selectFrom('posts')
  .select([
    'author_id',
    db.fn.count('id').as('post_count'),
    db.fn.max('created_at').as('latest_post'),
  ])
  .groupBy('author_id')
  .having(db.fn.count('id'), '>', 5)
  .execute();

Migrations

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('name', 'text', (col) => col.notNull())
    .addColumn('email', 'text', (col) => col.unique().notNull())
    .addColumn('created_at', 'timestamptz', (col) =>
      col.defaultTo(sql`now()`).notNull()
    )
    .execute();
}

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

---

Building data-intensive apps? Check out my Apify actors for web scraping + database pipeline, or email [e-mail] for custom TypeScript solutions.

Kysely, Drizzle, or Prisma — which TypeScript database tool do you prefer? Share below!