Skip to Content
🔧 AdvancedReusable Helpers

Last Updated: 3/20/2026

Reusable Helpers

Building reusable type-safe query helpers is one of Kysely’s strengths. By returning Expression<T> from helper functions, you can compose complex queries from small, testable pieces while maintaining full type safety.

The Expression Pattern

An Expression<T> (defined in src/expression/expression.ts) is anything that can be converted to an operation node and has a type. Helper functions that return Expression<T> can be used anywhere an expression is expected: in SELECT lists, WHERE clauses, HAVING clauses, ORDER BY, and more.

The key is to use expressionBuilder() (from src/expression/expression-builder.ts) to create expressions without assuming a specific query context:

import { expressionBuilder, type Expression } from 'kysely'
 
function fullName<DB>(): Expression<string> {
  const eb = expressionBuilder<DB, 'person'>()
  
  return eb.fn<string>('concat', [
    eb.ref('first_name'),
    eb.val(' '),
    eb.ref('last_name')
  ])
}

This helper can be used in any query that has a person table in scope:

const result = await db
  .selectFrom('person')
  .select([
    'id',
    fullName<Database>().as('full_name')
  ])
  .execute()

The generated SQL (PostgreSQL):

select "id", concat("first_name", $1, "last_name") as "full_name"
from "person"

Using eb.ref() and eb.val()

Use eb.ref() to reference columns and eb.val() to pass values. This ensures type safety and proper parameter binding:

import { expressionBuilder, type Expression } from 'kysely'
 
function isAdult<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb('age', '>=', 18)
}
 
const adults = await db
  .selectFrom('person')
  .where(isAdult<Database>())
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "age" >= $1

Parameterized Helpers

Helpers can accept parameters to make them more flexible:

import { expressionBuilder, type Expression } from 'kysely'
 
function ageGreaterThan<DB>(minAge: number): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb('age', '>', minAge)
}
 
const result = await db
  .selectFrom('person')
  .where(ageGreaterThan<Database>(21))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "age" > $1

Combining Helpers

Helpers can be composed using eb.and(), eb.or(), and other expression builder methods:

import { expressionBuilder, type Expression } from 'kysely'
 
function isAdult<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb('age', '>=', 18)
}
 
function isSenior<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb('age', '>=', 65)
}
 
function isWorkingAge<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb.and([
    isAdult<DB>(),
    eb.not(isSenior<DB>())
  ])
}
 
const result = await db
  .selectFrom('person')
  .where(isWorkingAge<Database>())
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where ("age" >= $1 and not ("age" >= $2))

Helpers with Subqueries

Helpers can include subqueries using eb.selectFrom():

import { expressionBuilder, type Expression } from 'kysely'
 
function hasPets<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  
  return eb.exists(
    eb.selectFrom('pet')
      .whereRef('pet.owner_id', '=', 'person.id')
      .select('pet.id')
  )
}
 
const result = await db
  .selectFrom('person')
  .where(hasPets<Database>())
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person"
where exists (
  select "pet"."id" from "pet" where "pet"."owner_id" = "person"."id"
)

Helpers for Aggregations

Create helpers for common aggregations:

import { expressionBuilder, type Expression } from 'kysely'
 
function petCount<DB>(): Expression<number> {
  const eb = expressionBuilder<DB, 'person'>()
  
  return eb.selectFrom('pet')
    .whereRef('pet.owner_id', '=', 'person.id')
    .select((eb) => eb.fn.count('pet.id').as('count'))
}
 
const result = await db
  .selectFrom('person')
  .select([
    'id',
    'first_name',
    petCount<Database>().as('pet_count')
  ])
  .execute()

The generated SQL (PostgreSQL):

select
  "id",
  "first_name",
  (
    select count("pet"."id") as "count"
    from "pet"
    where "pet"."owner_id" = "person"."id"
  ) as "pet_count"
from "person"

Avoiding Context Assumptions

Don’t assume a specific query context in your helpers. Use generic type parameters to make them work with any database schema:

// ❌ Bad: Assumes a specific Database type
function isAdult(): Expression<boolean> {
  const eb = expressionBuilder<MyDatabase, 'person'>()
  return eb('age', '>=', 18)
}
 
// ✅ Good: Generic over the database type
function isAdult<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb('age', '>=', 18)
}

This makes the helper reusable across different database schemas and testable in isolation.

Helpers with Raw SQL

Combine helpers with raw SQL when needed:

import { sql, expressionBuilder, type Expression } from 'kysely'
 
function searchName<DB>(query: string): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  
  return sql<boolean>`
    to_tsvector('english', ${eb.ref('first_name')} || ' ' || ${eb.ref('last_name')})
    @@ plainto_tsquery('english', ${query})
  `
}
 
const result = await db
  .selectFrom('person')
  .where(searchName<Database>('John'))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person"
where to_tsvector('english', "first_name" || ' ' || "last_name")
  @@ plainto_tsquery('english', $1)

Testing Helpers

Because helpers return Expression<T> and don’t execute queries, they’re easy to test:

import { expressionBuilder } from 'kysely'
 
describe('isAdult', () => {
  it('creates the correct expression', () => {
    const expr = isAdult<Database>()
    const eb = expressionBuilder<Database, 'person'>()
    
    // You can compile the expression to SQL for testing
    const compiled = eb(expr).compile()
    expect(compiled.sql).toContain('"age" >= $1')
    expect(compiled.parameters).toEqual([18])
  })
})

Organizing Helpers

Group related helpers in modules:

// helpers/person.ts
import { expressionBuilder, type Expression } from 'kysely'
 
export function isAdult<DB>(): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb('age', '>=', 18)
}
 
export function fullName<DB>(): Expression<string> {
  const eb = expressionBuilder<DB, 'person'>()
  return eb.fn<string>('concat', [
    eb.ref('first_name'),
    eb.val(' '),
    eb.ref('last_name')
  ])
}
 
// Use in queries
import * as personHelpers from './helpers/person'
 
const result = await db
  .selectFrom('person')
  .where(personHelpers.isAdult<Database>())
  .select([
    'id',
    personHelpers.fullName<Database>().as('full_name')
  ])
  .execute()

What’s Next

  • Expressions — Learn more about the expression builder API.
  • Raw SQL — Combine helpers with raw SQL for maximum flexibility.
  • Plugins — Transform helper expressions using plugins.
Raw SqlRelations