Helper Methods

hypequery provides several helper methods and utilities to assist with query building and debugging. This guide covers these additional features.

Query builder syntax

These examples use a typed standalone db client so the query builder stays the focus.

SQL Generation

toSQL

Get the raw SQL string for a query:

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

const sql = query.toSQL();
console.log(sql); // SELECT id, name FROM users WHERE active = true

toSQLWithParams

Get the SQL string and parameters separately:

const query = db
  .table('orders')
  .where('amount', 'gt', 1000);

const { sql, parameters } = query.toSQLWithParams();
console.log(sql); // SELECT * FROM orders WHERE amount > ?
console.log(parameters); // [1000]

Raw SQL

Raw Expressions

QueryBuilder.raw() currently appends raw SQL to the HAVING clause. Use it for grouped-query filters that are easier to express directly in SQL:

const results = await db
  .table('orders')
  .groupBy('account_id')
  .count('id', 'order_count')
  .raw('COUNT(*) > 10')
  .execute();

Complex Raw SQL

Use having() or raw() for advanced aggregate filters:

const results = await db
  .table('events')
  .select(['event_type'])
  .count('id', 'event_count')
  .groupBy(['event_type'])
  .raw('COUNT(*) > 100 AND uniq(user_id) > 10')
  .execute();

Query Settings

Basic Settings

Configure ClickHouse query settings:

const results = await db
  .table('large_table')
  .settings({
    max_execution_time: 30,
    max_memory_usage: '10000000000'
  })
  .execute();

Common Settings

Frequently used settings:

// Timeout settings
.settings({ max_execution_time: 60 })

// Memory settings
.settings({ max_memory_usage: '4000000000' })

// Thread settings
.settings({ max_threads: 4 })

// Multiple settings
.settings({
  max_execution_time: 30,
  max_threads: 2,
  max_memory_usage: '2000000000'
})

Debugging

debug

Print query information for debugging:

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

// Logs:
// - Internal builder state
// - Internal query config

Query Configuration

getConfig

Access the current internal query configuration:

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

const config = query.getConfig();
console.log(config.settings);
console.log(config.joins);
console.log(config.groupBy);

Advanced API

getConfig() returns a legacy snapshot shape for advanced integrations and tests. Prefer getQueryNode() or toQueryNode() for structured inspection, and toSQL() / toSQLWithParams() for everyday debugging.

Type Safety

Helper methods maintain type safety:

interface Schema {
  users: {
    id: 'Int32';
    name: 'String';
    active: 'UInt8';
  }
}

const db = createQueryBuilder<Schema>();

// TypeScript will catch these errors:
db.table('users')
  .groupBy('status')
  .raw('COUNT(*) > invalid_column') // No type checking for raw SQL
  .settings({ invalid_setting: true }); // Error: invalid setting

Best Practices

1. Use Raw SQL Sparingly

// Prefer builder methods when possible
db.table('users').where('age', 'gt', 18) // Good

// Use raw HAVING fragments only when necessary
db.table('users').groupBy('status').raw('COUNT(*) > 10') // Less ideal

2. Debug in Development

if (process.env.NODE_ENV === 'development') {
  query.debug();
}

3. Handle Settings Carefully

// Consider environment when setting limits
const maxMemory = process.env.NODE_ENV === 'production'
  ? '10000000000'  // 10GB in production
  : '1000000000';  // 1GB in development

query.settings({ max_memory_usage: maxMemory });