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, a function declared with defineFn — fall back to guessing from the JavaScript value, which handles strings, numbers, bigints, booleans, dates and null; a string is written as an escaped literal. Anything structured (an array, an object), a symbol or a function fails compilation with a QueryBuilderError (InvalidLiteral) rather than being written as String(value), so an unsupported value that arrives untyped is never written as raw SQL. undefined fails too: as a function argument with InvalidLiteral, and compared against an expression (.gt(undefined)) with InvalidArguments, like any comparison with a missing value.

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

Comparisons

Every Expr<T> carries:

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.

Text search

.contains(value), .startsWith(value) and .endsWith(value) match text, not a pattern. %, _ and \ in the value match themselves: the helper escapes them with a backslash and adds the wildcards. Use them for a search box; use .like() only for a pattern you wrote.

.where(($) => [$.OrgId.eq("org_123"), $.Name.contains("50%_off", { insensitive: true })])
// ClickHouse: … AND events.Name ILIKE '%50\\%\\_off%'
// Postgres:   … AND "events"."Name" ILIKE $1                              -- $1 = '%50\%\_off%'
// SQLite, D1: … AND lower("events"."Name") LIKE lower(?) ESCAPE '\'       -- ? = '%50\%\_off%'

• The value is a string, or a param or expression of one. A string is escaped before it is bound. A param or expression is escaped in SQL (replace(…) three times), because its value is known only when the query runs.
• The backslash is the escape character on every dialect: ClickHouse and Postgres use it by default, and SQLite gets ESCAPE '\', as .like() does there.
• Case-sensitive by default, also on SQLite and D1, whose LIKE ignores ASCII case. There the helpers write GLOB instead ("Name" GLOB ? with ? = '*50%*'), escaping GLOB’s *, ? and [ the same way.
• { insensitive: true } writes ILIKE, or on SQLite and D1 (which have no ILIKE) a LIKE of lower() on both sides. Which characters fold is the database’s choice. ClickHouse folds UTF-8 ('ÉCOLE' ILIKE 'é%' is true). Postgres folds by the database’s LC_CTYPE: a C locale (PGlite’s default) folds ASCII only. SQLite’s lower() folds ASCII only (without the ICU extension).
• An empty value matches every non-NULL row. A NULL column never matches.
• escapeLike(text) is the escape step on its own, for a pattern you build for .like().

(Backed by docs/expressions.md > Text search escapes LIKE wildcards.)

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 and .contains() to search for it. 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.)

An expression passed as an argument keeps its boundary, so that is how to group: $.DurationMs.sub($.Offset.add(1)) is DurationMs - (Offset + 1), and CH.lit(0.5).mul($.DurationMs.sub(1)) is 0.5 * (DurationMs - 1).

(Backed by docs/expressions.md > An arithmetic argument keeps its parentheses.)

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.