SQL Expressions

The fluent query builder covers most workflows, but you can always run raw SQL or use expression helpers for complex cases. This page shows when and how to use them.

Raw SQL Queries

Execute raw SQL when you need complete control:

const results = await db.rawQuery<{ day: string; signups: number }>(
  `SELECT toStartOfDay(created_at) AS day, count(*) AS signups
   FROM signups
   WHERE account_id = ?
   GROUP BY day
   ORDER BY day`,
  [accountId]
);

Standalone:

const results = await db.rawQuery(sql, [accountId]);

Expression Helpers

When the builder handles most of the query but you need a custom expression, use raw, rawAs, or selectExpr. These helpers keep your queries type-safe.

raw() – create a SQL expression

Use raw() when you need an expression the fluent builder does not model directly:

import { raw } from '@hypequery/clickhouse';

db.table('sensors')
  .select(['id', 'reading'])
  .where(raw('toDate(recorded_at)'), 'eq', '2024-01-01')

• Accepts a SQL fragment string and returns a typed expression object
• Treat as an escape hatch; TypeScript cannot validate the expression
• Use for computed projections or predicate expressions the fluent API doesn't support

rawAs() – expression + alias

Use rawAs() when you need a SQL expression with a typed alias:

import { rawAs } from '@hypequery/clickhouse';

db.table('orders')
  .select([
    'account_id',
    rawAs('SUM(total)', 'total_revenue'),
    rawAs('COUNT(*)', 'order_count'),
  ])
  .groupBy(['account_id'])
  .having('SUM(total) > 1000')

Common patterns:

selectExpr() – shorthand for select + alias

Use selectExpr() for computed columns in your SELECT clause:

import { selectExpr } from '@hypequery/clickhouse';

db.table('rides')
  .select([
    selectExpr('toStartOfWeek(start_time)', 'week'),
    selectExpr('count()', 'ride_count'),
  ])
  .groupBy(['week'])
  .orderBy('week', 'ASC')

• Behaves like raw when no alias is supplied and like rawAs when you provide one
• Ideal when your entire projection consists of expressions

Built-in function helpers

Helpers like toDateTime, formatDateTime, toStartOfInterval, and datePart wrap common ClickHouse functions with type safety:

import { toDateTime, formatDateTime } from '@hypequery/clickhouse';

db.table('events')
  .select([
    'id',
    toDateTime('occurred_at', 'event_ts'),
    formatDateTime('occurred_at', 'Y-MM-dd', { alias: 'event_date' }),
  ])
  .where('event_type', 'eq', 'purchase')

Typing & Best Practices

Type generics for expressions

Expression helpers accept generics to describe the result type:

db.table('orders')
  .select([
    rawAs<number, 'total_revenue'>('SUM(total)', 'total_revenue'),
    rawAs<number, 'avg_order'>('AVG(total)', 'avg_order'),
  ])

The alias ('total_revenue') shows up on the result type with the specified generic (number). Without an alias, the builder can't add a strongly typed key, so always alias computed columns.

Type raw queries

rawQuery returns unknown[] by default. Pass a type argument for type safety:

const rows = await db.rawQuery<{ day: string; signups: number }>(sql, [accountId]);

Best practices

• Keep raw SQL static: Build raw expressions from trusted SQL snippets rather than interpolating user input
• Prefer fluent API: Use expression helpers only when the fluent API doesn't support your use case
• Alias all expressions: Computed columns need aliases for type safety
• Type raw queries: Always provide type arguments to rawQuery()