kysely-postgres

# Kysely for PostgreSQL Use this skill to make production-safe Kysely changes, not merely to assemble a query chain. **Compatibility target:** Kysely `0.29.5`, Node.js `>= 22`, TypeScript `>= 5.4`, ESM, and TypeScript target `es2023`. Kysely no longer ships CommonJS files. Check the project's installed `kysely` version, Node runtime, dialect, driver, DB type declarations, migrations, and local conventions before applying an example from this skill. Repository conventions take precedence when they are compatible with the installed version. ## Required Workflow Follow this order for every database change: 1. **Find the existing boundary.** Locate the shared `Kysely<DB>` bootstrap, DB type source/code generator, migration runner, and the nearest query with the same purpose. Do not create a pool or a Kysely instance per request. 2. **Confirm the runtime contract.** Kysely's TypeScript types do not validate input or convert values returned by the driver. Ensure `DB` types match the actual `pg` parser output, especially for `int8`, `numeric`, `date`, timestamps, JSON, and arrays. 3. **Validate external input before query construction.** HTTP, job, event, and tenant inputs need application-level validation and authorization. Do not rely on type assertions, Kysely, or quoted SQL identifiers as security controls. 4. **Use the narrowest query API that expresses the intent.** Select explicit columns; use `whereRef` for column-to-column comparisons; use the deliberate `execute*` result method; parameterize values. 5. **Inspect and validate.** Run the project's typecheck and focused tests. For non-obvious queries, inspect `query.compile()` and review SQL and parameter shape. Run migrations in the project's migration environment, never opportunistically in application code. Compilation proves only the Kysely/TypeScript relationships it can see. It does **not** prove runtime value types, authorization, database constraints, index usage, query plans, or business invariants. ## Database Type Contract ### Model the values your driver returns `Kysely<DB>` is a compile-time contract for query construction. It must describe JavaScript values **after** driver type parsers run. ```typescript import type { ColumnType, Generated } from "kysely"; type Timestamp = ColumnType<Date, Date | string, Date | string>; interface UserTable { id: Generated<number>; email: string; deleted_at: Timestamp | null; // If pg is configured to return DATE strings, model it as a string. birth_date: string | null; } interface DB { user: UserTable; } ``` - Use `T | null` for nullable database columns, not an optional property. - Use `Generated<T>` for database-generated values. - Use `ColumnType<Select, Insert, Update>` when a column has different read/write representations. - Use `Selectable<T>`, `Insertable<T>`, and `Updateable<T>` for table-shaped application inputs only when the project needs them; do not expose raw table interfaces as API contracts by default. - Generated DB types must be regenerated or checked after schema changes. `kysely-codegen`, Prisma generators, and similar tools are integrations, not Kysely core APIs. See [runtime-types-and-bootstrap.ts](references/runtime-types-and-bootstrap.ts) for a `pg` parser, singleton, and graceful-shutdown pattern. ### Runtime validation boundary Validate untrusted input before `.where`, `.values`, dynamic sorting, `DynamicModule`, or `withSchema`. Database constraints remain the final integrity layer; they do not replace clear boundary errors. Never interpolate user input into `sql` fragments. Dynamic table, column, schema, and order-by choices need a server-owned allowlist and authorization check. ## Query Construction Rules ### Results and execution Queries are lazy. Always finish them with an execution method: ```typescript const users = await db.selectFrom("user").select(["id", "email"]).execute(); // Row[] const user = await db .selectFrom("user") .select(["id", "email"]) .where("id", "=", userId) .executeTakeFirst(); // Row | undefined; this does not add LIMIT automatically. const requiredUser = await db .selectFrom("user") .selectAll() .where("id", "=", userId) .executeTakeFirstOrThrow(); // Throws deliberately when no row exists. ``` Use `.returning(...)` when PostgreSQL must return inserted, updated, or deleted rows. Without it, mutation results contain operation metadata, not the changed rows. ### References, values, and raw SQL ```typescript // Value comparison: the value is parameterized. .where("status", "=", "active") // Column-to-column comparison: use whereRef. .whereRef("order.user_id", "=", "user.id") // A typed raw expression participates in result inference. .select(sql<number>`count(*)`.as("count")) // A SQL fragment used only as DDL/default syntax need not invent a result type. .defaultTo(sql`now()`) ``` Prefer Kysely builders when they express the query clearly. Use `sql<T>` deliberately for PostgreSQL-specific expressions, extensions, locking clauses, or a capability without a builder API: - Provide `T` when the expression's type affects query inference or a returned value. - Parameterize values with the `sql` template or `sql.val`; never concatenate/interpolate raw user strings. - Use `eb.ref`/`whereRef` for column references inside helpers so Kysely can type-check and transform them. - Alias raw expressions selected into a result. `sql.val` and `sql.lit` replace removed `sql.value` and `sql.literal`. `sql.lit` is only appropriate for safe immediate literals; it is not a user-input mechanism. ### Nullability and conditional queries Use SQL null operators explicitly: ```typescript .where("deleted_at", "is", null) .where("owner_id", "is not", null) ``` `SafeNullComparisonPlugin` can convert equality comparisons against `null` to `IS NULL` / `IS NOT NULL`, but explicit SQL semantics remain clearer in most application code. SQL predicates do not automatically narrow every result type. Use `$notNull()` or `$narrowType()` only when the query or application invariant truly proves the value: ```typescript .select((eb) => [ eb.ref("last_name").$notNull().as("lastName"), ]) ``` This is a TypeScript assertion, not a runtime validation step. See [select-where.ts](references/select-where.ts) and [expressions.ts](references/expressions.ts). ### Dynamic query construction Query builders are immutable. Reassign the builder after a conditional modification: ```typescript let query = db.selectFrom("user").select(["id", "email"]); if (activeOnly) { query = query.where("is_active", "=", true); } const users = await query.execute(); ``` Use `$if` for concise conditional query changes. Columns added through `$if` are optional in the result because their presence is not guaranteed at compile time. ## PostgreSQL Runtime Behavior ### JSON, JSONB, and arrays Kysely passes values to the configured dialect/driver and receives that driver's runtime output. `pg` normally parses JSON/JSONB and PostgreSQL arrays, but this is a driver/parser contract — not a Kysely guarantee. 1. Check what the installed driver returns. 2. Make the `DB` type match that value. 3. If JSON results arrive as strings, use `ParseJSONResultsPlugin` for the affected query or parse at an explicit boundary. 4. For raw JSONB expressions, serialize/type them intentionally instead of assuming a JavaScript object is valid for every SQL path. See [json-arrays.ts](references/json-arrays.ts) and [relations.ts](references/relations.ts). ### PostgreSQL-only features Mark these as PostgreSQL-specific in application code and do not reuse them under another dialect without a compatibility check: - JSONB and array operators, JSONPath, `jsonArrayFrom`, `jsonObjectFrom`. - `distinctOn`, ordered aggregates, and DML inside CTEs. - `withSchema` for schema-qualified queries. Locking clauses such as `FOR UPDATE` and `SKIP LOCKED` are supported by multiple SQL dialects, but their exact capabilities are dialect- and database-version-specific. Check the target dialect before treating a locking query as portable. For tenant schemas, resolve an external tenant ID through a server-owned allowlist and authorize it before calling `withSchema`. Schema qualification is not authorization. See [schemas.ts](references/schemas.ts). ## Transactions Use a callback transaction by default. It automatically commits on success and rolls back when the callback throws: ```typescript await db.transaction().execute(async (trx) => { const user = await trx .insertInto("user") .values({ email: "user@example.com" }) .returning("id") .executeTakeFirstOrThrow(); await trx .insertInto("order") .values({ user_id: user.id, status: "pending" }) .execute(); }); ``` Pass `trx` to every helper called inside the transaction. Do not accidentally use the global `db` for one of the writes. ### Controlled transactions and savepoints (0.29.5) Use a controlled transaction only when the lifetime must outlive one callback or when you need savepoints. Its builders require `.execute()`: ```typescript const trx = await db.startTransaction().execute(); try { await trx.insertInto("user").values({ email: "user@example.com" }).execute(); await trx.commit().execute(); } catch (error) { await trx.rollback().execute(); throw error; } ``` Do **not** use `await using` as an automatic rollback pattern for controlled transactions in `0.29.5`. Savepoints exist on `ControlledTransaction`, are named, and return a new transaction handle: ```typescript const trx = await db.startTransaction().execute(); const afterSavepoint = await trx.savepoint("before_risky_work").execute(); try { await afterSavepoint.insertInto("order").values(order).execute(); const released = await afterSavepoint.releaseSavepoint("before_risky_work").execute(); await released.commit().execute(); } catch (error) { const rolledBackToSavepoint = await afterSavepoint .rollbackToSavepoint("before_risky_work") .execute(); await rolledBackToSavepoint.rollback().execute(); throw error; } ``` For read-only transactions, use the exact `0.29.5` API: ```typescript await db .transaction() .setAccessMode("read only") .execute((trx) => trx.selectFrom("user").selectAll().execute()); ``` ### Cancellation Abortable query execution accepts an `AbortSignal`: ```typescript const users = await db .selectFrom("user") .selectAll() .execute({ signal: AbortSignal.timeout(3_000), inflightQueryAbortStrategy: "cancel query", }); ``` - Default strategy `"ignore query"` stops waiting but lets the SQL continue. - `"cancel query"` and `"kill session"` have different database-side consequences; use the latter only when killing the connection is acceptable. - `executeTakeFirstOrThrow` does not accept `AbortableQueryOptions` in `0.29.5`; `stream` accepts `signal` but not `inflightQueryAbortStrategy`. - `PostgresDialect` can receive an optional `controlClient`; it is not a substitute for timeout, cancellation, or connection-pool policy. ## Migrations Migration code is frozen in time. Use `Kysely<any>` in migration files so past migrations do not depend on the application's current DB type. ```typescript import { sql } from "kysely"; import type { Kysely } from "kysely"; export async function up(db: Kysely<any>): Promise<void> { await db.schema .createTable("user") .addColumn("id", "bigint", (col) => col.primaryKey().generatedAlwaysAsIdentity()) .addColumn("email", "text", (col) => col.notNull().unique()) .addColumn("created_at", "timestamptz", (col) => col.notNull().defaultTo(sql`now()`)) .execute(); } export async function down(db: Kysely<any>): Promise<void> { await db.schema.dropTable("user").execute(); } ``` - Import `Migrator`, `FileMigrationProvider`, and migration APIs from `kysely/migration`. - Give migrations sortable, forward-only names (for example, ISO-8601-prefixed). Do not insert a newly authored migration before one that may already be applied unless the project explicitly permits unordered migrations. - Handle both `error` and `results` from migration execution, and call `db.destroy()` in standalone migration scripts. - Kysely serializes concurrent migration attempts with a database-level lock, but deployments must still surface an error and stop on a failed migration. - `kysely-ctl` is an optional external CLI integration. Prefer the project's existing migration runner and configuration rather than assuming a universal command. - Indexes, lock-sensitive DDL, and zero-downtime rollout strategy depend on the workload and deployment plan. Review query plans and PostgreSQL operational constraints; do not apply a universal FK-index rule. ## Observability and Type-checker Performance Configure a custom Kysely logger in production. Record query duration, SQL, and errors, but mask bound parameter values because they can contain PII or secrets. Never interpolate secrets into raw SQL literals. ```typescript const compiled = db .selectFrom("user") .select(["id", "email"]) .where("is_active", "=", true) .compile(); console.debug({ sql: compiled.sql, parameterCount: compiled.parameters.length }); ``` For `Type instantiation is excessively deep` errors, apply `$assertType<ExactResult>()` at intermediate query/CTE boundaries only when the asserted type is structurally identical to the actual result. `Simplify<T>` changes IDE presentation but does not reduce the underlying type complexity. See [operations.ts](references/operations.ts) and [expressions.ts](references/expressions.ts). ## Kysely 0.29 Highlights - `$pickTables`, `$omitTables`, and `$extendTables` replace deprecated `withTables` for compile-time table scope. - `ReadonlyKysely<DB>` from `kysely/readonly` blocks mutations at compile time. It is not database authorization. - `eb.case()` supports `whenRef`, `thenRef`, and `elseRef`. - `$narrowType` supports nested object keys and discriminated unions. - `SafeNullComparisonPlugin`, query cancellation, and configurable `ParseJSONResultsPlugin` behavior are available. - `QueryResult.numUpdatedOrDeletedRows`, `sql.value`, and `sql.literal` are removed; use their replacements described above. ## Reference Map Read only the reference file relevant to the current task: - [runtime-types-and-bootstrap.ts](references/runtime-types-and-bootstrap.ts) — pg parsers, DB runtime types, shared Kysely lifecycle. - [select-where.ts](references/select-where.ts) — selects, filters, result semantics, null narrowing. - [joins.ts](references/joins.ts) — joins, aliases, lateral joins. - [aggregations.ts](references/aggregations.ts) — aggregate and ordered-set aggregate patterns. - [orderby-pagination.ts](references/orderby-pagination.ts) — ordering, pagination, `distinctOn`. - [ctes.ts](references/ctes.ts) — regular, recursive, and PostgreSQL DML CTEs. - [json-arrays.ts](references/json-arrays.ts) — JSONB, JSON parsing fallback, and arrays. - [relations.ts](references/relations.ts) — nested JSON relations. - [mutations.ts](references/mutations.ts) — inserts, updates, deletes, upserts, and `returning`. - [expressions.ts](references/expressions.ts) — expression builder, CASE, `$if`, `$narrowType`, `$assertType`. - [schemas.ts](references/schemas.ts) — `withSchema` and tenant schema safety.