Agent Skills: ClickHouse SDK Patterns

ClickHouse SDK Patterns

Overview

Production patterns for @clickhouse/client — typed queries, streaming inserts, error handling, and connection lifecycle management. Start from the typed query helper below, then drill into references/implementation.md for the streaming, batching, and lifecycle patterns.

Prerequisites

• @clickhouse/client installed and authenticated (see clickhouse-install-auth)
• Node.js 18+ with a CLICKHOUSE_HOST / CLICKHOUSE_USER / CLICKHOUSE_PASSWORD env set
• Familiarity with async/await and Node.js streams (backpressure, drain, Readable)

Instructions

Apply the pattern that fits your workload. Steps 2–7 live in references/implementation.md with full, copy-pasteable code; the core typed-query skeleton stays here.

1. Typed query helper — the foundation every other pattern builds on. Define a generic query<T> wrapper that returns parsed rows (skeleton below).
2. Streaming insert (backpressure-safe) — stream large inserts through a Readable instead of buffering in memory; honor drain.
3. Batch insert with retry — chunk rows (default 10k) with exponential-backoff retries, returning { inserted, errors }.
4. Streaming SELECT (low memory) — consume large result sets as an AsyncGenerator so you never load the full set into RAM.
5. Error handling — distinguish server-side ClickHouseError (code + message) from network/client errors and normalize into a structured result.
6. Connection lifecycle — flush pending inserts on SIGTERM via client.close(); expose a ping()-based health check.
7. Per-query settings — override max_threads, max_memory_usage, max_execution_time, and max_result_rows for heavy queries.

Skeleton: Typed Query Helper

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: process.env.CLICKHOUSE_HOST!,
  username: process.env.CLICKHOUSE_USER ?? 'default',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
});

// Generic typed query — returns parsed JSON rows
async function query<T>(sql: string, params?: Record<string, unknown>): Promise<T[]> {
  const rs = await client.query({
    query: sql,
    query_params: params,
    format: 'JSONEachRow',
  });
  return rs.json<T>();
}

Note on parameterized queries: ClickHouse uses {name:Type} syntax for parameters, not $1 or ?. Always use typed parameters to prevent SQL injection.

Output

Applying these patterns produces:

• A single reusable client instance plus a generic query<T> helper that returns typed, parsed rows.
• Streaming insert/read paths that keep memory flat regardless of dataset size.
• A batch-insert result object { inserted: number; errors: Error[] } you can act on programmatically.
• Normalized error results (CH-<code>: <message> for server-side failures) rather than raw thrown exceptions.
• Graceful shutdown that flushes pending inserts before the process exits.

Error Handling

Map common ClickHouse server error codes to a corrective action:

| Error Code | Meaning | Action | |------------|---------|--------| | SYNTAX_ERROR (62) | Bad SQL | Fix query syntax | | UNKNOWN_TABLE (60) | Table doesn't exist | Check table name, database | | TOO_MANY_SIMULTANEOUS_QUERIES (202) | Connection overload | Reduce concurrency or pool | | MEMORY_LIMIT_EXCEEDED (241) | Query uses too much RAM | Add filters, use streaming | | TIMEOUT_EXCEEDED (159) | Query too slow | Optimize ORDER BY, add indexes |

Full safeQuery wrapper (server-vs-client error discrimination) is in references/implementation.md under Pattern 5.

Examples

Worked, runnable usage of each helper is in references/examples.md. Quick look — a typed aggregation query with named parameters:

interface EventCount {
  event_type: string;
  cnt: string;  // ClickHouse JSON returns numbers as strings
}

const rows = await query<EventCount>(
  'SELECT event_type, count() AS cnt FROM events WHERE user_id = {user_id:UInt64} GROUP BY event_type',
  { user_id: 42 }
);

See references/examples.md for streaming reads and structured error-result usage.

Resources

• references/implementation.md — full code for patterns 2–7 + format table
• references/examples.md — worked, runnable usage examples
• Node.js Client Docs
• Client Examples (GitHub)
• Query Settings Reference

Next Steps

Apply these patterns in clickhouse-core-workflow-a for real data modeling, then tune query cost and concurrency with clickhouse-cost-tuning and clickhouse-performance-tuning.