Query Building
Helper Methods
Learn about utility methods and additional features in hypequery
Helper Methods
hypequery provides several helper methods and utilities to assist with query building and debugging. This guide covers these additional features.
Serve context
Assume every snippet runs inside query(({ ctx }) => …) and starts with const db = ctx.db;. That way you can copy the examples directly into your serve queries.
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 = truetoSQLWithParams
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
Include raw SQL expressions in your queries:
const results = await db
.table('users')
.raw('WHERE age > 18 AND status = "active"')
.execute();Complex Raw SQL
Use raw SQL for complex conditions:
const results = await db
.table('events')
.raw(`
WHERE date BETWEEN
toStartOfMonth(now())
AND toStartOfMonth(now()) + INTERVAL 1 MONTH
`)
.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:
// - SQL query
// - Parameters
// - SettingsQuery Configuration
getConfig
Access the current query configuration:
const query = db
.table('users')
.select(['id', 'name'])
.where('active', 'eq', true);
const config = query.getConfig();
console.log(config);
// {
// select: ['id', 'name'],
// where: [...],
// parameters: [...],
// ...
// }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:
ctx.db.table('users')
.raw('WHERE invalid_column = 1') // No type checking for raw SQL
.settings({ invalid_setting: true }); // Error: invalid settingBest Practices
1. Use Raw SQL Sparingly
// Prefer builder methods when possible
ctx.db.table('users').where('age', 'gt', 18) // Good
// Use raw SQL only when necessary
ctx.db.table('users').raw('WHERE age > 18') // Less ideal2. 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 });