Query Building

hypequery’s query builder provides a fluent, type-safe API for building ClickHouse queries. The same API works in both standalone and serve contexts.

This page covers the core query builder API: select, where, join, groupBy, having, orderBy, limit/offset, and more.

Using the Query Builder

The query builder API is identical in both contexts. All queries use .execute().

In serve framework:

// Inside query definitions - return the query with .execute()
query: ({ ctx }) =>
  ctx.db.table('users')
    .where('status', 'eq', 'active')
    .select(['id', 'name'])
    .execute()

Standalone:

// Direct execution - await the query
const users = await db.table('users')
  .where('status', 'eq', 'active')
  .select(['id', 'name'])
  .execute();

The only difference: Use ctx.db in serve, db in standalone. The query builder API is the same.

Examples below show the query builder without .execute() for brevity. Remember to add .execute() in your actual code.

Selecting columns

db.table('users').select([
  'id',
  'email AS user_email',
  rawAs('status', 'account_status'),
])

• Write aliases inline ('email AS user_email')

• Use helpers like rawAs, selectExpr, toDateTime for expressions

• Use select('*') when you need every column:

  db.table('users').select('*')

Where conditions

where is the most flexible clause in the builder: pass column/operator/value triples, raw predicates, or predicate-builder callbacks, and chain them in any combination.

db.table('trials')
  .where('started_at', 'gte', '2024-01-01')
  .where('started_at', 'lt', '2024-01-31')
  .where('status', 'eq', 'active')

Type definitions

where(column, operator, value): this
where(condition: WhereExpression): this
where(builder: (expr) => PredicateExpression): this

orWhere(column, operator, value): this
orWhere(builder: (expr) => PredicateExpression): this

whereGroup(callback: (builder) => void): this
orWhereGroup(callback: (builder) => void): this

• column – Valid base/joined column name.
• operator – eq, neq, gt, gte, lt, lte, like, notLike, in, notIn, between, etc.
• value – Value/array passed to the operator (strings, numbers, arrays, subqueries).
• condition – Prebuilt WhereExpression (useful when reusing predicates).
• builder – Receives the predicate helpers shown below.
• Every call returns the builder for chaining.

Comparison cheat sheet:

Function Predicates

db.table('products')
  .where((expr) =>
    expr.and([
      expr.fn('hasAny', 'tags', ['launch', 'beta']),
      expr.fn('endsWith', 'status', expr.literal('active')),
    ])
  )

The callback receives a PredicateBuilder with helpers like expr.fn, expr.literal, expr.array, expr.raw, expr.and, and expr.or so you can model ClickHouse functions safely.

Builder helper summary:

Advanced IN Operators

The where method supports advanced IN operators for ClickHouse, including distributed/global, tuple, subquery, and table reference forms.

All IN operator variants are supported:

db.table('events')
  .where(['counter_id', 'user_id'], 'inTuple', [
    [34, 123],
    [101500, 456],
  ])

Joins

db.table('orders')
  .leftJoin('users', 'orders.user_id', 'users.id')
  .select([
    'orders.id',
    'orders.total',
    'users.email AS customer_email',
  ])

Available helpers: innerJoin, leftJoin, rightJoin, fullJoin.

Group By

db.table('orders')
  .select(['country'])
  .sum('total', 'total_revenue')
  .groupBy(['country'])

Call aggregation helpers (sum, avg, count, distinctCount, etc.) before groupBy.

Order by

db.table('sessions')
  .select(['id', 'user_id', 'started_at'])
  .orderBy('started_at', 'DESC')
  .orderBy('id', 'ASC')

• Signature: orderBy(column, direction = 'ASC')
• column accepts any base/joined column. direction is 'ASC' or 'DESC' (defaults to ascending).
• Chain multiple calls for secondary sorting.

Limit & Offset

db.table('events')
  .select(['id', 'name'])
  .orderBy('occurred_at', 'DESC')
  .limit(50)
  .offset(0)

Both helpers validate non-negative numbers.

toSQL

const query = db.table('orders')
  .select(['user_id'])
  .sum('total', 'total_spent')
  .groupBy(['user_id']);

const sql = query.toSQL();
// SELECT user_id, sum(total) AS total_spent FROM orders GROUP BY user_id

toSQLWithParams() returns { sql, params } for parameterized output.

Distinct

db.table('users')
  .select(['country'])
  .distinct()

• Signature: distinct(): this
• Removes duplicate rows from the result set (ClickHouse DISTINCT).

Time-based functions

db.table('events')
  .select([
    toDateTime('occurred_at', 'event_ts'),
    formatDateTime('occurred_at', 'Y-MM-dd HH:00', { alias: 'hour_bucket' }),
    toStartOfInterval('occurred_at', '1 hour', 'hour_start'),
    datePart('week', 'occurred_at', 'week_no'),
  ])
  .groupBy(['hour_bucket', 'hour_start', 'week_no'])
  .orderBy('hour_bucket', 'ASC')

• toDateTime(field, alias?) converts values into ClickHouse DateTime objects.
• formatDateTime(field, format, { timezone?, alias? }) formats timestamps with optional timezone.
• toStartOfInterval(field, interval, alias?) truncates to windows like '15 minute', '7 day'.
• datePart(part, field, alias?) extracts year, month, day, etc.

Grouping by time intervals

db.table('orders')
  .sum('total', 'total_spend')
  .groupByTimeInterval('created_at', '1 hour')

• Built-in helpers: groupByTimeInterval('timestamp', null, 'toStartOfMinute' | 'toStartOfHour' | 'toStartOfDay' | 'toStartOfWeek' | 'toStartOfMonth' | 'toStartOfQuarter' | 'toStartOfYear').
• Custom intervals: pass '5 minute', '2 hour', '7 day', etc., as the second argument.

Query settings

db.table('large_table')
  .settings({
    max_execution_time: 60,
    max_threads: 4,
    max_memory_usage: '10000000000',
  })
  .select(['id'])
  .limit(1000)

• Settings map directly to ClickHouse query settings.
• TypeScript ensures date/time columns are used where required (groupByTimeInterval errors if you pass a numeric column).

Common Table Expressions (CTEs)

const activeUsers = db.table('users')
  .select(['id', 'name'])
  .where('status', 'eq', 'active');

db.table('orders')
  .withCTE('active_users', activeUsers)
  .leftJoin('active_users', 'orders.user_id', 'active_users.id')
  .select(['orders.id', 'active_users.name'])

• Signature: withCTE(alias, subquery | rawSql).
• Chain multiple CTEs:

const activeUsers = db.table('users')
  .select(['id', 'name'])
  .where('status', 'eq', 'active');

const highValueOrders = db.table('orders')
  .select(['user_id', 'total'])
  .where('total', 'gte', 1000);

db.table('users')
  .withCTE('active_users', activeUsers)
  .withCTE('high_value_orders', highValueOrders)
  .leftJoin('high_value_orders', 'users.id', 'high_value_orders.user_id')
  .select(['users.name', 'high_value_orders.total'])

Use raw SQL strings when needed: .withCTE('recent', 'SELECT ...').

Next Steps

Continue: Filtering - Master where clauses and operators

Or explore:

• Joins - Join tables together
• Time Functions - Work with dates and time intervals