Adds an on conflict clause to the query.

on conflict is only supported by some dialects like PostgreSQL and SQLite. On MySQL you can use ignore and onDuplicateKeyUpdate to achieve similar results.

Examples

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .column('name')
    .doUpdateSet({ species: 'hamster' })
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict ("name")
do update set "species" = $3

You can provide the name of the constraint instead of a column name:

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .constraint('pet_name_key')
    .doUpdateSet({ species: 'hamster' })
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict on constraint "pet_name_key"
do update set "species" = $3

You can also specify an expression as the conflict target in case the unique index is an expression index:

import { sql } from 'kysely'

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .expression(sql`lower(name)`)
    .doUpdateSet({ species: 'hamster' })
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict (lower(name))
do update set "species" = $3

You can add a filter for the update statement like this:

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .column('name')
    .doUpdateSet({ species: 'hamster' })
    .where('excluded.name', '!=', 'Catto'')
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict ("name")
do update set "species" = $3
where "excluded"."name" != $4

You can create an on conflict do nothing clauses like this:

await db
  .insertInto('pet')
  .values({
    name: 'Catto',
    species: 'cat',
  })
  .onConflict((oc) => oc
    .column('name')
    .doNothing()
  )
  .execute()

The generated SQL (PostgreSQL):

insert into "pet" ("name", "species")
values ($1, $2)
on conflict ("name") do nothing

You can refer to the columns of the virtual excluded table in a type-safe way using a callback and the ref method of ExpressionBuilder:

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