ExpressionBuilder

A type-safe expression builder for creating SQL expressions in Kysely queries

ExpressionBuilder

ExpressionBuilder provides a type-safe way to build SQL expressions in Kysely. It’s commonly passed as a callback parameter (often named eb) in methods like where, select, set, and many others.

Type Parameters

DB · object · required

The database schema type

TB · keyof DB · required

The table references available in the current query context

Methods

Binary Expression (Call Signature)

Creates a binary expression for comparing values or performing arithmetic operations.

<
  RE extends ReferenceExpression<DB, TB>,
  OP extends BinaryOperatorExpression,
  VE extends OperandValueExpressionOrList<DB, TB, RE>,
>(
  lhs: RE,
  op: OP,
  rhs: VE,
): ExpressionWrapper<DB, TB, ...>

lhs · ReferenceExpression · required

Left-hand side of the expression (typically a column reference)

op · BinaryOperatorExpression · required

The operator (e.g., =, >, <, +, -, in, like)

rhs · OperandValueExpressionOrList · required

Right-hand side value or expression

Example:

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

eb

Returns a copy of the expression builder for destructuring.

get eb(): ExpressionBuilder<DB, TB>

Example:

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()

fn

Returns a FunctionModule for writing type-safe function calls.

get fn(): FunctionModule<DB, TB>

The difference between this and Kysely.fn is that this one is more type safe - you can only refer to columns visible to the part of the query you are building.

Example:

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()

selectFrom

Creates a subquery that can reference parent query tables.

selectFrom<TE extends TableExpressionOrList<DB, TB>>(
  from: TE,
): SelectFrom<DB, TB, TE>

from · TableExpressionOrList · required

Table expression or list of tables to select from

Example:

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()

case

Creates a case statement/operator.

case(): CaseBuilder<DB, TB>
case<C extends SimpleReferenceExpression<DB, TB>>(
  column: C,
): CaseBuilder<DB, TB, ExtractTypeFromReferenceExpression<DB, TB, C>>
case<E extends Expression<any>>(
  expression: E,
): CaseBuilder<DB, TB, ExtractTypeFromValueExpression<E>>

column · SimpleReferenceExpression

Optional column or expression to compare in when clauses

Example:

const { title, name } = await db
  .selectFrom('person')
  .where('id', '=', 123)
  .select((eb) => [
    eb.fn.coalesce('last_name', 'first_name').as('name'),
    eb
      .case()
      .when('gender', '=', 'male')
      .then('Mr.')
      .when('gender', '=', 'female')
      .then(
        eb
          .case('marital_status')
          .when('single')
          .then('Ms.')
          .else('Mrs.')
          .end()
      )
      .end()
      .as('title'),
  ])
  .executeTakeFirstOrThrow()

ref

Creates a column reference expression.

ref<RE extends StringReference<DB, TB>>(
  reference: RE,
): ExpressionWrapper<DB, TB, ExtractTypeFromReferenceExpression<DB, TB, RE>>

ref<RE extends StringReference<DB, TB>>(
  reference: RE,
  op: JSONOperatorWith$,
): JSONPathBuilder<ExtractTypeFromReferenceExpression<DB, TB, RE>>

reference · StringReference · required

Column reference string (e.g., first_name, person.id)

op · JSONOperatorWith$

JSON path operator (->$ or ->>$) for JSON traversal

Example:

const result = await db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb.or([
    eb('first_name', '=', eb.ref('last_name')),
    eb('first_name', '=', eb.ref('middle_name'))
  ]))
  .execute()

JSON path example:

const result = await db.selectFrom('person')
  .where(({ eb, ref }) => eb(
    ref('profile', '->').key('addresses').at(0).key('city'),
    '=',
    'San Diego'
  ))
  .selectAll()
  .execute()

jsonPath

Creates a JSON path expression with the provided column as root document.

jsonPath<$ extends StringReference<DB, TB>>(): JSONPathBuilder<ExtractTypeFromReferenceExpression<DB, TB, $>>

$ · StringReference · required

Column reference to use as JSON path root (passed as type parameter)

Example:

await db.updateTable('person')
  .set('profile', (eb) => eb.fn('json_set', [
    'profile',
    eb.jsonPath<'profile'>().key('addresses').at('last').key('city'),
    eb.val('San Diego')
  ]))
  .where('id', '=', 3)
  .execute()

table

Creates a table reference.

table<T extends TB & string>(
  table: T,
): ExpressionWrapper<DB, TB, Selectable<DB[T]>>

table · string · required

Table name

Example:

const result = await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select(eb => [
    'person.id',
    sql<Pet[]>`jsonb_agg(${eb.table('pet')})`.as('pets')
  ])
  .groupBy('person.id')
  .execute()

val

Returns a value expression.

val<VE>(
  value: VE,
): ExpressionWrapper<DB, TB, ExtractTypeFromValueExpression<VE>>

value · any · required

The value to use in the expression

This can be used to pass in a value where a reference is taken by default.

Example:

const result = await db.selectFrom('person')
  .selectAll()
  .where((eb) => eb(
    eb.val('cat'),
    '=',
    eb.fn.any(
      eb.selectFrom('pet')
        .select('species')
        .whereRef('owner_id', '=', 'person.id')
    )
  ))
  .execute()

refTuple

Creates a tuple expression using column references.

refTuple<
  R1 extends ReferenceExpression<DB, TB>,
  R2 extends ReferenceExpression<DB, TB>,
>(
  value1: R1,
  value2: R2,
): ExpressionWrapper<DB, TB, RefTuple2<DB, TB, R1, R2>>

Supports 2-5 tuple elements.

value1, value2, ... · ReferenceExpression · required

Column references for the tuple

Example:

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

tuple

Creates a value tuple expression.

tuple<V1, V2>(
  value1: V1,
  value2: V2,
): ExpressionWrapper<DB, TB, ValTuple2<V1, V2>>

Supports 2-5 tuple elements.

value1, value2, ... · any · required

Values for the tuple

Example:

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

lit

Returns a literal value expression.

lit<VE extends number | boolean | null>(
  literal: VE,
): ExpressionWrapper<DB, TB, VE>

literal · number | boolean | null · required

Literal value (only safe types allowed to prevent SQL injection)

Just like val but creates a literal value that gets merged in the SQL. To prevent SQL injections, only boolean, number and null values are accepted.

Example:

const result = await db.selectFrom('person')
  .select((eb) => eb.lit(1).as('one'))
  .execute()

unary

Creates a unary expression.

unary<RE extends ReferenceExpression<DB, TB>>(
  op: UnaryOperator,
  expr: RE,
): ExpressionWrapper<DB, TB, ExtractTypeFromReferenceExpression<DB, TB, RE>>

op · UnaryOperator · required

Unary operator (e.g., not, exists, -)

expr · ReferenceExpression · required

Expression to apply the operator to

Example:

const result = await db.selectFrom('person')
  .select((eb) => [
    'first_name',
    eb.unary('-', 'age').as('negative_age')
  ])
  .execute()

not

Creates a not operation.

not<RE extends ReferenceExpression<DB, TB>>(
  expr: RE,
): ExpressionWrapper<DB, TB, ExtractTypeFromReferenceExpression<DB, TB, RE>>

A shortcut for unary('not', expr).

exists

Creates an exists operation.

exists<RE extends ReferenceExpression<DB, TB>>(
  expr: RE,
): ExpressionWrapper<DB, TB, SqlBool>

A shortcut for unary('exists', expr).

neg

Creates a negation operation.

neg<RE extends ReferenceExpression<DB, TB>>(
  expr: RE,
): ExpressionWrapper<DB, TB, ExtractTypeFromReferenceExpression<DB, TB, RE>>

A shortcut for unary('-', expr).

between

Creates a between expression.

between<
  RE extends ReferenceExpression<DB, TB>,
  SE extends OperandValueExpression<DB, TB, RE>,
  EE extends OperandValueExpression<DB, TB, RE>,
>(
  expr: RE,
  start: SE,
  end: EE,
): ExpressionWrapper<DB, TB, SqlBool>

expr · ReferenceExpression · required

Expression to check

start · OperandValueExpression · required

Start of range

end · OperandValueExpression · required

End of range

Example:

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

betweenSymmetric

Creates a between symmetric expression.

betweenSymmetric<
  RE extends ReferenceExpression<DB, TB>,
  SE extends OperandValueExpression<DB, TB, RE>,
  EE extends OperandValueExpression<DB, TB, RE>,
>(
  expr: RE,
  start: SE,
  end: EE,
): ExpressionWrapper<DB, TB, SqlBool>

Example:

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

and

Combines expressions using the logical and operator.

and<E extends OperandExpression<SqlBool>>(
  exprs: ReadonlyArray<E>,
): ExpressionWrapper<DB, TB, SqlBool>

and<E extends Readonly<FilterObject<DB, TB>>>(
  exprs: E,
): ExpressionWrapper<DB, TB, SqlBool>

exprs · array | object · required

Array of expressions or filter object for equality comparisons

An empty array produces a true expression.

Example:

const result = await db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb.and([
    eb('first_name', '=', 'Jennifer'),
    eb('last_name', '=', 'Aniston'),
    eb('age', '>', 30)
  ]))
  .execute()

Object notation:

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

or

Combines expressions using the logical or operator.

or<E extends OperandExpression<SqlBool>>(
  exprs: ReadonlyArray<E>,
): ExpressionWrapper<DB, TB, SqlBool>

or<E extends Readonly<FilterObject<DB, TB>>>(
  exprs: E,
): ExpressionWrapper<DB, TB, SqlBool>

exprs · array | object · required

Array of expressions or filter object for equality comparisons

An empty array produces a false expression.

Example:

const result = await db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb.or([
    eb('first_name', '=', 'Jennifer'),
    eb('first_name', '=', 'Arnold'),
    eb('first_name', '=', 'Sylvester')
  ]))
  .execute()

parens

Wraps the expression in parentheses.

parens<
  RE extends ReferenceExpression<DB, TB>,
  OP extends BinaryOperatorExpression,
  VE extends OperandValueExpressionOrList<DB, TB, RE>,
>(
  lhs: RE,
  op: OP,
  rhs: VE,
): ExpressionWrapper<DB, TB, ...>

parens<T>(expr: Expression<T>): ExpressionWrapper<DB, TB, T>

Example:

const result = await db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb(eb.parens('age', '+', 1), '/', 100), '<', 0.1)
  .execute()

cast

Creates a cast(expr as dataType) expression.

cast<T, RE extends ReferenceExpression<DB, TB>>(
  expr: RE,
  dataType: DataTypeExpression,
): ExpressionWrapper<DB, TB, T>

expr · ReferenceExpression · required

Expression to cast

dataType · DataTypeExpression · required

Target data type

T · type parameter · required

TypeScript type to cast to (must be provided as type parameter)

Example:

const result = await db.selectFrom('person')
  .select((eb) => [
    'id',
    'first_name',
    eb.cast<number>('age', 'integer').as('age')
  ])
  .execute()

withSchema

Deprecated. Will be removed in kysely 0.25.0.

withSchema(schema: string): ExpressionBuilder<DB, TB>