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 {@link ignore} and {@link 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')
    })
  )