import { type kysely } from "https://deno.land/x/kysely_deno_postgres_dialect@v0.27.1/mod.ts";
const { FunctionModule } = kysely;
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 (where calls, updates, inserts etc.). The only difference is that you
leave out the alias (the as call) if you use these in any other place than select.
import { sql } from 'kysely'
const result = await db.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select(({ fn, val, ref }) => [
'person.id',
// The `fn` module contains the most common
// functions.
fn.count<number>('pet.id').as('pet_count'),
// You can call any function by calling `fn`
// directly. The arguments are treated as column
// references by default. If you want to pass in
// values, use the `val` function.
fn<string>('concat', [
val('Ms. '),
'first_name',
val(' '),
'last_name'
]).as('full_name_with_title'),
// You can call any aggregate function using the
// `fn.agg` function.
fn.agg<string[]>('array_agg', ['pet.name']).as('pet_names'),
// And once again, you can use the `sql`
// template tag. The template tag substitutions
// are treated as values by default. If you want
// to reference columns, you can use the `ref`
// function.
sql<string>`concat(
${ref('first_name')},
' ',
${ref('last_name')}
)`.as('full_name')
])
.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",
concat($1, "first_name", $2, "last_name") as "full_name_with_title",
array_agg("pet"."name") as "pet_names",
concat("first_name", ' ', "last_name") as "full_name"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."id"
having count("pet"."id") > $3
Type Parameters
Call Signatures
Creates a function call.
To create an aggregate function call, use FunctionModule.agg.
Examples
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')
Methods
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.
Examples
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"
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.
Examples
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.
Examples
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.
Examples
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.
Examples
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()
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.
Examples
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.
Examples
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.
Examples
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()
Calls the any function for the column or expression given as the argument.
The argument must be a subquery or evaluate to an array.
Examples
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")
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"
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"