Skip to content
Saasfly Docs

Kysely Where

Short and simple examples of how to use the where method to add a WHERE statement. While most of the examples show a SELECT query, the where method works exactly the same in UPDATE and DELETE queries too.

Simple where clause

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

SELECT
  *
FROM
  "person"
WHERE
  "first_name" = $1
  AND "age" > $2


-- Parameters
-- [1] Jennifer
-- [2] 40

Where in

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

SELECT
  *
FROM
  "person"
WHERE
  "id" IN ($1, $2, $3)


-- Parameters
-- [1] 1
-- [2] 2
-- [3] 3

Object filter

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

SELECT
  *
FROM
  "person"
WHERE
  (
    "first_name" = $1
    AND "last_name" = "first_name"
  )


-- Parameters
-- [1] Jennifer

OR where

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

SELECT
  *
FROM
  "person"
WHERE
  (
    "first_name" = $1
    OR "first_name" = $2
  )
  AND (
    "last_name" = $3
    OR "last_name" = $4
  )


-- Parameters
-- [1] Jennifer
-- [2] Sylvester
-- [3] Aniston
-- [4] Stallone

Conditional where calls

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

SELECT
  *
FROM
  "person"
WHERE
  "first_name" = $1
  AND "last_name" = $2
  AND (
    "age" < $3
    OR "age" > $4
  )


-- Parameters
-- [1] Jennifer
-- [2] Aniston
-- [3] 18
-- [4] 60

Complex where clause

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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

Result

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


-- Parameters
-- [1] Jennifer
-- [2] 60