ClickHouse Filter Operators in TypeScript — WHERE Conditions with hypequery

Filtering ClickHouse from TypeScript is mostly a choice between two styles: parameterized SQL strings with @clickhouse/client, or a typed .where() chain in hypequery. Both are valid. The difference is how much query structure and type checking you want the compiler to carry for you.

Raw client: query_params

The official ClickHouse Node.js client uses query_params for parameterized queries. The parameter syntax is {name: Type} inside the SQL string:

This is safe — the {name: Type} syntax prevents SQL injection. The ClickHouse server receives the query template and the parameters separately and substitutes them server-side.

The friction is the boilerplate. Every filter requires three things: a placeholder in the SQL string with the right ClickHouse type name, an entry in query_params, and manual type annotation on the result. Adding or removing a filter means editing the SQL string and the params object in sync. The TypeScript compiler doesn't verify that {tenant_id: UInt32} in the SQL string matches what's in query_params.

String interpolation without query_params is unsafe — never do this:

Always use query_params or {name: Type} syntax when working with the raw client.

hypequery's typed .where() operators

hypequery wraps the same ClickHouse client underneath but exposes filters as typed method calls. The schema generator creates column types from your actual ClickHouse schema, so TypeScript knows that tenant_id is UInt32 and that email is String.

The filter operators available in hypequery are the builder's typed operator strings:

| Operator | SQL equivalent | Example | |----------|---------------|---------| | 'eq' | col = val | .where('status', 'eq', 'active') | | 'neq' | col != val | .where('status', 'neq', 'deleted') | | 'gt' | col > val | .where('amount', 'gt', 100) | | 'lt' | col < val | .where('amount', 'lt', 1000) | | 'gte' | col >= val | .where('created_at', 'gte', from) | | 'lte' | col <= val | .where('created_at', 'lte', to) | | 'like' | col LIKE val | .where('email', 'like', '%@company.com') | | 'in' | col IN (...) | .where('status', 'in', ['active', 'trial']) | | 'notIn' | col NOT IN (...) | .where('plan', 'notIn', ['free', 'cancelled']) |

Basic Usage

First, generate your schema types:

Then build typed queries:

Multiple .where() calls are combined with AND. For OR conditions, use .orWhere() or an expression builder.

IN and NOT IN Filters

Pass an array as the value:

Empty arrays in 'in' conditions will produce a SQL error — guard against this if the array comes from user input.

LIKE Filters

ClickHouse LIKE uses % as wildcard. The 'like' operator passes the pattern through directly:

For case-insensitive matching, use ilike in raw SQL — hypequery's 'like' operator is case-sensitive, matching ClickHouse's default behavior.

OR Conditions and Expressions

When a filter cannot be expressed as a straight AND chain, use .orWhere() or an expression builder:

If you specifically need ClickHouse PREWHERE, that is still a raw SQL concern today rather than a dedicated builder helper. See PREWHERE vs WHERE for where that tradeoff matters.

Type Safety: Why It Matters

hypequery's operators are type-checked against the column type from the schema. If tenant_id is UInt32 in ClickHouse, passing a string throws a TypeScript error at build time, not a runtime error from ClickHouse. If created_at is DateTime, you can't accidentally pass a number.

Compare the two approaches for a query with five filter conditions:

The hypequery version is shorter, the column names are checked against the schema, and the operator strings are a closed union type — a typo like '===' fails TypeScript compilation rather than producing a runtime SQL error.

---

Related: ClickHouse Query Builder · ClickHouse TypeScript Guide · hypequery vs @clickhouse/client