ExpressionBuilder
§Call Signatures
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:
eb.selectFrom('person')
.selectAll()
.where((eb) => eb('first_name', '=', 'Jennifer'))
The generated SQL (PostgreSQL):
select *
from "person"
where "first_name" = $1
By default the third argument is interpreted as a value. To pass in a column reference, you can use {@link ref}:
eb.selectFrom('person')
.selectAll()
.where((eb) => eb('first_name', '=', eb.ref('last_name')))
The generated SQL (PostgreSQL):
select *
from "person"
where "first_name" = "last_name"
In the following example eb
is used to increment an integer column:
db.updateTable('person')
.set((eb) => ({
age: eb('age', '+', 1)
}))
.where('id', '=', id)
The generated SQL (PostgreSQL):
update "person"
set "age" = "age" + $1
where "id" = $2
As always, expressions can be nested. Both the first and the third argument can be any expression:
eb.selectFrom('person')
.selectAll()
.where((eb) => eb(
eb.fn('lower', ['first_name']),
'in',
eb.selectFrom('pet')
.select('pet.name')
.where('pet.species', '=', 'cat')
))
§Methods
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.
Examples
In this example we use and
to create a WHERE expr1 AND expr2 AND expr3
statement:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.and([
eb('first_name', '=', 'Jennifer'),
eb('fist_name', '=', 'Arnold'),
eb('fist_name', '=', 'Sylvester')
]))
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:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.and({
first_name: 'Jennifer',
last_name: 'Aniston'
}))
The generated SQL (PostgreSQL):
select "person".*
from "person"
where (
"first_name" = $1
and "last_name" = $2
)
Creates a between
expression.
Examples
db.selectFrom('person')
.selectAll()
.where((eb) => eb.between('age', 40, 60))
The generated SQL (PostgreSQL):
select * from "person" where "age" between $1 and $2
Creates a between symmetric
expression.
Examples
db.selectFrom('person')
.selectAll()
.where((eb) => eb.betweenSymmetric('age', 40, 60))
The generated SQL (PostgreSQL):
select * from "person" where "age" between symmetric $1 and $2
Creates a case
statement/operator.
Examples
Kitchen sink example with 2 flavors of case
operator:
import { sql } from 'kysely'
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('maritalStatus')
.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 "maritalStatus"
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.
Examples
db.selectFrom('person')
.select((eb) => [
'id',
'first_name',
eb.cast<number>('age', 'integer').as('age')
])
The generated SQL (PostgreSQL):
select cast("age" as integer) as "age"
from "person"
Returns a copy of this
expression builder, for destructuring purposes.
Examples
db.selectFrom('person')
.where(({ eb, exists, selectFrom }) =>
eb('first_name', '=', 'Jennifer').and(
exists(selectFrom('pet').whereRef('owner_id', '=', 'person.id').select('pet.id'))
)
)
.selectAll()
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 {@link 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. {@link Kysely.fn} allows you to refer to columns in any table of the database even if it doesn't produce valid SQL.
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
Creates a JSON path expression with provided column as root document (the $).
For a JSON reference expression, see {@link ref}.
Examples
db.updateTable('person')
.set('experience', (eb) => eb.fn('json_set', [
'experience',
eb.jsonPath<'experience'>().at('last').key('title'),
eb.val('CEO')
]))
.where('id', '=', id)
.execute()
The generated SQL (MySQL):
update `person`
set `experience` = json_set(`experience`, '$[last].title', ?)
where `id` = ?
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.
Examples
db.selectFrom('person')
.select((eb) => eb.lit(1).as('one'))
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.
Examples
In this example we use or
to create a WHERE expr1 OR expr2 OR expr3
statement:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.or([
eb('first_name', '=', 'Jennifer'),
eb('fist_name', '=', 'Arnold'),
eb('fist_name', '=', 'Sylvester')
]))
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:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.or({
first_name: 'Jennifer',
last_name: 'Aniston'
}))
The generated SQL (PostgreSQL):
select "person".*
from "person"
where (
"first_name" = $1
or "last_name" = $2
)
Wraps the expression in parentheses.
Examples
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb(eb.parens('age', '+', 1), '/', 100), '<', 0.1))
The generated SQL (PostgreSQL):
select "person".*
from "person"
where ("age" + $1) / $2 < $3
You can also pass in any expression as the only argument:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.parens(
eb('age', '=', 1).or('age', '=', 2))
).and(
eb('first_name', '=', 'Jennifer').or('first_name', '=', 'Arnold')
))
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 {@link 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 {@link jsonPath}.
Examples
By default the third argument of binary expressions is a value. This function can be used to pass in a column reference instead:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb.or([
eb('first_name', '=', eb.ref('last_name')),
eb('first_name', '=', eb.ref('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:
db.insertInto('person')
.values(person)
.onConflict((oc) => oc
.column('id')
.doUpdateSet(({ ref }) => ({
first_name: ref('excluded.first_name'),
last_name: ref('excluded.last_name')
}))
)
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:
db.update('pet').set((eb) => ({
name: sql`concat(${eb.ref('pet.name')}, ${suffix})`
}))
In the next example we use ref
to reference a nested JSON property:
db.selectFrom('person')
.where(({ eb, ref }) => eb(
ref('address', '->').key('state').key('abbr'),
'=',
'CA'
))
.selectAll()
The generated SQL (PostgreSQL):
select * from "person" where "address"->'state'->'abbr' = $1
You can also compile to a JSON path expression by using the ->$
or ->>$
operator:
db.selectFrom('person')
.select(({ ref }) =>
ref('experience', '->$')
.at('last')
.key('title')
.as('current_job')
)
The generated SQL (MySQL):
select `experience`->'$[last].title' as `current_job` from `person`
Creates a tuple expression.
This creates a tuple using column references by default. See {@link tuple} if you need to create value tuples.
Examples
db.selectFrom('person')
.selectAll('person')
.where(({ eb, refTuple, tuple }) => eb(
refTuple('first_name', 'last_name'),
'in',
[
tuple('Jennifer', 'Aniston'),
tuple('Sylvester', 'Stallone')
]
))
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 {@link @SelectQueryBuilder.$asTuple | $asTuple} function:
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')
))
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 {@link QueryCreator.selectFrom}.
Examples
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.
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"
Creates a value tuple expression.
This creates a tuple using values by default. See {@link refTuple} if you need to create tuples using column references.
Examples
db.selectFrom('person')
.selectAll('person')
.where(({ eb, refTuple, tuple }) => eb(
refTuple('first_name', 'last_name'),
'in',
[
tuple('Jennifer', 'Aniston'),
tuple('Sylvester', 'Stallone')
]
))
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.
Examples
Binary expressions take a reference by default as the first argument. val
could
be used to pass in a value instead:
eb(val(38), '=', ref('age'))
The generated SQL (PostgreSQL):
$1 = "age"
See {@link QueryCreator.withSchema}