Kysely Type-safe SQL Query Builder

Kysely (pronounced “Key-Seh-Lee”) is a type-safe and autocompletion-friendly TypeScript SQL query builder. It is inspired by Knex.js and is designed to ensure that you only refer to tables and columns that are visible to the specific part of the query you are writing.

Key features include:

• Type Safety: Result types only contain the selected columns with correct types and aliases.
• Autocompletion: Full autocompletion for tables, columns, aliases, and subqueries.
• Advanced Inference: Automatically infers column names, aliases, and types from selected subqueries, joined subqueries, with statements, and more.
• Environment Agnostic: While primarily developed for Node.js, it runs on Deno, Bun, Cloudflare Workers, and web browsers.

If you encounter situations that cannot be typed at compile time, Kysely provides escape hatches such as the sql template tag and DynamicModule.

Kysely is a query builder, not an ORM, so it does not have a built-in concept of relations. To nest related rows (e.g., fetching a person and their pets in a single query), you should use SQL JSON functions.

Kysely provides dialect-specific helpers to simplify this process by wrapping subqueries into JSON arrays or objects. This approach is highly efficient if you have proper indices on the foreign keys used in the subqueries.

Supported Dialects

• PostgreSQL: Use kysely/helpers/postgres
• MySQL: Use kysely/helpers/mysql (Requires MySQL 8.0.14+)
• SQLite: Use kysely/helpers/sqlite

import { jsonArrayFrom, jsonObjectFrom } from 'kysely/helpers/postgres'

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select((eb) => [
    // Nesting an array of related rows
    jsonArrayFrom(
      eb.selectFrom('pet')
        .select(['pet.id', 'pet.name'])
        .whereRef('pet.owner_id', '=', 'person.id')
        .orderBy('pet.name')
    ).as('pets'),

    // Nesting a single related object
    jsonObjectFrom(
      eb.selectFrom('person as mother')
        .select(['mother.id', 'mother.first_name'])
        .whereRef('mother.id', '=', 'person.mother_id')
    ).as('mother')
  ])
  .execute()

Understanding the Kysely execution flow helps developers understand how type-safe method calls are transformed into database results. The process follows these distinct stages:

1. Immutable Query Building: Calling methods on the QueryBuilder (like selectFrom, where) does not mutate the existing instance. Instead, each call returns a new QueryBuilder instance containing an updated, immutable QueryAST (Abstract Syntax Tree).
2. Initiating Execution: The process begins when the final .execute() method is called on the QueryBuilder.
3. Query Transformation: The QueryExecutor iterates through all registered plugins, calling transformQuery on each to allow modification of the QueryAST before compilation.
4. Query Compilation: The QueryExecutor delegates the QueryAST to a dialect-specific QueryCompiler, which produces a CompiledQuery (the final SQL string and its parameters).
5. Connection Handling: The QueryExecutor uses a Driver to acquire a DatabaseConnection. The Driver abstracts vendor-specific details (like pg or mysql2) to manage connections from a pool.
6. Database Query: The DatabaseConnection sends the CompiledQuery to the underlying DatabaseDriver. The driver returns raw results, which the DatabaseConnection standardizes into a QueryResult.
7. Result Transformation: The QueryExecutor passes the QueryResult through the plugin system again, calling transformResults on each plugin to allow for final modifications.
8. Returning to the App: The final, transformed results are returned to the application, resolving the promise from the initial .execute() call.

To enable Kysely's type-safety and autocompletion, you must define a Database interface. This interface acts as the single source of truth for your database structure, where keys are table names and values are the corresponding table schema interfaces.

Important Rules:

• Table Interfaces: Use table interfaces (e.g., PersonTable) only as values within the Database interface. Never use them directly as the return type of a query.
• Nullability: If a column is nullable in the database, use a union with null (e.g., string | null). Do not use TypeScript optional properties (?), as Kysely determines optionality automatically based on the types provided.
• Runtime Types: Kysely is a TypeScript-level tool. The actual runtime types are determined by your database driver (like pg or mysql2). You are responsible for ensuring your TypeScript definitions match the driver's behavior.

export interface Database {
  person: PersonTable
  pet: PetTable
}

export interface PersonTable {
  id: Generated<number>
  first_name: string
  last_name: string | null
}

If Kysely lacks a built-in type-safe method for a specific feature, you can implement the Expression<T> interface. This interface requires a type T (representing the data type) and a toOperationNode() method that returns instructions for SQL compilation.

For most use cases, you don't need to implement the interface manually; you can simply wrap the sql template tag in a function that returns a RawBuilder<T>, which implements Expression<T>.

import { Expression, Kysely, OperationNode, sql } from 'kysely'

class JsonValue<T> implements Expression<T> {
  #value: T

  constructor(value: T) {
    this.#value = value
  }

  get expressionType(): T | undefined {
    return undefined
  }

  toOperationNode(): OperationNode {
    const json = JSON.stringify(this.#value)
    return sql`CAST(${json} AS JSONB)`.toOperationNode()
  }
}

// Simplified version using RawBuilder:
function json<T>(value: T): RawBuilder<T> {
  return sql`CAST(${JSON.stringify(value)} AS JSONB)`
}

Expressions are typically constructed using an ExpressionBuilder<DB, TB>.

• DB: Your database schema type.
• TB: A union of table names currently visible in the query context.

To ensure maximum type safety and correct context inference (so that only available columns are auto-completed), you should obtain the expression builder via a callback provided by Kysely methods. This allows Kysely to automatically infer which tables and columns are accessible in that specific part of the query.

const person = await db
  .selectFrom('person')
  .select((eb) => [
    // Using eb.fn for SQL functions
    eb.fn('upper', ['first_name']).as('upper_first_name'),

    // Using eb to select a subquery
    eb.selectFrom('pet')
      .select('name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .limit(1)
      .as('pet_name'),

    // Using eb for boolean expressions
    eb('first_name', '=', 'Jennifer').as('is_jennifer'),

    // Using eb.val for static values
    eb.val('Some value').as('string_value'),

    // Using eb.lit for literal values
    eb.lit(42).as('literal_value'),
  ])
  .executeTakeFirstOrThrow();