# A deep dive into Kysely

> Build a small PostgreSQL-backed store with Kysely, from the first typed query to joins, transactions, migrations, debugging, and tests.

Author: [Flavio Copes](https://flaviocopes.com/about/) | Published: 2026-08-11 | Updated: 2026-08-11 | Topics: [Database](https://flaviocopes.com/tags/database/) | Canonical: https://flaviocopes.com/kysely/

I like SQL.

I like seeing the tables I am reading, the columns I am selecting, and the conditions that decide which rows come back.

But I also like TypeScript.

If I rename a column, I want the editor to tell me which queries broke. If a query can return no row, I want that possibility in the result type. If I mistype a table name, I would rather find out before the code reaches production.

This is the problem [Kysely](https://www.kysely.dev/) solves.

Kysely is a type-safe SQL query builder for TypeScript. It does not try to make the database disappear. It gives us a TypeScript API that still looks and feels like SQL.

In this tutorial we'll build a small store that sells software products.

We'll add users, products, and orders. We'll start with one select query, then add filters, joins, inserts, updates, a transaction, migrations, and tests.

By the end, you'll know what using Kysely in a real application feels like.

## The store we are building

Our store needs three tables:

- `users` contains sellers and customers
- `products` contains the products for sale
- `orders` connects a customer to a product

The relationships look like this:

```mermaid
erDiagram
  USERS ||--o{ PRODUCTS : creates
  USERS ||--o{ ORDERS : places
  PRODUCTS ||--o{ ORDERS : appears_in
```

We could write every query as a SQL string.

That would work, but TypeScript would know almost nothing about those strings. It would not know that `products.price_in_cents` is a number or that an order status can only be `pending`, `paid`, or `cancelled`.

A full ORM goes in the other direction. It often gives us models and relations that hide some of the SQL.

Kysely sits in the middle.

We write this:

```ts
const product = await db
  .selectFrom('products')
  .select(['id', 'name', 'price_in_cents'])
  .where('id', '=', productId)
  .executeTakeFirst()
```

Kysely compiles it to parameterized PostgreSQL:

```sql
select "id", "name", "price_in_cents"
from "products"
where "id" = $1
```

The value of `productId` is sent separately as a parameter. It is not pasted into the SQL string.

The result type is inferred too:

```ts
{
  id: number
  name: string
  price_in_cents: number
} | undefined
```

The `undefined` is important. A product with that ID might not exist.

This is the core Kysely loop: write a query that looks like SQL, get help from TypeScript, then let the normal database driver execute it.

## Install Kysely

We'll use PostgreSQL and its standard Node.js driver, `pg`:

```bash
npm install kysely pg
npm install --save-dev @types/pg
```

Kysely expects TypeScript strict mode. Check your `tsconfig.json`:

```json
{
  "compilerOptions": {
    "strict": true
  }
}
```

Kysely also has built-in dialects for MySQL, Microsoft SQL Server, SQLite, and PGlite. Community dialects cover other databases and runtimes.

A dialect connects two worlds. It compiles the query for one SQL flavor and talks to a compatible database driver.

We'll stay with PostgreSQL so we can focus on Kysely itself.

## Tell TypeScript what is in the database

Kysely does not inspect the database every time we write a query.

We give it a TypeScript interface that describes the schema:

```ts
import type {
  ColumnType,
  Generated,
  Insertable,
  Selectable,
  Updateable,
} from 'kysely'

interface UserTable {
  id: Generated<number>
  email: string
  name: string
  created_at: ColumnType<Date, string | undefined, never>
}

interface ProductTable {
  id: Generated<number>
  created_by: number
  name: string
  price_in_cents: number
}

interface OrderTable {
  id: Generated<number>
  user_id: number
  product_id: number
  quantity: number
  status: 'pending' | 'paid' | 'cancelled'
  created_at: ColumnType<Date, string | undefined, never>
}

export interface Database {
  users: UserTable
  products: ProductTable
  orders: OrderTable
}

export type User = Selectable<UserTable>
export type NewUser = Insertable<UserTable>
export type UserUpdate = Updateable<UserTable>
```

The keys in `Database` are the real table names. Each table interface describes its columns.

There are two Kysely types worth pausing on.

`Generated<number>` means PostgreSQL creates the ID. The field exists when we select a row, but we do not have to provide it when inserting one.

`ColumnType<Date, string | undefined, never>` describes three moments in the life of `created_at`:

1. A select returns a `Date`.
2. An insert can pass a string or omit the value.
3. An update cannot change it.

This is more precise than pretending one type works for every operation.

Also notice that a nullable column would use `string | null`, not an optional property. `null` is a value the database can return. An optional property might not exist at all.

The interface describes the database. It does not create the tables, validate incoming data, or convert runtime values. We'll return to those boundaries later.

## Connect to PostgreSQL

Now we can create the Kysely instance:

```ts
import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import type { Database } from './database-types'

const dialect = new PostgresDialect({
  pool: new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,
  }),
})

export const db = new Kysely<Database>({ dialect })
```

Create one instance for the database and reuse it.

Do not put this code inside every request handler. The PostgreSQL dialect owns a connection pool. Recreating the pool for every request can open far too many connections.

For a short-lived script, close it when the work is done:

```ts
await db.destroy()
```

Serverless and edge runtimes need a driver designed for their environment. A traditional TCP pool does not belong inside every Cloudflare Worker.

## Make the first query

Let's add our first user:

```ts
const user = await db
  .insertInto('users')
  .values({
    email: 'flavio@flaviocopes.com',
    name: 'Flavio',
  })
  .returning(['id', 'email'])
  .executeTakeFirstOrThrow()
```

TypeScript already knows a few useful things.

We do not need to provide `id` because it is generated. We can omit `created_at` because its insert type allows `undefined`. But if we remove `email`, TypeScript complains before the query runs.

PostgreSQL does not return the inserted row by default. `returning()` asks it to send back only the fields we need.

Now let's read the user:

```ts
const user = await db
  .selectFrom('users')
  .select(['id', 'name', 'email'])
  .where('email', '=', 'flavio@flaviocopes.com')
  .executeTakeFirst()
```

Use `executeTakeFirst()` when no match is a normal result. It returns the first row or `undefined`.

Use `executeTakeFirstOrThrow()` when the row must exist:

```ts
const product = await db
  .selectFrom('products')
  .selectAll()
  .where('id', '=', productId)
  .executeTakeFirstOrThrow()
```

The query throws if the product is missing.

For several rows, use `execute()`:

```ts
const products = await db
  .selectFrom('products')
  .select(['id', 'name', 'price_in_cents'])
  .execute()
```

This returns an array.

My advice is to select specific columns at application boundaries. It prevents a private column added later from accidentally appearing in an API response.

## Look at the SQL

Before adding more features, I want to show one of my favorite Kysely tools.

Query builders do nothing until we call an execution method. We can compile a query without sending it to PostgreSQL:

```ts
const query = db
  .selectFrom('products')
  .select(['id', 'name'])
  .where('price_in_cents', '>=', 2900)

const compiled = query.compile()

console.log(compiled.sql)
console.log(compiled.parameters)
```

The result contains the SQL and its parameters:

```js
{
  sql: 'select "id", "name" from "products" where "price_in_cents" >= $1',
  parameters: [2900]
}
```

There is no mystery left. We can see exactly what Kysely will send.

This is also a good reminder that Kysely is a query builder, not a separate database language.

## Add products and filters

Let's put two products in the store:

```ts
await db
  .insertInto('products')
  .values([
    {
      created_by: user.id,
      name: 'Waiting Lists',
      price_in_cents: 2900,
    },
    {
      created_by: user.id,
      name: 'Events Logger',
      price_in_cents: 3900,
    },
  ])
  .execute()
```

Now imagine the products endpoint accepts two optional filters: a seller ID and a minimum price.

Kysely query builders are immutable. Each call returns a new builder, so we reassign it as filters arrive:

```ts
let query = db
  .selectFrom('products')
  .select(['id', 'name', 'price_in_cents'])

if (sellerId !== undefined) {
  query = query.where('created_by', '=', sellerId)
}

if (minimumPrice !== undefined) {
  query = query.where('price_in_cents', '>=', minimumPrice)
}

const products = await query.execute()
```

This pattern appeared in a Hono marketplace API I built. The endpoint returned every product by default, then narrowed the query when the URL included a seller.

Notice the reassignment.

This does not change the original builder:

```ts
query.where('created_by', '=', sellerId)
```

We must keep the returned builder:

```ts
query = query.where('created_by', '=', sellerId)
```

That small detail matters as soon as a query becomes conditional.

## Join the store together

A customer places an order. Now we want to show the order together with the product name.

In SQL, that means a join. Kysely keeps the same shape:

```ts
const orders = await db
  .selectFrom('orders')
  .innerJoin('products', 'products.id', 'orders.product_id')
  .select([
    'orders.id',
    'orders.quantity',
    'orders.status',
    'products.name as product_name',
  ])
  .where('orders.user_id', '=', userId)
  .execute()
```

Kysely knows that `products` becomes visible after the join. It also understands the alias, so every result has a `product_name` property.

The SQL still looks familiar:

```sql
select
  "orders"."id",
  "orders"."quantity",
  "orders"."status",
  "products"."name" as "product_name"
from "orders"
inner join "products"
  on "products"."id" = "orders"."product_id"
where "orders"."user_id" = $1
```

Use a left join when the related row is optional. Kysely then includes `null` in the type of columns from the optional side.

If joins still feel mysterious, read my [SQL joins guide](https://flaviocopes.com/sql-joins/) first. Kysely makes joins typed, but their relational meaning stays the same.

## Place an order safely

Placing an order takes more than one step.

We need to find the product, then create the order. Those statements belong to one unit of work.

This is what a transaction is for:

```ts
const order = await db
  .transaction()
  .execute(async (trx) => {
    const product = await trx
      .selectFrom('products')
      .select(['id', 'price_in_cents'])
      .where('id', '=', productId)
      .executeTakeFirstOrThrow()

    return trx
      .insertInto('orders')
      .values({
        user_id: userId,
        product_id: product.id,
        quantity: 1,
        status: 'pending',
      })
      .returningAll()
      .executeTakeFirstOrThrow()
  })
```

If the callback finishes, Kysely commits the transaction. If it throws, Kysely rolls it back.

Use the `trx` object inside the callback. A query made through the outer `db` instance is not part of that transaction.

I used the same pattern in an Astro password-reset flow. The application selected a reset token and deleted it before returning the user ID:

```ts
const token = await db
  .transaction()
  .execute(async (trx) => {
    const storedToken = await trx
      .selectFrom('password_reset_token')
      .selectAll()
      .where('id', '=', tokenId)
      .executeTakeFirstOrThrow()

    await trx
      .deleteFrom('password_reset_token')
      .where('id', '=', tokenId)
      .execute()

    return storedToken
  })
```

The application, not Kysely, decides what a valid token means. Kysely makes the database operation explicit and typed.

For strict one-time behavior under concurrent requests, I would use row locking or a single atomic delete with `returning()`. A transaction creates a boundary, but the database still decides its isolation behavior.

## Change data as the story continues

Once payment succeeds, we update the order:

```ts
const paidOrder = await db
  .updateTable('orders')
  .set({ status: 'paid' })
  .where('id', '=', order.id)
  .returning(['id', 'status'])
  .executeTakeFirstOrThrow()
```

We can also build a new value from the current database value. This increments the quantity without reading it into JavaScript first:

```ts
await db
  .updateTable('orders')
  .set((eb) => ({
    quantity: eb('quantity', '+', 1),
  }))
  .where('id', '=', order.id)
  .execute()
```

To remove a cancelled order:

```ts
const deleted = await db
  .deleteFrom('orders')
  .where('id', '=', order.id)
  .returning('id')
  .executeTakeFirst()
```

Be careful with updates and deletes.

Kysely does not add a safety condition for us. A statement without `where()` is valid SQL and can affect every row. Type safety cannot protect us from a correctly typed destructive query.

## Ask a larger question

CRUD is only the beginning. Soon we want to know how many paid orders each user placed.

Kysely supports aggregates:

```ts
const totals = await db
  .selectFrom('orders')
  .select((eb) => [
    'user_id',
    eb.fn.count<number>('id').as('order_count'),
  ])
  .where('status', '=', 'paid')
  .groupBy('user_id')
  .execute()
```

For a larger query, a common table expression can make the steps clearer:

```ts
const result = await db
  .with('paid_orders', (db) =>
    db
      .selectFrom('orders')
      .select(['user_id', 'product_id'])
      .where('status', '=', 'paid'),
  )
  .selectFrom('paid_orders')
  .innerJoin(
    'products',
    'products.id',
    'paid_orders.product_id',
  )
  .select(['paid_orders.user_id', 'products.name'])
  .execute()
```

The CTE name and the columns it selects become part of the type system.

No query builder can cover every feature of every database. When the builder gets in the way, use Kysely's [`sql` template tag](https://www.kysely.dev/docs/recipes/raw-sql):

```ts
import { sql } from 'kysely'

const email = 'flavio@flaviocopes.com'

const result = await sql<{ id: number }>`
  select id from users where email = ${email}
`.execute(db)
```

The interpolated email still becomes a parameter.

Dynamic table names and column names are different. They are SQL identifiers, not values. If we use a powerful escape hatch such as `sql.ref()`, we must validate the allowed identifier ourselves.

Use raw SQL when it makes a query clearer or unlocks a database feature. Do not use it to rebuild unsafe string concatenation.

## Keep the types aligned with the database

We wrote the database interfaces by hand because it made the relationship easy to see.

In a production app, I usually want the database to remain the source of truth. I used [`kysely-codegen`](https://github.com/RobinBlomberg/kysely-codegen) in both of my Kysely projects:

```bash
npx kysely-codegen
```

The application then imports the generated type:

```ts
import type { DB } from 'kysely-codegen'

export const db = new Kysely<DB>({ dialect })
```

The workflow is important:

```mermaid
flowchart LR
  A[Migration] --> B[Database schema]
  B --> C[Generate types]
  C --> D[Type-check queries]
```

Run the migration first. Generate the types next. Type-check the application last.

If the generated interface and the database disagree, stop and fix the workflow. Editing the generated file only hides the drift until the next generation.

## Change the schema with migrations

Our store will change over time. Perhaps products need a description.

Kysely includes schema and migration primitives. A migration has an `up` function that applies the change and a `down` function that reverses it:

```ts
import { Kysely } from 'kysely'

export async function up(db: Kysely<any>) {
  await db.schema
    .alterTable('products')
    .addColumn('description', 'text')
    .execute()
}

export async function down(db: Kysely<any>) {
  await db.schema
    .alterTable('products')
    .dropColumn('description')
    .execute()
}
```

The [official migration guide](https://www.kysely.dev/docs/migrations) uses `Kysely<any>` on purpose.

A migration describes the schema at one moment in history. The application type describes the schema today. An old migration must keep working after today's interface changes.

Keep migrations frozen in time. Do not import current application helpers into them.

Kysely's `Migrator` can load and run migration files. [`kysely-ctl`](https://github.com/kysely-org/kysely-ctl) adds a CLI workflow if you do not want to build the runner yourself.

## Debug a surprising query

Kysely can generate correct SQL that is still slow.

When a query surprises me, I follow the database all the way down:

1. Call `.compile()` and inspect the SQL and parameters.
2. Run `EXPLAIN` or `EXPLAIN ANALYZE` in PostgreSQL.
3. Check indexes and row counts.
4. Verify the driver returns the runtime types in the interface.
5. Reduce the query until the unexpected part becomes obvious.

We can also enable query logging:

```ts
export const db = new Kysely<Database>({
  dialect,
  log: ['query', 'error'],
})
```

Do not log sensitive parameters in production. Queries can contain emails, password-reset tokens, and private content.

Query plans, indexes, locks, and database statistics remain database concerns. Kysely keeps the database visible, which makes those problems easier to investigate.

## Test the queries at two levels

For conditional query-building code, we can test the compiled result without opening a database connection:

```ts
const query = db
  .selectFrom('products')
  .select(['id', 'name'])
  .where('price_in_cents', '>=', 2900)

const compiled = query.compile()

expect(compiled.sql).toContain('price_in_cents')
expect(compiled.parameters).toEqual([2900])
```

This test is fast, but it only checks query construction.

An integration test against a temporary PostgreSQL database catches different problems:

- a migration and the generated types disagree
- a constraint behaves differently than expected
- the driver returns a surprising runtime value
- transaction isolation matters
- a query works in one dialect but not another

For important database code, a small real database is more valuable than a large mock.

## What Kysely does not do

After building the store, the boundaries are easier to see.

Kysely is not a full ORM. It does not give us model instances, lazy-loaded relations, an identity map, or a unit-of-work abstraction. We choose the joins and write the queries.

Kysely is not a runtime validator either.

TypeScript disappears at runtime. If an API request contains an invalid email, Kysely does not reject it for us. I validate untrusted input with a library such as [Zod](https://flaviocopes.com/zod/) before building the query.

Kysely also trusts the runtime types we declare.

If `pg` returns a PostgreSQL `bigint` as a string while our interface says `number`, the value is still a string. This also matters for `numeric`, JSON, and timestamp columns.

The complete flow looks like this:

```mermaid
flowchart LR
  A[HTTP request] --> B[Runtime validation]
  B --> C[Application rules]
  C --> D[Kysely query]
  D --> E[Dialect and driver]
  E --> F[(PostgreSQL)]
  F --> G[Runtime result]
```

Each layer has one job. Kysely owns the typed query, not the entire application.

## How I would use Kysely now

I would choose Kysely for a TypeScript service backed by an existing PostgreSQL database.

I would keep migrations as the schema history, generate the database interface with `kysely-codegen`, validate API input with Zod, and put reusable queries in small functions.

For example:

```ts
export async function findPaidOrders(userId: number) {
  return db
    .selectFrom('orders')
    .innerJoin('products', 'products.id', 'orders.product_id')
    .select([
      'orders.id',
      'orders.quantity',
      'products.name as product_name',
    ])
    .where('orders.user_id', '=', userId)
    .where('orders.status', '=', 'paid')
    .execute()
}
```

The function has one job and returns exactly what its caller needs.

This is close to how I used Kysely in my Hono marketplace API. Hono handled HTTP. Kysely handled direct queries with optional filters and joins.

I also used it in an Astro authentication flow, where transactions mattered more than basic CRUD.

Those are good Kysely projects. The endpoints map closely to SQL operations, and I want exact control over every selected column and join.

I would also consider it when several TypeScript services share an existing database. Generating types from the real schema reduces the chance that handwritten models drift away from it.

## When I would choose something else

I would not choose Kysely to avoid learning SQL.

The API follows SQL closely. We still need to understand joins, nulls, constraints, indexes, transactions, and query plans. Kysely makes SQL safer to write from TypeScript. It does not replace database knowledge.

I would consider [Drizzle](https://flaviocopes.com/drizzle/) when I want TypeScript schema declarations, relations, and a larger toolkit built around the schema.

I would consider a higher-level ORM when the team wants model-oriented relation loading and accepts the abstraction.

I would use raw SQL when a service contains only a few stable, complex queries and TypeScript composition adds little value.

Kysely is also a poor fit outside TypeScript. Most of its value lives in the type system.

For Cloudflare D1, I would first check whether the community dialect fits the project. D1 still has SQLite behavior and Worker runtime constraints. A query builder cannot make every database identical.

Large query types can also make TypeScript work hard. Kysely provides [`$assertType()`](https://www.kysely.dev/docs/recipes/excessively-deep-types) for deliberate boundaries where a structurally equal, simpler type can replace a deeply nested inferred one.

Use it to simplify a correct type, not to hide a mismatch.

## Why I like Kysely

Kysely does not save us from the database.

That is exactly why I like it.

The tables stay visible. The joins stay visible. The transaction stays visible. When a query is slow, we can inspect the SQL and ask PostgreSQL for its plan.

At the same time, TypeScript knows which columns exist, what values an insert needs, and what shape a query returns.

That is a useful balance.

Start with one database interface and one select query. Compile it and look at the SQL. Then add joins, transactions, generated types, and migrations when the application needs them.

Keep the database visible. That is the whole point.