All expressions need to have this getter for complicated type-related reasons. Simply add this getter for your expression and always return undefined from it:

class SomeExpression<T> implements Expression<T> {
  get expressionType(): T | undefined {
    return undefined
  }
}

The getter is needed to make the expression assignable to another expression only if the types T are assignable. Without this property (or some other property that references T), you could assing Expression<string> to Expression<number>.

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

Returns an aliased version of the function.

In addition to slapping as "the_alias" to the end of the SQL, this method also provides strict typing:

const result = await db
  .selectFrom('person')
  .select(
    (eb) => eb.fn.count<number>('id').as('person_count')
  )
  .executeTakeFirstOrThrow()

// `person_count: number` field exists in the result type.
console.log(result.person_count)

The generated SQL (PostgreSQL):

select count("id") as "person_count"
from "person"

Adds a distinct clause inside the function.

Examples

const result = await db
  .selectFrom('person')
  .select((eb) =>
    eb.fn.count<number>('first_name').distinct().as('first_name_count')
  )
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select count(distinct "first_name") as "first_name_count"
from "person"

Adds a filter clause with a nested where clause after the function.

Similar to WhereInterface's where method.

Also see filterWhereRef.

Examples

Count by gender:

const result = await db
  .selectFrom('person')
  .select((eb) => [
    eb.fn
      .count<number>('id')
      .filterWhere('gender', '=', 'female')
      .as('female_count'),
    eb.fn
      .count<number>('id')
      .filterWhere('gender', '=', 'male')
      .as('male_count'),
    eb.fn
      .count<number>('id')
      .filterWhere('gender', '=', 'other')
      .as('other_count'),
  ])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select
  count("id") filter(where "gender" = $1) as "female_count",
  count("id") filter(where "gender" = $2) as "male_count",
  count("id") filter(where "gender" = $3) as "other_count"
from "person"

Adds a filter clause with a nested where clause after the function, where both sides of the operator are references to columns.

Similar to WhereInterface's whereRef method.

Examples

Count people with same first and last names versus general public:

const result = await db
  .selectFrom('person')
  .select((eb) => [
    eb.fn
      .count<number>('id')
      .filterWhereRef('first_name', '=', 'last_name')
      .as('repeat_name_count'),
    eb.fn.count<number>('id').as('total_count'),
  ])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select
  count("id") filter(where "first_name" = "last_name") as "repeat_name_count",
  count("id") as "total_count"
from "person"

Adds an over clause (window functions) after the function.

Examples

const result = await db
  .selectFrom('person')
  .select(
    (eb) => eb.fn.avg<number>('age').over().as('average_age')
  )
  .execute()

The generated SQL (PostgreSQL):

select avg("age") over() as "average_age"
from "person"

Also supports passing a callback that returns an over builder, allowing to add partition by and sort by clauses inside over.

const result = await db
  .selectFrom('person')
  .select(
    (eb) => eb.fn.avg<number>('age').over(
      ob => ob.partitionBy('last_name').orderBy('first_name', 'asc')
    ).as('average_age')
  )
  .execute()

The generated SQL (PostgreSQL):

select avg("age") over(partition by "last_name" order by "first_name" asc) as "average_age"
from "person"

Creates the OperationNode that describes how to compile this expression into SQL.

If you are creating a custom expression, it's often easiest to use the sql template tag to build the node:

class SomeExpression<T> implements Expression<T> {
  toOperationNode(): OperationNode {
    return sql`some sql here`.toOperationNode()
  }
}