Returns a copy of this expression builder, for destructuring purposes.
const result = await db.selectFrom('person')
.where(({ eb, exists, selectFrom }) =>
eb('first_name', '=', 'Jennifer').and(exists(
selectFrom('pet').whereRef('owner_id', '=', 'person.id').select('pet.id')
))
)
.selectAll()
.execute()
The generated SQL (PostgreSQL):
select * from "person" where "first_name" = $1 and exists (
select "pet.id" from "pet" where "owner_id" = "person.id"
)
Returns a FunctionModule that can be used to write type safe function calls.
The difference between this and Kysely.fn is that this one is more type safe. You can only refer to columns visible to the part of the query you are building. Kysely.fn allows you to refer to columns in any table of the database even if it doesn't produce valid SQL.
const result = await db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select((eb) => [
'person.id',
eb.fn.count('pet.id').as('pet_count')
])
.groupBy('person.id')
.having((eb) => eb.fn.count('pet.id'), '>', 10)
.execute()
The generated SQL (PostgreSQL):
select "person"."id", count("pet"."id") as "pet_count"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."id"
having count("pet"."id") > $1
Combines two or more expressions using the logical and operator.
An empty array produces a true expression.
This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.
In this example we use and to create a WHERE expr1 AND expr2 AND expr3
statement:
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.and([
eb('first_name', '=', 'Jennifer'),
eb('first_name', '=', 'Arnold'),
eb('first_name', '=', 'Sylvester')
]))
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where (
"first_name" = $1
and "first_name" = $2
and "first_name" = $3
)
Optionally you can use the simpler object notation if you only need equality comparisons:
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.and({
first_name: 'Jennifer',
last_name: 'Aniston'
}))
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where (
"first_name" = $1
and "last_name" = $2
)
Creates a between expression.
const result = await db.selectFrom('person')
.selectAll()
.where((eb) => eb.between('age', 40, 60))
.execute()
The generated SQL (PostgreSQL):
select * from "person" where "age" between $1 and $2
Creates a between symmetric expression.
const result = await db.selectFrom('person')
.selectAll()
.where((eb) => eb.betweenSymmetric('age', 40, 60))
.execute()
The generated SQL (PostgreSQL):
select * from "person" where "age" between symmetric $1 and $2
Creates a case statement/operator.
Kitchen sink example with 2 flavors of case operator:
const { title, name } = await db
.selectFrom('person')
.where('id', '=', 123)
.select((eb) => [
eb.fn.coalesce('last_name', 'first_name').as('name'),
eb
.case()
.when('gender', '=', 'male')
.then('Mr.')
.when('gender', '=', 'female')
.then(
eb
.case('marital_status')
.when('single')
.then('Ms.')
.else('Mrs.')
.end()
)
.end()
.as('title'),
])
.executeTakeFirstOrThrow()
The generated SQL (PostgreSQL):
select
coalesce("last_name", "first_name") as "name",
case
when "gender" = $1 then $2
when "gender" = $3 then
case "marital_status"
when $4 then $5
else $6
end
end as "title"
from "person"
where "id" = $7
Creates a cast(expr as dataType) expression.
Since Kysely can't know the mapping between JavaScript and database types, you need to provide both explicitly.
const result = await db.selectFrom('person')
.select((eb) => [
'id',
'first_name',
eb.cast<number>('age', 'integer').as('age')
])
.execute()
The generated SQL (PostgreSQL):
select cast("age" as integer) as "age"
from "person"
Creates an exists operation.
A shortcut for unary('exists', expr).
Creates a JSON path expression with provided column as root document (the $).
For a JSON reference expression, see ref.
await db.updateTable('person')
.set('profile', (eb) => eb.fn('json_set', [
'profile',
eb.jsonPath<'profile'>().key('addresses').at('last').key('city'),
eb.val('San Diego')
]))
.where('id', '=', 3)
.execute()
The generated SQL (MySQL):
update `person`
set `profile` = json_set(`profile`, '$.addresses[last].city', $1)
where `id` = $2
Returns a literal value expression.
Just like val but creates a literal value that gets merged in the SQL.
To prevent SQL injections, only boolean, number and null values
are accepted. If you need string or other literals, use sql.lit instead.
const result = await db.selectFrom('person')
.select((eb) => eb.lit(1).as('one'))
.execute()
The generated SQL (PostgreSQL):
select 1 as "one" from "person"
Creates a negation operation.
A shortcut for unary('-', expr).
Creates a not operation.
A shortcut for unary('not', expr).
Combines two or more expressions using the logical or operator.
An empty array produces a false expression.
This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.
In this example we use or to create a WHERE expr1 OR expr2 OR expr3
statement:
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.or([
eb('first_name', '=', 'Jennifer'),
eb('first_name', '=', 'Arnold'),
eb('first_name', '=', 'Sylvester')
]))
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where (
"first_name" = $1
or "first_name" = $2
or "first_name" = $3
)
Optionally you can use the simpler object notation if you only need equality comparisons:
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.or({
first_name: 'Jennifer',
last_name: 'Aniston'
}))
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where (
"first_name" = $1
or "last_name" = $2
)
Wraps the expression in parentheses.
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb(eb.parens('age', '+', 1), '/', 100), '<', 0.1)
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where ("age" + $1) / $2 < $3
You can also pass in any expression as the only argument:
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.parens(
eb('age', '=', 1).or('age', '=', 2)
).and(
eb('first_name', '=', 'Jennifer').or('first_name', '=', 'Arnold')
))
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where ("age" = $1 or "age" = $2) and ("first_name" = $3 or "first_name" = $4)
This method can be used to reference columns within the query's context. For a non-type-safe version of this method see sql's version.
Additionally, this method can be used to reference nested JSON properties or array elements. See JSONPathBuilder for more information. For regular JSON path expressions you can use jsonPath.
By default the third argument of binary expressions is a value. This function can be used to pass in a column reference instead:
const result = await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.or([
eb('first_name', '=', eb.ref('last_name')),
eb('first_name', '=', eb.ref('middle_name'))
]))
.execute()
The generated SQL (PostgreSQL):
select "person".*
from "person"
where "first_name" = "last_name" or "first_name" = "middle_name"
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:
await db.insertInto('person')
.values({
id: 3,
first_name: 'Jennifer',
last_name: 'Aniston',
gender: 'female',
})
.onConflict((oc) => oc
.column('id')
.doUpdateSet(({ ref }) => ({
first_name: ref('excluded.first_name'),
last_name: ref('excluded.last_name'),
gender: ref('excluded.gender'),
}))
)
.execute()
The generated SQL (PostgreSQL):
insert into "person" ("id", "first_name", "last_name", "gender")
values ($1, $2, $3, $4)
on conflict ("id") do update set
"first_name" = "excluded"."first_name",
"last_name" = "excluded"."last_name",
"gender" = "excluded"."gender"
In the next example we use ref in a raw sql expression. Unless you want
to be as type-safe as possible, this is probably overkill:
import { sql } from 'kysely'
await db.updateTable('pet')
.set((eb) => ({
name: sql<string>`concat(${eb.ref('pet.name')}, ${' the animal'})`
}))
.execute()
The generated SQL (PostgreSQL):
update "pet" set "name" = concat("pet"."name", $1)
In the next example we use ref to reference a nested JSON property:
const result = await db.selectFrom('person')
.where(({ eb, ref }) => eb(
ref('profile', '->').key('addresses').at(0).key('city'),
'=',
'San Diego'
))
.selectAll()
.execute()
The generated SQL (PostgreSQL):
select * from "person" where "profile"->'addresses'->0->'city' = $1
You can also compile to a JSON path expression by using the ->$or ->>$ operator:
const result = await db.selectFrom('person')
.select(({ ref }) =>
ref('profile', '->$')
.key('addresses')
.at('last')
.key('city')
.as('current_city')
)
.execute()
The generated SQL (MySQL):
select `profile`->'$.addresses[last].city' as `current_city` from `person`
Creates a tuple expression.
This creates a tuple using column references by default. See tuple if you need to create value tuples.
const result = await db.selectFrom('person')
.selectAll('person')
.where(({ eb, refTuple, tuple }) => eb(
refTuple('first_name', 'last_name'),
'in',
[
tuple('Jennifer', 'Aniston'),
tuple('Sylvester', 'Stallone')
]
))
.execute()
The generated SQL (PostgreSQL):
select
"person".*
from
"person"
where
("first_name", "last_name")
in
(
($1, $2),
($3, $4)
)
In the next example a reference tuple is compared to a subquery. Note that in this case you need to use the @SelectQueryBuilder.$asTuple | $asTuple function:
const result = await db.selectFrom('person')
.selectAll('person')
.where(({ eb, refTuple, selectFrom }) => eb(
refTuple('first_name', 'last_name'),
'in',
selectFrom('pet')
.select(['name', 'species'])
.where('species', '!=', 'cat')
.$asTuple('name', 'species')
))
.execute()
The generated SQL (PostgreSQL):
select
"person".*
from
"person"
where
("first_name", "last_name")
in
(
select "name", "species"
from "pet"
where "species" != $1
)
Creates a subquery.
The query builder returned by this method is typed in a way that you can refer to all tables of the parent query in addition to the subquery's tables.
This method accepts all the same inputs as QueryCreator.selectFrom.
This example shows that you can refer to both pet.owner_id and person.id
columns from the subquery. This is needed to be able to create correlated
subqueries:
const result = await db.selectFrom('pet')
.select((eb) => [
'pet.name',
eb.selectFrom('person')
.whereRef('person.id', '=', 'pet.owner_id')
.select('person.first_name')
.as('owner_name')
])
.execute()
console.log(result[0]?.owner_name)
The generated SQL (PostgreSQL):
select
"pet"."name",
( select "person"."first_name"
from "person"
where "person"."id" = "pet"."owner_id"
) as "owner_name"
from "pet"
You can use a normal query in place of (qb) => qb.selectFrom(...) but in
that case Kysely typings wouldn't allow you to reference pet.owner_id
because pet is not joined to that query.
Creates a table reference.
import { sql } from 'kysely'
import type { Pet } from 'type-editor' // imaginary module
const result = await db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select(eb => [
'person.id',
sql<Pet[]>`jsonb_agg(${eb.table('pet')})`.as('pets')
])
.groupBy('person.id')
.execute()
The generated SQL (PostgreSQL):
select "person"."id", jsonb_agg("pet") as "pets"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."id"
If you need a column reference, use ref.
Creates a value tuple expression.
This creates a tuple using values by default. See refTuple if you need to create tuples using column references.
const result = await db.selectFrom('person')
.selectAll('person')
.where(({ eb, refTuple, tuple }) => eb(
refTuple('first_name', 'last_name'),
'in',
[
tuple('Jennifer', 'Aniston'),
tuple('Sylvester', 'Stallone')
]
))
.execute()
The generated SQL (PostgreSQL):
select
"person".*
from
"person"
where
("first_name", "last_name")
in
(
($1, $2),
($3, $4)
)
Creates an unary expression.
This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.
Returns a value expression.
This can be used to pass in a value where a reference is taken by default.
This function returns an Expression and can be used pretty much anywhere.
Binary expressions take a reference by default as the first argument. val could
be used to pass in a value instead:
const result = await db.selectFrom('person')
.selectAll()
.where((eb) => eb(
eb.val('cat'),
'=',
eb.fn.any(
eb.selectFrom('pet')
.select('species')
.whereRef('owner_id', '=', 'person.id')
)
))
.execute()
The generated SQL (PostgreSQL):
select *
from "person"
where $1 = any(
select "species"
from "pet"
where "owner_id" = "person"."id"
)
Creates a binary expression.
This function returns an Expression and can be used pretty much anywhere. See the examples for a couple of possible use cases.
Examples
A simple comparison:
The generated SQL (PostgreSQL):
By default the third argument is interpreted as a value. To pass in a column reference, you can use ref:
The generated SQL (PostgreSQL):
In the following example
ebis used to increment an integer column:The generated SQL (PostgreSQL):
As always, expressions can be nested. Both the first and the third argument can be any expression:
The generated SQL (PostgreSQL):