Query Basics

Learn the hypequery ClickHouse query builder for TypeScript. Build type-safe queries with autocomplete, reusable filters, joins, and strongly typed results.

Core Concepts

The query builder is designed to be:

Type-safe - TypeScript ensures columns and types are correct
Fluent - Chain methods naturally to build complex queries
ClickHouse-first - Models ClickHouse query building directly instead of pretending every database behaves the same way

Start with a typed ClickHouse client

Start by generating types from your ClickHouse schema if you have not done so already. See the CLI reference for hypequery generate.

The core builder API starts with a typed db client. These docs show the raw builder chain first because it is the clearest way to learn:

import { createClient } from '@clickhouse/client';
import { createQueryBuilder } from '@hypequery/clickhouse';
import type { Schema } from './generated-schema';

const client = createClient({
  url: process.env.CLICKHOUSE_URL!,
  username: process.env.CLICKHOUSE_USER!,
  password: process.env.CLICKHOUSE_PASSWORD!,
  database: process.env.CLICKHOUSE_DATABASE!,
});

const db = createQueryBuilder<Schema>({ client });

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

Here, client is the underlying ClickHouse JavaScript client. If you have not set that up yet, see Connecting to ClickHouse.

When you move into query definitions, the builder chain stays the same. The Query Building section keeps examples in terms of a standalone typed db client so the builder API is easier to learn in isolation.

Query Builder Pattern

All queries follow this pattern:

return db
  .table('table_name')
  .where('column', 'operator', 'value')
  .select(['col1', 'col2'])
  .orderBy('created_at', 'DESC')
  .limit(10)
  .execute();

Remember

Always finish your query chains with .execute() to run the query.

Builder immutability

Builder methods return a new builder state. In normal chained code that is easy to miss because the fluent style reads naturally, but it matters when you build queries conditionally.

// Good: reassign as you add optional clauses
let query = db.table('users');

if (onlyActive) {
  query = query.where('status', 'eq', 'active');
}

if (limit) {
  query = query.limit(limit);
}

const rows = await query.select(['id', 'email']).execute();

// Fragile: these calls do not update query unless you keep the return value
const query = db.table('users');

if (onlyActive) {
  query.where('status', 'eq', 'active');
}

if (limit) {
  query.limit(limit);
}

Migration note

If you have older code that conditionally calls builder methods without reassigning the returned builder, update it for v2.0.0.

Query Building Blocks

The query builder is organized into logical concepts. Each concept has detailed documentation:

Core Operations

Concept	Description	Link
Select	Choose which columns to return, use aliases, and expressions	Select →
Where	Filter rows using conditions, operators, and predicates	Where →
Joins	Combine data from multiple tables	Joins →
Aggregation	Group data and calculate summaries (sum, count, avg)	Aggregation →
Ordering	Sort results and paginate with limit/offset	Ordering →
Subqueries & CTEs	Compose subqueries, reusable CTEs, and more complex builder flows	Subqueries & CTEs →
SQL Expressions	Drop to raw SQL fragments and expression helpers when the fluent API is not enough	SQL Expressions →
Time Functions	Work with dates, timestamps, and time intervals	Time Functions →

Type Safety

hypequery ensures type safety throughout the query building process:

// TypeScript knows the exact columns in your schema
db
  .table('users')
  .select(['id', 'name', 'email'])
  .execute();
// Returns: Promise<Array<{ id: number; name: string; email: string }>>

// Invalid columns are caught at compile time
db
  .table('users')
  .select(['id', 'invalid_column']) // ❌ TypeScript error
  .execute();

// Operators match column types
db
  .table('users')
  .where('created_at', 'eq', '2024-01-01') // ✅ Valid
  .where('age', 'gte', 18) // ✅ Valid
  .execute();

Execution Methods

execute()

Run the query and get all results:

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

stream()

Stream results for large datasets:

const stream = await db
  .table('events')
  .select(['id', 'data'])
  .stream();

const reader = stream.getReader();
while (true) {
  const { done, value: rows } = await reader.read();
  if (done) break;
  // Process rows in batches
}

streamForEach()

Process rows with a callback:

await db
  .table('events')
  .select(['id', 'data'])
  .streamForEach(async (row) => {
    await processEvent(row);
  });