Creates a binary expression.

This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.

Examples

A simple comparison:

eb.selectFrom('person')
  .selectAll()
  .where((eb) => eb('first_name', '=', 'Jennifer'))

The generated SQL (PostgreSQL):

select *
from "person"
where "first_name" = $1

By default the third argument is interpreted as a value. To pass in a column reference, you can use ref:

eb.selectFrom('person')
  .selectAll()
  .where((eb) => eb('first_name', '=', eb.ref('last_name')))

The generated SQL (PostgreSQL):

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

In the following example eb is used to increment an integer column:

db.updateTable('person')
  .set((eb) => ({
    age: eb('age', '+', 1)
  }))
  .where('id', '=', id)

The generated SQL (PostgreSQL):

update "person"
set "age" = "age" + $1
where "id" = $2

As always, expressions can be nested. Both the first and the third argument can be any expression:

eb.selectFrom('person')
  .selectAll()
  .where((eb) => eb(
    eb.fn('lower', ['first_name']),
    'in',
    eb.selectFrom('pet')
      .select('pet.name')
      .where('pet.species', '=', 'cat')
  ))

Returns a copy of this expression builder, for destructuring purposes.

Examples

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

The generated SQL (PostgreSQL):

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

Returns a FunctionModule that can be used to write type safe function calls.

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. Kysely.fn allows you to refer to columns in any table of the database even if it doesn't produce valid SQL.

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

Creates a subquery.

The query builder returned by this method is typed in a way that you can refer to all tables of the parent query in addition to the subquery's tables.

This method accepts all the same inputs as QueryCreator.selectFrom.

Examples

This example shows that you can refer to both pet.owner_id and person.id columns from the subquery. This is needed to be able to create correlated subqueries:

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

console.log(result[0].owner_name)

The generated SQL (PostgreSQL):

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

You can use a normal query in place of (qb) => qb.selectFrom(...) but in that case Kysely typings wouldn't allow you to reference pet.owner_id because pet is not joined to that query.

Creates a case statement/operator.

Examples

Kitchen sink example with 2 flavors of case operator:

import { sql } from 'kysely'

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('martialStatus')
          .when('single')
          .then('Ms.')
          .else('Mrs.')
          .end()
      )
      .end()
      .as('title'),
  ])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select
  coalesce("last_name", "first_name") as "name",
  case
    when "gender" = $1 then $2
    when "gender" = $3 then
      case "martialStatus"
        when $4 then $5
        else $6
      end
  end as "title"
from "person"
where "id" = $7

This method can be used to reference columns within the query's context. For a non-type-safe version of this method see sql's version.

Additionally, this method can be used to reference nested JSON properties or array elements. See JSONPathBuilder for more information. For regular JSON path expressions you can use jsonPath.

Examples

By default the third argument of binary expressions is a value. This function can be used to pass in a column reference instead:

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

In the next example we use the ref method to reference columns of the virtual table excluded in a type-safe way to create an upsert operation:

db.insertInto('person')
  .values(person)
  .onConflict((oc) => oc
    .column('id')
    .doUpdateSet(({ ref }) => ({
      first_name: ref('excluded.first_name'),
      last_name: ref('excluded.last_name')
    }))
  )

In the next example we use ref in a raw sql expression. Unless you want to be as type-safe as possible, this is probably overkill:

db.update('pet').set((eb) => ({
  name: sql`concat(${eb.ref('pet.name')}, ${suffix})`
}))

In the next example we use ref to reference a nested JSON property:

db.selectFrom('person')
  .where(({ eb, ref }) => eb(
    ref('address', '->').key('state').key('abbr'),
    '=',
    'CA'
  ))
  .selectAll()

The generated SQL (PostgreSQL):

select * from "person" where "address"->'state'->'abbr' = $1

You can also compile to a JSON path expression by using the ->$or ->>$ operator:

db.selectFrom('person')
  .select(({ ref }) =>
    ref('experience', '->$')
      .at('last')
      .key('title')
      .as('current_job')
  )

The generated SQL (MySQL):

select `experience`->'$[last].title' as `current_job` from `person`

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

For a JSON reference expression, see ref.

Examples

db.updateTable('person')
  .set('experience', (eb) => eb.fn('json_set', [
    'experience',
    eb.jsonPath<'experience'>().at('last').key('title'),
    eb.val('CEO')
  ]))
  .where('id', '=', id)
  .execute()

The generated SQL (MySQL):

update `person`
set `experience` = json_set(`experience`, '$[last].title', ?)
where `id` = ?

Creates a table reference.

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

The generated SQL (PostgreSQL):

select "person"."id", jsonb_agg("pet") as "pets"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."id"

Returns a value expression.

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

This function returns an Expression and can be used pretty much anywhere.

Examples

Binary expressions take a reference by default as the first argument. val could be used to pass in a value instead:

eb(val(38), '=', ref('age'))

The generated SQL (PostgreSQL):

$1 = "age"

Creates a tuple expression.

This creates a tuple using column references by default. See tuple if you need to create value tuples.

Examples

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

The generated SQL (PostgreSQL):

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

In the next example a reference tuple is compared to a subquery. Note that in this case you need to use the @SelectQueryBuilder.$asTuple | $asTuple function:

db.selectFrom('person')
  .selectAll('person')
  .where(({ eb, refTuple, selectFrom }) => eb(
    refTuple('first_name', 'last_name'),
    'in',
    selectFrom('pet')
      .select(['name', 'species'])
      .where('species', '!=', 'cat')
      .$asTuple('name', 'species')
  ))

The generated SQL (PostgreSQL):

select
  "person".*
from
  "person"
where
  ("first_name", "last_name")
  in
  (
    select "name", "species"
    from "pet"
    where "species" != $1
  )

Creates a value tuple expression.

This creates a tuple using values by default. See refTuple if you need to create tuples using column references.

Examples

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

The generated SQL (PostgreSQL):

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

Returns a literal value expression.

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. If you need string or other literals, use sql.lit instead.

Examples

db.selectFrom('person')
  .select((eb) => eb.lit(1).as('one'))

The generated SQL (PostgreSQL):

select 1 as "one" from "person"

Creates an unary expression.

This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.

Creates a not operation.

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

Creates an exists operation.

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

Creates a negation operation.

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

Creates a between expression.

Examples

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

The generated SQL (PostgreSQL):

select * from "person" where "age" between $1 and $2

Creates a between symmetric expression.

Examples

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

The generated SQL (PostgreSQL):

select * from "person" where "age" between symmetric $1 and $2

Combines two or more expressions using the logical and operator.

An empty array produces a true expression.

This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.

Examples

In this example we use and to create a WHERE expr1 AND expr2 AND expr3 statement:

db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb.and([
    eb('first_name', '=', 'Jennifer'),
    eb('fist_name', '=', 'Arnold'),
    eb('fist_name', '=', 'Sylvester')
  ]))

The generated SQL (PostgreSQL):

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

Optionally you can use the simpler object notation if you only need equality comparisons:

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

The generated SQL (PostgreSQL):

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

Combines two or more expressions using the logical or operator.

An empty array produces a false expression.

This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.

Examples

In this example we use or to create a WHERE expr1 OR expr2 OR expr3 statement:

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

The generated SQL (PostgreSQL):

select "person".*
from "person"
where (
  "first_name" = $1
  or "first_name" = $2
  or "first_name" = $3
)

Optionally you can use the simpler object notation if you only need equality comparisons:

db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb.or({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  }))

The generated SQL (PostgreSQL):

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

Wraps the expression in parentheses.

Examples

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

The generated SQL (PostgreSQL):

select "person".*
from "person"
where ("age" + $1) / $2 < $3

You can also pass in any expression as the only argument:

db.selectFrom('person')
  .selectAll('person')
  .where((eb) => parens(
    eb('age', '=', 1).or('age', '=', 2))
  ).and(
    eb('first_name', '=', 'Jennifer').or('first_name', '=', 'Arnold')
  ))

The generated SQL (PostgreSQL):

select "person".*
from "person"
where ("age" = $1 or "age" = $2) and ("first_name" = $3 or "first_name" = $4)

See QueryCreator.withSchema