How to Test ClickHouse Queries in TypeScript

Testing database query code is awkward in any stack. ClickHouse adds its own quirks — no transactions to roll back, columnar storage that behaves differently from row-based databases, and a query language with ClickHouse-specific functions. This post covers three strategies, ordered from least to most confidence.

Strategy 1: Unit Testing with Mocks

Unit tests are fast and don't require a running database. The tradeoff: you're testing your application logic, not whether the query actually works.

With hypequery, the query builder is injectable, which makes mocking straightforward:

Unit tests are good for: transformation logic, error handling, default values, edge cases. They are not good for: catching wrong column names, wrong SQL, type mismatches between query and schema.

Strategy 2: Integration Testing with a Local ClickHouse Instance

For real confidence, run queries against an actual ClickHouse instance. Docker makes this easy:

Set up a test helper that creates tables, seeds data, and tears down after each test:

Run the test suite with docker compose -f docker-compose.test.yml up -d before running vitest. In CI, add a health check step to wait for ClickHouse to be ready before running tests.

Strategy 3: Compile-Time Type Checking

This isn't a test strategy in the traditional sense, but it eliminates an entire class of bugs before any test runs.

hypequery's schema generation produces TypeScript types from your ClickHouse schema:

The generated schema encodes column names and types. If you reference a column that doesn't exist or pass the wrong type to .where(), TypeScript catches it at compile time:

This means you don't need integration tests for basic schema correctness — the type checker handles it. Your integration tests can focus on behaviour: does the query return the right aggregated results, are edge cases handled, does pagination work correctly?

Putting It Together

A practical test strategy for a ClickHouse + hypequery TypeScript project:

1. Type check (tsc --noEmit) in CI — catches column name and type errors immediately.
2. Unit tests for application logic that sits around queries — transformations, defaults, error handling.
3. Integration tests for queries that have non-obvious behaviour — aggregations, window functions, PREWHERE logic, anything where you want to confirm the SQL does what you think.

Keep the integration test database separate from dev and production. Seed only the data each test needs, and clean up after. ClickHouse's TRUNCATE TABLE is fast even on large test datasets because it drops and recreates the data parts rather than deleting rows individually.