Type-safe SQL Queries with Kysely: The Lightweight Alternative to Bulky ORMs

The Middle Ground Between Raw SQL and Heavy ORMs

Starting a new Node.js backend usually forces a compromise between raw SQL and heavy ORMs. Raw SQL strings offer maximum performance and control. However, they are fragile.

A single typo in a column name can sit undetected until it crashes your production environment. On the other side, ORMs like Prisma or TypeORM provide safety but come with significant baggage. Prisma’s query engine can add over 100MB to your Docker image and introduce 5-10ms of latency per request just for its internal translation layer.

Kysely occupies the perfect middle ground. It is a TypeScript-first query builder that embraces SQL rather than hiding it. Instead of complex abstractions, it provides a type-safe layer that makes writing broken queries nearly impossible. My teams have used this in production for years to achieve high stability. You get the confidence of compile-time checks without the “magic” that makes traditional ORMs frustrating to debug.

Kysely leverages TypeScript’s advanced type system to map your database schema directly to your queries. If you select a non-existent column or join a table incorrectly, the compiler flags the error immediately. You catch bugs during development, not at 3 AM during an on-call rotation.

Installation: Setting Up the Foundation

Getting started requires the core library and a driver for your specific database. While I use PostgreSQL for most projects, the logic remains the same for MySQL or SQLite. You will need TypeScript configured in your environment first.

Initialize your project and install the necessary packages:

npm init -y
npm install kysely pg
npm install -D typescript @types/node @types/pg ts-node

Kysely handles query building but leaves the actual connection to dedicated drivers. The pg package manages the PostgreSQL connection pool. If you prefer MySQL, simply swap pg for mysql2. For local development or small edge functions, better-sqlite3 is an excellent alternative.

Manual interface creation is tedious. To save time, use kysely-codegen. This tool inspects your live schema and generates TypeScript definitions automatically, ensuring your code always matches your database state.

npm install -D kysely-codegen

Configuration: Mapping Your Schema

The Database interface acts as the single source of truth for your application. It defines every table, column, and data type within your system. Once this interface is set, Kysely provides full IDE autocomplete for every query you write.

Create a db.ts file to initialize the connection. Let’s look at a standard blog setup with users and posts tables.

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

interface UserTable {
  id: number;
  email: string;
  first_name: string;
  created_at: Date;
}

interface PostTable {
  id: number;
  title: string;
  content: string;
  author_id: number;
}

interface Database {
  users: UserTable;
  posts: PostTable;
}

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      database: 'my_blog_db',
      host: 'localhost',
      user: 'admin',
      port: 5432,
      max: 10, // Maintain up to 10 concurrent connections
    }),
  }),
});

Passing the Database interface into the Kysely constructor links your types to the execution engine. From this point on, the db object is fully aware of your schema. It won’t let you reference a table that doesn’t exist.

Writing Queries and Ensuring Stability

The benefits of this setup become obvious the moment you start typing. When you call db.selectFrom('...'), your IDE suggests users or posts. This eliminates the guesswork often associated with raw string queries.

Fetching Data with Type Safety

Joining tables is often where ORMs become confusing. Kysely keeps the syntax close to standard SQL while maintaining strict types:

async function getAuthorWithPosts(userId: number) {
  const result = await db
    .selectFrom('users')
    .innerJoin('posts', 'posts.author_id', 'users.id')
    .select([
      'users.email',
      'posts.title',
      'posts.content'
    ])
    .where('users.id', '=', userId)
    .execute();

  return result;
}

Imagine you rename email to contact_email in the database. Without Kysely, you might miss a reference in a distant file. With Kysely, the compiler will immediately mark this query as an error, preventing a runtime failure.

Inserting and Updating Data

Data mutation is equally safe. Kysely validates that the objects you insert match the expected column types. It prevents you from accidentally sending a string to an integer column.

async function createPost(title: string, content: string, authorId: number) {
  return await db
    .insertInto('posts')
    .values({
      title,
      content,
      author_id: authorId,
    })
    .returning('id')
    .executeTakeFirst();
}

Monitoring and Debugging

Abstraction often hides what is actually happening under the hood. Kysely avoids this by providing a simple logging hook. I recommend enabling this in development to verify query efficiency.

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ ... }),
  log(event) {
    if (event.level === 'query') {
      console.log(`SQL: ${event.query.sql}`);
      console.log(`Params: ${event.query.parameters}`);
    }
  },
});

This allows you to see the exact SQL sent to the server. If a query is slow, you can paste the output directly into pgAdmin or DBeaver. You can run EXPLAIN ANALYZE without translating from a proprietary ORM syntax back to SQL.

Refactoring with Confidence

Schema changes are inevitable as applications grow. When using Kysely, refactoring is no longer a high-risk activity. If you update a column type in your interface, every broken reference across your entire project will light up red in your editor. You can fix every query before the code ever reaches a test environment. This workflow has saved my team hundreds of hours of manual debugging and significantly reduced our production error rate.