# Kysely in Production: designing a type-safe SQL query builder as the “no ORM” choice (2026)

> Kysely is not an ORM — it is a type-safe SQL query builder. Zero dependencies, Node 22+, who owns the DB types, the Migrator that never throws, and the in () and NULL-comparison traps that are SQL semantics rather than bugs. Every fact verified against the source.

- Published: 2026-09-05
- Author: 友田 陽大
- Tags: TypeScript, 型安全, データベース, PostgreSQL, アーキテクチャ設計, 技術選定
- URL: https://tomodahinata.com/en/blog/kysely-typescript-type-safe-sql-query-builder-production-guide
- Category: Databases & RLS
- Pillar guide: https://tomodahinata.com/en/blog/supabase-production-guide-nextjs-rls-realtime-edge-functions

## Key points

- Kysely is not “a lighter ORM”. It resolves no relations and manages no schema — it keeps the shape of your SQL and guards it with types. It is only the right answer when you genuinely do not want an ORM
- The facts are in package.json: dependencies is empty, sideEffects is false, engines.node is >=22.0.0. On Node 20 LTS, 0.29.5 is off the table
- Kysely does not generate your database types. Deciding who owns `interface DB` is the central design call, and hand-writing, kysely-codegen and prisma-kysely each fail differently
- The Migrator does not throw on failure. A deploy script that ignores the `error` in `const { error, results } = await migrator.migrateToLatest()` ships a failed migration as a success
- The `in ()` syntax error and `= NULL` three-valued logic are closed by official plugins (HandleEmptyInLists / SafeNullComparison). Both are SQL semantics, not Kysely bugs

---

Say “build a data layer in TypeScript” and the first names on the table are ORMs. But on some projects, **the abstraction an ORM provides is exactly what you do not want**. Analytics-heavy services where aggregation and window functions are the main event. Platforms that have to reach for database-specific features. Teams who read SQL fine and just want the types guarded.

**Kysely** lives in that space. It is often introduced as “a lightweight ORM”, which is not accurate. Kysely is **not an ORM — it is a type-safe SQL query builder**. That distinction is not a matter of taste; it decides **what you remain responsible for**.

This article is about deciding whether to run Kysely in production, and how to assemble it so it does not break if you do. For a side-by-side of the four tools, see the [Prisma vs Drizzle vs TypeORM vs Kysely selection guide](/en/blog/prisma-vs-drizzle-vs-typeorm-kysely-orm-comparison-guide); here the focus is **Kysely alone, in production**.

> **Ground rules for this article**: every version, dependency and API behaviour below was verified against the **actual GitHub source** (`master`, as of v0.29.5). No numbers from summary sites or second-hand write-ups. Always confirm the latest in the [official docs](https://kysely.dev/docs/intro).

---

## 0. The answer first: when Kysely is the right call

If all three hold, Kysely fits. If even one does not, you will be happier with an ORM.

| Condition | Fits | Does not fit |
|---|---|---|
| **Writing SQL is itself the requirement** | Complex aggregation, window functions, DB-specific features | Mostly CRUD, and you would rather not write SQL |
| **You do not need automatic relation resolution** | Hand-written JOINs are faster and clearer for you | You want nested related data declaratively |
| **You can own the source of truth for types** | You can wire codegen into CI | You want everything derived from a schema definition |

Kysely is not “an ORM, thinner”. It is **the landing place for deciding against an ORM**. Get that wrong and you will be back six months later complaining that relations are tedious and schema management is thin.

---

## 1. Read the facts off `package.json`

This is where technical-selection writing goes wrong most often, so let us look at the actual manifest.

```jsonc
// kysely-org/kysely — package.json (v0.29.5), only the keys that drive selection
{
  "version": "0.29.5",
  "engines": { "node": ">=22.0.0" },
  "sideEffects": false       // tree-shakeable
  // `dependencies` / `peerDependencies` are not empty — the keys are absent
  // altogether. Only `devDependencies` exists. That is: zero runtime deps.
}
```

Four facts here bear directly on selection.

**① Zero dependencies.** `dependencies` is empty. Reducing the supply-chain attack surface to effectively Kysely itself is a real benefit where audit scope matters. Database drivers (`pg`, `mysql2`, …) are held directly by your application instead.

**② `engines.node` is `>=22.0.0`.** Easy to miss and heavy in practice: **if your service runs on Node 20 LTS, 0.29.5 is off the table**. Check whether you can move Node before anything else.

**③ `sideEffects: false`.** Bundlers can drop unused code — worth having where bundle size matters on edge or serverless.

**④ The version is `0.x`.** It has not reached 1.0. By semver convention that is territory where minor releases may carry breaking changes. It is used successfully in production every day, but **“this is pre-1.0” belongs in the selection meeting**, not hidden. Pin the version and read the changelog on upgrade.

### Built-in dialects

`src/dialect/` contains five: **PostgreSQL, MySQL, MSSQL, SQLite and PGlite**. PGlite (PostgreSQL compiled to WASM) is a relatively recent addition and is useful when you want tests to run entirely in Node or the browser.

---

## 2. The central design call: who owns the `DB` type

Kysely's type safety rests on exactly one thing.

```ts
import { Kysely, PostgresDialect } from "kysely";

// This interface becomes the single source of truth for "the shape of the DB"
interface Database {
  person: PersonTable;
  pet: PetTable;
}

const db = new Kysely<Database>({ dialect: new PostgresDialect({ pool }) });
```

**Kysely never checks that `Database` matches the real schema.** It trusts what you hand it and builds query types from that. So the correctness of your types depends entirely on **whether you can keep this interface in sync with the actual schema**.

That is Kysely's biggest design decision and its biggest source of incidents. There are three practical options, and they **fail in different ways**.

| Approach | Source of truth | How it breaks |
|---|---|---|
| **Hand-written** | Human memory | You change the DB and forget the interface. It compiles; it fails at runtime |
| **kysely-codegen** (community) | The live database | Forget to regenerate and the types are stale. Generation now needs DB access |
| **prisma-kysely** | `schema.prisma` | You carry Prisma purely for type definitions. Risk of dual maintenance |

Whichever you pick, the mitigation is the same: **wire regeneration into CI and fail on a diff.** Commit the generated file, regenerate in CI, and run `git diff --exit-code` — simple and reliable.

> **Important**: `kysely-codegen` is a community package, not an official one. You will see claims that “Kysely generates your types automatically” — **Kysely core has no type generation at all**. Adopt it on that misunderstanding and you skip an entire piece of operational design.

### Express read/write asymmetry with `Generated` and `ColumnType`

A column's type differs between reading and writing. `id` always exists on SELECT but is omitted on INSERT. Kysely expresses this in the type system.

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

interface PersonTable {
  id: Generated<number>;                    // optional on INSERT, always present on SELECT
  first_name: string;
  // <SELECT type, INSERT type, UPDATE type>
  created_at: ColumnType<Date, string | undefined, never>;  // updates forbidden
}

type Person = Selectable<PersonTable>;       // reading
type NewPerson = Insertable<PersonTable>;    // inserting
type PersonUpdate = Updateable<PersonTable>; // updating
```

The `never` in the third position of `created_at` is the point. **A business rule — “creation time is immutable” — is enforced by the compiler rather than by code review.** Invariants you would normally implement as an ORM hook become expressible as types, and that is where Kysely feels good.

---

## 3. The Migrator does not throw (the production trap)

This is the easiest thing to get wrong in Kysely operations. Here is the contract, straight from the source.

```ts
// from the docblock in src/migration/migrator.ts
const { error, results } = await migrator.migrateToLatest();

results?.forEach((it) => {
  if (it.status === "Success") { /* ... */ }
  else if (it.status === "Error") { /* ... */ }
});

if (error) {
  console.error("failed to run `migrateToLatest`");
  console.error(error);
}
```

`migrateToLatest()` returns `Promise<MigrationResultSet>`. **It does not throw on failure.** Which means this deploy script is broken:

```ts
// ❌ Broken: a failed migration passes as a success
await migrator.migrateToLatest();
console.log("migrated");
process.exit(0);
```

The `await` succeeds. The failure is merely sitting in `error`. CI goes green and the application ships against a stale schema. The correct shape:

```ts
// ✅ Always turn failure into a process exit code
const { error, results } = await migrator.migrateToLatest();

for (const it of results ?? []) {
  const line = `${it.status.padEnd(7)} ${it.migrationName}`;
  it.status === "Error" ? console.error(line) : console.log(line);
}

await db.destroy();

if (error) {
  console.error("migration failed:", error);
  process.exit(1);   // ← without this, CI swallows the failure
}
```

Not throwing is a defensible design — it lets the caller see partially applied results. **The problem is that the obvious way to write it loses the failure**, which is exactly why this wrapper is worth writing once, up front.

### History, locking, and ordering

The constants in the source tell you what you need for operations.

```ts
export const DEFAULT_MIGRATION_TABLE = "kysely_migration";
export const DEFAULT_MIGRATION_LOCK_TABLE = "kysely_migration_lock";
export const DEFAULT_ALLOW_UNORDERED_MIGRATIONS = false;
export const NO_MIGRATIONS: NoMigrations = freeze({ __noMigrations__: true });
```

- History lives in `kysely_migration`, mutual exclusion in `kysely_migration_lock`. **Concurrent deploys are serialised by that lock** — which also means the lock table belongs in your backup and restore scope.
- **`allowUnorderedMigrations` defaults to `false`.** When several people branch in parallel and each adds a migration, out-of-order names fail by default. Decide deliberately: either set it to `true`, or renumber on merge.
- `migrateTo(NO_MIGRATIONS)` **rolls everything back**. Any migration without a `down` halts that, so if you want to claim “we can roll back”, `down` is mandatory.

---

## 4. Where types cannot protect you: SQL's three-valued logic

Kysely does not hide SQL, so **SQL's semantic traps remain in full**. TypeScript's type system does not cover them. The official plugins exist precisely to close these two.

### 4.1 `in ()` is a syntax error

Passing an array to `in` is the natural thing to write.

```ts
const ids: number[] = await getSelectedIds();   // ← can be empty
const rows = await db.selectFrom("person").selectAll()
  .where("id", "in", ids)
  .execute();
```

If `ids` is empty, the generated SQL is `where id in ()`. **That is a syntax error on most RDBMSs.** It type-checks. If your fixtures are never empty, production is where you find out.

`HandleEmptyInListsPlugin` rewrites it. One strategy in the source, `replaceWithNoncontingentExpression`, replaces `in ()` with a contradiction (`1 = 0`) and `not in ()` with a tautology. The docblock describes the strategy as working **"similarily to how Knex.js, PrismaORM, Laravel, SQLAlchemy handle this"** (sic, including the misspelling), linking to each implementation. So it is not a Kysely-only trick.

The same docblock is careful to add that **"The workarounds used by other libraries always involve modifying the query under the hood, which is not aligned with Kysely's philosophy of WYSIWYG"** — which is exactly why the behaviour is opt-in. The accurate reading is not "everyone does it, so it is safe" but **"everyone does it, and Kysely still refuses to make it the default."** Guard against empty arrays yourself first; this plugin is the fallback.

```ts
import { Kysely, HandleEmptyInListsPlugin, replaceWithNoncontingentExpression } from "kysely";

const db = new Kysely<Database>({
  dialect,
  plugins: [new HandleEmptyInListsPlugin({ strategy: replaceWithNoncontingentExpression })],
});
```

The other strategy, `pushValueIntoList`, pushes a dummy value into the list so indexes keep being used. **Replacing with a contradiction changes what the optimiser does**, so on large tables, check the execution plan before choosing.

### 4.2 `= NULL` is NULL, not FALSE

The docblock for `SafeNullComparisonPlugin` states the reason outright:

> In SQL, comparing values with NULL using standard comparison operators (=, !=, <>) always yields NULL, which is usually not what developers expect.

SQL runs on three-valued logic: TRUE / FALSE / **UNKNOWN**. `x = NULL` evaluates to unknown rather than false, and a WHERE clause does not pass unknown. So **you get zero rows even when matching rows exist** — with no error.

```ts
// Pass a nullable search condition straight through and null always yields 0 rows
const name: string | null = req.query.name ?? null;
await db.selectFrom("person").selectAll().where("first_name", "=", name).execute();
```

When the value is null, the plugin swaps the operator (per the source docblock):

- `=` → `IS`
- `!=` → `IS NOT`
- `<>` → `IS NOT`

```ts
import { SafeNullComparisonPlugin } from "kysely";
const db = new Kysely<Database>({ dialect, plugins: [new SafeNullComparisonPlugin()] });
```

**Neither plugin works around a Kysely bug. They absorb the sharp edges of SQL itself in one place**, instead of you writing the same guard at every call site. ORMs hide these on the inside, so you never notice. Choosing a tool that exposes SQL means accepting this responsibility too.

---

## 5. Production shape: types, transactions, connections

### Close transactions with a callback

```ts
await db.transaction().execute(async (trx) => {
  const person = await trx.insertInto("person")
    .values({ first_name: "Jennifer" })
    .returningAll()
    .executeTakeFirstOrThrow();

  await trx.insertInto("pet").values({ owner_id: person.id, name: "Catto" }).execute();
});
```

`trx` presents the same interface as `Kysely<Database>`, so **typing your repository functions as `Kysely<DB> | Transaction<DB>` lets you reuse them inside and outside a transaction**. That small upfront cost is what saves you when someone asks to pull one more operation into the transaction later.

```ts
type Db = Kysely<Database> | Transaction<Database>;

export async function findPersonById(db: Db, id: number) {
  return db.selectFrom("person").selectAll().where("id", "=", id).executeTakeFirst();
}
```

### Choose deliberately between `executeTakeFirst` and `executeTakeFirstOrThrow`

- `execute()` → `T[]`
- `executeTakeFirst()` → `T | undefined`
- `executeTakeFirstOrThrow()` → `T` (throws when absent)

**If you used the version that returns `undefined`, the type is telling you it might not be there.** Silencing that with `!` instead of writing the branch throws away the reason you chose Kysely. Conversely, if absence is an anomaly, use `OrThrow` and delete the branch entirely.

### Raw SQL through the `sql` tag — always parameterise values

```ts
import { sql } from "kysely";

// ✅ values become placeholders automatically
const rows = await sql<{ id: number }>`
  select id from person where first_name = ${name}
`.execute(db);

// ❌ concatenating identifiers is SQL injection
// sql`select * from ${sql.raw(tableName)}`   ← dangerous if tableName is user input
```

`${}` inside the `sql` tag is **parameterised**. `sql.raw()`, by contrast, embeds the string verbatim, so **never hand it external input**. When a table name or sort column has to be dynamic, take it through an allowlist.

```ts
const SORTABLE = { name: "first_name", created: "created_at" } as const;

const sort = String(req.query.sort ?? "");
// `Object.hasOwn` is the part that makes this safe. Written as
// `SORTABLE[sort] ?? "created_at"`, the allowlist leaks: `?sort=constructor`
// and `?sort=toString` return inherited values, which are not undefined, so
// the `??` fallback never fires.
const column = Object.hasOwn(SORTABLE, sort)
  ? SORTABLE[sort as keyof typeof SORTABLE]
  : "created_at";
// column originates from the allowlist, so it is safe
```

---

## 6. When not to choose Kysely

Bluntly: **if any of these apply, do not pick Kysely.**

1. **You run Node 20 or below** — `engines.node >= 22.0.0`. There is nothing to debate here.
2. **You want declarative relation traversal** — you write the JOINs and the reshaping yourself. There is no equivalent of an ORM's `include`.
3. **Your team does not read or write SQL** — a tool that thins the abstraction only pays off if someone can read what is underneath.
4. **You want a schema definition as the single source of truth** — Kysely generates no types, so you must design that ownership separately.

If none of 1–4 apply and “we can write SQL” is an advantage rather than a cost, Kysely is a very good tool. **The thinner abstraction means fewer surprises** — a property that pays for itself the day you are chasing an execution plan during an incident.

---

## 7. Adoption checklist

Confirm these seven before going to production.

- [ ] **Node is 22 or newer** (`engines.node >= 22.0.0`)
- [ ] You have **decided the source of truth** for the `DB` type (hand-written / kysely-codegen / prisma-kysely)
- [ ] Type **regeneration runs in CI** and fails on a diff
- [ ] Migration execution **inspects `error` and exits non-zero**
- [ ] The team has **agreed a policy** for `allowUnorderedMigrations`
- [ ] You have **decided whether to adopt** `HandleEmptyInListsPlugin` / `SafeNullComparisonPlugin`
- [ ] “**No external input reaches `sql.raw()`**” is a review criterion

Items 4 and 6 are **precisely the ones types will not protect** — which is why they earn a place on the list.

---

## Summary

Kysely is not “a lighter ORM”. It is **the tool for keeping type safety after you have decided against an ORM**.

- The facts are in `package.json`: zero dependencies, `sideEffects: false`, **`engines.node >= 22.0.0`**, and **still 0.x**
- **Types are not generated.** Deciding who owns `interface DB` and enforcing sync in CI is the heart of the design
- **`Migrator` does not throw.** A deploy script that ignores `error` swallows failure
- `in ()` and `= NULL` are **SQL semantics**, and the official plugins let you contain them in one place

Technical selection is not a holy war; it is matching constraints. For the side-by-side of all four tools see the [selection guide](/en/blog/prisma-vs-drizzle-vs-typeorm-kysely-orm-comparison-guide), and for going deep on Prisma or Drizzle themselves, the [Prisma production guide](/en/blog/prisma-orm-production-guide-type-safe-database-v7-driver-adapters) and the [Drizzle production guide](/en/blog/drizzle-orm-typescript-type-safe-database-production-guide).