Last Updated: 3/20/2026

Extending Kysely

Kysely doesn’t provide built-in methods for every SQL feature. Instead, it’s designed to be extended with custom expressions and helpers that fit your specific use case. The core extension points are the Expression<T> and AliasedExpression<T, A> interfaces, along with the sql template tag.

The Expression Interface

The Expression<T> interface (defined in src/expression/expression.ts) represents any SQL expression with a type T. Most Kysely methods accept expressions, and most Kysely classes implement this interface:


interface Expression<T> {
  get expressionType(): T | undefined
  toOperationNode(): OperationNode
}

The expressionType getter exists solely for TypeScript’s type system. It allows TypeScript to verify that Expression<string> is not assignable to Expression<number>. Always return undefined from this getter.

The toOperationNode() method returns instructions for compiling the expression to SQL. Kysely’s query compiler traverses the operation node tree to generate the final SQL string.

Creating Custom Expressions

Here’s a custom expression for PostgreSQL JSON values:


import { Expression, 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 {
    return sql`CAST(${JSON.stringify(this.#value)} AS JSONB)`.toOperationNode()
  }
}

Use this expression anywhere a value is expected:


interface Database {
  person: {
    id: number
    address: {
      street: string
      city: string
      postalCode: string
    }
  }
}
 
await db
  .insertInto('person')
  .values({
    id: 1,
    address: new JsonValue({
      street: 'Kysely Avenue 42',
      city: 'Type City',
      postalCode: '12345'
    })
  })
  .execute()
 
await db
  .selectFrom('person')
  .selectAll()
  .where('address', '@>', new JsonValue({ city: 'Type City' }))
  .execute()

The JsonValue class is type-safe: TypeScript validates that the value matches the column’s type.

Using the sql Template Tag

Most custom expressions can be built with the sql template tag instead of implementing Expression<T> directly. The tag returns a RawBuilder<T> that implements Expression<T>:


import { RawBuilder, sql } from 'kysely'
 
function json<T>(value: T): RawBuilder<T> {
  return sql`CAST(${JSON.stringify(value)} AS JSONB)`
}

This three-line helper provides the same functionality as the JsonValue class:


await db
  .insertInto('person')
  .values({
    id: 1,
    address: json({
      street: 'Kysely Avenue 42',
      city: 'Type City',
      postalCode: '12345'
    })
  })
  .execute()

The sql tag (defined in src/raw-builder/sql.ts) handles parameter binding automatically. Template substitutions are treated as values by default and passed as parameters to the database.

The AliasedExpression Interface

The AliasedExpression<T, A> interface (defined in src/expression/expression.ts) represents an expression with a name. It’s used in select clauses and other places where you need to reference the expression by name:


interface AliasedExpression<T, A extends string> {
  get expression(): Expression<T>
  get alias(): A | Expression<unknown>
  toOperationNode(): AliasNode
}

To make an expression aliasable, add an .as() method that returns an AliasedExpression:


import {
  Expression,
  AliasedExpression,
  AliasNode,
  IdentifierNode,
  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 {
    return sql`CAST(${JSON.stringify(this.#value)} AS JSONB)`.toOperationNode()
  }
 
  as<A extends string>(alias: A): AliasedJsonValue<T, A> {
    return new AliasedJsonValue(this, alias)
  }
}
 
class AliasedJsonValue<T, A extends string> implements AliasedExpression<T, A> {
  #expression: Expression<T>
  #alias: A
 
  constructor(expression: Expression<T>, alias: A) {
    this.#expression = expression
    this.#alias = alias
  }
 
  get expression(): Expression<T> {
    return this.#expression
  }
 
  get alias(): A {
    return this.#alias
  }
 
  toOperationNode(): AliasNode {
    return AliasNode.create(
      this.#expression.toOperationNode(),
      IdentifierNode.create(this.#alias)
    )
  }
}

Now you can use JsonValue in select clauses:


const result = await db
  .selectFrom('person')
  .select([
    'id',
    new JsonValue({ status: 'active' }).as('metadata')
  ])
  .executeTakeFirst()
 
console.log(result.metadata.status) // Type-safe access

Aliasing with RawBuilder

RawBuilder already has an .as() method, so you can skip the custom AliasedExpression class:


function json<T>(value: T): RawBuilder<T> {
  return sql`CAST(${JSON.stringify(value)} AS JSONB)`
}
 
const result = await db
  .selectFrom('person')
  .select([
    'id',
    json({ status: 'active' }).as('metadata')
  ])
  .executeTakeFirst()
 
console.log(result.metadata.status)

The RawBuilder class (defined in src/raw-builder/raw-builder.ts) implements both Expression<T> and has an .as() method that returns an AliasedRawBuilder<T, A>.

A Complex Example: VALUES Clause

Here’s a helper for PostgreSQL’s VALUES clause in a FROM context:


import { AliasedRawBuilder, sql } from 'kysely'
 
function values<R extends Record<string, unknown>, A extends string>(
  records: R[],
  alias: A
): AliasedRawBuilder<R, A> {
  // Get column names from the first record
  const keys = Object.keys(records[0])
 
  // Transform records into (val1, val2, val3), (val4, val5, val6)
  const valuesList = sql.join(
    records.map(record => 
      sql`(${sql.join(keys.map(key => record[key]))})`
    )
  )
 
  // Create alias with column names: v(id, name, age)
  const wrappedAlias = sql.ref(alias)
  const wrappedColumns = sql.join(keys.map(sql.ref))
  const aliasSql = sql`${wrappedAlias}(${wrappedColumns})`
 
  // Return AliasedRawBuilder with explicit alias type
  return sql<R>`(values ${valuesList})`.as<A>(aliasSql)
}

Use this helper to insert data from an array:


const records = [
  { id: 1, name: 'Alice', age: 30 },
  { id: 2, name: 'Bob', age: 25 }
]
 
await db
  .insertInto('person')
  .columns(['id', 'name', 'age'])
  .expression(
    db
      .selectFrom(values(records, 'v'))
      .select(['v.id', 'v.name', 'v.age'])
  )
  .execute()

The values helper demonstrates several techniques:

sql.join() combines multiple SQL fragments
sql.ref() creates a column reference
.as<A>() with a raw SQL alias provides the column names
TypeScript infers the record type from the input array

Expression Wrapper Pattern

For simple helpers, wrap an existing expression instead of implementing Expression<T>:


import { ExpressionWrapper } from 'kysely'
 
function upper(column: string): ExpressionWrapper<DB, TB, string> {
  return new ExpressionWrapper(sql<string>`upper(${sql.ref(column)})`)
}
 
const result = await db
  .selectFrom('person')
  .select(upper('first_name').as('upper_name'))
  .execute()

The ExpressionWrapper class (defined in src/expression/expression-wrapper.ts) implements Expression<T> and delegates to the wrapped expression’s toOperationNode() method.

Functional Helpers vs Classes

You can create helpers as functions or classes. Functions are simpler and more idiomatic in TypeScript:


// Function style (recommended)
function json<T>(value: T): RawBuilder<T> {
  return sql`CAST(${JSON.stringify(value)} AS JSONB)`
}
 
// Class style
class JsonValue<T> implements Expression<T> {
  // ... implementation
}

Use classes when:

You need to store state or configuration
You want to provide multiple methods
You’re building a complex abstraction

Use functions when:

The helper is a simple transformation
You don’t need state
You want a lightweight API

Module Augmentation (Not Recommended)

You can extend Kysely’s builder classes using TypeScript module augmentation, but this approach is fragile and not officially supported:


declare module 'kysely/dist/cjs/schema/create-table-builder' {
  interface CreateTableBuilder<TB extends string, C extends string = never> {
    addIdColumn<CN extends string = 'id'>(
      col?: CN
    ): CreateTableBuilder<TB, C | CN>
  }
}
 
CreateTableBuilder.prototype.addIdColumn = function(
  this: CreateTableBuilder<any, any>,
  col?: string
) {
  return this.addColumn(col || 'id', 'uuid', (col) =>
    col.primaryKey().defaultTo(sql`gen_random_uuid()`)
  )
}

This adds an addIdColumn method to CreateTableBuilder. However:

TypeScript’s type system has limitations with inheritance and return types
Module augmentation can break with Kysely updates
It’s harder to test and maintain than standalone functions

Prefer standalone helper functions over module augmentation.

Type Utilities

Kysely provides type utilities for extracting types from expressions:

InferResult<T>: Extract the result type from a query builder or compiled query
Selectable<T>: Convert a table type to a selectable row type
Insertable<T>: Convert a table type to an insertable row type
Updateable<T>: Convert a table type to an updateable row type

These utilities (defined in src/util/type-utils.ts and related files) help you build type-safe helpers that work with Kysely’s type system.

What’s Next

Dynamic Module: Build queries with runtime-determined table and column names.
Raw SQL: Use the sql template tag for complex SQL expressions.
Plugins: Create Kysely plugins to transform queries or results globally.