Clears all where expressions from the query.
db.selectFrom('person')
.selectAll()
.where('id','=',42)
.clearWhere()
The generated SQL(PostgreSQL):
select * from "person"
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.
await db
.insertInto('person')
.values({ first_name, pic })
.onConflict((oc) => oc
.column('pic')
.doNothing()
)
The generated SQL (PostgreSQL):
insert into "person" ("first_name", "pic")
values ($1, $2)
on conflict ("pic") do nothing
Adds the "do update set" conflict action.
await db
.insertInto('person')
.values({ first_name, pic })
.onConflict((oc) => oc
.column('pic')
.doUpdateSet({ first_name })
)
The generated SQL (PostgreSQL):
insert into "person" ("first_name", "pic")
values ($1, $2)
on conflict ("pic")
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:
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')
}))
)
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
where method calls are combined with 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:
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
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:
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()
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:
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:
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`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:
import { sql } from 'kysely'
const { ref } = db.dynamic
const persons = await db
.selectFrom('person')
.selectAll()
.where(ref(columnFromUserInput), '=', 1)
.where(sql.id(columnFromUserInput), '=', 2)
.execute()
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.
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"
Generated using TypeDoc
Simply calls the provided function passing
thisas the only argument.$callreturns what the provided function returns.