Last Updated: 3/20/2026

Expressions & Expression Builder

Expressions are the building blocks of type-safe query composition in Kysely. The Expression<T> interface (defined in src/expression/expression.ts) represents any SQL fragment that produces a value of type T. The ExpressionBuilder<DB, TB> (defined in src/expression/expression-builder.ts) provides methods for creating and combining expressions in a type-safe way.

What Is an Expression?

An Expression<T> is anything that can be converted to an operation node and has a type. Examples include:

Column references: 'person.first_name'
Raw SQL: sql<string>concat(first_name, ’ ’, last_name)“
Subqueries: db.selectFrom('pet').select('name')
Function calls: eb.fn.count('id')
Binary operations: eb('age', '>=', 18)

Kysely uses expressions everywhere: in SELECT lists, WHERE clauses, HAVING clauses, ON conditions, ORDER BY, and more. The type parameter T tells TypeScript what type the expression produces at runtime.

The Expression Builder

The ExpressionBuilder<DB, TB> is a factory for creating expressions. It’s generic over your Database type and the tables currently in scope (TB). You get an expression builder in callback functions like .where(), .select(), and .having():


const result = await db
  .selectFrom('person')
  .where((eb) => eb('age', '>=', 18))
  .selectAll()
  .execute()

Here, eb is an ExpressionBuilder<Database, 'person'>. It knows which columns are available and validates references at compile time.

Creating Expressions with `eb()`

The expression builder itself is a function. Calling eb(lhs, op, rhs) creates a binary expression:


const adults = await db
  .selectFrom('person')
  .where((eb) => eb('age', '>=', 18))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


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

By default, the third argument is interpreted as a value. To pass a column reference, use eb.ref():


const sameNames = await db
  .selectFrom('person')
  .where((eb) => eb('first_name', '=', eb.ref('last_name')))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person" where "first_name" = "last_name"

You can nest expressions. Both the first and third arguments can be any expression:


const result = await db
  .selectFrom('person')
  .where((eb) => eb(
    eb.fn<string>('lower', ['first_name']),
    'in',
    eb.selectFrom('pet').select('name').where('species', '=', 'cat')
  ))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person"
where lower("first_name") in (
  select "name" from "pet" where "species" = $1
)

Logical Operators: `and()`, `or()`, `not()`

Combine multiple expressions using eb.and() and eb.or():


const result = await db
  .selectFrom('person')
  .where((eb) => eb.and([
    eb('age', '>=', 18),
    eb('age', '<', 65),
    eb('first_name', '=', 'Jennifer')
  ]))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person"
where ("age" >= $1 and "age" < $2 and "first_name" = $3)

For simple equality checks, you can use object notation:


const result = await db
  .selectFrom('person')
  .where((eb) => eb.and({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  }))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person"
where ("first_name" = $1 and "last_name" = $2)

The eb.or() method works the same way but uses OR instead of AND. An empty array passed to eb.and() produces true; an empty array passed to eb.or() produces false.

Use eb.not() to negate an expression:


const result = await db
  .selectFrom('person')
  .where((eb) => eb.not(eb('age', '<', 18)))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person" where not ("age" < $1)

Exists and Subqueries

Use eb.exists() to check if a subquery returns any rows:


const result = await db
  .selectFrom('person')
  .where((eb) => eb.exists(
    eb.selectFrom('pet')
      .whereRef('pet.owner_id', '=', 'person.id')
      .select('pet.id')
  ))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


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

References: `ref()`, `val()`, `lit()`

eb.ref(column) — Creates a column reference. Use this when you need to reference a column instead of passing a value.
eb.val(value) — Creates a value expression. Use this when you need to pass a value where a reference is expected by default.
eb.lit(literal) — Creates a literal value that gets merged into the SQL (not a parameter). Only accepts number, boolean, or null to prevent SQL injection.


const result = await db
  .selectFrom('person')
  .select((eb) => [
    'first_name',
    eb.lit(1).as('one'),
    eb.val('constant').as('constant_value')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", 1 as "one", $1 as "constant_value" from "person"

Function Module: `eb.fn`

The eb.fn property provides type-safe access to SQL functions. It’s scoped to the tables in the current query context:


const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select((eb) => [
    'person.id',
    eb.fn.count('pet.id').as('pet_count')
  ])
  .groupBy('person.id')
  .having((eb) => eb.fn.count('pet.id'), '>', 10)
  .execute()

The generated SQL (PostgreSQL):


select "person"."id", count("pet"."id") as "pet_count"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."id"
having count("pet"."id") > $1

Case Expressions: `eb.case()`

Build CASE statements using eb.case():


const result = await db
  .selectFrom('person')
  .select((eb) => [
    'first_name',
    eb.case()
      .when('age', '<', 18).then('minor')
      .when('age', '>=', 65).then('senior')
      .else('adult')
      .end()
      .as('age_group')
  ])
  .execute()

The generated SQL (PostgreSQL):


select
  "first_name",
  case
    when "age" < $1 then $2
    when "age" >= $3 then $4
    else $5
  end as "age_group"
from "person"

You can also use the “simple” case syntax by passing a column reference:


const result = await db
  .selectFrom('person')
  .select((eb) => [
    'first_name',
    eb.case('gender')
      .when('male').then('Mr.')
      .when('female').then('Ms.')
      .else('Mx.')
      .end()
      .as('title')
  ])
  .execute()

The generated SQL (PostgreSQL):


select
  "first_name",
  case "gender"
    when $1 then $2
    when $3 then $4
    else $5
  end as "title"
from "person"

Subqueries: `eb.selectFrom()`

Create correlated subqueries using eb.selectFrom(). The returned query builder is typed to allow references to both the parent query’s tables and the subquery’s tables:


const result = await db
  .selectFrom('pet')
  .select((eb) => [
    'pet.name',
    eb.selectFrom('person')
      .whereRef('person.id', '=', 'pet.owner_id')
      .select('person.first_name')
      .as('owner_name')
  ])
  .execute()

The generated SQL (PostgreSQL):


select
  "pet"."name",
  (
    select "person"."first_name"
    from "person"
    where "person"."id" = "pet"."owner_id"
  ) as "owner_name"
from "pet"

Destructuring: `eb.eb`

The eb.eb property returns a copy of the expression builder, which is useful for destructuring:


const result = await db
  .selectFrom('person')
  .where(({ eb, exists, selectFrom }) =>
    eb('first_name', '=', 'Jennifer').and(exists(
      selectFrom('pet').whereRef('owner_id', '=', 'person.id').select('pet.id')
    ))
  )
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person"
where "first_name" = $1 and exists (
  select "pet"."id" from "pet" where "owner_id" = "person"."id"
)

Tuples: `refTuple()` and `tuple()`

Create tuple expressions for multi-column comparisons:


const result = await db
  .selectFrom('person')
  .where(({ eb, refTuple, tuple }) => eb(
    refTuple('first_name', 'last_name'),
    'in',
    [
      tuple('Jennifer', 'Aniston'),
      tuple('Sylvester', 'Stallone')
    ]
  ))
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


select * from "person"
where ("first_name", "last_name") in (($1, $2), ($3, $4))

What’s Next

Raw SQL — Use the sql template tag for raw SQL fragments when the query builder doesn’t cover your use case.
Reusable Helpers — Build reusable type-safe query helpers using Expression<T> and expressionBuilder().
CTEs — Use db.with() and db.withRecursive() to create common table expressions.

Ctes Window Functions