Class OnConflictBuilder<DB, TB>

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

Clears all where expressions from the query.

Examples

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

The generated SQL(PostgreSQL):

select * from "person"
Copy

Specify a single column as the conflict target.

Also see the columns, constraint and expression methods for alternative ways to specify the conflict target.

Specify a list of columns as the conflict target.

Also see the column, constraint and expression methods for alternative ways to specify the conflict target.

Specify a specific constraint by name as the conflict target.

Also see the column, columns and expression methods for alternative ways to specify the conflict target.

Adds the "do nothing" conflict action.

Examples

const id = 1
const first_name = 'John'

await db
  .insertInto('person')
  .values({ first_name, id })
  .onConflict((oc) => oc
    .column('id')
    .doNothing()
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "id")
values ($1, $2)
on conflict ("id") do nothing
Copy

Adds the "do update set" conflict action.

Examples

const id = 1
const first_name = 'John'

await db
  .insertInto('person')
  .values({ first_name, id })
  .onConflict((oc) => oc
    .column('id')
    .doUpdateSet({ first_name })
  )
  .execute()
Copy

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "id")
values ($1, $2)
on conflict ("id")
do update set "first_name" = $3
Copy

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:

import type { NewPerson } from 'type-editor' // imaginary module

async function upsertPerson(person: NewPerson): Promise<void> {
  await db.insertInto('person')
    .values(person)
    .onConflict((oc) => oc
      .column('id')
      .doUpdateSet((eb) => ({
        first_name: eb.ref('excluded.first_name'),
        last_name: eb.ref('excluded.last_name')
      })
    )
  )
  .execute()
}
Copy

The generated SQL (PostgreSQL):

insert into "person" ("first_name", "last_name")
values ($1, $2)
on conflict ("id")
do update set
 "first_name" = excluded."first_name",
 "last_name" = excluded."last_name"
Copy

Specify an expression as the conflict target.

This can be used if the unique index is an expression index.

Also see the column, columns and constraint methods for alternative ways to specify the conflict target.

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

The generated SQL (PostgreSQL):

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

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`
Copy

Find multiple items using a list of identifiers:

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

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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

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

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

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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

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

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

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

The generated SQL (PostgreSQL):

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

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

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')
Copy

The generated SQL (PostgreSQL):

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

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

The generated SQL (PostgreSQL):

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