Last Updated: 3/10/2026

WhereInterface

Interface WhereInterface

interface WhereInterface<DB, TB extends keyof DB> {
    clearWhere(): WhereInterface<DB, TB>;
    where<
        RE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        VE extends any,
    >(
        lhs: RE,
        op: ComparisonOperatorExpression,
        rhs: VE,
    ): WhereInterface<DB, TB>;
    where<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
        expression: E,
    ): WhereInterface<DB, TB>;
    whereRef<
        LRE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        RRE extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
    >(
        lhs: LRE,
        op: ComparisonOperatorExpression,
        rhs: RRE,
    ): WhereInterface<DB, TB>;
}

Type Parameters

DB
TB extends keyof DB

Hierarchy (View Summary)

WhereInterface
- SelectQueryBuilder

Implemented by

Index

Methods

clearWhere where whereRef

Methods

clearWhere

clearWhere(): WhereInterface<DB, TB>

Clears all where expressions from the query.

Examples
```
db. selectFrom('person') . selectAll() . where('id','=', 42) . clearWhere()
```
The generated SQL(PostgreSQL):
```
select * from "person" 
```
Returns WhereInterface<DB, TB>

where

where<
    RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>,
    VE extends any,

(
    lhs: RE,
    op: ComparisonOperatorExpression,
    rhs: VE,
): WhereInterface<DB, TB>

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:


import { sql } from  'kysely' 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< string>`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  columnFromUserInput: string = 'id' const  persons = await  db . selectFrom('person') . selectAll() . where(ref(columnFromUserInput), '=', 1) . where(sql. id(columnFromUserInput), '=', 2) . execute()

Type Parameters

RE extends
    | string
    | Expression<any>
    | DynamicReferenceBuilder<any>
    | SelectQueryBuilderExpression<Record<string, any>>
    | OperandExpressionFactory<DB, TB, any>
VE extends any

Parameters

Returns WhereInterface<DB, TB>

where<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
expression: E,
): WhereInterface<DB, TB>

Type Parameters
- E extends ExpressionOrFactory<DB, TB, SqlBool>
Parameters
- expression: E
Returns WhereInterface<DB, TB>

whereRef

whereRef<
    LRE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>,
    RRE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>,

(
    lhs: LRE,
    op: ComparisonOperatorExpression,
    rhs: RRE,
): WhereInterface<DB, TB>

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" 
```
Type Parameters
- LRE extends
      | string
      | Expression<any>
      | DynamicReferenceBuilder<any>
      | SelectQueryBuilderExpression<Record<string, any>>
      | OperandExpressionFactory<DB, TB, any>
- RRE extends
      | string
      | Expression<any>
      | DynamicReferenceBuilder<any>
      | SelectQueryBuilderExpression<Record<string, any>>
      | OperandExpressionFactory<DB, TB, any>
Parameters
- lhs: LRE
- op: ComparisonOperatorExpression
- rhs: RRE
Returns WhereInterface<DB, TB>

Settings

Member Visibility

On This Page

Methods

- - Examples

- - Examples

Examples

QueryLogEvent ColumnDefinitionNode