Kysely in action: building a clean and maintainable database layer

Using the Kysely library in a Node.js application is a powerful way to construct a database layer that’s not only clean and maintainable but also type-safe. As a TypeScript SQL query builder, Kysely offers static type safety and intelligent auto-completion for your queries, significantly reducing runtime errors and elevating the developer experience. This guide will walk you through how to use this library to its full potential, highlighting its core features and benefits.

Getting started with Kysely

Before you can begin leveraging the power of Kysely, you’ll need to set up your project environment. A proper setup ensures that you can take advantage of all the library’s features, including its robust type-checking capabilities. The initial steps involve installing the core library alongside the appropriate database driver.

To get started, simply use your package manager to install Kysely and the driver that matches your database system. For instance, if you’re working with PostgreSQL, your command would be: npm install kysely pg. For MySQL, you would use: npm install kysely mysql2. And for SQLite, you would run: npm install kysely sqlite3. To unlock Kysely’s full potential, you must also install the corresponding type definitions for your chosen driver. For PostgreSQL, this command is: npm install -D @types/pg. This simple step enables static analysis, autocompletion, and compile-time type-checking across your entire database code, which is a key advantage of using Kysely.

Defining your schema for type safety

Achieving type safety with Kysely requires defining your database schema, and you have two primary methods for accomplishing this. Each approach offers distinct advantages, allowing you to choose the one that best fits your project’s scale and workflow. Let’s examine both the manual and automated approaches to schema generation.

Approach 1: manual schema typing

The most direct way to get started is by manually defining your database schema using TypeScript interfaces. This method is ideal for smaller projects where the schema is stable and less likely to change frequently. You’ll create a central Database interface that maps each table to its corresponding row type.

This manual definition serves as the foundation for Kysely’s static typing. You explicitly describe the structure of each table as a TypeScript interface, then combine them into a single Database interface. Once defined, you pass this interface as a type parameter when creating your Kysely instance. This step ensures that your database connection is fully type-aware, providing accurate IntelliSense and compile-time validation for every query you write. This is the cornerstone of a type-safe database layer.

// src/db/schema.ts

export interface Database {
  users: User;
  posts: Post;
}

export interface User {
  id: number;
  name: string;
  email: string;
  created_at: Date;
}

export interface Post {
  id: number;
  user_id: number;
  title: string;
  content: string;
  published: boolean;
  created_at: Date;
}

import { Kysely, PostgresDialect } from 'kysely';
import { Database } from './types/database';
import { Pool } from 'pg';

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

Approach 2: schema introspection with kysely-codegen

For large-scale or rapidly evolving projects, manually updating schema types can quickly become a cumbersome and error-prone task. This is where a powerful tool like kysely-codegen proves invaluable. It automates the process by generating Kysely-compatible TypeScript interfaces directly from your live database.

By running a simple command-line interface command, kysely-codegen connects to your database, analyzes its structure, and produces a generated Database interface file. The output precisely reflects your current schema, including column names and types. This method ensures your code is always in sync with the database schema, eliminating the risk of human error and significantly simplifying maintenance. This powerful automation is essential for any production environment.

npx kysely-codegen --out-file src/db.generated.ts

export interface Database {
  users: Users;
  posts: Posts;
}

export interface Users {
  id: number;
  name: string | null;
  email: string;
  created_at: Date;
}

export interface Posts {
  id: number;
  user_id: number;
  title: string;
  content: string | null;
  published: boolean;
  created_at: Date;
}

import { Database } from './db.generated';
export const db = new Kysely<Database>(...);

The power of Kysely’s typization

Kysely’s core strength lies in its ability to provide genuine type safety at compile-time, a feature that distinguishes it from many other tools. The library’s type system is not a mere post-query inference but an integral part of the query-building process. Let’s explore how this powerful mechanism works in practice.

When you construct a query with Kysely, you get immediate feedback. The library provides auto-completion for table names and column fields directly in your IDE, guiding you as you write. More importantly, it performs compile-time validation. For example, your IDE will flag an error if you try to select a non-existent column or use an incorrect data type in a where clause. The system catches these common mistakes before you even run your code. This is what true type safety looks like. The returned data is also precisely typed, so you always know the exact shape of your result set.

const result = await db
  .selectFrom('user')
  .select(['id', 'email'])
  .where('name', '=', 'Alice')
  .execute();

Designing a multilayered architecture with Kysely

Building a clean and scalable application requires a thoughtful approach to its architecture. A multilayered design pattern is the ideal choice for this, as it organizes your system into separate, distinct layers, each with a specific responsibility. This structure is a perfect match for Kysely’s design philosophy, promoting a clear separation of concerns.

In a typical Node.js project, this architecture includes a Presentation Layer for handling user interaction, a Service Layer for housing business logic, and a Persistence Layer that manages all database access. Kysely is an excellent fit for this model because it’s a pure SQL query builder that doesn’t impose its internals on your business logic. By confining all SQL queries to the Persistence Layer, your services remain clean and focused on business rules, completely isolated from the database details. This powerful decoupling makes your codebase far more testable, maintainable, and scalable.

/src
  ├── db/
  │   ├── schema.ts      # Schema definitions and types
  │   ├── kysely.ts      # DB instance setup
  │   └── migrations.ts  # Optional: Migration runner
  ├── repositories/
  │   └── user.repository.ts  # Example: clean repository layer
  ├── services/
  │   └── user.service.ts     # Business logic
  ├── routes/
  │   └── user.routes.ts      # Express routes, for example
  └── index.ts          # Entry point

The perfect fit for a multilayered system

Kysely’s minimalistic, database-agnostic, and fully typed nature makes it the ideal choice for implementing the Persistence Layer in your application. It stands in stark contrast to heavy ORMs, which often rely on complex abstractions and metadata that can obscure the underlying SQL. Kysely simplifies the process by enabling you to write explicit SQL queries with all the benefits of static typing.

Initializing the database instance

Your journey begins by creating a dedicated module to initialize the Kysely instance. This module resides within the Persistence Layer and abstracts the database connection setup, ensuring it can be shared across your entire application. This simple yet crucial step lays the groundwork for a scalable and maintainable system. All database interactions will flow through this single, strongly-typed instance.

// src/db/kysely.ts

import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from './schema';

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

The Repository Pattern

The Repository Pattern is a key part of this layered design, and Kysely makes it effortless to implement. Repositories encapsulate all your direct database queries, providing a clean interface that shields the rest of your application from raw SQL logic. You define clear, reusable methods like findById or create, making your data access code easy to manage and test. This isolation is a major win for maintainability. If you ever need to change databases or add a caching system, you only have to modify this layer, leaving the rest of your application untouched.

// src/repositories/user.repository.ts

import { db } from '../db/kysely';

export const UserRepository = {
  findById: (id: number) => {
    return db.selectFrom('user')
      .selectAll()
      .where('id', '=', id)
      .executeTakeFirst();
  },

  create: (name: string, email: string) => {
    return db.insertInto('user')
      .values({
        name,
        email,
        created_at: new Date(),
      })
      .returningAll()
      .executeTakeFirst();
  },

  listAll: () => {
    return db.selectFrom('user')
      .select(['id', 'name', 'email'])
      .execute();
  }
};

The Service Layer

The Service Layer is where your application’s business logic lives. It orchestrates the flow of data and defines the actual behavior of your system. This layer calls methods from the Repository Layer without any knowledge of how the data is retrieved or stored. This powerful separation ensures that your business rules are decoupled from the underlying data technology. By focusing on the “what” and not the “how”, the service layer remains clean, reusable, and easy to test.

// src/services/user.service.ts

import { UserRepository } from '../repositories/user.repository';

export const UserService = {
  getUser: async (id: number) => {
    const user = await UserRepository.findById(id);
    if (!user) throw new Error('User not found');
    return user;
  },

  registerUser: async (name: string, email: string) => {
    return UserRepository.create(name, email);
  },

  getAllUsers: () => {
    return UserRepository.listAll();
  }
};

The Presentation Layer

The final layer is responsible for handling all incoming requests and outgoing responses. In a web application, this would be your routing layer, such as with the Express framework. The Presentation Layer is intentionally kept “thin.” It receives requests, calls the appropriate methods in the Service Layer to process the business logic, and then sends back a response to the client. Crucially, routes in this layer never interact directly with the database. This clear division of labor makes your application easier to test and extend over time.

// src/routes/user.routes.ts

import express from 'express';
import { UserService } from '../services/user.service';

const router = express.Router();

router.get('/:id', async (req, res) => {
  const user = await UserService.getUser(Number(req.params.id));
  res.json(user);
});

router.post('/', async (req, res) => {
  const { name, email } = req.body;
  const newUser = await UserService.registerUser(name, email);
  res.status(201).json(newUser);
});

export default router;

What makes Kysely stand out 

A clean database layer is one that is both explicit and easy to read. Kysely achieves this by providing a fluent, strongly typed query builder that looks remarkably similar to SQL, but with the added benefits of clear structure and autocompletion. Queries are declarative, so you can clearly express your intent without complex abstractions. Furthermore, Kysely encourages a modular design, so your query logic is never scattered or duplicated across your codebase. This organization drastically improves clarity and reduces the chance of errors.

A maintainable database layer is one that can evolve without creating friction. Since Kysely enforces schema typing at compile time, any changes to your database schema that would break a query are caught immediately as a compile-time error. This prevents runtime bugs from ever reaching production. Kysely also allows you to compose and reuse query parts, which keeps your code DRY (Don’t Repeat Yourself) and easier to maintain. This clear separation of concerns, combined with built-in testability, ensures your database layer can stand the test of time.

Putting it all together

A resilient backend starts with a reliable database layer, and Kysely gives you the means to build one that is safe, explicit, and adaptable. By combining a type-safe query builder with a clean architectural pattern, it allows you to write queries that are both transparent and protected against schema drift. At the same time, it enforces a separation of responsibilities: repositories stay focused on persistence, services remain dedicated to business logic, and routes handle communication with the outside world.

If you’re beginning a new Node.js project, start small by defining a schema and experimenting with Kysely in a repository. Once you see the benefits of compile-time validation and clear query structure, scaling the approach across a larger system becomes straightforward. In doing so, you’re not just improving code quality — you’re building a foundation for a backend that can grow and adapt with your product for years to come.