Writing queries with Kysely

Kysely is a query builder, not an ORM. There's no entity layer, no lazy loading, no magic session flushing things behind your back. You describe your schema once as a TypeScript interface and from there every method on the builder narrows what you're allowed to do next.

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

interface UsersTable {
  id: Generated<number>
  email: string
  name: string | null
  created_at: Generated<Date>
}

interface Database {
  users: UsersTable
}

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

It's worth pointing out that Generated<T> marks columns the database fills in for you, like a serial primary key. When inserting data, the field is optional, but when you select it, it is always there. This is exactly what you want to prevent mistakes when writing code and it's where handwritten interfaces usually fail.

Actually querying something

const users = await db
  .selectFrom('users')
  .select(['id', 'email', 'created_at'])
  .where('email', 'like', '%@lucastonelli.com')
  .orderBy('created_at', 'desc')
  .limit(10)
  .execute()

// users: { id: number; email: string; created_at: Date }[]

The return type isn't something you annotated, it's inferred from what you selected. If you ask for two columns, you get a type with two columns. Make a typo (emial) and the build fails before anything reaches a database. Join another table and the autocomplete for where immediately knows about the new columns.

Kysely also ships Selectable, Insertable and Updateable helpers, so your service functions can take the right shape without you maintaining three nearly identical interfaces by hand.

import { Insertable } from 'kysely'

async function createUser(user: Insertable<UsersTable>) {
  return await db
    .insertInto('users')
    .values(user)
    .returningAll()
    .executeTakeFirstOrThrow()
}

Introspecting the database and generating the types

Everything above depends on that Database interface being correct. Writing it by hand is fine for three tables, but for thirty, it's tedious and it goes stale the moment a teammate merges a migration. At that point your types are no longer describing your database, they're describing your optimism.

The package kysely-codegen fixes that by connecting to the database, introspecting it and writing the types for you. You can start by installing it.

npm install --save-dev kysely-codegen

Then point it at a connection string (it reads DATABASE_URL from your .env by default) and run it.

kysely-codegen --out-file ./src/db/types.ts

Voilà, your types were generated! By default, the output lands in ./node_modules/kysely-codegen/dist/db.d.ts, which means the types vanish on a clean install and nobody can see them in a pull request, so the --out-file flag matters. Writing them into src and committing them means schema changes show up in the diff, where a human can actually look at them.

From there, the wiring barely changes:

import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import { DB } from './db/types'

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

There are a few options worth knowing about: --camel-case if you'd rather not use underscores everywhere (because of ESLint); --singularize to get User instead of Users. And you can have a config file (.kysely-codegenrc.ts, or a kysely-codegen key in package.json) so the whole team runs it the same way instead of everyone inventing their own flags.

Migrations

Generated types are only as good as the database they were generated from, so migrations need to be part of the same flow. Kysely exposes a Migrator class you can wire up by hand, but there's no reason to write that script yourself anymore since kysely-ctl is the official CLI and it does the wiring for you. It also mirrors Knex.js' CLI, so if you've used Knex before, the commands will feel suspiciously familiar.

npm install --save-dev kysely-ctl

Once installed in the project, it becomes available as kysely and a kysely.config.ts file will be required to use it, either in the project root or inside a .config folder. You can write it yourself, but the CLI will scaffold one for you by running kysely init.

import { defineConfig } from 'kysely-ctl'

export default defineConfig({
  dialect: 'pg',
  dialectConfig: {
    connectionString: process.env.DATABASE_URL,
  },
  migrations: {
    migrationFolder: './src/db/migrations',
  },
})

Notice that dialect here is just the name of the driver library, and dialectConfig gets handed to the matching Kysely dialect, so you don't have to instantiate anything - or just configure it from scratch here, it's fine either way. But if you'd rather not configure the connection twice, you can skip both and pass the kysely instance your app already exports instead.

Creating a migration

No more inventing your own file naming convention and then arguing about it in code review.

kysely migrate:make create_users_table

That drops a timestamped TypeScript file into your migration folder. There's a default prefix, but it can be customized, along with other settings in your config file.

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('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('name', 'varchar(255)')
    .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()
}

Yes... that's an any typing, and it's intentional, don't judge me (yet). A migration runs against whatever the schema looked like at that point in history, not against today's generated types. If you type it with your current DB, the file will keep compiling for a while and then start lying to you the moment a later migration drops that table. Kysely recommends any here and this is one of the rare places where it's the honest answer.

Running migrations

Every command can be written with a colon or a space, so kysely migrate:latest and kysely migrate latest do the same thing.

# run every pending migration
kysely migrate:latest

# see what has run and what hasn't
kysely migrate:list

# step back one migration
kysely migrate:down

# undo everything (the --all is not optional)
kysely migrate:rollback --all

That --all is the one gotcha worth remembering: a partial rollback isn't supported, because Kysely doesn't keep track of migration batches the way Knex does. If you want to undo a single migration, migrate:down is your friend. The good part is that the CLI reports each migration and its status for you, so you no longer have to remember that migrateToLatest never throws and quietly hands you an error object your deployment script forgot to check.

Under the hood it's still Kysely's own migrator, which means migrations take a database-level lock. Calling them from multiple instances at once is safe since they run serially and each migration executes exactly once.

As a bonus, the same CLI gives you seeds (kysely seed:make and kysely seed:run) and a kysely sql command that runs queries through your configured instance, which is handy when you just want to poke at something without opening a client.

Migrate first, then generate

The order matters, and it's the whole point of this setup. The database is the source of truth, the types are a derived artifact.

{
  "scripts": {
    "db:migrate": "kysely migrate:latest",
    "db:types": "kysely-codegen --out-file ./src/db/types.ts",
    "db:sync": "npm run db:migrate && npm run db:types"
  }
}

Run db:sync locally after writing a migration, commit both the migration and the regenerated types, and let CI type-check the result. If someone changes the schema and forgets to regenerate, the diff will look wrong to a reviewer. If someone regenerates the types and a query no longer compiles, that's not an annoyance, that's the tool doing its job and telling you a rename broke something.

Wrap-up

None of this is a framework and none of it asks you to give up SQL. Kysely types the queries you were already writing, kysely-codegen makes the database itself the source of those types, and kysely-ctl keeps the schema moving forward in a way you can replay and roll back. The library feels like a breath of fresh air among other options, fully integrated with TypeScript, helping us keep our sanity.