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; }>.execute(): Promise<{
    id: number;
    email: string;
}[]> (+1 overload)

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.

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);

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)

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

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.

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").

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(client?)

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 pass a different client per call if you need to:

const rows = await db
  .selectFrom("users as u")
  .select("u.id", "u.email")
  .execute();

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.).

executeTakeFirst(client?)

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(client?)

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.