Skip to main content
An Expression<T> is the basic type-safe query building block in Kysely. Pretty much all methods accept expressions as inputs.

​
What is an Expression?

Expression<T> represents an arbitrary SQL expression, like:
  • A binary expression (e.g., a + b)
  • A function call (e.g., concat(arg1, ' ', arg2, ...))
  • Any combination of these, no matter how complex
T is the output type of the expression.
Most internal classes like SelectQueryBuilder and RawBuilder (the return value of the sql tag) are expressions themselves.

​
Expression Builder

Expressions are usually built using an instance of ExpressionBuilder<DB, TB>:
  • DB is the same database type you give to Kysely
  • TB is the union of all table names visible in the context
For example, ExpressionBuilder<DB, 'person' | 'pet'> means you can reference person and pet columns.

​
Getting an expression builder

You can get an instance using a callback: 
const person = await db
  .selectFrom('person')
  // `eb` is ExpressionBuilder<DB, 'person'>
  .select((eb) => [
    // Call functions
    eb.fn('upper', ['first_name']).as('upper_first_name'),
    
    // Select a subquery
    eb.selectFrom('pet')
      .select('name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .limit(1)
      .as('pet_name'),
    
    // Boolean expression
    eb('first_name', '=', 'Jennifer').as('is_jennifer'),
    
    // Static value
    eb.val('Some value').as('string_value'),
    
    // Literal value
    eb.lit(42).as('literal_value'),
  ])
  .where(({ and, or, eb, not, exists, selectFrom }) => or([
    and([
      eb('first_name', '=', firstName),
      eb('last_name', '=', lastName)
    ]),
    not(exists(
      selectFrom('pet')
        .select('pet.id')
        .whereRef('pet.owner_id', '=', 'person.id')
        .where('pet.species', 'in', ['dog', 'cat'])
    ))
  ]))
  .executeTakeFirstOrThrow()

console.log(person.upper_first_name)
console.log(person.pet_name)
console.log(person.is_jennifer)
This generates: 
select
  upper("first_name") as "upper_first_name",
  
  (
    select "name"
    from "pet"
    where "pet"."owner_id" = "person"."id"
    limit 1
  ) as "pet_name",
  
  "first_name" = $1 as "is_jennifer",
  $2 as "string_value",
  42 as "literal_value"
from
  "person"
where (
  (
    "first_name" = $3
    and "last_name" = $4
  )
  or not exists (
    select "pet.id"
    from "pet"
    where "pet"."owner_id" = "person"."id"
    and "pet"."species" in ($5, $6)
  )
)

​
Why use callbacks?

You might wonder: “Why use a callback to get the expression builder? Why not a global function?”
Callbacks allow Kysely to infer the context correctly. The expression builder’s methods only auto-complete and accept column and table names that are available in the context. This provides more type-safety!

​
Using expressionBuilder globally

There’s also a global expressionBuilder function: 
import { expressionBuilder } from 'kysely'

// No tables in context - use this in helper functions
const eb1 = expressionBuilder<DB>()

// Can reference 'person' columns
const eb2 = expressionBuilder<DB, 'person'>()

// Can reference 'person' and 'pet' columns
const eb3 = expressionBuilder<DB, 'person' | 'pet'>()

// Infer context from a query builder
let qb = query
  .selectFrom('person')
  .innerJoin('movie as m', 'm.director_id', 'person.id')

// Type: ExpressionBuilder<DB & { m: Movie }, 'person' | 'm'>
const eb = expressionBuilder(qb)

qb = qb.where(eb.not(eb.exists(
  eb.selectFrom('pet')
    .select('pet.id')
    .whereRef('pet.name', '=', 'm.name')
)))

​
Composability

All expressions are composable:

Pass expressions as arguments

You can pass expressions as arguments to other expressions

Use anywhere

All query builder methods accept expressions and callbacks

Type-safe

All methods offer auto-completions and type checking

No runtime cost

Expressions compile to efficient SQL

​
Creating reusable helpers

The expression builder is perfect for creating reusable helpers. Here’s a basic example: 
function hasDogNamed(name: string): Expression<boolean> {
  const eb = expressionBuilder<DB, 'person'>()
  
  return eb.exists(
    eb.selectFrom('pet')
      .select('pet.id')
      .whereRef('pet.owner_id', '=', 'person.id')
      .where('pet.species', '=', 'dog')
      .where('pet.name', '=', name)
  )
}

const doggoPersons = await db
  .selectFrom('person')
  .selectAll('person')
  .where(hasDogNamed('Doggo'))
  .execute()
The above helper is not very type-safe. The following would compile but fail at runtime:
const bigFatFailure = await db
  .selectFrom('movie') // person table is not in context!
  .selectAll('movie')
  .where(hasDogNamed('Doggo')) // but we're referring to person.id
  .execute()

​
Type-safe helpers

It’s better to not make assumptions about the calling context: 
function hasDogNamed(name: Expression<string>, ownerId: Expression<number>) {
  // No tables in context - no assumptions about caller
  const eb = expressionBuilder<DB>()
  
  return eb.exists(
    eb.selectFrom('pet')
      .select('pet.id')
      .where('pet.owner_id', '=', ownerId)
      .where('pet.species', '=', 'dog')
      .where('pet.name', '=', name)
  )
}

const doggoPersons = await db
  .selectFrom('person')
  .selectAll('person')
  .where((eb) => hasDogNamed(eb.val('Doggo'), eb.ref('person.id')))
  .execute()
Learn more in the reusable helpers recipe.

​
Conditional expressions

This section covers conditional where expressions. For conditional selections in select clauses, see the conditional selects recipe.

​
Basic conditional filters

For optional filters combined with and, use additive where calls: 
let query = db
  .selectFrom('person')
  .selectAll('person')

if (firstName) {
  // Query builder is immutable - replace with new instance
  query = query.where('first_name', '=', firstName)
}

if (lastName) {
  query = query.where('last_name', '=', lastName)
}

const persons = await query.execute()

​
Using expression builder

The same query using the expression builder: 
const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .where((eb) => {
    const filters: Expression<SqlBool>[] = []
    
    if (firstName) {
      filters.push(eb('first_name', '=', firstName))
    }
    
    if (lastName) {
      filters.push(eb('last_name', '=', lastName))
    }
    
    return eb.and(filters)
  })
  .execute()
Using this pattern, you can build conditional expressions of any complexity.

​
Common expression builder methods

MethodDescriptionExample
eb(left, op, right)Binary expressioneb('age', '>', 18)
eb.fn(name, args)Function calleb.fn('upper', ['name'])
eb.val(value)Value expressioneb.val('hello')
eb.lit(value)Literal valueeb.lit(42)
eb.ref(column)Column referenceeb.ref('person.id')
eb.and(exprs)AND expressioneb.and([expr1, expr2])
eb.or(exprs)OR expressioneb.or([expr1, expr2])
eb.not(expr)NOT expressioneb.not(expr)
eb.exists(subquery)EXISTS checkeb.exists(subquery)
eb.selectFrom(table)Start subqueryeb.selectFrom('pet')

​
Where to use expressions

Expressions work in all parts of a query:
1

select

Build complex selections with functions and subqueries
2

where / having

Create complex filter conditions
3

on

Use in join conditions
4

orderBy / groupBy

Sort and group by expressions
5

set / values

Use in updates and inserts

Build docs developers (and LLMs) love

Get started for freeTalk to us