Building queries

A query starts with from, fromQuery, or fromUnion and is refined by chaining. Every method returns a new query — nothing mutates.

const base = CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123")])

const limited = base.limit(10) // `base` still has no LIMIT

That makes shared base queries safe to hand around and specialise per caller.

(Backed by docs/queries.md > Queries are immutable.)

select

Two forms.

By column name — output keys match the column names:

CH.from(Events).select("Name", "DurationMs")
// SELECT Name AS Name, DurationMs AS DurationMs FROM events

By callback — for computed expressions, aliases, and aggregates:

CH.from(Events).select(($) => ({
	name: $.Name,
	p95: CH.quantile(0.95)($.DurationMs),
	count: CH.count(),
}))

The object keys become the SQL aliases and the keys of the output row type. select is required: compiling without one raises QueryBuilderDefect, which stays a defect — no request value can remove a select().

Calling select again replaces the previous projection rather than adding to it.

(Backed by docs/queries.md > select by column name.)

where

where takes a callback returning an array of conditions, AND-joined together:

.where(($) => [
	$.OrgId.eq("org_123"),
	$.DurationMs.gt(1000),
])

Entries may be undefined, which drops them — that is what makes optional filters clean. See when / whenTrue.

Calling where again replaces the previous callback. It does not append predicates. Put the complete filter set in one callback, including tenant and time bounds. Both flat conditions and .and() preserve tenant scoping; .or() does not.

The same replacement rule applies to select, groupBy, having, orderBy, limit, offset, and format. Joins and CTEs accumulate. Immutable does not mean additive: a second .where(...) on a shared base can remove its tenant filter.

groupBy

Takes output keys (the aliases from select), not raw column names:

.select(($) => ({ name: $.Name, count: CH.count() }))
.groupBy("name")

having

Filter groups after aggregation. The callback has the input-column accessor, so either repeat an aggregate expression or reference an output alias with CH.dynamicColumn("alias", type):

const query = CH.from(Events)
	.select(($) => ({ name: $.Name, count: CH.count() }))
	.where(($) => [$.OrgId.eq(CH.param.string("orgId"))])
	.groupBy("name")
	.having(() => [CH.dynamicColumn("count", T.uint64).gte(CH.param.int("minimumCount"))])

Compile with { orgId: "org_123", minimumCount: 10 } to emit HAVING count >= 10. Use WHERE for row filters and HAVING for aggregate filters. A tenant predicate in HAVING does not establish tenant scope.

The complete HAVING recipe is checked directly from Markdown.

orderBy

Takes [column, direction] tuples, one per sort key:

.orderBy(["count", "desc"], ["name", "asc"])
// ORDER BY count DESC, name ASC

Passing two bare strings, .orderBy("count", "desc"), is a type error. Untyped callers receive QueryBuilderDefect; use a tuple for each sort key.

(Backed by docs/queries.md > orderBy takes tuples and > orderBy rejects a bare string.)

limit / offset

.limit(50).offset(100)

Both take numbers, not param.* expressions, and are rounded with Math.round before emission. That is not input validation: reject non-finite, negative, or fractional values at your request boundary, and enforce an application maximum. Use a stable orderBy when paging; Recipes shows where an offset is appropriate.

format

.format("JSON") // appends `FORMAT JSON`

Accepts "JSON" or "JSONEachRow". Most clients set the format themselves; use this only when you are sending raw SQL somewhere that does not.

withCTE

See Unions and CTEs.

Route and scope declarations

.route("ingest") and .crossTenant() attach metadata to the compiled result rather than changing the SQL. Both are covered in Tenant scoping.

Compiling

const compiled = CH.compileUnsafe(query, params)

compile is an alias of compileCH; both are exported. Unions compile with compileUnion. See Params and compilation.