Skip to main content

Functions and Expressions

Beyond plain column references, @sqb/builder has element types for conditional expressions and common SQL functions — usable anywhere an SQL element is accepted (typically a Select column, but also Insert values, operator operands, and more). Every one of them is chainable with .as(alias) to name the resulting column.

Case​

A CASE WHEN ... THEN ... ELSE ... END expression.

import { Case, Gt, Select } from '@sqb/builder';

Select(Case().when(Gt('age', 16)).then(1).else(0)).from('customers');
// select case when age > 16 then 1 else 0 end from customers

.when(...conditions) sets the pending condition (multiple conditions combine with an implicit And); the next .then(value) commits it as one branch. Chain .when()/.then() pairs for multiple branches, and finish with an optional .else(value). .as(alias) renders as end alias (no AS keyword). Full reference: Case.

Coalesce​

A coalesce(...) expression — the first non-null argument.

import { Coalesce, Select } from '@sqb/builder';

Select(Coalesce('nick_name', 'given_name', "'Unnamed'")).from('customers');
// select coalesce(nick_name, given_name, 'Unnamed') from customers

Full reference: Coalesce.

Count / Max / Min​

Aggregate functions. Count always counts rows (count(*)) — it doesn't take a column argument; Max/Min take one expression each.

import { Count, Max, Min, Select } from '@sqb/builder';

Select('country', Count().as('total'), Max('age').as('oldest'), Min('age').as('youngest'))
  .from('customers')
  .groupBy('country');
// select country, count(*) total, max(age) oldest, min(age) youngest
// from customers group by country

Full reference: Count, Max, Min.

Upper / Lower​

Scalar string functions.

import { Upper, Lower, Select } from '@sqb/builder';

Select(Upper('country_code').as('cc'), Lower('email').as('email_lc')).from('customers');
// select upper(country_code) cc, lower(email) email_lc from customers

Full reference: Upper, Lower.

StringAgg​

A string_agg(field, 'delimiter' [order by ...]) aggregate — concatenates a column's values across grouped rows.

import { StringAgg, Select } from '@sqb/builder';

Select('country', StringAgg('name').delimiter(', ').orderBy('-created_at').as('names'))
  .from('customers')
  .groupBy('country');
// select country, string_agg(name,', ' order by created_at desc) names
// from customers group by country

.orderBy() here accepts the same +/-/asc/desc string syntax as Select#orderBy(). Full reference: StringAgg.

Sequence​

A sequence-getter expression — nextval('name') or currval('name').

import { Sequence, Insert } from '@sqb/builder';

Insert('customers', { id: Sequence('customers_id_seq', true), name: 'Jane' });
// insert into customers (id, name) values (nextval('customers_id_seq'), 'Jane')

The rendered nextval(...)/currval(...) syntax is PostgreSQL-flavored — a dialect extension can override it for databases with different sequence syntax (e.g. Oracle's seq.NEXTVAL). See The Dialect Plugin System. Full reference: Sequence.

See also​