Creates a function call.
To create an aggregate function call, use FunctionModule.agg.
await db.selectFrom('person')
.selectAll('person')
.where(db.fn('upper', ['first_name']), '=', 'JENNIFER')
.execute()
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:
import { sql } from 'kysely'
await db.selectFrom('person')
.selectAll('person')
.where(sql<string>`upper(first_name)`, '=', 'JENNIFER')
.execute()
Optionalargs: 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.
await db.selectFrom('person')
.select(({ fn }) => [
fn.agg<number>('rank').over().as('rank'),
fn.agg<string>('group_concat', ['first_name']).distinct().as('first_names')
])
.execute()
The generated SQL (MySQL):
select rank() over() as "rank",
group_concat(distinct "first_name") as "first_names"
from "person"
Optionalargs: 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[]:
await db.selectFrom('person')
.selectAll('person')
.where((eb) => eb(
eb.val('Jen'), '=', eb.fn.any('person.nicknames')
))
.execute()
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.
await 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"
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:
await 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.
await 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.
import { sql } from 'kysely'
await db.selectFrom('person')
.select((eb) => eb.fn.coalesce('nullable_column', sql.lit('<unknown>')).as('column'))
.where('first_name', '=', 'Jessie')
.execute()
The generated SQL (PostgreSQL):
select coalesce("nullable_column", '<unknown>') as "column" from "person" where "first_name" = $1
You can combine this function with other helpers in this module:
await db.selectFrom('person')
.select((eb) => eb.fn.coalesce(eb.fn.avg<number | null>('age'), eb.lit(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.
await 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:
await db.selectFrom('toy')
.select((eb) => eb.fn.count<number>('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.
await 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:
await 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:
await 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"
Creates a json_agg function call.
This is only supported by some dialects like PostgreSQL.
You can use it on table expressions:
await 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"
or on columns:
await db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select((eb) => [
'first_name',
eb.fn.jsonAgg('pet.name').as('pet_names'),
])
.groupBy('person.first_name')
.execute()
The generated SQL (PostgreSQL):
select "first_name", json_agg("pet"."name") AS "pet_names"
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.
await 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"
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.
await 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.
await 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"
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.
await 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.
await 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"
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:
await 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.
await 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.
await 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"
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):