Query Building

hypequery provides a fluent, type-safe API for building SQL queries. This guide covers all the query building features available in the library.

Basic Queries

The most basic query starts with selecting a table and columns:

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

const results = await db
  .table('users')
  .select(['id', 'name', 'email'])
  .execute();

Filtering

Basic WHERE Clauses

Add WHERE clauses to filter your results:

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

OR Conditions

Use orWhere for OR conditions:

const results = await db
  .table('users')
  .where('status', 'eq', 'active')
  .orWhere('role', 'eq', 'admin')
  .execute();

Between Conditions

Filter values within a range:

const results = await db
  .table('orders')
  .whereBetween('amount', [100, 1000])
  .execute();

Aggregations

Perform calculations on your data:

const results = await db
  .table('orders')
  .select(['user_id'])
  .sum('amount', 'total_spent')
  .count('id', 'order_count')
  .avg('amount', 'average_order')
  .min('amount', 'smallest_order')
  .max('amount', 'largest_order')
  .groupBy('user_id')
  .execute();

Having Clauses

Filter aggregated results:

const results = await db
  .table('orders')
  .select(['user_id'])
  .sum('amount', 'total_spent')
  .groupBy('user_id')
  .having('total_spent > 1000')
  .execute();

Distinct Queries

Remove duplicate rows:

const results = await db
  .table('orders')
  .select(['status'])
  .distinct()
  .execute();

Ordering Results

Control the order of your results:

const results = await db
  .table('posts')
  .select(['title', 'created_at'])
  .orderBy('created_at', 'DESC')
  .orderBy('title', 'ASC')
  .execute();

Limiting Results

Control the number of results:

const results = await db
  .table('posts')
  .limit(10)
  .offset(20) // Skip first 20 results
  .execute();

Complex Queries

Combine multiple features for complex queries:

const results = await db
  .table('orders')
  .select(['user_id', 'status'])
  .where('created_at', 'gt', '2024-01-01')
  .orWhere('status', 'eq', 'priority')
  .groupBy(['user_id', 'status'])
  .sum('amount', 'total_amount')
  .having('total_amount > 5000')
  .orderBy('total_amount', 'DESC')
  .limit(100)
  .execute();

Type Safety

All queries are fully type-safe:

interface Schema {
  orders: {
    id: 'Int32';
    amount: 'Float64';
    status: 'String';
    created_at: 'Date';
  }
}

const db = createQueryBuilder<Schema>();

// TypeScript will catch these errors:
db.table('invalid_table'); // Error: invalid table name
db.table('orders').select(['invalid_column']); // Error: invalid column
db.table('orders').where('amount', 'gt', 'invalid'); // Error: amount expects number

Next Steps