ExpressionBuilder

The expression builder is the API you reach for when the simple (column, operator, value) form of where, having, etc. is not enough. You access it through callbacks:

db.selectFrom("users as u").where((eb) => /* eb is the expression builder */);
db.selectFrom("users as u").selectExpr((eb) => [/* projections */]);

The builder is parameterised by the scope of the surrounding query, so all column references and function arguments are type-checked against the columns currently in scope.

Building expressions

eb.ref(columnRef)

Wraps a column reference as an Expression. The result type matches the column's TypeScript type from the schema.

db
  .selectFrom("users as u")
  .selectExpr((eb) => [eb.ref("u.email")]);

In most places where you can pass eb.ref("u.email") you can also pass the plain string "u.email" directly. ref is useful when you specifically need an Expression<T> value (e.g. to pass into a function helper that takes either a column or an expression).

eb.val(value)

Wraps a JavaScript value as a typed parameterised expression. The compiled SQL gets a {pN:Type} placeholder; the value is sent through the driver's parameter map, not string-interpolated.

import { param } from "@oorestisime/quarry";

db
  .selectFrom("users as u")
  .where((eb) =>
    eb.cmpRef("u.id", "=", "u.id"), /* placeholder */
  )
  .selectExpr((eb) => [eb.val(param(42, "UInt32"))]);

For literal values where Quarry can infer the type, you can pass the JS value directly to most APIs without going through eb.val.

eb.raw(sql)

Inserts raw SQL into the expression. No escaping is performed, so this is an explicit escape hatch — only use it for trusted, hard-coded SQL fragments.

db
  .selectFrom("users as u")
  .selectExpr((eb) => [eb.raw<string>("toString(u.id)").as("id_text")]);

The optional type parameter (<string> above) controls how the result type is treated downstream.

Comparing values

Quarry exposes two comparison helpers on the expression builder. eb.cmp(...) is the general-purpose one you reach for most of the time. eb.cmpRef(...) is a narrower variant for comparing two columns directly.

eb.cmp(left, operator, right)

The callback-form counterpart of where(column, operator, value). Use it inside eb.and([...]) or eb.or([...]) to combine multi-part conditions, or when the left side needs to be an expression rather than a raw column reference.

The left side accepts either a column reference or another Expression. The right side accepts a scalar value, a param(...), another Expression, or a subquery.

db
  .selectFrom("users as u")
  .where((eb) =>
    eb.and([
      eb.cmp("u.email", "!=", ""),
      eb.cmp("u.status", "in", ["active", "pending"]),
      eb.cmp(eb.fn.length("u.tags"), ">", 0),
    ]),
  );

Comparing an expression against a subquery:

const activeIds = db
  .selectFrom("event_logs as e")
  .select("e.user_id")
  .where("e.event_type", "=", "signup");

db
  .selectFrom("users as u")
  .where((eb) => eb.cmp("u.id", "in", activeIds));

Operators allowed: =, !=, >, >=, <, <=, in, not in. When the operator is in / not in, the right side must be an array, a param wrapping an array, or a subquery.

eb.cmpRef(left, operator, right)

Compares two column references. Both must resolve to columns in the current scope. Use this when both sides are columns — for everything else use eb.cmp.

db
  .selectFrom("users as a")
  .innerJoin("users as b", (eb) =>
    eb.and([
      eb.cmpRef("a.id", "=", "b.id"),
      eb.cmpRef("a.email", "=", "b.email"),
    ]),
  );

Operators allowed for cmpRef are restricted to equality and ordering (=, !=, <, <=, >, >=).

Combining expressions

eb.and(expressions)

Wraps an array of expressions in a logical AND.

db
  .selectFrom("users as u")
  .where((eb) =>
    eb.and([
      eb.fn.has("u.tags", "premium"),
      eb.fn.notEmpty("u.email"),
    ]),
  );

eb.or(expressions)

Same shape, logical OR.

db
  .selectFrom("users as u")
  .where((eb) =>
    eb.or([
      eb.fn.has("u.tags", "premium"),
      eb.fn.has("u.tags", "trial"),
    ]),
  );

and and or can be nested freely:

db
  .selectFrom("users as u")
  .where((eb) =>
    eb.and([
      eb.fn.notEmpty("u.email"),
      eb.or([
        eb.fn.has("u.tags", "premium"),
        eb.fn.has("u.tags", "trial"),
      ]),
    ]),
  );

Functions: eb.fn

Every helper under eb.fn.* returns an Expression<T> where T is the runtime TypeScript type for the value, not the ClickHouse type. See runtime semantics for why some 64-bit types come back as string.

You can chain .as("alias") on any Expression to give it a name in the projection.

Aggregates

Every aggregate returns an Expression<T> where T follows the runtime-honest typing rules: 64-bit integer results (count, uniq, sum of UInt64) come back as string; avg always returns number because ClickHouse produces Float64 for it; min / max / any / anyLast preserve the input type; groupArray preserves the input type except that NULL elements are dropped from nullable inputs.

Count and numeric

Distinct

Collection and first-value

Example: combining aggregates

import { param } from "@oorestisime/quarry";

const stats = await db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    "e.user_id",
    eb.fn.count().as("total_events"),
    eb.fn.countIf(eb.cmp("e.event_type", "=", "signup")).as("signups"),
    eb.fn.sum("e.amount").as("total_amount"),
    eb.fn
      .avgIf("e.amount", eb.cmp("e.event_type", "=", "purchase"))
      .as("avg_purchase"),
    eb.fn.uniqExact("e.session_id").as("unique_sessions"),
    eb.fn.groupArray("e.event_type").as("all_event_types"),
  ])
  .groupBy("e.user_id")
  .having("total_events", ">", param(0, "UInt64"))
  .execute();

Type conversions

These mirror the ClickHouse toX family. The result type follows the runtime semantics: 64-bit ints come back as string, decimals come back as number, dates come back as string.

precision for toDateTime64 must be an integer between 0 and 9 (inclusive). scale for toDecimal64 must be 0..18, and for toDecimal128 must be 0..38. Out-of-range values throw at build time.

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    eb.fn.toDate("e.created_at").as("event_date"),
    eb.fn.toDateTime64("e.created_at", 3).as("event_time_ms"),
  ]);

Date and time helpers

These helpers cover the common ClickHouse date/time patterns that show up in analytics queries: current timestamps, bucket boundaries, formatting, relative diffs, relative offsets, and compact integer date keys.

Accepted units for dateDiff, dateAdd, and dateSub are: nanosecond, microsecond, millisecond, second, minute, hour, day, week, month, quarter, and year. Uppercase forms such as "DAY" also work.

Quarry does not yet model ClickHouse INTERVAL as a first-class expression. Use dateAdd(...) / dateSub(...) for typed relative offsets, or the raw expression escape hatch when you specifically need literal INTERVAL ... syntax.

If one side of dateDiff is a literal date/time rather than a column or expression, wrap it with eb.val(param(...)) so Quarry does not treat the string as a column reference.

import { param } from "@oorestisime/quarry";

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    eb.fn.now().as("query_time"),
    eb.fn.today().as("query_date"),
    eb.fn.toStartOfMonth("e.created_at").as("month_start"),
    eb.fn.formatDateTime("e.created_at", "%Y-%m-%d").as("event_day"),
    eb.fn
      .dateDiff("day", eb.fn.toDate("e.created_at"), eb.val(param("2025-01-03", "Date")))
      .as("days_until_cutoff"),
    eb.fn.dateAdd("day", 7, "e.created_at").as("plus_week"),
    eb.fn.toYYYYMM("e.created_at").as("event_yyyymm"),
  ]);

JSON helpers

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    eb.fn.jsonExtractString("e.properties", "campaign").as("campaign"),
  ]);

The key argument is escaped for use as a single-quoted string literal. More JSON helpers will be added — see the roadmap.

Null helpers

isNull / isNotNull follow ClickHouse boolean semantics, so Quarry types them as number (0 / 1).

coalesce(...) is expression-oriented: bare strings are treated as column references, not string literals. Use eb.val(...) when you want a literal fallback, and eb.val(param(...)) if that literal needs an explicit ClickHouse type.

db
  .selectFrom("users as u")
  .selectExpr((eb) => [
    eb.fn.nullIf("u.email", "").as("email_or_null"),
    eb.fn.coalesce("u.nickname", "u.email").as("display_name"),
    eb.fn.coalesce("u.nickname", eb.val("Unknown")).as("display_name_with_fallback"),
    eb.fn.ifNull("u.nickname", "Unknown").as("nickname_or_default"),
  ]);

Array helpers

empty and notEmpty accept any "emptyable" value — arrays, strings, or nullable strings. ClickHouse uses 0/1 for booleans, hence the number return type.

db
  .selectFrom("users as u")
  .where((eb) => eb.fn.hasAny("u.tags", ["premium", "trial"]))
  .selectExpr((eb) => [
    "u.id",
    eb.fn.length("u.tags").as("tag_count"),
  ]);

String helpers

concat requires at least two parts. The pattern for like / ilike follows the ClickHouse LIKE syntax (% and _ wildcards).

db
  .selectFrom("users as u")
  .where((eb) => eb.fn.ilike("u.email", "%@example.com"))
  .selectExpr((eb) => [
    "u.id",
    eb.fn.lower("u.email").as("email_normalized"),
    eb.fn.concat(eb.fn.upper("u.status"), " - ", "u.email").as("label"),
  ]);

lower, upper, and the trim* helpers accept either a column reference or a string-typed Expression. like, ilike, and substring accept the same on the value side and ParamLike<string> on the pattern/length side (so you can pass a literal string, a param(...), or another expression).

The Expression<T> and AliasedExpression<T, Alias> classes

eb.* always returns an Expression<T>. You can give it a name with .as("alias"), which produces an AliasedExpression<T, Alias>. Both are accepted by selectExpr and similar APIs:

const id = (eb: any) => eb.ref("u.id");           // Expression<number>
const idText = (eb: any) =>
  eb.fn.toString("u.id").as("id_text");           // AliasedExpression<string, "id_text">

In normal usage you do not need to construct these classes directly — they are returned to you by the builder.