expressions.md

Expressions and conditions

Two brands flow through the DSL. An Expr<T> is anything that evaluates to a value — a column, a literal, a function call. A Condition is a boolean predicate, which is what where collects. Comparison methods turn an Expr into a Condition.

How a value becomes a literal

Comparing a column against a plain value encodes that value through the column's own type, so the literal is whatever ClickHouse expects for that column rather than whatever the JavaScript value looks like:

$.Attributes.eq({ "http.method": "GET" }) // Attributes = map('http.method', 'GET')
$.Tags.eq(["a", "b"])                     // Tags = ['a', 'b']
$.Live.eq(true)                           // Live = 1
$.Timestamp.gte(new Date(...))            // Timestamp >= '2026-01-01 00:00:00'

Testing for NULL

= NULL is never true in SQL, so a comparison does not take null: .eq(null), .in_(null) and the like are type errors, and a null that arrives at runtime fails compilation with a QueryBuilderError. Use .isNull() (or .isNotNull() for present values), which write IS NULL and work on every dialect:

import * as CH from "@maple-dev/effect-orm/clickhouse"

const Notes = CH.table("notes", {
	columns: { Note: CH.nullable(CH.string) },
	engine: CH.engine.mergeTree(),
	orderBy: [],
})
export const compiled = CH.compileUnsafe(CH.from(Notes).select("Note").where(($) => [$.Note.isNull()]))
console.log(compiled.sql) // SELECT Note AS Note FROM notes WHERE Note IS NULL

Invalid literals

A value the column cannot hold fails while the SQL is being built:

$.Count.eq("lots")
// QueryBuilderError { code: "InvalidLiteral" }: column Count: string "lots" is not a valid value

Expressions with no type to read — untypedExpr, an untyped dynamicColumn — fall back to guessing from the JavaScript value, which handles strings, numbers, booleans and dates but nothing structured.

(Backed by src/ch/literal.test.ts.)

Comparisons

Every Expr<T> carries:

Method	SQL
.eq(x) / .neq(x)	= x / != x
.gt(x) / .gte(x)	> x / >= x
.lt(x) / .lte(x)	< x / <= x
.in_(...xs) / .notIn(...xs)	IN (…) / NOT IN (…)
.between(a, b) / .notBetween(a, b)	BETWEEN a AND b / NOT BETWEEN a AND b
.isNull() / .isNotNull()	IS NULL / IS NOT NULL

Each accepts a raw value or another Expr<T>. String literals are escaped; booleans emit as 1 / 0.

in_ carries a trailing underscore because in is a reserved word in JavaScript. An empty list is written as the constant it means, 1 = 0 for in_() and 1 = 1 for notIn(), since IN () is not SQL.

String-only

.like(pattern), .notLike(pattern), .ilike(pattern) are constrained by this to Expr<string>, so calling them on a numeric column is a type error.

Combining conditions

.where(($) => [
	$.OrgId.eq("org_123"),
	$.Name.eq("checkout").or($.Name.eq("cart")),
])
// … WHERE OrgId = 'org_123' AND (Name = 'checkout' OR Name = 'cart')

.and() / .or() parenthesise their result, so precedence is explicit. CH.not(condition) wraps in NOT (…) and is available from both dialect entries and the /expr subpath.

CH.and(...) and CH.or(...) take any number of conditions, skip undefined ones, and write one flat group. With none left they return undefined, which where skips, so optional filters combine without special cases:

.where(($) => [
	$.OrgId.eq("org_123"),
	CH.or(CH.when(name, (n) => $.Name.eq(n)), CH.when(minMs, (ms) => $.Ms.gte(ms))),
])
// both given -> … AND (Name = 'checkout' OR Ms >= 100); neither -> only the OrgId test

The where array is AND-joined. Tenant scoping preserves evidence through both separate entries, .and() and CH.and(); .or() and CH.or() discard it.

(Backed by docs/expressions.md > Combining conditions with and/or.)

Optional predicates

when and whenTrue return Condition | undefined, and where drops undefined entries. This is how you build filters from optional inputs without string-concatenating SQL:

const build = (nameFilter?: string) =>
	CH.from(Events)
		.select(($) => ({ name: $.Name }))
		.where(($) => [$.OrgId.eq("org_123"), CH.when(nameFilter, (n) => $.Name.eq(n))])

// build("checkout") -> … WHERE OrgId = 'org_123' AND Name = 'checkout'
// build()           -> … WHERE OrgId = 'org_123'

when skips undefined, null, and false, and narrows the value for the callback. whenTrue(flag, () => cond) is the variant for a plain boolean gate.

(Backed by docs/expressions.md > Optional predicates with when.)

Empty lists and wildcard searches

Decide what an empty filter list means before building the query: omit it for “no filter”, or return no rows for “nothing selected”. Do not accidentally turn an empty selection into an unbounded read. The optional-filter recipe shows an explicit choice.

Escaping SQL strings does not escape LIKE wildcards. % and _ in a .like() argument are patterns; use .eq() for exact text. Keep table names, aliases, and raw SQL under application control: value escaping is not an allowlist for identifiers.

Arithmetic

Expr<number> carries .add(), .sub(), .mul(), .div(). Addition, subtraction, and multiplication stay as nullable as their operands. Finite floating-point inputs can still overflow, and ClickHouse encodes the non-finite result as JSON null; a non-Nullable result decodes that null as NaN. sum, sumIf, and toFloat64OrZero behave the same way. Use ifNotFinite when a finite fallback is required.

These do not parenthesise. Chaining follows SQL operator precedence, not call order:

$.DurationMs.sub(1).div(2)
// DurationMs - 1 / 2   →  DurationMs - (1 / 2)
// NOT (DurationMs - 1) / 2

Order the calls so precedence works in your favour, or bind an intermediate alias in a subquery. This is a deliberate trade — the emitted SQL stays readable — but it is the most common source of quietly wrong numbers.

(Backed by docs/expressions.md > Arithmetic does not parenthesise.)

Division can produce a NULL

.div() and .mod() decode nullably, whatever their operands are. ClickHouse renders 1 / 0 as inf and 0 / 0 as nan, and both come back as JSON null — so a division that meets a zero denominator returns a null the column type has to accept, or the row fails to decode.

Both operators return Expr<number | null> — unless the divisor is a numeric literal of magnitude 1 or more. $.Duration.div(1_000_000) cannot manufacture a null from a finite dividend, so it stays as nullable as $.Duration (and is exactly rounded, where .mul(0.000001) drifts by an ulp on a third of integer inputs). A zero, a literal below 1 (1 / 5e-324 overflows to inf), a plain number, or another expression as the divisor makes the result nullable. Modulo by zero can also raise a ClickHouse error; nullable decoding does not suppress server errors.

When the output must be numeric, guard both non-finite numbers and SQL NULL:

.select(($) => ({
	errorRate: CH.ifNull(CH.ifNotFinite(CH.sum($.Errors).div(CH.sum($.Total)), 0), CH.lit(0)),
}))

CH.ifNotFinite(expr, fallback) replaces nan/inf, but SQL NULL passes through unchanged. CH.ifNull supplies the remaining fallback. CH.nullIf(expr, value) returns Expr<T | null>; for example, CH.sum(x).div(CH.nullIf(CH.sum(y), 0)) keeps a null for an absent denominator.

Addition, subtraction, and multiplication preserve nullable operands in their types and codecs.

(Backed by docs/expressions.md > division decodes nullably and ifNotFinite guards it.)

Literals and raw escape hatches

• lit(value) — an explicit Expr from a string or number. You rarely need it, since comparison methods accept raw values directly.
• rawExpr(sql, type) — an Expr from a SQL string, with the column type it produces.
• untypedExpr<T>(sql) — the same with no type declared; selecting one costs the query its row schema, so it is a separate name rather than an omitted argument.
• rawCond(sql) — a Condition from a SQL string.

Raw helpers interpolate nothing and escape nothing. Never build one from user input. See Extending the DSL.

Aggregates

count(), sum(), avg(), min(), max(), uniq(), groupUniqArray(), argMaxMerge(), and the conditional forms countIf(), sumIf(), avgIf(), minIf(), maxIf(), anyIf().

The *If family takes a Condition as its last argument:

.select(($) => ({
	total: CH.count(),
	slow: CH.countIf($.DurationMs.gt(1000)),
}))
// count() AS total, countIf(DurationMs > 1000) AS slow

quantile is curried, taking the quantile first:

CH.quantile(0.95)($.DurationMs) // quantile(0.95)(DurationMs)

So are the parametric funnel aggregates — the window / pattern is a parameter, the timestamp and step conditions are the arguments:

CH.windowFunnel(3600)($.Timestamp, $.Name.eq("view"), $.Name.eq("signup"))
// windowFunnel(3600)(Timestamp, Name = 'view', Name = 'signup')
CH.windowFunnel(3600, "strict_order")($.Timestamp, …)
CH.sequenceMatch("(?1)(?t<3600)(?2)")($.Timestamp, $.Name.eq("view"), $.Name.eq("signup"))

windowFunnel takes Date, DateTime or an unsigned integer for the timestamp (not DateTime64) and the window is in that column's unit.

(Backed by docs/expressions.md > Conditional aggregation.)

avg, avgIf, and quantile return Expr<number | null> because empty input produces NaN, which ClickHouse serializes as JSON null. Use the guards above when the empty result should be zero.

Conditionals

• if_(cond, then, else) — note the underscore; if is a reserved word.
• multiIf([[cond, value], …], fallback)
• coalesce(...exprs)
• nullIf(expr, value)

Everything else

String, numeric, date/time, array, map, JSON, and window functions are catalogued in the API reference. Anything not wrapped can be declared in one line with defineFn.