ClickHouse quirks

This page is the answer to "what does Quarry actually support, and what should I know about ClickHouse before relying on the result?" — in a single matrix you can scan, plus a section on the ClickHouse-specific behaviors that catch people off-guard.

For the runtime type story (UInt64 → string, etc.), see Runtime semantics instead.

Supported clauses matrix

The current direction is corpus-driven — implement what real queries actually need first, not exhaustive coverage of the ClickHouse spec.

Quirks that catch people off-guard

LEFT JOIN does not produce nulls by default

This is the single biggest ClickHouse footgun for people coming from PostgreSQL or other databases. Without join_use_nulls = 1, unmatched right-side columns come back as their type defaults:

Quarry explicitly requests that default behavior during execution and its typing follows it — left-joined columns are typed as the actual column type, not T | null. For nullable right-side results, use the typed operation:

db.selectFrom("users as u")
  .leftJoinNullable("event_logs as e", "u.id", "e.user_id")
  .select("u.id", "e.event_type");

See Runtime semantics for the longer version.

PREWHERE is a separate clause

ClickHouse's PREWHERE runs before the main WHERE, and it can dramatically reduce how many rows have to be read for the rest of the query. Use it for highly selective predicates over indexed columns:

db
  .selectFrom("event_logs as e")
  .prewhere("e.event_date", "=", "2025-01-01")
  .where("e.event_type", "=", "signup");

Quarry treats prewhere and where as completely separate clauses with the same shape. There is no automatic promotion — you decide which one a condition belongs in based on your data.

FINAL belongs to the table source, not the query

ClickHouse FINAL is a property of the table you are reading from, not of the surrounding query. When you only have one table, .final() on the builder is fine:

db.selectFrom("event_logs as e").final();

But in a join, the table source itself has to carry the FINAL flag, so you build it explicitly:

db
  .selectFrom("users as u")
  .innerJoin(
    db.table("event_logs").as("e").final(),
    "u.id",
    "e.user_id",
  );

Calling .final() on a query whose source is a subquery throws — FINAL is not meaningful there.

SETTINGS is a query-level feature

ClickHouse lets you attach SETTINGS k = v, k = v to a query to override session-level settings for that one query. Quarry exposes this as .settings({...}):

.settings({ max_threads: 4 })

Multiple .settings(...) calls are merged. Values are coerced to the right SQL form by the compiler (strings get single-quoted, booleans become 1/0).

Bare null is rejected

Quarry refuses to accept null as a predicate value:

db
  .selectFrom("users as u")
  .where("u.email", "=", null);    // throws at runtime

The error is:

Bare null predicate values are not supported. Use whereNull()/whereNotNull() or param(null, "Nullable(...)").

This is intentional. ClickHouse NULL semantics differ from ANSI SQL in both joins and equality, so silently accepting null would do the wrong thing roughly half the time. The library forces you to be explicit:

• Use whereNull(column) / whereNotNull(column) for null checks.
• Use param(null, "Nullable(String)") (or whatever the column type is) if you really do want to bind a typed null.

Typed parameter placeholders

Quarry compiles bound values into ClickHouse's typed placeholder syntax:

WHERE u.id = {p0:Int64}

The driver substitutes {p0:Int64} server-side using a separate parameter map — the value never gets concatenated into the SQL string. Type inference picks the placeholder type from the JS value (see architecture), and you can override it with param(value, "Type") when inference would be wrong.

The most common time to reach for param(...) is for date/time values, unsigned integers (so the placeholder says UInt64 instead of Int64), and decimal types.

Transient select failures

ClickHouse Cloud can temporarily fail existing or new connections during events such as autoscaling or replica changes. If you want Quarry to retry select queries through that window, configure retries on the DB handle:

const db = createClickHouseDB<DB>({
  client,
  retries: {
    attempts: 3,
    delayMs: 100,
  },
});

Retries use a fixed delay and attempts includes the initial call. Quarry only retries likely transient select execution failures. It deliberately does not retry inserts, commands, or ordinary query errors.

Result types come from JSONEachRow

execute() and stream() call the driver with format: "JSONEachRow", which means the runtime type of every column is whatever @clickhouse/client produces under that format. That is why UInt64 and count() come back as string, DateTime64 comes back as string, and Decimal comes back as number.

That last detail is important: for larger or higher-precision decimals, returning a JavaScript number can lose precision even if ClickHouse stored the exact decimal value correctly.

If you fetch the same query through a different format, the runtime types will be different and Quarry's typed result will lie. The library is built around JSONEachRow. The one deliberate exception is executeWithTotals(), which uses ClickHouse's JSON document format so it can return the separate totals object alongside data.

Value inserts use JSONEachRow

For values([...]), Quarry uses format: "JSONEachRow". Your row objects are sent to the driver's insert() method directly — they never get serialized into the SQL query. This is what makes large batch inserts efficient.

fromSelect(...) is the other insert path. It compiles to INSERT INTO ... SELECT ... and runs through the driver's command API instead, which keeps ETL-style transforms entirely inside ClickHouse.

Things that are deliberately not in scope

These are not "missing features" — they are decisions:

• Migrations and schema diff. Quarry is a query builder, not a schema manager. If Quarry ever grows migration tooling, it should remain a separate concern.
• A generic SQL abstraction across multiple databases. Quarry models ClickHouse directly. There is no plan to make it portable.
• An ORM-style "model" layer. No entities, no relationships, no lazy loading. Just typed query construction.
• Hiding ClickHouse runtime types behind safer-looking JS types. The library exposes what the driver returns, not what users wish it returned. See Runtime semantics.