SelectQueryBuilder

SelectQueryBuilder is what you get back from db.selectFrom(...). Every method returns a new builder, so chains are immutable and you can fork at any point.

import { createClient } from "@clickhouse/client";
import { createClickHouseDB } from "@oorestisime/quarry";

interface DB {
  users: { id: number; email: string };
  event_logs: { user_id: number; event_type: string; created_at: string };
}

const db = createClickHouseDB<DB>({
  client: createClient({ url: "http://localhost:8123" }),
});

const base = db.selectFrom("users as u").where("u.email", "!=", "");
const recent = base.orderBy("u.id", "desc").limit(10);
       
const recent: SelectQueryBuilder<DB, {
    u: {
        id: number;
        email: string;
    };
}, {}, {}>

Most filtering methods come in two flavors:

• Positional form — where("col", "=", value) — for the common case of comparing one column against one value.
• Callback form — where((eb) => eb.fn.has("u.tags", "pro")) — when you need the expression builder for functions, multi-condition logic, or refs to other columns.

Selecting columns

select(...selections)

Adds named columns to the projection. Each argument is either a column ref ("u.id") or a column-with-alias string ("u.id as user_id"). The result type narrows to exactly the columns you asked for.

const rows = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .execute();
    
SelectQueryBuilder<DB, { u: { id: number; email: string; status: string; }; }, { id: number; email: string; }, { id: QueryColumn<number, number>; email: QueryColumn<string, string>; }>.execute(options?: ClickHouseExecutionOptions): Promise<{
    id: number;
    email: string;
}[]>

selectAll() / selectAll(alias)

selectAll() with no argument is for single-source queries — it expands to SELECT *. The argument-less form is a type error if your query has more than one source.

For joined queries, prefer selectAll(alias) so the resulting columns are unambiguous:

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

selectExpr(callback)

Same as select(...), but the argument is a callback that receives the expression builder. Use this when you need function calls or aliased expressions in the projection.

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    "e.user_id",
    eb.fn.count().as("event_count"),
  ])
  .groupBy("e.user_id");

You can mix plain column refs and expression builder results in the same array.

distinct()

Adds a query-wide DISTINCT modifier. ClickHouse deduplicates on the entire selected row.

db
  .selectFrom("event_logs as e")
  .distinct()
  .select("e.event_type");

This compiles to:

SELECT DISTINCT e.event_type FROM event_logs AS e

With one selected column, that means "distinct values of this column". With multiple selected columns, the distinctness applies to the full combination.

distinctOn(...expressions)

Adds ClickHouse DISTINCT ON (...). Each argument can be either a column ref in scope or an expression-builder callback, matching the accepted shapes for groupBy.

db
  .selectFrom("event_logs as e")
  .distinctOn("e.user_id")
  .select("e.user_id", "e.event_type");

This compiles to:

SELECT DISTINCT ON (e.user_id) e.user_id, e.event_type FROM event_logs AS e

The DISTINCT ON expressions do not have to appear in the SELECT list. They define the deduplication key, not the projected columns.

Filtering: where

where(column, operator, value)

The positional form. Type-checks value against the column's TypeScript type and against the operator (e.g. in requires an array, equality requires a scalar).

db
  .selectFrom("users as u")
  .select("u.id")
  .where("u.email", "=", "alice@example.com");

where(column, operator, subquery)

Pass a SelectQueryBuilder as the right-hand side for IN style predicates:

const activeUsers = db
  .selectFrom("event_logs as e")
  .select("e.user_id")
  .where("e.event_type", "=", "signup");

db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .where("u.id", "in", activeUsers);

where((eb) => expression)

Single-argument callback form. Use this when you need an and/or combination, a function call as the predicate, or anything the positional form cannot express.

db
  .selectFrom("users as u")
  .where((eb) =>
    eb.and([
      eb.cmpRef("u.id", "=", "u.id"),
      eb.fn.has("u.tags", "premium"),
    ]),
  );

where((eb) => expression, operator, value)

Three-argument callback form. The first argument is an expression (typically a function call), the next two are the operator and value to compare against.

db
  .selectFrom("event_logs as e")
  .where((eb) => eb.fn.toUInt32("e.user_id"), ">", 0);

where(expression)

Pass a pre-built Expression object directly. This is useful when an external package or helper function composes a predicate tree and you want to inject it into the query.

import { ExpressionBuilder } from "@oorestisime/quarry";

const eb = new ExpressionBuilder<any>();
const isPremium = eb.fn.has("u.tags", "premium");

db.selectFrom("users as u").select("u.id").where(isPremium);

whereRef(left, operator, right)

Compare two column references. Both sides must be columns in scope.

db
  .selectFrom("users as a")
  .innerJoin("users as b", (eb) => eb.cmpRef("a.id", "=", "b.id"))
  .whereRef("a.email", "=", "b.email");

whereNull(column) / whereNotNull(column)

Use these instead of comparing against null directly. ClickHouse NULL semantics differ from ANSI SQL in joins (see runtime semantics), so Quarry forces you to be explicit.

db.selectFrom("users as u").whereNull("u.email");
db.selectFrom("users as u").whereNotNull("u.email");

Filtering: prewhere

PREWHERE is ClickHouse-specific. It runs before the main WHERE clause and can dramatically reduce the rows that have to be read for the rest of the query. Use it for highly selective predicates over indexed columns.

prewhere(...) accepts the same overloads as where:

• prewhere(column, operator, value)
• prewhere(column, operator, subquery)
• prewhere((eb) => expression)
• prewhere((eb) => expression, operator, value)
• prewhere(expression)

There is also a prewhereRef(left, operator, right) variant matching whereRef.

Repeated prewhere(...) calls append with AND, just like repeated where(...) calls.

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

having(...)

having(...) filters grouped rows. Its column-name form accepts:

• real columns in scope, and
• aliases that you defined earlier in selectExpr(...)

This matches ClickHouse SQL semantics.

import { param } from "@oorestisime/quarry";

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => ["e.user_id", eb.fn.count().as("event_count")])
  .groupBy("e.user_id")
  .having("event_count", ">", param(1, "Int64"));

having accepts the same callback overloads as where, including the pre-built Expression object form.

groupBy(...expressions)

Adds expressions to the GROUP BY clause. Each argument can be either:

• a column reference in scope, or
• a callback that receives the expression builder and returns an expression.

Aliases from selectExpr are still not accepted here; use the real column ref or expression instead.

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => [
    "e.user_id",
    eb.fn.toDate("e.created_at").as("event_date"),
    eb.fn.count().as("c"),
  ])
  .groupBy("e.user_id", (eb) => eb.fn.toDate("e.created_at"));

Joins

innerJoin(source, leftRef, rightRef)

Adds an INNER JOIN. The two refs must resolve to columns in the combined scope (the existing scope plus the new joined source).

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

innerJoin(source, callback)

Multi-condition or expression-based join. The callback receives an expression builder bound to the combined scope.

db
  .selectFrom("users as a")
  .innerJoin("users as b", (eb) =>
    eb.and([
      eb.cmpRef("a.id", "=", "b.id"),
      eb.cmpRef("a.email", "=", "b.email"),
    ]),
  );

leftJoin(source, leftRef, rightRef) / leftJoin(source, callback)

Same shapes as innerJoin.

The source argument can be:

• a "table as alias" string,
• an explicit db.table("name").as("alias") builder (for final() or other source-level options),
• another select query that has been aliased with .as("alias").

leftAntiJoin(source, leftRef, rightRef) / leftAntiJoin(source, callback)

Adds a ClickHouse LEFT ANTI JOIN. Use it when you want rows from the left side only if there is no matching row on the right side.

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

Like leftJoin, ClickHouse still exposes right-side columns if you select them, and unmatched rows come back as ClickHouse default values such as 0, '', or false.

Ordering and pagination

orderBy(column, direction?)

direction defaults to "asc". The column reference is checked against the current scope and any aliases you created with selectExpr.

db
  .selectFrom("event_logs as e")
  .selectExpr((eb) => ["e.user_id", eb.fn.count().as("c")])
  .groupBy("e.user_id")
  .orderBy("c", "desc");

limit(n)

Throws if n is not a non-negative integer. Compiles to LIMIT n.

offset(n)

Throws if n is not a non-negative integer. Compiles to OFFSET n.

ClickHouse-specific clauses

settings(record)

Merges query-level ClickHouse settings.

db
  .selectFrom("users as u")
  .leftJoin("event_logs as e", "u.id", "e.user_id")
  .settings({ join_use_nulls: 1 });

Multiple .settings(...) calls are merged. Values are coerced to ClickHouse's expected encoding by the driver.

final()

Adds FINAL to the table source.

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

final() only works when the current source is a single table. Calling it on a query whose source is a subquery throws:

FINAL can only be applied to table sources.

If you need FINAL on a join's right-hand side, build the source explicitly:

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

Using a query as a source

as(alias)

Wraps the current query in an AliasedQuery so it can be used as the source of another selectFrom or as a join source. The alias contributes its selected columns to the parent query's scope.

const downloads = db
  .selectFrom(db.table("event_logs").as("e").final())
  .selectExpr((eb) => ["e.user_id", eb.fn.count().as("event_count")])
  .groupBy("e.user_id")
  .as("downloads");

db
  .selectFrom("users as u")
  .leftJoin(downloads, "downloads.user_id", "u.id")
  .select("u.id", "u.email", "downloads.event_count");

Compilation and execution

SelectQueryBuilder implements the ExecutableQuery<Output> interface:

toSQL()

Returns the compiled query without running it. The result is a CompiledQuery object with query (the SQL string) and params (the parameter map).

const compiled = db
  .selectFrom("users as u")
  .select("u.id")
  .where("u.email", "=", "alice@example.com")
  .toSQL();

compiled.query;
// SELECT u.id FROM users AS u WHERE u.email = {p0:String}
compiled.params;
// { p0: "alice@example.com" }

execute(options?)

Runs the query against ClickHouse and returns all rows as Output[].

If you passed a client to createClickHouseDB({ client }), that client is used by default. You can override it per call and pass driver-level execution settings when needed:

const rows = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .execute({
    client: otherClient,
    queryId: "users-list",
    clickhouse_settings: {
      max_threads: 1,
    },
  });

Throws if no client is configured:

No ClickHouse client configured. Pass one to execute() or createClickHouseDB().

The result is parsed from JSONEachRow, so the runtime types follow the runtime semantics rules (UInt64 → string, etc.).

If the DB handle was created with createClickHouseDB({ retries }), failed select execution is retried according to that DB-level retry configuration. Retries cover both the driver's query() call and JSONEachRow response parsing, but only for likely transient transport or temporary availability failures.

The same options shape is supported by executeTakeFirst() and executeTakeFirstOrThrow().

executeTakeFirst(options?)

Runs the query and returns the first row, or undefined if there were none.

const user = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .where("u.id", "=", 1)
  .executeTakeFirst();
//      ^?

executeTakeFirstOrThrow(options?)

Same as executeTakeFirst, but throws Query returned no rows. instead of returning undefined.

const user = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .where("u.id", "=", 1)
  .executeTakeFirstOrThrow();

toAST()

Returns a structured clone of the internal AST node. This is an escape hatch for tooling and tests — you do not need it for normal use.

Type parameters

SelectQueryBuilder<Sources, Scope, Output> carries three parameters that encode the query's type-level state:

You almost never write these by hand — they are inferred from the methods you call.