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 CLICKHOUSEHOST / CLICKHOUSEUSER / 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 wrapper that returns parsed rows (skeleton below).

1. Streaming insert (backpressure-safe) — stream large inserts through a

Readable instead of buffering in memory; honor drain.

1. Batch insert with retry — chunk rows (default 10k) with exponential-backoff

retries, returning { inserted, errors }.

1. Streaming SELECT (low memory) — consume large result sets as an

AsyncGenerator so you never load the full set into RAM.

1. Error handling — distinguish server-side ClickHouseError (code + message)

from network/client errors and normalize into a structured result.

1. Connection lifecycle — flush pending inserts on SIGTERM via

client.close(); expose a ping()-based health check.

1. Per-query settings — override maxthreads, maxmemory_usage,

maxexecutiontime, and maxresultrows 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 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-: for server-side failures) rather

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 }
);