Build Type-Safe SQL Queries With Kysely

If you use a query builder to generate SQL code in a TypeScript application, you may have encountered this situation: Someone makes a change to the database schema and updates the TypeScript type definitions accordingly. But they forget to update the return type of one method that uses the query builder, and now you have a runtime error.

In this article, we will see how Kysely lets us strongly type our queries to avoid such pitfalls. We will see how to infer TypeScript types from the database to make sure types are always up to date.

What Is Kysely?

Kysely is an open-source query builder for Node.js with strong typing. It lets developers build SQL queries with a fluent API, inspired by Knex. It also helps to detect errors in queries by leveraging TypeScript.

In the following example, we use the Kysely query builder to fetch a user from the database:

import { Kysely, PostgresDialect } from 'kysely';

interface User {
    id: number;
    name: string;
    email: string;
}

interface Post {
    id: number;
    title: string;
    content: string;
    authorId: number;
}

interface DB {
    users: User;
    posts: Post;
}

const db = new Kysely<DB>({
    dialect: new PostgresDialect({
        pool: new Pool({
            host: process.env.DATABASE_HOST,
            database: process.env.DATABASE_DB,
            user: process.env.DATABASE_USER,
            password: process.env.DATABASE_PASSWORD,
            port: Number(process.env.DATABASE_PORT),
        }),
    }),
});

const user = db
    .selectFrom('users')
    .where('id', 1)
    .select(['id', 'name', 'email'])
    .executeTakeFirst();

Kysely uses the DB interface to type the result of the query. As a consequence, TypeScript infers the user variable as User | undefined (because the query may return an empty result).

Kysely helps to type the query result, and also the query itself. For example, if we try to select a column foo that does not exist in the user type, we will have a TypeScript error:

const user = db
    .selectFrom('users')
    .where('id', 1)
    // Type Error: Argument of type 'string[]' is not assignable to parameter of type 'SelectExpression<DB, "users">'.
    .select(['id', 'name', 'email', 'foo'])
    .executeTakeFirst();

The error message is not very clear, but it helps to detect the bug and prevent runtime errors.

Typing Joins

Even better: when using joins, Kysely continues to offer strong typing.

const userWidthPostTitles = db
    .selectFrom('users')
    .innerJoin('posts', 'posts.authorId', 'users.id')
    .where('users.id', 1)
    .select(['users.id', 'users.name', 'users.email', 'posts.title'])
    .execute();

The userWidthPostTitles variable is typed as { id: number; name: string; email: string; title: string; }[];. You may have noticed things like users.id, this is typed as well. Kysely infers the complete string with the table name and the column name. users.foo or foo.id will not compile.

You can also use named joins (JOIN ... AS ...) with Kysely. It's useful when you have multiple joins on the same table. Again it's strongly typed.

In the following example, we fetch a post with its two authors:

const postWithAuthors = await db
    .selectFrom('post')
    .innerJoin('author as firstAuthor', 'firstAuthor.id', 'post.author_id')
    .innerJoin(
        'author as secondAuthor',
        'secondAuthor.id',
        'post.secondary_author_id',
    )
    .select([
        'post.id as id',
        'post.title as title',
        'firstAuthor.id as firstAuthorId',
        'firstAuthor.name as firstAuthorName',
        'secondAuthor.id as secondAuthorId',
        'secondAuthor.name as secondAuthorName',
    ])
    .where('post.id', '=', post.id)
    .executeTakeFirst();

Kysely is a powerful library with a lot more features, but we will not cover them in this article. You can find more information on the Kysely documentation.

Enhancing Kysely with Database Types

Kysely is a great tool, but it requires that we specify the record types (like the User and Post types above) manually. This can lead to a desynchronization if the database schema is changed. Ideally, we want the database to be the source of truth. So we have to find a way to generate the types from the database.

kysely-codegen is a library made by Robin Blomberg that generates the types by introspecting the database. It supports a lot of database vendors; in this article, we will use PostgreSQL.

In the previous examples, we built the DB interface manually. With kysely-codegen, we can let the library define the types itself:

import { Kysely, PostgresDialect } from "kysely";
import type { DB } from "kysely-codegen";

const db = new Kysely<DB>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: process.env.DATABASE_HOST,
      database: process.env.DATABASE_DB,
      user: process.env.DATABASE_USER,
      password: process.env.DATABASE_PASSWORD,
      port: Number(process.env.DATABASE_PORT),
    }),
  }),
});

To generate the types, we have to run the following command:

npm run kysely-codegen

The command will use environment variables to connect to the database and generate types in the kysely-codegen package folder. You must regenerate the types each time you change the database schema, and after each npm install. The types are not committed to the repository as they are in node_modules.

And that's it. Now we have a query builder that is strongly typed from the database. If we change the database, the types will be updated.

Using Special Column Types

PostgreSQL offers some unique column types, like jsonb or uuid. Kysely and kysely-codegen both support them. JSON is natively supported by both libraries. For insertion, we have to serialize the data to JSON. For selection, Kysely will deserialize the data to JavaScript objects.

Another special Postgres column type is ltree. It has minimal support by both libraries, but it's enough to use it. kysely-codegen will type the column as string. And Kysely will serialize and deserialize the data to string. That's enough to use it.

The following query will select all cities that are in France or a subregion of France. The <@ operator is directly supported. But we can use any operator or native function by using the sql tagged template, for example sql`?<@`.

const cities = await db
    .selectFrom('cities')
    .where('path', '<@', 'France')
    .selectAll()
    .execute();

Running Database Migrations

Kysely also supports migrations, but it does not provide a CLI to run them. Instead, kysely provides a Migrator class that you must use to run migrations.

Here is a snippet that runs all migrations found in a migrations folder:

import * as path from "node:path";
import pg from "pg";
import type { DB } from "kysely-codegen";
import { promises as fs } from "node:fs";
import {
  Kysely,
  Migrator,
  PostgresDialect,
  FileMigrationProvider,
} from "kysely";
import "dotenv/config";

const { Pool } = pg;

const db = new Kysely<DB>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: process.env.DATABASE_HOST,
      database: process.env.DATABASE_DB,
      user: process.env.DATABASE_USER,
      password: process.env.DATABASE_PASSWORD,
      port: Number(process.env.DATABASE_PORT),
    }),
  }),
});

const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    // This needs to be an absolute path.
    migrationFolder: path.join(import.meta.dirname, "migrations"),
  }),
});

const { error, results } = await migrator.migrateToLatest();

results?.forEach((it) => {
  if (it.status === "Success") {
    console.log(`Migration "${it.migrationName}" was executed successfully`);
  } else if (it.status === "Error") {
    console.error(`Failed to execute migration "${it.migrationName}"`);
  }
});

if (error) {
  console.error("Failed to migrate");
  console.error(error);
  process.exit(1);
}

await db.destroy();

You can write your migration files with TypeScript, and run them with tsx. Here is an example migration file:

import { sql } from "kysely";
import { Kysely } from "kysely";
import type { DB } from "kysely-codegen";

export async function up(db: Kysely<DB>): Promise<void> {
  await db.schema
    .createTable("author")
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`)
    )
    .addColumn("name", "varchar(255)", (col) => col.notNull())
    .execute();
}

export async function down(db: Kysely<DB>): Promise<void> {
  await db.schema.dropTable("author").execute();
}

Did you notice the DB type here? We use it to type the query builder in the migration files. But it comes with a downside: If we change the database, some past migrations can be broken and throw errors.

But luckily Kysely does not put a hash in the migration table. So we can fix the migration files by adding a @ts-ignore comment, or by removing the DB type. It's not ideal, but past migration files are supposed to be for past versions of the database. So it's OK to ignore type errors in them. But for new migrations, we have to be careful and use the types.

By the way, the above snippet also contains an example of custom SQL built with the sql tagged template.

Conclusion

Kysely proposes a great developer experience: generate types from the database and write strongly typed queries. It helps to write more robust code and detect errors at compile time. And with kysely-codegen, we can generate types from the database. It's a great combo.

In my opinion, Kysely is better than Knex. I already use it in my projects and I'm very satisfied with it. I recommend it!