Kysely: Type-Safe SQL Patterns

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.

When to Use

• "write a Kysely query"
• "create database migration"
• "add a new table"
• "query with joins / subqueries / CTEs"
• "JSONB or array column operations"
• Working with an existing Kysely + PostgreSQL codebase
• Debugging Kysely type inference issues

Outcomes

• Artifact: Type-safe queries using ExpressionBuilder patterns
• Artifact: Migration files via kysely-ctl
• Decision: When to use query builder vs sql template tag

Core Philosophy

Prefer 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>`...`)

ExpressionBuilder (eb) Cheat Sheet

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.

Database Types

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.

Pitfalls

These are the most common mistakes when writing Kysely code.

1. eb.val() vs eb.lit() confusion

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

2. Forgetting .execute()

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

3. .where() vs .whereRef() for column comparisons

.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

4. Always type sql`` template literals

sql template literals infer as unknown. Always provide an explicit type parameter.

sql`now()`                      // Expression<unknown> -- bad
sql<Date>`now()`                // Expression<Date> -- good

5. selectAll() breaks nested json helper type inference (#1059)

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.

6. DATE columns cause timezone drift

The pg driver converts DATE to JS Date, causing timezone issues. Parse DATE as string instead. See references/migrations.md.

7. "Type instantiation is excessively deep"

Complex queries with many CTEs can exceed TypeScript's type depth. Use $assertType<T>() on intermediate CTEs. See references/relations-helpers.md.

8. PostgreSQL does NOT auto-index foreign keys

Always create indexes on FK columns manually in migrations. See references/migrations.md.

9. CamelCasePlugin causes drift with raw SQL

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.

10. JSONB inserts need JSON.stringify only in sql templates (#209)

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.

11. Pool queries use different connections (API, #330)

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.

12. WHERE does not narrow result types (#310)

.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.

13. Team migration ordering (#697)

Migrations added on parallel branches may fail strict ordering when merged. Set allowUnorderedMigrations: true on the Migrator. See references/migrations.md.

14. JSON aggregation changes runtime types (#1412)

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.

Official Resources

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.

Reference Files

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 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