Query Builder API Reference

Complete API reference for all query builder methods. Works identically in both serve framework and standalone usage.

Core Methods

table()

Start a query from a table:

db.table('users')

Parameters:

• tableName: Name of the table (must exist in schema)

Returns: QueryBuilder instance

select()

Specify which columns to return:

// Select specific columns
db.table('users').select(['id', 'name', 'email'])

// Select all columns
db.table('users').select('*')

// Select with aliases
db.table('users').select(['id', 'name AS user_name'])

// Select from joined tables
db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .select(['orders.id', 'users.name', 'orders.amount'])

Parameters:

• columns: Array of column names or '*' for all columns

Returns: QueryBuilder instance with updated output type

where()

Filter rows based on conditions:

// Simple condition
db.table('users').where('status', 'eq', 'active')

// Multiple conditions (AND)
db.table('users')
  .where('status', 'eq', 'active')
  .where('age', 'gte', 18)

// Conditional where (null/undefined is ignored)
db.table('users')
  .where(input.status ? ['status', 'eq', input.status] : null)

Operators:

• eq, neq: Equal, not equal
• gt, gte, lt, lte: Comparison
• like, notLike: Pattern matching
• in, notIn: Array membership
• isNull, isNotNull: Null checks

Parameters:

• column: Column name
• operator: Comparison operator
• value: Value to compare against

Returns: QueryBuilder instance

See Filtering for more details.

execute()

Execute the query and return results:

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

Parameters:

• options (optional): Execution options
  • cache: Cache configuration override
  • queryId: Custom query ID for logging

Returns: Promise of typed result array

stream()

Execute query and stream results:

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

const reader = stream.getReader();
while (true) {
  const { done, value: rows } = await reader.read();
  if (done) break;
  console.log(rows);
}

Returns: Promise of ReadableStream

streamForEach()

Stream results with callback:

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

Parameters:

• callback: Function called for each row batch

Returns: Promise that resolves when stream completes

Joins

innerJoin()

Perform an inner join:

db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .select(['orders.id', 'users.name'])

Parameters:

• table: Table to join
• leftColumn: Column from current table
• rightColumn: Column from joined table (format: 'table.column')
• alias (optional): Alias for joined table

leftJoin()

Perform a left join:

db.table('orders')
  .leftJoin('users', 'user_id', 'users.id')
  .select(['orders.id', 'users.name'])

rightJoin()

Perform a right join:

db.table('users')
  .rightJoin('orders', 'id', 'orders.user_id')

fullJoin()

Perform a full outer join:

db.table('users')
  .fullJoin('orders', 'id', 'orders.user_id')

withRelation()

Use predefined relationships:

// First define relationships
const relationships = new JoinRelationships<Schema>();
relationships.define('userOrders', {
  from: 'orders',
  to: 'users',
  leftColumn: 'user_id',
  rightColumn: 'id',
  type: 'LEFT'
});

QueryBuilder.setJoinRelationships(relationships);

// Then use in queries
db.table('orders')
  .withRelation('userOrders')
  .select(['orders.id', 'users.name'])

See Joins and Join Relationships for more details.

Aggregations

sum()

Calculate sum of a column:

db.table('orders')
  .sum('amount', 'total_revenue')

Parameters:

• column: Column to sum
• alias: Alias for the result

count()

Count rows or non-null values:

db.table('users')
  .count('id', 'user_count')

avg()

Calculate average:

db.table('orders')
  .avg('amount', 'avg_order_value')

min()

Find minimum value:

db.table('orders')
  .min('amount', 'min_order')

max()

Find maximum value:

db.table('orders')
  .max('amount', 'max_order')

See Aggregations for more details.

Grouping & Ordering

groupBy()

Group results:

db.table('orders')
  .select(['country'])
  .sum('amount', 'revenue')
  .groupBy(['country'])

Parameters:

• columns: Array of columns to group by

groupByTimeInterval()

Group by time intervals:

// Built-in intervals
db.table('events')
  .count('id', 'event_count')
  .groupByTimeInterval('created_at', null, 'toStartOfHour')

// Custom intervals
db.table('events')
  .count('id', 'event_count')
  .groupByTimeInterval('created_at', '5 minute')

Parameters:

• column: Timestamp column
• interval: Custom interval string (e.g., '5 minute', '1 hour')
• function: Built-in ClickHouse function (e.g., 'toStartOfHour')

See Time Functions for more details.

orderBy()

Sort results:

db.table('users')
  .orderBy('created_at', 'DESC')

// Multiple sorts
db.table('users')
  .orderBy('country', 'ASC')
  .orderBy('created_at', 'DESC')

Parameters:

• column: Column to sort by
• direction: 'ASC' or 'DESC'

having()

Filter aggregated results:

db.table('orders')
  .select(['country'])
  .sum('amount', 'revenue')
  .groupBy(['country'])
  .having('revenue > 100000')

Parameters:

• condition: SQL condition string
• parameters (optional): Parameter values

Limiting & Pagination

limit()

Limit number of results:

db.table('users').limit(10)

Parameters:

• count: Maximum number of rows to return

offset()

Skip rows:

db.table('users')
  .orderBy('created_at', 'DESC')
  .limit(20)
  .offset(40) // Page 3 (skip first 40)

Parameters:

• count: Number of rows to skip

distinct()

Return only unique rows:

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

Utility Methods

toSQL()

Get generated SQL string:

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

const sql = query.toSQL();
// SELECT id, name FROM users WHERE status = 'active'

Returns: SQL string with parameters interpolated

toSQLWithParams()

Get SQL and parameters separately:

const { sql, parameters } = db
  .table('orders')
  .where('amount', 'gt', 1000)
  .toSQLWithParams();

console.log(sql);        // SELECT * FROM orders WHERE amount > ?
console.log(parameters); // [1000]

Returns: Object with sql string and parameters array

debug()

Print query information:

db.table('users')
  .where('status', 'eq', 'active')
  .debug() // Logs SQL, parameters, settings
  .execute();

Returns: QueryBuilder instance (chainable)

getConfig()

Access current query configuration:

const config = db
  .table('users')
  .select(['id'])
  .getConfig();

console.log(config);
// { table: 'users', select: ['id'], where: [], ... }

Returns: Query configuration object

raw()

Insert raw SQL fragments:

db.table('events')
  .raw('WHERE date BETWEEN toStartOfMonth(now()) AND now()')
  .execute();

Parameters:

• sql: Raw SQL string

Returns: QueryBuilder instance

Warning: Raw SQL bypasses type safety. Use sparingly.

settings()

Configure ClickHouse query settings:

db.table('large_table')
  .settings({
    max_execution_time: 30,
    max_memory_usage: '10000000000',
    max_threads: 4,
  })
  .execute();

Parameters:

• settings: ClickHouse settings object

Returns: QueryBuilder instance

cache()

Configure query caching:

db.table('users')
  .where('status', 'eq', 'active')
  .cache({
    mode: 'cache-first',
    ttlMs: 60000, // 1 minute
    tags: ['users'],
  })
  .execute();

Parameters:

• options: Cache configuration or false to disable

Returns: QueryBuilder instance

See Caching for more details.

Time Functions

toStartOfInterval()

Convert timestamp to interval start:

db.table('events')
  .select([
    'id',
    toStartOfInterval('created_at', '1 hour', 'hour_bucket'),
  ])
  .groupBy(['hour_bucket'])

Parameters:

• column: Timestamp column
• interval: Interval string (e.g., '1 hour', '5 minute')
• alias: Alias for the result

datePart()

Extract date component:

db.table('events')
  .select([
    datePart('year', 'created_at', 'year'),
    datePart('month', 'created_at', 'month'),
    datePart('day', 'created_at', 'day'),
  ])

Parameters:

• part: Date component ('year', 'month', 'day', 'hour', etc.)
• column: Timestamp column
• alias: Alias for the result

See Time Functions for complete reference.

Type Safety

All methods maintain full TypeScript type safety:

interface Schema {
  users: {
    id: 'Int32';
    name: 'String';
    age: 'UInt8';
    created_at: 'DateTime';
  };
  orders: {
    id: 'Int32';
    user_id: 'Int32';
    amount: 'Decimal64(2)';
    status: 'String';
  };
}

const db = createQueryBuilder<Schema>();

// ✅ Type-safe
const users = await db
  .table('users')
  .where('age', 'gte', 18)
  .select(['id', 'name'])
  .execute();
// users: Array<{ id: number; name: string }>

// ❌ TypeScript errors
db.table('users')
  .where('invalid_column', 'eq', 'value') // Error: column doesn't exist
  .select(['id', 'invalid']) // Error: column doesn't exist
  .sum('name', 'total'); // Error: can't sum string column