Last Updated: 3/9/2026
Class OnConflictBuilder<DB, TB>
Type Parameters
Implements
Index
Constructors
Methods
Constructors
constructor
Type Parameters
Parameters
Returns OnConflictBuilder<DB, TB>
Methods
$call
Simply calls the provided function passing this as the only argument. $call returns
what the provided function returns.
this
$call
Type Parameters
Parameters
Returns T
clearWhere
Clears all where expressions from the query.
Examples
db.selectFrom('person') .selectAll() .where('id','=',42) .clearWhere()
The generated SQL(PostgreSQL):
select * from "person"
Returns OnConflictBuilder<DB, TB>
Implementation of WhereInterface.clearWhere
column
Specify a single column as the conflict target.
Also see the columns, constraint and expression methods for alternative ways to specify the conflict target.
Parameters
Returns OnConflictBuilder<DB, TB>
columns
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.
Parameters
Returns OnConflictBuilder<DB, TB>
constraint
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.
Parameters
Returns OnConflictBuilder<DB, TB>
doNothing
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()`
The generated SQL (PostgreSQL):
insert into "person" ("first_name", "id") values ($1, $2) on conflict ("id") do nothing
Returns OnConflictDoNothingBuilder<DB, TB>
doUpdateSet
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()`
The generated SQL (PostgreSQL):
insert into "person" ("first_name", "id") values ($1, $2) on conflict ("id") do update set "first_name" = $3
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:
ref
excluded
`import type { NewPerson } from ‘type-editor’ // imaginary module
async function upsertPerson(person: NewPerson): Promise
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()
}`
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"
Parameters
Returns OnConflictUpdateBuilder<OnConflictDatabase<DB, TB>, OnConflictTables<TB>>
expression
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.
Parameters
Returns OnConflictBuilder<DB, TB>
where
Adds a where expression to the query.
where
Calling this method multiple times will combine the expressions using and.
and
Also see whereRef
Examples
where method calls are combined with AND:
where
AND
const person = await db .selectFrom('person') .selectAll() .where('first_name', '=', 'Jennifer') .where('age', '>', 40) .executeTakeFirst()
The generated SQL (PostgreSQL):
select * from "person" where "first_name" = $1 and "age" > $2
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“
Find multiple items using a list of identifiers:
const persons = await db .selectFrom('person') .selectAll() .where('id', 'in', [1, 2, 3]) .execute()
The generated SQL (PostgreSQL):
select * from "person" where "id" in ($1, $2, $3)
You can use the and function to create a simple equality
filter using an object
and
const persons = await db .selectFrom('person') .selectAll() .where((eb) => eb.and({ first_name: 'Jennifer', last_name: eb.ref('first_name') })) .execute()
The generated SQL (PostgreSQL):
select * from "person" where ( "first_name" = $1 and "last_name" = "first_name" )
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:
OR
OR
const persons = await db .selectFrom('person') .selectAll() // 1. Using the ormethod on the expression builder: .where((eb) => eb.or([ eb('first_name', '=', 'Jennifer'), eb('first_name', '=', 'Sylvester') ])) // 2. Chaining expressions using theor method on the // created expressions: .where((eb) => eb('last_name', '=', 'Aniston').or('last_name', '=', 'Stallone') ) .execute()
The generated SQL (PostgreSQL):
select * from "person" where ( ("first_name" = $1 or "first_name" = $2) and ("last_name" = $3 or "last_name" = $4) )
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()`
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()
The generated SQL (PostgreSQL):
select * from "person" where ( select "pet"."name" from "pet" where "pet"."owner_id" = "person"."id" limit $1 ) = $2
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:
where in
in
const persons = await db .selectFrom('person') .selectAll() .where('person.id', 'in', [100, 200, 300]) .execute()
The generated SQL (PostgreSQL):
select * from "person" where "id" in ($1, $2, $3)
For complex where expressions you can pass in a single callback and
use the ExpressionBuilder to build your expression:
where
ExpressionBuilder
`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()`
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" ) )
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()`
The generated SQL (PostgreSQL):
select * from "person" where coalesce(first_name, last_name) like $1
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:
db.dynamic
`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()`
Type Parameters
Parameters
Returns OnConflictBuilder<DB, TB>
Implementation of WhereInterface.where
Type Parameters
Parameters
Returns OnConflictBuilder<DB, TB>
Implementation of WhereInterface.where
whereRef
Adds a where clause where both sides of the operator are references
to columns.
where
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.
where
whereRef
Examples
Usage with a join:
db.selectFrom(['person', 'pet']) .selectAll() .whereRef('person.first_name', '=', 'pet.name')
The generated SQL (PostgreSQL):
select * from "person", "pet" where "person"."first_name" = "pet"."name"
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()
The generated SQL (PostgreSQL):
select "person".*, ( select "name" from "pet" where "pet"."owner_id" = "person"."id" limit $1 ) as "pet_name" from "person"
Type Parameters
Parameters
Returns OnConflictBuilder<DB, TB>
Implementation of WhereInterface.whereRef
Settings
On This Page
Generated using TypeDoc