Creates a function call.
To create an aggregate function call, use agg.
db.selectFrom('person')
.selectAll('person')
.where(db.fn('upper', ['first_name']), '=', 'JENNIFER')
The generated SQL (PostgreSQL):
select "person".*
from "person"
where upper("first_name") = $1
If you prefer readability over type-safety, you can always use raw sql:
db.selectFrom('person')
.selectAll('person')
.where(sql`upper(first_name)`, '=', 'JENNIFER')
Optional args: readonly RE[]Creates an aggregate function call.
This is a specialized version of the fn method, that returns an AggregateFunctionBuilder
instance. A builder that allows you to chain additional methods such as distinct,
filterWhere and over.
See avg, count, countAll, max, min, sum shortcuts of common aggregate functions.
db.selectFrom('person')
.select(({ fn }) => [
fn.agg<number>('rank').over().as('rank'),
fn.agg<string>('group_concat', ['first_name']).distinct().as('first_names')
])
The generated SQL (MySQL):
select rank() over() as "rank",
group_concat(distinct "first_name") as "first_names"
from "person"
Optional args: readonly RE[]Calls the any function for the column or expression given as the argument.
The argument must be a subquery or evaluate to an array.
In the following example, nicknames is assumed to be a column of type string[]:
db.selectFrom('person')
.selectAll('person')
.where((eb) => eb(
eb.val('Jen'), '=', eb.fn.any('person.nicknames')
))
The generated SQL (PostgreSQL):
select
"person".*
from
"person"
where
$1 = any("person"."nicknames")
Calls the avg function for the column or expression given as the argument.
This sql function calculates the average value for a given column.
For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.
db.selectFrom('toy')
.select((eb) => eb.fn.avg('price').as('avg_price'))
.execute()
The generated SQL (PostgreSQL):
select avg("price") as "avg_price" from "toy"
You can limit column range to only columns participating in current query:
db.selectFrom('toy')
.select((eb) => eb.fn.avg('price').as('avg_price'))
.execute()
If this function is used in a select statement, the type of the selected
expression will be number | string by default. This is because Kysely can't know the
type the db driver outputs. Sometimes the output can be larger than the largest
javascript number and a string is returned instead. Most drivers allow you
to configure the output type of large numbers and Kysely can't know if you've
done so.
You can specify the output type of the expression by providing the type as the first type argument:
db.selectFrom('toy')
.select((eb) => eb.fn.avg<number>('price').as('avg_price'))
.execute()
Sometimes a null is returned, e.g. when row count is 0, and no group by
was used. It is highly recommended to include null in the output type union
and handle null values in post-execute code, or wrap the function with a coalesce
function.
db.selectFrom('toy')
.select((eb) => eb.fn.avg<number | null>('price').as('avg_price'))
.execute()
Calls the coalesce function for given arguments.
This sql function returns the first non-null value from left to right, commonly used to provide a default scalar for nullable columns or functions.
If this function is used in a select statement, the type of the selected
expression is inferred in the same manner that the sql function computes.
A union of arguments' types - if a non-nullable argument exists, it stops
there (ignoring any further arguments' types) and exludes null from the final
union type.
(string | null, number | null) is inferred as string | number | null.
(string | null, number, Date | null) is inferred as string | number.
(number, string | null) is inferred as number.
db.selectFrom('participant')
.select((eb) => eb.fn.coalesce('nickname', sql<string>`'<anonymous>'`).as('nickname'))
.where('room_id', '=', roomId)
.execute()
The generated SQL (PostgreSQL):
select coalesce("nickname", '<anonymous>') as "nickname"
from "participant" where "room_id" = $1
You can limit column range to only columns participating in current query:
db.selectFrom('participant')
.select((eb) =>
eb.fn.coalesce('nickname', sql<string>`'<anonymous>'`).as('nickname')
)
.where('room_id', '=', roomId)
.execute()
You can combine this function with other helpers in this module:
db.selectFrom('person')
.select((eb) => eb.fn.coalesce(eb.fn.avg<number | null>('age'), sql<number>`0`).as('avg_age'))
.where('first_name', '=', 'Jennifer')
.execute()
The generated SQL (PostgreSQL):
select coalesce(avg("age"), 0) as "avg_age" from "person" where "first_name" = $1
Calls the count function for the column or expression given as the argument.
When called with a column as argument, this sql function counts the number of rows where there is a non-null value in that column.
For counting all rows nulls included (count(*)), see countAll.
For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.
db.selectFrom('toy')
.select((eb) => eb.fn.count('id').as('num_toys'))
.execute()
The generated SQL (PostgreSQL):
select count("id") as "num_toys" from "toy"
If this function is used in a select statement, the type of the selected
expression will be number | string | bigint by default. This is because
Kysely can't know the type the db driver outputs. Sometimes the output can
be larger than the largest javascript number and a string is returned instead.
Most drivers allow you to configure the output type of large numbers and Kysely
can't know if you've done so.
You can specify the output type of the expression by providing the type as the first type argument:
db.selectFrom('toy')
.select((eb) => eb.fn.count<number>('id').as('num_toys'))
.execute()
You can limit column range to only columns participating in current query:
db.selectFrom('toy')
.select((eb) => eb.fn.count('id').as('num_toys'))
.execute()
Calls the count function with * or table.* as argument.
When called with * as argument, this sql function counts the number of rows,
nulls included.
For counting rows with non-null values in a given column (count(column)),
see count.
For additional functionality such as filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.
db.selectFrom('toy')
.select((eb) => eb.fn.countAll().as('num_toys'))
.execute()
The generated SQL (PostgreSQL):
select count(*) as "num_toys" from "toy"
If this is used in a select statement, the type of the selected expression
will be number | string | bigint by default. This is because Kysely
can't know the type the db driver outputs. Sometimes the output can be larger
than the largest javascript number and a string is returned instead. Most
drivers allow you to configure the output type of large numbers and Kysely
can't know if you've done so.
You can specify the output type of the expression by providing the type as the first type argument:
db.selectFrom('toy')
.select((eb) => eb.fn.countAll<number>().as('num_toys'))
.execute()
Some databases, such as PostgreSQL, support scoping the function to a specific table:
db.selectFrom('toy')
.innerJoin('pet', 'pet.id', 'toy.pet_id')
.select((eb) => eb.fn.countAll('toy').as('num_toys'))
.execute()
The generated SQL (PostgreSQL):
select count("toy".*) as "num_toys"
from "toy" inner join "pet" on "pet"."id" = "toy"."pet_id"
You can limit table range to only tables participating in current query:
db.selectFrom('toy')
.innerJoin('pet', 'pet.id', 'toy.pet_id')
.select((eb) => eb.fn.countAll('toy').as('num_toys'))
.execute()
Creates a json_agg function call.
This function is only available on PostgreSQL.
db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select((eb) => ['first_name', eb.fn.jsonAgg('pet').as('pets')])
.groupBy('person.first_name')
.execute()
The generated SQL (PostgreSQL):
select "first_name", json_agg("pet") as "pets"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."first_name"
Calls the max function for the column or expression given as the argument.
This sql function calculates the maximum value for a given column.
For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.
If this function is used in a select statement, the type of the selected
expression will be the referenced column's type. This is because the result
is within the column's value range.
db.selectFrom('toy')
.select((eb) => eb.fn.max('price').as('max_price'))
.execute()
The generated SQL (PostgreSQL):
select max("price") as "max_price" from "toy"
You can limit column range to only columns participating in current query:
db.selectFrom('toy')
.select((eb) => eb.fn.max('price').as('max_price'))
.execute()
Sometimes a null is returned, e.g. when row count is 0, and no group by
was used. It is highly recommended to include null in the output type union
and handle null values in post-execute code, or wrap the function with a coalesce
function.
db.selectFrom('toy')
.select((eb) => eb.fn.max<number | null>('price').as('max_price'))
.execute()
Calls the min function for the column or expression given as the argument.
This sql function calculates the minimum value for a given column.
For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.
If this function is used in a select statement, the type of the selected
expression will be the referenced column's type. This is because the result
is within the column's value range.
db.selectFrom('toy')
.select((eb) => eb.fn.min('price').as('min_price'))
.execute()
The generated SQL (PostgreSQL):
select min("price") as "min_price" from "toy"
You can limit column range to only columns participating in current query:
db.selectFrom('toy')
.select((eb) => eb.fn.min('price').as('min_price'))
.execute()
Sometimes a null is returned, e.g. when row count is 0, and no group by
was used. It is highly recommended to include null in the output type union
and handle null values in post-execute code, or wrap the function with a coalesce
function.
db.selectFrom('toy')
.select((eb) => eb.fn.min<number | null>('price').as('min_price'))
.execute()
Calls the sum function for the column or expression given as the argument.
This sql function sums the values of a given column.
For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.
db.selectFrom('toy')
.select((eb) => eb.fn.sum('price').as('total_price'))
.execute()
The generated SQL (PostgreSQL):
select sum("price") as "total_price" from "toy"
You can limit column range to only columns participating in current query:
db.selectFrom('toy')
.select((eb) => eb.fn.sum('price').as('total_price'))
.execute()
If this function is used in a select statement, the type of the selected
expression will be number | string by default. This is because Kysely can't know the
type the db driver outputs. Sometimes the output can be larger than the largest
javascript number and a string is returned instead. Most drivers allow you
to configure the output type of large numbers and Kysely can't know if you've
done so.
You can specify the output type of the expression by providing the type as the first type argument:
db.selectFrom('toy')
.select((eb) => eb.fn.sum<number>('price').as('total_price'))
.execute()
Sometimes a null is returned, e.g. when row count is 0, and no group by
was used. It is highly recommended to include null in the output type union
and handle null values in post-execute code, or wrap the function with a coalesce
function.
db.selectFrom('toy')
.select((eb) => eb.fn.sum<number | null>('price').as('total_price'))
.execute()
Creates a to_json function call.
This function is only available on PostgreSQL.
db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select((eb) => ['first_name', eb.fn.toJson('pet').as('pet')])
.execute()
The generated SQL (PostgreSQL):
select "first_name", to_json("pet") as "pet"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
Generated using TypeDoc
Helpers for type safe SQL function calls.
You can always use the sql tag to call functions and build arbitrary expressions. This module simply has shortcuts for most common function calls.
Examples
This example shows how to create function calls. These examples also work in any other place (
wherecalls, updates, inserts etc.). The only difference is that you leave out the alias (theascall) if you use these in any other place thanselect.The generated SQL (PostgreSQL):