Expressions and functions

The expression builder is the callback-based API you reach for when the simple (column, operator, value) form of where and friends is not enough.

You access it by passing a callback to select, where, having, etc.:

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

Explicit parameter types

By default Quarry infers the ClickHouse placeholder type from the JS value. When you need to control it — for example to force DateTime instead of plain String — use param(value, "Type").

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

const rows = await db
  .selectFrom("event_logs as e")
  .select("e.user_id", "e.created_at")
  .where("e.created_at", ">=", param("2025-01-01 00:00:00", "DateTime"))
  .execute();

The string "DateTime" here is a ClickHouse type name, not a TypeScript type. It is the type that ends up inside the {p0:DateTime} placeholder in the generated SQL.

Type conversion helpers

Common ClickHouse cast functions are exposed under eb.fn:

const rows = await db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    eb.fn.toInt32("e.user_id").as("user_id_i32"),
    eb.fn.toDate("e.created_at").as("event_date"),
    eb.fn.toDateTime64("e.created_at", 3).as("event_time_ms"),
    eb.fn.toString("e.user_id").as("user_id_text"),
  ])
  .where((eb) => eb.fn.toUInt32("e.user_id"), ">", 0)
  .execute();

The currently exposed cast helpers are:

The result types reflect the runtime-honest typing story: 64-bit ints return as string because that is what @clickhouse/client actually returns.

Date and time helpers

Common ClickHouse date/time helpers are also exposed under eb.fn:

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

const rows = await 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.toStartOfWeek("e.created_at").as("week_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.dateSub("hour", 1, "e.created_at").as("minus_one_hour"),
    eb.fn.toYYYYMM("e.created_at").as("event_yyyymm"),
  ])
  .execute();

Available date/time helpers:

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

Quarry does not yet have first-class INTERVAL expression nodes. For now, prefer dateAdd(...) and dateSub(...); drop to raw SQL only when you need the literal ClickHouse INTERVAL ... form itself.

formatDateTime(...) intentionally takes a literal format string. For literal date/time arguments in dateDiff(...), use eb.val(param(...)) because bare strings are interpreted as column references in these APIs.

Null checks

ClickHouse NULL semantics are not the same as ANSI SQL when joins are involved (see Runtime semantics). Quarry forces you to be explicit by exposing dedicated helpers instead of null equality.

const withEmail = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .whereNotNull("u.email")
  .execute();

const withoutEmail = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .whereNull("u.email")
  .execute();

Combining conditions

When you need multiple conditions combined with AND or OR inside a where(...) or having(...), use the callback form with eb.cmp(...) and eb.and([...]) / eb.or([...]).

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

eb.cmp(left, operator, right) is the expression-level counterpart of the top-level where(column, operator, value) form. The left side can be a column reference or another expression (including an eb.fn.* call), and the right side accepts values, param(...), expressions, and subqueries.

For comparing two columns directly (both sides are refs), use eb.cmpRef(left, operator, right) instead — it is the narrower sibling.

Array helpers

const rows = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .where((eb) => eb.fn.has("u.tags", "premium"))
  .execute();

const rows = await db
  .selectFrom("users as u")
  .selectExpr((eb) => ["u.id", eb.fn.length("u.tags").as("tag_count")])
  .where((eb) => eb.fn.hasAny("u.tags", ["premium", "trial"]))
  .where((eb) => eb.fn.length("u.tags"), ">", param(0, "UInt64"))
  .execute();

Currently available array helpers:

length() returns ClickHouse UInt64, so under JSONEachRow it comes back as string at runtime. See Runtime semantics for why.

empty(...) and notEmpty(...) are deliberately broader than the rest of this section — they accept any "emptyable" input. That includes arrays, non-null strings, and nullable strings, so the same helper works whether you are checking that a tag list has entries or that an email field is set.

String helpers

const rows = await 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"),
  ])
  .execute();

Available string helpers:

The pattern argument for like / ilike and the parts of concat accept a literal JS string, a param(...), or another expression — you do not need to wrap them in eb.val(...) yourself. ClickHouse string predicates return 0/1, hence the number return type.

Null helpers

const rows = await db
  .selectFrom("users as u")
  .selectExpr((eb) => [
    "u.id",
    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"),
  ])
  .execute();

Available null helpers:

coalesce(...) is the one helper here that is expression-oriented: bare strings are treated as column references. If you want a literal fallback, wrap it with eb.val(...).

JSON helpers

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

Aggregates and the -If combinator

The aggregate surface covers the common statistical and set helpers you reach for in analytics queries: count, sum, avg, min, max, uniq, uniqExact, groupArray, any, anyLast, plus their -If combinators. See the ExpressionBuilder reference for the full list and return types.

const stats = await db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    "e.user_id",
    eb.fn.count().as("total"),
    eb.fn.sum("e.amount").as("total_amount"),
    eb.fn.avg("e.amount").as("avg_amount"),
    eb.fn.uniqExact("e.session_id").as("unique_sessions"),
  ])
  .groupBy("e.user_id")
  .execute();

The -If combinator

ClickHouse's -If combinator takes an aggregate and restricts it to rows where an extra condition is truthy. It is one of the features that makes ClickHouse aggregations dramatically more expressive than standard SQL.

Quarry exposes typed helpers for the common -If variants: countIf, sumIf, avgIf, and uniqIf. The second argument is an expression — typically an eb.cmp(...) call.

const conversion = 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.countIf(eb.cmp("e.event_type", "=", "purchase")).as("purchases"),
    eb.fn
      .sumIf("e.amount", eb.cmp("e.event_type", "=", "purchase"))
      .as("revenue"),
  ])
  .groupBy("e.user_id")
  .execute();

One query gives you total events, signup count, purchase count, and purchase revenue — all computed in a single pass. In standard SQL you would need CASE WHEN ... END expressions inside each aggregate; ClickHouse (and Quarry) lets you write it directly.