tl-kysely-patterns

<!-- Copyright (c) 2026 Todd Levy. Licensed under MIT. SPDX-License-Identifier: MIT -->

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.

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:

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

When using Cursor @Docs, reference https://kysely.dev/llms-full.txt for the most complete context.

Reference Files

Consult these for detailed code patterns: