Last Updated: 3/10/2026

kysely

• FunctionModule

Interface FunctionModule

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 

interface FunctionModule<DB, TB extends keyof DB> {
    agg<
        O,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        name: string,
        args?: readonly RE[],
    ): AggregateFunctionBuilder<DB, TB, O>;
    any<RE extends string>(
        expr: RE,
    ): Exclude<ExtractTypeFromReferenceExpression<DB, TB, RE>, null> extends readonly I[]
        ? ExpressionWrapper<DB, TB, I>
        : KyselyTypeError<“any(expr) call failed: expr must be an array”>;
    any<T>(
        subquery: SelectQueryBuilderExpression<Record<string, T>>,
    ): ExpressionWrapper<DB, TB, T>;
    any<T>(expr: Expression<readonly T[]>): ExpressionWrapper<DB, TB, T>;
    avg<
        O extends null
        | string
        | number = string | number,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        expr: RE,
    ): AggregateFunctionBuilder<DB, TB, O>;
    coalesce<
        V1 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
    >(
        v1: V1,
    ): ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce1<DB, TB, V1>>;
    coalesce<
        V1 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V2 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
    >(
        v1: V1,
        v2: V2,
    ): ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce2<DB, TB, V1, V2>>;
    coalesce<
        V1 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V2 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V3 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
    >(
        v1: V1,
        v2: V2,
        v3: V3,
    ): ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce3<DB, TB, V1, V2, V3>>;
    coalesce<
        V1 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V2 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V3 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V4 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
    >(
        v1: V1,
        v2: V2,
        v3: V3,
        v4: V4,
    ): ExpressionWrapper<
        DB,
        TB,
        ExtractTypeFromCoalesce4<DB, TB, V1, V2, V3, V4>,
    >;
    coalesce<
        V1 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V2 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V3 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V4 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
        V5 extends
            | string
            | Expression<any>
            | DynamicReferenceBuilder<any>
            | SelectQueryBuilderExpression<Record<string, any>>
            | OperandExpressionFactory<DB, TB, any>,
    >(
        v1: V1,
        v2: V2,
        v3: V3,
        v4: V4,
        v5: V5,
    ): ExpressionWrapper<
        DB,
        TB,
        ExtractTypeFromCoalesce5<DB, TB, V1, V2, V3, V4, V5>,
    >;
    count<
        O extends string
        | number
        | bigint,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        expr: RE,
    ): AggregateFunctionBuilder<DB, TB, O>;
    countAll<
        O extends string
        | number
        | bigint,
        T extends string | number | symbol = TB,
    >(
        table: T,
    ): AggregateFunctionBuilder<DB, TB, O>;
    countAll<O extends string | number | bigint>(): AggregateFunctionBuilder<
        DB,
        TB,
        O,
    >;
    jsonAgg<T extends string | Expression<unknown>>(
        table: T,
    ): AggregateFunctionBuilder<
        DB,
        TB,
        T extends TB
            ? Simplify<ShallowDehydrateObject<Selectable<DB[T<T>]>>>[]
            : T extends Expression<O> ? Simplify<ShallowDehydrateObject<O>>[] : never,
    >;
    jsonAgg<RE extends string>(
        column: RE,
    ): AggregateFunctionBuilder<
        DB,
        TB,
        | null
        | ShallowDehydrateValue<
            SelectType<ExtractTypeFromStringReference<DB, TB, RE>>,
        >[],
    >;
    max<
        O extends null
        | string
        | number
        | bigint
        | Date = never,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        expr: RE,
    ): AggregateFunctionBuilder<
        DB,
        TB,
        IsNever<O> extends true
            ? ExtractTypeFromReferenceExpression<
                DB,
                TB,
                RE,
                string
                | number
                | bigint
                | Date,
            >
            : O,
    >;
    min<
        O extends null
        | string
        | number
        | bigint
        | Date = never,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        expr: RE,
    ): AggregateFunctionBuilder<
        DB,
        TB,
        IsNever<O> extends true
            ? ExtractTypeFromReferenceExpression<
                DB,
                TB,
                RE,
                string
                | number
                | bigint
                | Date,
            >
            : O,
    >;
    sum<
        O extends null
        | string
        | number
        | bigint = string | number | bigint,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        expr: RE,
    ): AggregateFunctionBuilder<DB, TB, O>;
    toJson<T extends string | Expression<unknown>>(
        table: T,
    ): ExpressionWrapper<
        DB,
        TB,
        T extends TB
            ? Simplify<ShallowDehydrateObject<Selectable<DB[T<T>]>>>
            : T extends Expression<O> ? Simplify<ShallowDehydrateObject<O>> : never,
    >;
    <
        O,
        RE extends
        
                | string
                | Expression<any>
                | DynamicReferenceBuilder<any>
                | SelectQueryBuilderExpression<Record<string, any>>
                | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,
    >(
        name: string,
        args?: readonly RE[],
    ): ExpressionWrapper<DB, TB, O>;
}

Type Parameters

• DB

• TB extends keyof DB

• FunctionModule<
      O,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      name: string,
      args?: readonly RE[],
  ): ExpressionWrapper<DB, TB, O>

  Creates a function call.

  To create an aggregate function call, use FunctionModule.agg.

  Examples

  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()

  Type Parameters

  • O
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • name: string
  • Optionalargs: readonly RE[]

  Returns ExpressionWrapper<DB, TB, O>

Index

Methods

agg any avg coalesce count countAll jsonAgg max min sum toJson

Methods

agg

• agg<
      O,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      name: string,
      args?: readonly RE[],
  ): AggregateFunctionBuilder<DB, TB, O>

  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

  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" 

  Type Parameters

  • O
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • name: string
  • Optionalargs: readonly RE[]

  Returns AggregateFunctionBuilder<DB, TB, O>

any

• any<RE extends string>(
      expr: RE,
  ): Exclude<ExtractTypeFromReferenceExpression<DB, TB, RE>, null> extends readonly I[]
      ? ExpressionWrapper<DB, TB, I>
      : KyselyTypeError<“any(expr) call failed: expr must be an array”>

  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[]:

  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") 

  Type Parameters

  • RE extends string

  Parameters

  • expr: RE

  Returns Exclude<ExtractTypeFromReferenceExpression<DB, TB, RE>, null> extends readonly I[]     ? ExpressionWrapper<DB, TB, I>     : KyselyTypeError<“any(expr) call failed: expr must be an array”>

• any<T>(
      subquery: SelectQueryBuilderExpression<Record<string, T>>,
  ): ExpressionWrapper<DB, TB, T>

  Type Parameters

  • T

  Parameters

  • subquery: SelectQueryBuilderExpression<Record<string, T>>

  Returns ExpressionWrapper<DB, TB, T>

• any<T>(expr: Expression<readonly T[]>): ExpressionWrapper<DB, TB, T>

  Type Parameters

  • T

  Parameters

  • expr: Expression<readonly T[]>

  Returns ExpressionWrapper<DB, TB, T>

avg

• avg<
      O extends null
      | string
      | number = string | number,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      expr: RE,
  ): AggregateFunctionBuilder<DB, TB, O>

  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

  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()

  Type Parameters

  • O extends null | string | number = string | number
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • expr: RE

  Returns AggregateFunctionBuilder<DB, TB, O>

coalesce

• coalesce<
      V1 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,

  (
      v1: V1,
  ): ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce1<DB, TB, V1>>

  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

  import { sql } from  'kysely' await  db. selectFrom('person') . select((eb) =>  eb. fn. coalesce('nullable_column', sql. lit('')). as('column')) . where('first_name', '=', 'Jessie') . execute()

  The generated SQL (PostgreSQL):

  select coalesce("nullable_column", '') 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 

  Type Parameters

  • V1 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>

  Parameters

  • v1: V1

  Returns ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce1<DB, TB, V1>>

• coalesce<
      V1 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V2 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,

  (
      v1: V1,
      v2: V2,
  ): ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce2<DB, TB, V1, V2>>

  Type Parameters

  • V1 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V2 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>

  Parameters

  • v1: V1
  • v2: V2

  Returns ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce2<DB, TB, V1, V2>>

• coalesce<
      V1 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V2 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V3 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,

  (
      v1: V1,
      v2: V2,
      v3: V3,
  ): ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce3<DB, TB, V1, V2, V3>>

  Type Parameters

  • V1 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V2 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V3 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>

  Parameters

  • v1: V1
  • v2: V2
  • v3: V3

  Returns ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce3<DB, TB, V1, V2, V3>>

• coalesce<
      V1 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V2 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V3 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V4 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,

  (
      v1: V1,
      v2: V2,
      v3: V3,
      v4: V4,
  ): ExpressionWrapper<
      DB,
      TB,
      ExtractTypeFromCoalesce4<DB, TB, V1, V2, V3, V4>,

  Type Parameters

  • V1 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V2 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V3 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V4 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>

  Parameters

  • v1: V1
  • v2: V2
  • v3: V3
  • v4: V4

  Returns ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce4<DB, TB, V1, V2, V3, V4>>

• coalesce<
      V1 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V2 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V3 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V4 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,
      V5 extends
          | string
          | Expression<any>
          | DynamicReferenceBuilder<any>
          | SelectQueryBuilderExpression<Record<string, any>>
          | OperandExpressionFactory<DB, TB, any>,

  (
      v1: V1,
      v2: V2,
      v3: V3,
      v4: V4,
      v5: V5,
  ): ExpressionWrapper<
      DB,
      TB,
      ExtractTypeFromCoalesce5<DB, TB, V1, V2, V3, V4, V5>,

  Type Parameters

  • V1 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V2 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V3 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V4 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>
  • V5 extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any>

  Parameters

  • v1: V1
  • v2: V2
  • v3: V3
  • v4: V4
  • v5: V5

  Returns ExpressionWrapper<DB, TB, ExtractTypeFromCoalesce5<DB, TB, V1, V2, V3, V4, V5>>

count

• count<
      O extends string
      | number
      | bigint,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      expr: RE,
  ): AggregateFunctionBuilder<DB, TB, O>

  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

  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()

  Type Parameters

  • O extends string | number | bigint
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • expr: RE

  Returns AggregateFunctionBuilder<DB, TB, O>

countAll

• countAll<
      O extends string
      | number
      | bigint,
      T extends string | number | symbol = TB,

  (
      table: T,
  ): AggregateFunctionBuilder<DB, TB, O>

  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

  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" 

  Type Parameters

  • O extends string | number | bigint
  • T extends string | number | symbol = TB

  Parameters

  • table: T

  Returns AggregateFunctionBuilder<DB, TB, O>

• countAll<O extends string | number | bigint>(): AggregateFunctionBuilder<
      DB,
      TB,
      O,

  Type Parameters

  • O extends string | number | bigint

  Returns AggregateFunctionBuilder<DB, TB, O>

jsonAgg

• jsonAgg<T extends string | Expression<unknown>>(
      table: T,
  ): AggregateFunctionBuilder<
      DB,
      TB,
      T extends TB
          ? Simplify<ShallowDehydrateObject<Selectable<DB[T<T>]>>>[]
          : T extends Expression<O> ? Simplify<ShallowDehydrateObject<O>>[] : never,

  Creates a json_agg function call.

  This is only supported by some dialects like PostgreSQL.

  Examples

  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" 

  Type Parameters

  • T extends string | Expression<unknown>

  Parameters

  • table: T

  Returns AggregateFunctionBuilder<     DB,     TB,     T extends TB         ? Simplify<ShallowDehydrateObject<Selectable<DB[T<T>]>>>[]         : T extends Expression<O> ? Simplify<ShallowDehydrateObject<O>>[] : never, >

• jsonAgg<RE extends string>(
      column: RE,
  ): AggregateFunctionBuilder<
      DB,
      TB,
      | null
      | ShallowDehydrateValue<
          SelectType<ExtractTypeFromStringReference<DB, TB, RE>>,
      >[],

  Type Parameters

  • RE extends string

  Parameters

  • column: RE

  Returns AggregateFunctionBuilder<     DB,     TB,     | null     | ShallowDehydrateValue<         SelectType<ExtractTypeFromStringReference<DB, TB, RE>>,     >[], >

max

• max<
      O extends null
      | string
      | number
      | bigint
      | Date = never,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      expr: RE,
  ): AggregateFunctionBuilder<
      DB,
      TB,
      IsNever<O> extends true
          ? ExtractTypeFromReferenceExpression<
              DB,
              TB,
              RE,
              string
              | number
              | bigint
              | Date,
          >
          : O,

  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

  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()

  Type Parameters

  • O extends null | string | number | bigint | Date = never
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • expr: RE

  Returns AggregateFunctionBuilder<     DB,     TB,     IsNever<O> extends true         ? ExtractTypeFromReferenceExpression<             DB,             TB,             RE,             string             | number             | bigint             | Date,         >         : O, >

min

• min<
      O extends null
      | string
      | number
      | bigint
      | Date = never,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      expr: RE,
  ): AggregateFunctionBuilder<
      DB,
      TB,
      IsNever<O> extends true
          ? ExtractTypeFromReferenceExpression<
              DB,
              TB,
              RE,
              string
              | number
              | bigint
              | Date,
          >
          : O,

  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

  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()

  Type Parameters

  • O extends null | string | number | bigint | Date = never
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • expr: RE

  Returns AggregateFunctionBuilder<     DB,     TB,     IsNever<O> extends true         ? ExtractTypeFromReferenceExpression<             DB,             TB,             RE,             string             | number             | bigint             | Date,         >         : O, >

sum

• sum<
      O extends null
      | string
      | number
      | bigint = string | number | bigint,
      RE extends
      
              | string
              | Expression<any>
              | DynamicReferenceBuilder<any>
              | SelectQueryBuilderExpression<Record<string, any>>
              | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>,

  (
      expr: RE,
  ): AggregateFunctionBuilder<DB, TB, O>

  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

  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()

  Type Parameters

  • O extends null | string | number | bigint = string | number | bigint
  • RE extends
        | string
        | Expression<any>
        | DynamicReferenceBuilder<any>
        | SelectQueryBuilderExpression<Record<string, any>>
        | OperandExpressionFactory<DB, TB, any> = ReferenceExpression<DB, TB>

  Parameters

  • expr: RE

  Returns AggregateFunctionBuilder<DB, TB, O>

toJson

• toJson<T extends string | Expression<unknown>>(
      table: T,
  ): ExpressionWrapper<
      DB,
      TB,
      T extends TB
          ? Simplify<ShallowDehydrateObject<Selectable<DB[T<T>]>>>
          : T extends Expression<O> ? Simplify<ShallowDehydrateObject<O>> : never,

  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" 

  Type Parameters

  • T extends string | Expression<unknown>

  Parameters

  • table: T

  Returns ExpressionWrapper<     DB,     TB,     T extends TB         ? Simplify<ShallowDehydrateObject<Selectable<DB[T<T>]>>>         : T extends Expression<O> ? Simplify<ShallowDehydrateObject<O>> : never, >

Settings

Member Visibility

On This Page

ExamplesExamplesMethods

agg

• • • Examples

any

• • • Examples

avg

• • • Examples

coalesce

• • • Examples

count

• • • Examples

countAll

• • • Examples

jsonAgg

• • • Examples

max

• • • Examples

min

• • • Examples

sum

• • • Examples

toJson