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
Database-agnostic API - Works with ClickHouse using familiar patterns
Consistent - The same chain continues to work inside query({ ... })

Start with `db`

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({
  host: process.env.CLICKHOUSE_HOST!,
  username: process.env.CLICKHOUSE_USERNAME!,
  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.

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