Kysely Has a Free API: Type-Safe SQL Query Builder That's Not an ORM

Kysely is a type-safe TypeScript SQL query builder. It's not an ORM — it generates raw SQL with full type safety. Your IDE autocompletes table names, column names, and infers return types.

Why Kysely?

• Type-safe SQL — autocomplete for tables, columns, joins
• Not an ORM — generates real SQL, no magic
• Zero overhead — compiles to raw SQL strings
• Any database — Postgres, MySQL, SQLite, MSSQL
• Migrations — built-in migration system

Install

npm install kysely pg  # Postgres
# or: kysely mysql2, kysely better-sqlite3

Setup

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

// Define your schema
interface Database {
  users: {
    id: Generated<number>;
    name: string;
    email: string;
    role: 'admin' | 'user';
    created_at: Generated<Date>;
  };
  posts: {
    id: Generated<number>;
    title: string;
    content: string;
    author_id: number;
    published: boolean;
    created_at: Generated<Date>;
  };
}

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

Queries (Fully Type-Safe)

// SELECT — IDE knows the return type!
const users = await db
  .selectFrom('users')
  .select(['id', 'name', 'email'])
  .where('role', '=', 'admin')
  .orderBy('name')
  .execute();
// Type: { id: number; name: string; email: string }[]

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

// UPDATE
await db
  .updateTable('users')
  .set({ role: 'admin' })
  .where('id', '=', 1)
  .execute();

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

// JOIN — types from both tables!
const postsWithAuthors = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.author_id')
  .select(['posts.title', 'posts.content', 'users.name as author_name'])
  .where('posts.published', '=', true)
  .execute();
// Type: { title: string; content: string; author_name: string }[]

// Subquery
const activeAuthors = await db
  .selectFrom('users')
  .select(['name', 'email'])
  .where('id', 'in',
    db.selectFrom('posts')
      .select('author_id')
      .where('published', '=', true)
  )
  .execute();

// Transaction
await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto('users')
    .values({ name: 'Bob', email: '[e-mail]', role: 'user' })
    .returningAll()
    .executeTakeFirstOrThrow();

  await trx
    .insertInto('posts')
    .values({ title: 'Hello', content: 'World', author_id: user.id, published: true })
    .execute();
});

Dynamic Queries

function findUsers(filters: { name?: string; role?: string; limit?: number }) {
  let query = db.selectFrom('users').selectAll();

  if (filters.name) {
    query = query.where('name', 'like', `%${filters.name}%`);
  }
  if (filters.role) {
    query = query.where('role', '=', filters.role as any);
  }
  if (filters.limit) {
    query = query.limit(filters.limit);
  }

  return query.execute();
}

Kysely vs Alternatives

	Kysely	Drizzle	Prisma	TypeORM
Approach	Query builder	Query builder	ORM	ORM
SQL control	Full	Full	Limited	Limited
Type safety	Excellent	Excellent	Good	Partial
Codegen	No	No	Yes	No
Bundle size	Tiny	Small	Large	Large

Resources

• Kysely Docs
• API Reference
• GitHub

---

Building TypeScript backends? Check my Apify actors or email [e-mail].