Execute & toSQL

The execute() method runs your query against the ClickHouse database and returns the results, while toSQL() generates the SQL string without executing it.

Basic Usage

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

const db = createQueryBuilder<Schema>();

// Generate SQL without executing
const sql = db
  .table('users')
  .select(['id', 'name', 'email'])
  .where('status', 'eq', 'active')
  .toSQL();
// Result: SELECT id, name, email FROM users WHERE status = 'active'

// Execute the query and get results
const results = await db
  .table('users')
  .select(['id', 'name', 'email'])
  .where('status', 'eq', 'active')
  .execute();
// Returns: Promise<User[]>

Type Definitions

toSQL(): string
toSQLWithParams(): { sql: string, parameters: any[] }
execute(): Promise<T[]>
stream(): Promise<ReadableStream<T[]>>
streamForEach<R = void>(callback: (row: T) => R | Promise<R>): Promise<void>

Returns

• toSQL(): Returns the final SQL string with parameters substituted
• toSQLWithParams(): Returns both SQL string and parameters array
• execute(): Returns a Promise that resolves to an array of results
• stream(): Returns a Promise that resolves to a ReadableStream
• streamForEach(): Processes each row with a callback function

Examples

Basic Execution

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

console.log(users);
// Output: [{ id: 1, name: 'John Doe', email: 'john@example.com' }, ...]

SQL Generation

// Generate SQL for debugging or logging
const sql = db
  .table('orders')
  .select(['user_id', 'total'])
  .where('total', 'gte', 1000)
  .groupBy(['user_id'])
  .sum('total', 'total_spent')
  .toSQL();

console.log('Generated SQL:', sql);
// Output: SELECT user_id, total, SUM(total) AS total_spent FROM orders WHERE total >= 1000 GROUP BY user_id

Error Handling

Common Error Scenarios

// Handle connection errors
try {
  const results = await db.table('users').execute();
} catch (error) {
  if (error.message.includes('Connection')) {
    console.error('Database connection failed');
  } else if (error.message.includes('Table')) {
    console.error('Table does not exist');
  } else {
    console.error('Query execution failed:', error.message);
  }
}

// Handle syntax errors
try {
  const sql = db
    .table('users')
    .select(['invalid_column']) // This will cause an error
    .toSQL();
} catch (error) {
  console.error('SQL generation failed:', error.message);
}