import { kysely } from "https://dotland.deno.dev/x/kysely_postgrs_js_dialect@v0.27.3/mod.ts";
const { InsertQueryBuilder } = kysely;
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')
})
)