Type-safe SQL query building with Kysely for PostgreSQL. Covers query patterns, ExpressionBuilder, JSONB/arrays, migrations, and common pitfalls. Use when writing Kysely queries, creating migrations, debugging type issues, or working with a Kysely codebase.
Kysely (pronounced "Key-Seh-Lee") is a type-safe TypeScript SQL query builder. It generates plain SQL with zero runtime ORM overhead. Every query is validated at compile time with full autocompletion.
Kysely is not an ORM -- no relations, no lazy loading, no magic. Just SQL with types.
sql template tagPrefer Kysely's query builder for everything it can express. Fall back to sql template tag only when the builder lacks support.
| Use Case | Approach |
|---|---|
| Schema definitions | Kysely migrations (db.schema.createTable) |
| Simple CRUD | Query builder (selectFrom, insertInto, updateTable, deleteFrom) |
| JOINs (any complexity) | Query builder (callback format for complex joins) |
| Aggregations / GROUP BY | Query builder with eb.fn |
| CTEs | Query builder (.with()) |
| Relations / nested JSON | jsonArrayFrom / jsonObjectFrom helpers |
| Conditional queries | $if() or dynamic filter arrays |
| Reusable fragments | Expression<T> helper functions |
| Dynamic columns/tables | db.dynamic.ref() / db.dynamic.table() with allowlisted values |
| Dynamic SQL fragments | sql.raw() with allowlisted values, sql.join() for arrays |
| Dialect-specific syntax | sql template tag |
| Unsupported operators | sql template tag |
Need a query?
Can Kysely's builder express it?
YES -> Use the query builder (type-safe, composable)
NO -> Use sql`` template tag (always type your output: sql<Type>`...`)
The eb callback parameter is the foundation of type-safe query building:
| Method | Purpose | Example |
|---|---|---|
eb.ref("col") | Column reference | eb.ref("user.email") |
eb.val(value) | Parameterized value ($1) | eb.val("hello") |
eb.lit(value) | SQL literal (numbers, bools, null only) | eb.lit(0), eb.lit(null) |
eb.fn<T>("name", [...]) | Typed function call | eb.fn<string>("upper", [eb.ref("email")]) |
eb.fn.count("col") | COUNT aggregate | eb.fn.count("id").as("count") |
eb.fn.sum / avg / min / max | Other aggregates | eb.fn.sum("amount").as("total") |
eb.fn.coalesce(col, fallback) | COALESCE | eb.fn.coalesce("col", eb.val(0)) |
eb.case().when().then().else().end() | CASE expression | see query-patterns.md |
eb.and([...]) / eb.or([...]) | Combine conditions | eb.or([eb("a","=",1), eb("b","=",2)]) |
eb.exists(subquery) | EXISTS check | eb.exists(db.selectFrom(...)) |
eb.not(expr) | Negate expression | eb.not(eb.exists(...)) |
eb.cast(expr, "type") | SQL CAST | eb.cast(eb.val("x"), "text") |
eb(left, op, right) | Binary expression | eb("qty", "*", eb.ref("price")) |
For full query examples, see references/query-patterns.md.
import { Generated, Insertable, Selectable, Updateable } from "kysely"
interface Database {
users: UsersTable
posts: PostsTable
}
interface UsersTable {
id: Generated<number>
email: string
name: string
created_at: Generated<Date>
}
// Helper types make Generated fields optional for inserts/updates
type NewUser = Insertable<UsersTable>
type UserUpdate = Updateable<UsersTable>
type User = Selectable<UsersTable>
Use kysely-codegen to generate these types from your database. See references/migrations.md.
These are the most common mistakes when writing Kysely code.
eb.val() creates parameterized values ($1) -- use for user input. eb.lit() creates SQL literals -- only accepts numbers, booleans, null (not strings). For string literals, use sql\'value'``.
eb.val("safe input") // $1 -- parameterized, safe
eb.lit(42) // 42 -- literal in SQL
eb.lit("text") // THROWS "unsafe immediate value"
eb.cast(eb.val("text"), "text") // $1::text -- workaround for typed string params
Queries are lazy builders. Without an execute method, nothing runs.
db.selectFrom("user").selectAll() // does nothing
await db.selectFrom("user").selectAll().execute() // runs the query
.where("a", "=", "b") compares column a to the string "b". Use .whereRef() for column-to-column comparisons.
.where("table.col", "=", "other.col") // compares to string literal
.whereRef("table.col", "=", "other.col") // compares two columns
sql template literals infer as unknown. Always provide an explicit type parameter.
sql`now()` // Expression<unknown> -- bad
sql<Date>`now()` // Expression<Date> -- good
Bare .selectAll() inside json helper subqueries merges outer table columns into the type. Use table-qualified .selectAll("table_name") instead. See references/relations-helpers.md.
The pg driver converts DATE to JS Date, causing timezone issues. Parse DATE as string instead. See references/migrations.md.
Complex queries with many CTEs can exceed TypeScript's type depth. Use $assertType<T>() on intermediate CTEs. See references/relations-helpers.md.
Always create indexes on FK columns manually in migrations. See references/migrations.md.
CamelCasePlugin converts snake_case DB columns to camelCase in the builder. But raw sql template queries bypass the plugin, creating inconsistent naming between builder and raw queries in the same codebase. If you use significant raw SQL alongside the builder, avoid this plugin and keep snake_case throughout. See references/migrations.md.
The pg driver auto-serializes objects for .values()/.set() JSONB params (pg types). You only need explicit JSON.stringify inside sql template expressions or with non-pg drivers. See references/jsonb-arrays.md.
Each query may use a different pooled connection. SET, session variables, and RLS context do not persist across queries. Use db.transaction() or db.connection() to pin multiple statements to one connection. See references/advanced-patterns.md.
.where('col', 'is not', null) does not remove null from the result type. Use $narrowType to manually assert the narrowed shape. See references/advanced-patterns.md.
Migrations added on parallel branches may fail strict ordering when merged. Set allowUnorderedMigrations: true on the Migrator. See references/migrations.md.
Date columns inside jsonArrayFrom/jsonObjectFrom/json_agg results become strings at runtime because JSON has no Date type. TypeScript types still say Date. Parse dates manually at the boundary. See references/jsonb-arrays.md.
| Resource | URL |
|---|---|
| LLM-friendly docs (full) | https://kysely.dev/llms-full.txt |
| API documentation | https://kysely-org.github.io/kysely-apidoc |
| Playground | https://kyse.link |
| GitHub | https://github.com/kysely-org/kysely |
| Awesome Kysely (ecosystem) | https://github.com/kysely-org/awesome-kysely |
When using Cursor @Docs, reference https://kysely.dev/llms-full.txt for the most complete context.
Consult these for detailed code patterns:
| Reference | When to Use |
|---|---|
| query-patterns.md | SELECT, WHERE, JOINs, aggregations, ORDER BY, mutations, $if, subqueries, transactions |
| jsonb-arrays.md | JSONB columns, array columns, JSONPath, querying JSON/array data |
| relations-helpers.md | jsonArrayFrom, jsonObjectFrom, reusable Expression<T> helpers, CTEs, compile/InferResult |
| migrations.md | kysely-ctl setup, migration files, column types, type generation, plugins, Neon dialect, DATE fix |
| advanced-patterns.md | Dynamic columns, withSchema, connection pinning, RLS, $narrowType, streaming, MERGE, views, FTS, testing |
| ecosystem.md | Pagination, auth adapters, Fastify plugin, community dialects |