Adds a where expression to the query.

Calling this method multiple times will combine the expressions using and.

Also see whereRef

Examples

where method calls are combined with AND:

const person = await db
  .selectFrom('person')
  .selectAll()
  .where('first_name', '=', 'Jennifer')
  .where('age', '>', 40)
  .executeTakeFirst()

The generated SQL (PostgreSQL):

select * from "person" where "first_name" = $1 and "age" > $2

Operator can be any supported operator or if the typings don't support it you can always use:

sql`your operator`

Find multiple items using a list of identifiers:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('id', 'in', ['1', '2', '3'])
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "id" in ($1, $2, $3)

You can use the and function to create a simple equality filter using an object

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

The generated SQL (PostgreSQL):

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

To combine conditions using OR, you can use the expression builder. There are two ways to create OR expressions. Both are shown in this example:

const persons = await db
  .selectFrom('person')
  .selectAll()
  // 1. Using the `or` method on the expression builder:
  .where((eb) => eb.or([
    eb('first_name', '=', 'Jennifer'),
    eb('first_name', '=', 'Sylvester')
  ]))
  // 2. Chaining expressions using the `or` method on the
  // created expressions:
  .where((eb) =>
    eb('last_name', '=', 'Aniston').or('last_name', '=', 'Stallone')
  )
  .execute()

The generated SQL (PostgreSQL):

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

You can add expressions conditionally like this:

import { Expression, SqlBool } from 'kysely'

const firstName: string | undefined = 'Jennifer'
const lastName: string | undefined = 'Aniston'
const under18 = true
const over60 = true

let query = db
  .selectFrom('person')
  .selectAll()

if (firstName) {
  // The query builder is immutable. Remember to reassign
  // the result back to the query variable.
  query = query.where('first_name', '=', firstName)
}

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

if (under18 || over60) {
  // Conditional OR expressions can be added like this.
  query = query.where((eb) => {
    const ors: Expression<SqlBool>[] = []

    if (under18) {
      ors.push(eb('age', '<', 18))
    }

    if (over60) {
      ors.push(eb('age', '>', 60))
    }

    return eb.or(ors)
  })
}

const persons = await query.execute()

Both the first and third argument can also be arbitrary expressions like subqueries. An expression can defined by passing a function and calling the methods of the ExpressionBuilder passed to the callback:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(
    (qb) => qb.selectFrom('pet')
      .select('pet.name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .limit(1),
    '=',
    'Fluffy'
  )
  .execute()

The generated SQL (PostgreSQL):

select *
from "person"
where (
  select "pet"."name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  limit $1
) = $2

A where in query can be built by using the in operator and an array of values. The values in the array can also be expressions:

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('person.id', 'in', [100, 200, 300])
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "id" in ($1, $2, $3)

For complex where expressions you can pass in a single callback and use the ExpressionBuilder to build your expression:

const firstName = 'Jennifer'
const maxAge = 60

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .where(({ eb, or, and, not, exists, selectFrom }) => and([
    or([
      eb('first_name', '=', firstName),
      eb('age', '<', maxAge)
    ]),
    not(exists(
      selectFrom('pet')
        .select('pet.id')
        .whereRef('pet.owner_id', '=', 'person.id')
    ))
  ]))
  .execute()

The generated SQL (PostgreSQL):

select "person".*
from "person"
where (
  (
    "first_name" = $1
    or "age" < $2
  )
  and not exists (
    select "pet"."id" from "pet" where "pet"."owner_id" = "person"."id"
  )
)

If everything else fails, you can always use the sql tag as any of the arguments, including the operator:

import { sql } from 'kysely'

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(
    sql`coalesce(first_name, last_name)`,
    'like',
    '%' + name + '%',
  )
  .execute()

The generated SQL (PostgreSQL):

select * from "person"
where coalesce(first_name, last_name) like $1

In all examples above the columns were known at compile time (except for the raw sql expressions). By default kysely only allows you to refer to columns that exist in the database and can be referred to in the current query and context.

Sometimes you may want to refer to columns that come from the user input and thus are not available at compile time.

You have two options, the sql tag or db.dynamic. The example below uses both:

import { sql } from 'kysely'
const { ref } = db.dynamic

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where(ref(columnFromUserInput), '=', 1)
  .where(sql.id(columnFromUserInput), '=', 2)
  .execute()

Adds a where clause where both sides of the operator are references to columns.

The normal where method treats the right hand side argument as a value by default. whereRef treats it as a column reference. This method is expecially useful with joins and correlated subqueries.

Examples

Usage with a join:

db.selectFrom(['person', 'pet'])
  .selectAll()
  .whereRef('person.first_name', '=', 'pet.name')

The generated SQL (PostgreSQL):

select * from "person", "pet" where "person"."first_name" = "pet"."name"

Usage in a subquery:

const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .select((eb) => eb
    .selectFrom('pet')
    .select('name')
    .whereRef('pet.owner_id', '=', 'person.id')
    .limit(1)
    .as('pet_name')
  )
  .execute()

The generated SQL (PostgreSQL):

select "person".*, (
  select "name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  limit $1
) as "pet_name"
from "person"

Clears all where expressions from the query.

Examples

db.selectFrom('person')
  .selectAll()
  .where('id','=',42)
  .clearWhere()

The generated SQL(PostgreSQL):

select * from "person"