Clickhouse Js Node Coding

ClickHouse Node.js Client — Coding

Reference: https://clickhouse.com/docs/integrations/javascript

⚠️ Node.js runtime only. This skill covers the @clickhouse/client

package running in a Node.js runtime exclusively — including **Next.js

Node runtime** API routes, React Server Components, Server Actions, and

standard Node.js processes. Do not apply this skill to browser client

components, Web Workers, Next.js Edge runtime, Cloudflare Workers, or

any usage of @clickhouse/client-web. For browser/edge environments, the

correct package is @clickhouse/client-web.

---

How to Use This Skill

1. Match the user's intent to a row in the Task Index below and read the corresponding reference file before writing code. After reading it, scan any Answer checklist in that reference and make sure the final answer covers each relevant item; those checklists capture details users usually need but are easy to omit in short answers. 2. Always import from `@clickhouse/client` (never @clickhouse/client-web) and create a client with createClient({ url }) or rely on supported defaults when appropriate. Close it with await client.close() preferably when it's no longer needed or during graceful shutdown for global resources. 3. Prefer `JSONEachRow` for typical row inserts/selects unless the user has already chosen another format or is streaming raw bytes (CSV / TSV / Parquet — see examples/node/performance/). Note on `clickhouse_settings`: settings passed to createClient are defaults for every request; they can be overridden per-call by passing clickhouse_settings directly to insert(), query(), or command(). Always mention this when the user configures settings at the client level. 4. Always use `query_params` for user-supplied values — never template- literal-interpolate them into SQL. See reference/query-parameters.md. When answering a parameter-binding question, your response must explicitly name template-literal interpolation as a "SQL injection risk" — even when the user only asked about syntax and did not raise security. The literal phrase "SQL injection" needs to appear; this is the most common mistake from PostgreSQL/MySQL users and the security framing is part of the correct answer, not an optional aside. 5. Pick the right method for the job:

• client.insert() — write rows.
• client.query() + resultSet.json() / .text() / .stream() — read

rows that return data.

• client.command() — DDL and other statements that don't return rows

(CREATE, DROP, TRUNCATE, ALTER, SET in a session, etc.).

• client.exec() — when you need the raw response stream of an arbitrary

statement (rare in coding scenarios).

• client.ping() — health check; returns { success, error? }, never

throws on connection failure. 6. Note version constraints when relevant. Examples:

• pathname config option: client >= 1.0.0.
• BigInt values in query_params: client >= 1.15.0.
• TupleParam and JS Map in query_params: client >= 1.9.0.
• Configurable json.parse / json.stringify: client >= 1.14.0.
• Time / Time64 data types: ClickHouse server >= 25.6.
• QBit data type: ClickHouse server >= 25.10 (GA on 26.x).
• Dynamic / Variant / new JSON types: ClickHouse server >= 24.1 /

24.5 / 24.8 (no longer experimental since 25.3).

---

Task Index

Identify the user's task and read the matching reference file.

---

Conventions used in answers

• Always show import { createClient } from '@clickhouse/client' (Node, never

Web).

• Always await client.close() at the end of self-contained snippets; in

long-running services, close on graceful shutdown.

• For inserts, prefer format: 'JSONEachRow' and values: [...] unless the

user's scenario requires otherwise.

• For selects, prefer await (await client.query({...})).json<RowType>() for

small / medium result sets; for bigger results suggest streaming.

• When showing parameter binding, use ClickHouse's native {name: Type}

syntax — never $1, ?, or :name.

• For DDL inside a cluster or behind a load balancer, set

clickhouse_settings: { wait_end_of_query: 1 } on the command() call so the server only acknowledges after the change is applied. See https://clickhouse.com/docs/en/interfaces/http/#response-buffering.

---

Out of scope

This skill covers day-to-day coding against @clickhouse/client (Node). The following topics are intentionally not covered here:

• **Errors, hangs, type mismatches, proxy pathname surprises, log silence,

socket hang-ups, ECONNRESET** → use the clickhouse-js-node-troubleshooting skill.

• **Streaming, Parquet, file streams, server-side bulk moves, progress

streaming, async-insert throughput tuning** — see `examples/node/performance/`.

• TLS, RBAC / read-only users, deeper SQL-injection guidance — see

`examples/node/security/`.

• **CREATE TABLE patterns, deployment-shaped connection strings,

replication / sharding choices** — see `examples/node/schema-and-deployments/`.

• Browser, Web Worker, Next.js Edge, Cloudflare Workers — use

@clickhouse/client-web and see `examples/web/`.

---

Still Stuck?

• `examples/node/coding/` — the runnable corpus this skill is built on.
• ClickHouse JS client docs
• ClickHouse supported formats
• ClickHouse data types

Async Inserts

Applies to: all client versions; the relevant settings are server-side.

See https://clickhouse.com/docs/en/optimize/asynchronous-inserts.

When to use async inserts: when many small inserts arrive concurrently

(e.g., one per HTTP request) and you don't want to maintain a client-side

batching layer. ClickHouse will batch them server-side. This is also the

recommended ingestion pattern for ClickHouse Cloud.

When _not_ to use async inserts: when you already build large batches

client-side (e.g., from a stream). Plain inserts are simpler and lower

latency.

Setup

Enable on the client level or per-request via clickhouse_settings:

import { createClient, ClickHouseError } from "@clickhouse/client";

const client = createClient({
  url: process.env.CLICKHOUSE_URL,
  password: process.env.CLICKHOUSE_PASSWORD,
  max_open_connections: 10,
  clickhouse_settings: {
    async_insert: 1,
    wait_for_async_insert: 1, // wait for ack from server
    async_insert_max_data_size: "1000000",
    async_insert_busy_timeout_ms: 1000,
  },
});

Concurrent small inserts

Each call still uses the client's normal insert() API — the server merges the batches.

const promises = [...new Array(10)].map(async () => {
  const values = [...new Array(1000).keys()].map(() => ({
    id: Math.floor(Math.random() * 100_000) + 1,
    data: Math.random().toString(36).slice(2),
  }));

  await client
    .insert({ table: "async_insert_example", values, format: "JSONEachRow" })
    .catch((err) => {
      if (err instanceof ClickHouseError) {
        // err.code matches a row in system.errors
        console.error(`ClickHouse error ${err.code}:`, err);
        return;
      }
      console.error("Insert failed:", err);
    });
});

await Promise.all(promises);

wait_for_async_insert — fire-and-forget vs ack

With wait_for_async_insert: 1, expect each insert call to take roughly async_insert_busy_timeout_ms to resolve when traffic is light, because the server waits for more rows or for the timer to fire before flushing.

Combining DDL with async inserts

When creating tables in scripts that immediately insert, ack the DDL with wait_end_of_query: 1 so the table is ready before the first insert:

await client.command({
  query: `
    CREATE OR REPLACE TABLE async_insert_example (id Int32, data String)
    ENGINE MergeTree ORDER BY id
  `,
  clickhouse_settings: { wait_end_of_query: 1 },
});

Even better is to create a specialized client for inserts with the appropriate async settings and a separate client for DDL and other queries.

Common pitfalls

• Setting `async_insert` per call but expecting client-side batching.

The client still issues each insert() as a separate HTTP request — the batching happens on the server.

• **Confusing wait_for_async_insert (async-insert ack) with

wait_end_of_query (DDL ack).** They are unrelated.

• **Treating a resolved insert under wait_for_async_insert: 0 as

durably written.** It only means the server accepted the bytes; flush failures will not surface to the client.

• Not handling `ClickHouseError`. It exposes err.code, which maps to

rows in the system.errors table — use it to decide whether to retry.

See also

• For raw throughput tuning of large async-insert workloads,

see `examples/node/performance/`.

Client Configuration

Applies to: all versions, with these notable additions:

>

- pathname config option: client >= 1.0.0.

- clickhouse_setting_* / ch_* URL parameters: client >= 1.0.0.

- keep_alive.idle_socket_ttl (Node-only): client >= 1.0.0.

Answer checklist

When answering configuration questions, include the relevant points:

• Show createClient from @clickhouse/client with explicit fields when the

user is writing code; this is easier to read and review than encoding everything into a URL string.

• When mentioning the URL form for environment variables / DSNs: show a Bash

export with the literal URL value, and createClient({ url: process.env.CLICKHOUSE_URL }) in the Node code. Never construct a URL in application code — no string concatenation, no template literals, no query-string builders.

• If URL parameters and object fields both set the same option, URL parameters

override the rest of the configuration object.

• If clickhouse_settings appear on createClient, explain that they are

defaults for every request and can be overridden on individual query(), insert(), command(), or exec() calls.

• Remind long-running services to close the client during graceful shutdown.
• The application field sets the name that appears in system.query_log.

Do not mention any specific HTTP header name — the client handles header mapping internally and the header names are an implementation detail.

Minimal client

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

const client = createClient({
  url: process.env.CLICKHOUSE_URL, // defaults to 'http://localhost:8123'
  username: process.env.CLICKHOUSE_USER, // defaults to 'default'
  password: process.env.CLICKHOUSE_PASSWORD, // defaults to ''
  database: "analytics", // defaults to 'default'
});
// ... your queries ...
await client.close();

url accepts a string or a URL object. The accepted string format is:

http[s]://[username:password@]hostname:port[/database][?param1=value1&param2=value2]

Configuration via URL

Prefer explicit object fields in application code. Use the URL form when the application receives one connection string from an environment variable, secret manager, or config file. The URL value belongs in the environment, not in the source code — show it as a shell export and read it in Node:

# In your shell environment / deployment config (e.g. .env, Kubernetes secret):
export CLICKHOUSE_URL='https://bob:secret@my.host:8124/analytics'

// In your Node.js code — no URL construction needed:
const client = createClient({ url: process.env.CLICKHOUSE_URL });

Per-client vs per-request clickhouse_settings ⭐

Always mention this when discussing `clickhouse_settings`: settings set

on createClient are defaults; any individual call can override them.

Settings on createClient apply to every request. Settings on a single operation (query, insert, command, exec) override the client defaults for that call only.

const client = createClient({
  clickhouse_settings: {
    output_format_json_quote_64bit_integers: 0, // applied to every request
  },
});

const rows = await client.query({
  query: "SELECT number FROM system.numbers LIMIT 2 FORMAT JSONEachRow",
  clickhouse_settings: {
    output_format_json_quote_64bit_integers: 1, // overrides client default for this call
  },
});

default_format for exec()

client.exec() runs an arbitrary statement and returns a stream. If your query has no trailing FORMAT … clause, set default_format so the server knows what to send back, then wrap the response in a ResultSet:

import { createClient, ResultSet } from "@clickhouse/client";

const client = createClient();
const format = "JSONCompactEachRowWithNamesAndTypes";
const { stream, query_id } = await client.exec({
  query: "SELECT database, name, engine FROM system.tables LIMIT 5",
  clickhouse_settings: { default_format: format },
});
const rs = new ResultSet(stream, format, query_id);
console.log(await rs.json());
await client.close();

For ordinary SELECTs prefer client.query({ format }) — default_format is only needed for raw exec().

Common pitfalls

• **Don't put a path in url and expect it to be the database name when

you're behind a proxy.** Use pathname for the proxy path and database for the DB. (Symptom: "wrong database selected.") See the troubleshooting skill for diagnosis.

• Don't create a client per request. createClient opens a connection

pool; share one client across requests and close() on shutdown.

• `max_open_connections` must be `>= 1` when set explicitly.

Compression

Applies to: all versions support boolean compression. The explicit

codec object form, per-codec request options, and zstd support are a

Node-only addition in @clickhouse/client >= 1.22.0; Brotli

({ codec: "br" }) is also added (any Node.js version, no minimum). Request

compression is Node-only regardless of codec; response decompression on the

web client is handled by the browser.

The client can compress the outgoing request (insert) body and ask the server to compress the response (read) body. Both are configured under compression on createClient.

Answer checklist

When answering compression questions, include the relevant points:

• The option shape is boolean | { codec }, not a bare string. true

means gzip (backwards compatible); { codec: "zstd" } selects zstd. There is no compression: { request: "zstd" } shorthand — it must be { request: { codec: "zstd" } }.

• zstd is Node-only (@clickhouse/client) and requires **Node.js >=

22.15.0** (the built-in zlib zstd APIs). On @clickhouse/client-web or an older Node runtime, requesting zstd throws a clear error at createClient.

• Supported codecs are gzip, zstd, and br (Brotli). Unlike zstd, br

works on any supported Node.js version (it ships in zlib). Request-body compression is Node-only for every codec (the web client sends requests uncompressed); for responses, the web client rejects zstd but allows gzip/br, which the browser decompresses.

• The request object takes a per-codec tuning option: a level for

gzip/zstd, a quality for br ({ codec: "br", quality }). Brotli defaults to quality 4 — zlib's brotli default of 11 is far too slow for a streaming insert.

• Response compression cannot be enabled for a readonly=1 user — the server

rejects the required enable_http_compression setting change.

• Prefer zstd for write-heavy (insert) workloads: a similar-or-better ratio

than gzip at materially lower CPU, and ClickHouse decompresses gzip single-threaded.

gzip (default, all versions)

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

const client = createClient({
  compression: {
    request: true, // compress insert bodies with gzip
    response: true, // ask the server for a gzip-compressed response
  },
});

request: true / response: true are equivalent to { codec: "gzip" }.

zstd (Node.js >= 22.15.0)

const client = createClient({
  compression: {
    request: { codec: "zstd" },
    response: { codec: "zstd" },
  },
});

You can mix codecs and directions, e.g. zstd inserts with uncompressed reads:

const client = createClient({
  compression: {
    request: { codec: "zstd" },
    // response omitted → uncompressed reads
  },
});

Brotli (any Node.js version)

const client = createClient({
  compression: {
    request: { codec: "br" }, // brotli insert bodies (quality 4 by default)
    response: { codec: "br" }, // ask the server for a brotli-compressed response
  },
});

Unlike zstd, Brotli needs no minimum Node.js version. Request-body compression is Node-only (the web client sends requests uncompressed); br responses also work on the web client, decompressed by the browser. Its tuning option is quality (0-11), not level:

const client = createClient({
  compression: {
    request: { codec: "br", quality: 6 },
  },
});

Request compression options (Node.js)

The request object accepts a per-codec tuning option — a level for gzip (zlib level) and zstd (zstd compression level), or a quality for br (Brotli quality, 0-11). When omitted, the codec default is used (Brotli defaults to 4). This applies to the request direction only; the response compression options are chosen by the ClickHouse server.

const client = createClient({
  compression: {
    request: { codec: "zstd", level: 19 }, // higher ratio, more CPU
  },
});

Common pitfalls

• `compression: { request: "zstd" }` is a type error. Use the object form:

{ request: { codec: "zstd" } }.

• `zstd` on the web client throws. @clickhouse/client-web does not

compress request bodies, and zstd response handling depends on the browser; the web client rejects the zstd codec at createClient. Use Node, or gzip.

• `zstd` on Node < 22.15 throws at client creation, not deep inside a later

insert/query. The error names the running Node version and tells you to use gzip instead.

• Response compression + `readonly=1` user fails. Response decompression

needs enable_http_compression=1, which a readonly user cannot set.

Custom JSON parse / stringify

Requires: client >= 1.14.0 (configurable json.parse and

json.stringify). Earlier versions cannot swap the JSON implementation.

Answer checklist

When the user wants UInt64/Int64 values back as BigInt:

• State that configurable json.parse / json.stringify requires

@clickhouse/client >= 1.14.0.

• Show the supported createClient({ json: { parse, stringify } }) option,

usually with json-bigint and useNativeBigInt: true.

• Combine it with output_format_json_quote_64bit_integers: 0 so the server

emits unquoted 64-bit integers that the parser can turn into BigInt.

• Mention that output_format_json_quote_64bit_integers: 0 is the default

since ClickHouse 25.8, but setting it explicitly is useful for older servers or portable examples.

• Warn that casting to JavaScript Number / parseInt / parseFloat loses

precision above Number.MAX_SAFE_INTEGER.

Why customize?

The default JSON.stringify / JSON.parse:

• Throws on BigInt.
• Calls Date.prototype.toJSON() (ISO string) — fine for DateTime with

date_time_input_format: 'best_effort', surprising in some workflows.

• Loses precision for 64-bit integers returned as numbers (a separate

issue — covered in the troubleshooting skill).

A custom { parse, stringify } lets you plug in JSONBig, safe-stable-stringify, your own BigInt-aware serializer, etc.

Recipe: BigInt-safe stringify, custom Date handling

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

const valueSerializer = (value: unknown): unknown => {
  // Serialize Date as a UNIX millis number (instead of toJSON's ISO string)
  if (value instanceof Date) {
    return value.getTime();
  }

  // Serialize BigInt as a string so JSON.stringify won't throw
  if (typeof value === "bigint") {
    return value.toString();
  }

  if (Array.isArray(value)) {
    return value.map(valueSerializer);
  }

  if (typeof value === "object" && value !== null) {
    return Object.fromEntries(
      Object.entries(value).map(([k, v]) => [k, valueSerializer(v)]),
    );
  }

  return value;
};

const client = createClient({
  json: {
    parse: JSON.parse, // use default parsing
    stringify: (obj: unknown) => JSON.stringify(valueSerializer(obj)),
  },
});

await client.command({
  query: `
    CREATE OR REPLACE TABLE inserts_custom_json_handling
    (id UInt64, dt DateTime64(3, 'UTC'))
    ENGINE MergeTree
    ORDER BY id
  `,
});

await client.insert({
  table: "inserts_custom_json_handling",
  format: "JSONEachRow",
  values: [
    {
      id: BigInt("250000000000000200"), // serialized as a string
      dt: new Date(), // serialized as ms since epoch
    },
  ],
});

await client.close();

The custom valueSerializer runs before JSON.stringify, so values

are transformed before the standard hooks (Date.prototype.toJSON,

object toJSON() methods, etc.) ever run.

Recipe: BigInt-safe parsing for 64-bit integer columns

If you want UInt64/Int64 to come back as BigInts (instead of strings or precision-lossy numbers), plug in a BigInt-aware parser such as `json-bigint`:

import { createClient } from "@clickhouse/client";
import JSONBig from "json-bigint";

const bigJson = JSONBig({ useNativeBigInt: true });

const client = createClient({
  json: {
    parse: bigJson.parse,
    stringify: bigJson.stringify,
  },
  clickhouse_settings: {
    output_format_json_quote_64bit_integers: 0,
  },
});

output_format_json_quote_64bit_integers: 0 is the default since ClickHouse 25.8; setting it explicitly is useful for older servers and makes the example self-contained. With it off, the server emits unquoted 64-bit integers that json-bigint parses straight to BigInt. The json option applies to both outgoing JSON bodies and incoming JSON-format responses.

Recipe: Zero-dep BigInt parsing (no npm install)

If adding a dependency is awkward (locked lockfile, restricted environment, or you just don't want to pull in json-bigint), you can plug in a hand-rolled reviver. This uses the context.source argument that JSON.parse revivers gained in Node >= 20.16 / >= 21.7, so the raw numeric literal is available before it's coerced to a JS number:

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

const parseBigInt = (text: string) =>
  JSON.parse(text, function (key, value, context) {
    if (key.endsWith("__bigint")) {
      return BigInt(context.source);
    }
    return value;
  });

const client = createClient({
  json: {
    parse: parseBigInt,
    stringify: JSON.stringify, // use default stringify
  },
  clickhouse_settings: { output_format_json_quote_64bit_integers: 0 },
});

const rs = await client.query({
  query: "SELECT toUInt64(250000000000000200) AS id__bigint",
});
const { data } = await rs.json();
console.log(data[0].id__bigint); // 250000000000000200

await client.close();

Trade-offs versus json-bigint:

• ✓ No new dependency to install.
• ✓ Only promotes known to be 64-bit integers to BigInt.
• ✗ Requires Node >= 20.16 / >= 21.7 for the reviver context.source.

On older Node, prefer json-bigint or upgrade Node.

• ✗ Outgoing stringify still uses default JSON.stringify, which throws

on BigInt. Pair with the valueSerializer pattern above if your inserts contain BigInt values.

Common pitfalls

• Setting `json.parse` only. That only affects reading JSON responses;

outgoing JSON bodies use json.stringify. If you want consistent custom handling in both directions, generally provide a matching stringify too or a throwing serializer that prevents mismatches.

• Forgetting `bigint` handling in `stringify`. Default JSON.stringify

throws on BigInt; if your data ever contains one, the insert will fail with TypeError: Do not know how to serialize a BigInt.

• Targeting client `< 1.14.0`. The json option doesn't exist; you'll

need to convert values manually before calling insert() / query() (or upgrade).

• Casting 64-bit integers to `Number`. JavaScript's number type has

only 53 bits of mantissa — values above Number.MAX_SAFE_INTEGER (2^53 − 1) are silently rounded. Do not try to fix precision loss by calling Number(), parseInt(), or parseFloat() on the value. The correct fix is a BigInt-aware parser (shown above), not a lossy cast.

• Mixing BigInt and number for the same column. If some values are BigInt and

others are number, your app code needs to handle both types. Otherwise JavaScript will throw a TypeError: Cannot mix BigInt and other types.

Modern Data Types: Dynamic, Variant, JSON, Time, Time64, QBit

Applies to (server side):

>

- Variant: ClickHouse >= 24.1.

- Dynamic: ClickHouse >= 24.5.

- New JSON (object) type: ClickHouse >= 24.8.

- All three are no longer experimental since `25.3`; on older servers,

you must enable the corresponding allow_experimental_*_type setting.

- Time / Time64: ClickHouse >= 25.6 and require

enable_time_time64_type: 1.

- QBit: ClickHouse >= 25.10 (experimental, gated by

allow_experimental_qbit_type); GA on 26.x.

Answer checklist

When answering about storing and reading JSON objects:

• Use the new JSON column type, introduced in ClickHouse >= 24.8.
• Say JSON is no longer experimental since ClickHouse 25.3; on older

supported versions, enable allow_experimental_json_type.

• State the version policy in your explanation AND inline in the code.

When you set allow_experimental_json_type (or any allow_experimental_*_type) in code, you must do BOTH of the following: 1. Put an inline comment directly above the setting that names the version where the type was introduced and the version where it became non-experimental. The comment is the durable version provenance — it lives in the user's source file long after the chat reply is gone. 2. Repeat the version policy in your prose reply.

For the JSON column type the inline comment must look like:

  clickhouse_settings: {
    // JSON type introduced in ClickHouse 24.8, non-experimental since 25.3.
    // This setting is required only on 24.8–25.2; harmless on >= 25.3.
    allow_experimental_json_type: 1,
  }

Without the inline comment, a reader on a newer server has no idea the setting is a no-op and a reader on an older server has no idea why it's required.

• Insert real JS objects with format: 'JSONEachRow'; do not

JSON.stringify() the column value.

• Read with a JSON output format such as JSONEachRow and resultSet.json();

JSON column values come back as parsed JS objects.

Dynamic, Variant(...), JSON

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

const client = createClient({
  clickhouse_settings: {
    // Variant introduced in 24.1, Dynamic in 24.5, JSON in 24.8.
    // All three are non-experimental since 25.3; these settings are
    // required only on 24.1–25.2 and are harmless on >= 25.3.
    allow_experimental_variant_type: 1,
    allow_experimental_dynamic_type: 1,
    allow_experimental_json_type: 1,
  },
});

await client.command({
  query: `
    CREATE OR REPLACE TABLE chjs_dynamic_variant_json
    (
      id      UInt64,
      var     Variant(Int64, String),
      dynamic Dynamic,
      json    JSON
    )
    ENGINE MergeTree
    ORDER BY id
  `,
});

await client.insert({
  table: "chjs_dynamic_variant_json",
  format: "JSONEachRow",
  values: [
    { id: 1, var: 42, dynamic: "foo", json: { foo: "x" } },
    { id: 2, var: "str", dynamic: 144, json: { bar: 10 } },
  ],
});

const rs = await client.query({
  query: `
    SELECT *,
           variantType(var),
           dynamicType(dynamic),
           dynamicType(json.foo),
           dynamicType(json.bar)
    FROM chjs_dynamic_variant_json
  `,
  format: "JSONEachRow",
});
console.log(await rs.json());

Outputs:

[
  {
    id: "1",
    var: "42",
    dynamic: "foo",
    json: { foo: "x" },
    "variantType(var)": "Int64",
    "dynamicType(dynamic)": "String",
    "dynamicType(json.foo)": "String",
    "dynamicType(json.bar)": "None",
  },
  {
    id: "2",
    var: "str",
    dynamic: "144",
    json: { bar: "10" },
    "variantType(var)": "String",
    "dynamicType(dynamic)": "Int64",
    "dynamicType(json.foo)": "None",
    "dynamicType(json.bar)": "Int64",
  },
];

Notes

• The JSON column type accepts a real JS object on insert and returns one

on select — no need for JSON.stringify / JSON.parse in your app code.

• A JS number written into a Dynamic or Variant column defaults to

Int64 on the server. In JSON formats, output_format_json_quote_64bit_integers controls how 64-bit integers are returned: 1 returns them as JSON strings, while 0 returns them as JSON numbers (and 0 is the default since CH 25.8). In JS, large 64-bit integers returned as numbers can lose precision, so use quoted output if you need exact integer values in application code.

• Use variantType(...), dynamicType(...) to introspect what the server

ended up storing.

Time and Time64(p)

Time is signed seconds (-999:59:59 … 999:59:59). Time64(p) adds sub-second precision (p digits, up to 9 for nanoseconds). Both require enable_time_time64_type: 1 on >= 25.6.

const client = createClient({
  clickhouse_settings: { enable_time_time64_type: 1 },
});

await client.command({
  query: `
    CREATE OR REPLACE TABLE chjs_time_time64
    (
      id    UInt64,
      t     Time,
      t64_0 Time64(0),
      t64_3 Time64(3),
      t64_6 Time64(6),
      t64_9 Time64(9),
    )
    ENGINE MergeTree
    ORDER BY id
  `,
});

await client.insert({
  table: "chjs_time_time64",
  format: "JSONEachRow",
  values: [
    {
      id: 1,
      t: "12:34:56",
      t64_0: "12:34:56",
      t64_3: "12:34:56.123",
      t64_6: "12:34:56.123456",
      t64_9: "12:34:56.123456789",
    },
    {
      id: 2,
      t: "999:59:59",
      t64_0: "999:59:59",
      t64_3: "999:59:59.999",
      t64_6: "999:59:59.999999",
      t64_9: "999:59:59.999999999",
    },
    {
      id: 3,
      t: "-999:59:59",
      t64_0: "-999:59:59",
      t64_3: "-999:59:59.999",
      t64_6: "-999:59:59.999999",
      t64_9: "-999:59:59.999999999",
    },
  ],
});

Notes

• Pass values as strings in the HH:MM:SS[.fraction] format. Negatives

are supported; the magnitude can exceed 24 hours.

• For Time64(p) with p > 3, do not use JS Date — it tops out at

millisecond precision and will silently truncate. Store nanosecond values separately and provide on stringify as needed.

QBit (vector search)

QBit(element_type, dimension) stores float vectors in bit-sliced ("transposed") form so approximate vector search can read only the most significant bit planes at query time, trading precision for I/O and CPU.

• element_type: BFloat16 | Float32 | Float64.
• dimension: number of elements in each vector.

Introduced in ClickHouse 25.10 as an experimental type (gated by allow_experimental_qbit_type) and GA on 26.x; the setting is a no-op on newer servers but required on 25.10.

Internally a QBit column is a Tuple(FixedString(N), ...) of bit planes, so the raw bytes are not valid UTF-8. JSON\* formats handle this transparently: the server serializes the column as the original numeric array on SELECT and accepts the same array shape on INSERT. Query the column as a vector and let ClickHouse handle the bit-plane layout — don't feed raw FixedString bytes through JSON yourself.

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

const tableName = `chjs_qbit`;
const client = createClient({
  clickhouse_settings: {
    // QBit introduced in ClickHouse 25.10 (experimental), GA since 26.x.
    // This setting is required only on 25.10; harmless/no-op on >= 26.x.
    allow_experimental_qbit_type: 1,
  },
});

await client.command({
  query: `
    CREATE OR REPLACE TABLE ${tableName}
    (
      id  UInt64,
      vec QBit(Float32, 8)
    )
    ENGINE MergeTree
    ORDER BY id
  `,
});

// Even though QBit is stored internally as a Tuple of FixedString bit planes,
// JSON* formats accept (and return) the original Array(Float32) shape.
const values = [
  { id: 1, vec: [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0] },
  { id: 2, vec: [8.0, 7.0, 6.0, 5.0, 4.0, 3.0, 2.0, 1.0] },
  { id: 3, vec: [1.5, 2.5, 3.5, 4.5, 5.5, 6.5, 7.5, 8.5] },
];
await client.insert({
  table: tableName,
  format: "JSONEachRow",
  values,
});

// Round-trip via JSONEachRow: the vec column comes back as an array of numbers.
const rs = await client.query({
  query: `SELECT id, vec FROM ${tableName} ORDER BY id`,
  format: "JSONEachRow",
});
const rows = await rs.json<{ id: number; vec: number[] }>();
// vec comes back unchanged as the original Float32 array.
console.log(rows);

// Approximate vector search via L2DistanceTransposed.
// The third argument is the precision in bits: lower = less I/O, less accurate.
const search = await client.query({
  query: `
    SELECT id,
           L2DistanceTransposed(vec, {ref:Array(Float32)}, {bits:UInt8}) AS dist
    FROM ${tableName}
    ORDER BY dist ASC
  `,
  query_params: {
    ref: [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0],
    bits: 16,
  },
  format: "JSONEachRow",
});
const nearest = await search.json<{ id: number; dist: number }>();
// The reference vector is exactly row #1, so it's the closest match (dist 0).
console.log(nearest);

await client.close();

Notes

• Insert and read QBit columns as plain numeric arrays with JSONEachRow;

the server transposes them into bit planes for you.

• Use L2DistanceTransposed(vec, ref, bits) for approximate nearest-neighbour

search. Bind ref and bits via query_params ({ref:Array(Float32)}, {bits:UInt8}) — never interpolate them into the SQL string.

• The bits argument is the precision in bits: lower values read fewer bit

planes (less I/O, faster, less accurate); higher values are more precise.

• The bit-plane subcolumns (vec.N) are FixedString and not valid

UTF-8. Selecting them directly with a JSON\* format forces the server to escape every byte as \uXXXX. If you need the raw planes, use a binary format such as RowBinary, or read them as hex/base64 (e.g. hex(vec.1)) to keep the JSON output UTF-8 safe.

Common pitfalls

• *Targeting old ClickHouse servers without the `allow_experimental_`

setting.** On < 25.3, CREATE TABLE will fail without them.

• Expecting `JSON`-column reads to be raw strings. They come back as

parsed objects in JSON formats.

• Inserting `Time64(9)` from JS `Date` and losing precision. Use a

string instead.

• **Reading a Variant/Dynamic value of type Int64 and being surprised

it's a string.** That's the standard 64-bit-integers-in-JSON behavior; see the troubleshooting skill if you need to change it.

• Avoid parsing Variant/Dynamic/JSON columns that mix strings and 64-bit

without checking their returned types first. Otherwise a number stored in a string will come back as a number or vice versa.

• Selecting `QBit` bit-plane subcolumns (`vec.N`) directly in JSON formats.

They are FixedString and not UTF-8; read them as hex/base64 or use a binary format. Read the whole vector (vec) as a numeric array instead.

Insert into Specific Columns / Other Databases

Applies to: all versions. The columns option (both forms) and the

database config field are universally supported.

Answer checklist

When explaining partial-column inserts:

• Show columns: ['col_a', 'col_b'] for the allowlist form.
• Also mention the inverse columns: { except: ['col_to_skip'] } form so the

user knows both supported shapes.

• Explain that omitted columns receive their server-side defaults

(DEFAULT, MATERIALIZED, ALIAS, nullable/type defaults) and inserts can still fail or produce surprising zero/empty values if the table definition has no appropriate defaults.

Insert into specific columns

Pass columns: string[] to limit the INSERT to a subset. Omitted columns get their declared default.

await client.insert({
  table: "events",
  columns: ["message"], // the rest of the events table columns get their DEFAULTs
  format: "JSONEachRow",
  values: [{ message: "foo" }],
});

Insert excluding columns

Use columns: { except: string[] } for the inverse. Useful when most columns should default but you want to name only the few to skip.

await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [{ message: "bar" }],
  columns: { except: ["id"] },
});

Tables with EPHEMERAL columns

Ephemeral columns are not stored — they only exist to drive DEFAULT expressions of other columns. To trigger that default logic, the ephemeral column must be in the `columns` list, even though no value will be persisted for it.

await client.command({
  query: `
    CREATE OR REPLACE TABLE events
    (
      id              UInt64,
      message         String DEFAULT message_default,
      message_default String EPHEMERAL
    )
    ENGINE MergeTree
    ORDER BY id
  `,
});

await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [
    { id: "42", message_default: "foo" },
    { id: "144", message_default: "bar" },
  ],
  // Including the ephemeral column name triggers the DEFAULT expression
  columns: ["id", "message_default"],
});

Insert into a different database

If the client's default database is not the target, qualify the table name with db.table:

const client = createClient({ database: "system" });

await client.command({ query: "CREATE DATABASE IF NOT EXISTS analytics" });

await client.insert({
  table: "analytics.events", // fully qualified
  format: "JSONEachRow",
  values: [{ id: 42, message: "foo" }],
});

There is no per-call database override on insert() / query() — qualify the identifier, or create a second client with the desired database.

Common pitfalls

• Forgetting the ephemeral column in `columns`. If you list only the

non-ephemeral columns, the DEFAULT expression that depends on the ephemeral value won't fire and you'll get empty/zero defaults instead.

• Hoping `client.insert({ database: '…' })` works. It doesn't — qualify

the table instead.

• Mixing the two `columns` forms. Use either string[] _or_

{ except: string[] }, not both.

Insert Data Formats

Applies to: all versions. The JSON type column / new JSON family is a

ClickHouse feature; the JSON _formats_ listed here are universally supported

by the client.

**Raw / binary formats (CSV, TSV, CustomSeparated, Parquet) require a Node

stream as input.** Suggest streaming when the user wants to insert from a file or Readable.

Answer checklist

When answering "what format/call should I use for an array of JS objects?":

• Use client.insert({ table, values, format: 'JSONEachRow' }).
• Say the array of plain objects can be passed directly as values for

ordinary in-memory batches such as a few thousand or tens of thousands of rows.

• Do not steer the user to streaming, Parquet, or file APIs unless their input

is already a stream/file or the task is explicitly about throughput.

• Warn not to wrap JSONEachRow rows in a { data: [...] } envelope; that

shape belongs to single-document formats.

• Mention JSONCompactEachRow* as a denser alternative for larger payloads

when the caller can provide positional arrays or explicit names/types.

Default choice: JSONEachRow with an array of objects

This is the right answer for ~90% of inserts.

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

const client = createClient();

await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [
    { id: 42, name: "foo" },
    { id: 43, name: "bar" },
  ],
});

await client.close();

The shape of values must match the chosen format.

Streamable JSON formats (pass an array)

await client.insert({
  table: "events",
  format: "JSONCompactEachRowWithNamesAndTypes",
  values: [
    ["id", "name", "sku"],
    ["UInt32", "String", "Array(UInt32)"],
    [11, "foo", [1, 2, 3]],
    [12, "bar", [4, 5, 6]],
  ],
});

These formats can be streamed — pass a Node stream of rows instead of an array. See `examples/node/performance/` for streaming guidance.

Single-document JSON formats (pass an object)

These cannot be streamed — the entire body is sent in one shot.

import type { InputJSON, InputJSONObjectEachRow } from "@clickhouse/client";

const meta: InputJSON["meta"] = [
  { name: "id", type: "UInt32" },
  { name: "name", type: "String" },
];

await client.insert({
  table: "events",
  format: "JSONCompact",
  values: {
    meta,
    data: [
      [19, "foo"],
      [20, "bar"],
    ],
  },
});

await client.insert({
  table: "events",
  format: "JSONObjectEachRow",
  values: {
    row_1: { id: 23, name: "foo" },
    row_2: { id: 24, name: "bar" },
  } satisfies InputJSONObjectEachRow<{ id: number; name: string }>,
});

Quick chooser

Common pitfalls

• Wrong shape for the format. The most common cause of insert failures —

e.g., passing Array<{...}> to JSONCompact (which expects { meta, data }).

• Don't wrap a `JSONEachRow` array in a `{ data: [...] }` envelope. That

envelope only belongs to single-document formats (JSON / JSONCompact / JSONColumnsWithMetadata).

• For type guidance (Decimal strings, Date objects, BigInt), see

insert-values.md and custom-json.md.

• Use runtime type checkers like `zod` or `io-ts` if your app ingests untrusted JSON.

It's easier to debug mismatches between your data and the format's expected shape with a validation library used at the place of ingestion than with ClickHouse errors, especially in the middle of a large insert batch or streaming operation.

Insert Values, SQL Expressions, Dates, Decimals

Applies to: all versions. wait_end_of_query: 1 is a server-side

setting available on every supported ClickHouse version.

INSERT … SELECT (no values payload)

When the data already lives in ClickHouse, use client.command() with a raw INSERT … SELECT:

await client.command({
  query: `
    INSERT INTO target
    SELECT * FROM source
  `,
});

Use command() (not insert()) — there is no row payload to send.

INSERT … VALUES with SQL functions

When you need unhex(...), toUUID(...), now(), or any other SQL function around a value, keep the SQL shape static and pass values with ClickHouse {name: Type} parameters. Run it via command() and set wait_end_of_query: 1 for safety in clustered setups.

await client.command({
  query: `
    INSERT INTO events (id, timestamp, email, name)
    VALUES (
      unhex({id: String}),
      {timestamp: DateTime},
      {email: String},
      {name: Nullable(String)}
    )
  `,
  query_params: {
    id: "00112233445566778899aabbccddeeff",
    timestamp: "2026-05-06 12:34:56",
    email: "alice@example.com",
    name: "Alice",
  },
  clickhouse_settings: { wait_end_of_query: 1 },
});

Do not build VALUES rows with string interpolation or manual escaping. If you need to insert many ordinary JS rows, prefer client.insert() with format: 'JSONEachRow'; use this command() pattern when the SQL itself needs functions or expressions around the values.

Inserting JS Date objects

JS Date objects work for DateTime and DateTime64 columns once the server is set to accept ISO-8601 strings. Either set date_time_input_format: 'best_effort' per request, on the client, or session-wide.

await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [{ id: "42", dt: new Date() }],
  clickhouse_settings: {
    date_time_input_format: "best_effort", // default on the Cloud
  },
});

JS Date objects do not work for the Date type (date-only) — pass

'YYYY-MM-DD' strings for that.

Inserting Decimal* values

IMPORTANT: Make sure that the application code you're working on or the user prompt clearly indicates that floats are not used anywhere for decimal values. The most common scenario is using floats for money amounts in the app while the database uses Decimal for them. In that case, the app code should be changed to use a proper decimal library and serialization strategy (custom serializer or a class using toJSON()) to string instead of JS number.

Decimals must be passed as strings in JSON formats to avoid precision loss in JavaScript:

await client.command({
  query: `
    CREATE OR REPLACE TABLE prices (
      id     UInt32,
      dec32  Decimal(9, 2),
      dec64  Decimal(18, 3),
      dec128 Decimal(38, 10),
      dec256 Decimal(76, 20)
    )
    ENGINE MergeTree ORDER BY id
  `,
});

await client.insert({
  table: "prices",
  format: "JSONEachRow",
  values: [
    {
      id: 1,
      dec32: "1234567.89",
      dec64: "123456789123456.789",
      dec128: "1234567891234567891234567891.1234567891",
      dec256:
        "12345678912345678912345678911234567891234567891234567891.12345678911234567891",
    },
  ],
});

When reading them back, cast to string in the SELECT to avoid the same precision loss:

const rs = await client.query({
  query: `
    SELECT toString(dec64)  AS decimal64,
           toString(dec128) AS decimal128
    FROM prices
  `,
  format: "JSONEachRow",
});

Inserting a UUID into a UInt128 column

ClickHouse converts a UUID into UInt128 implicitly only for the `VALUES` clause. With the row-oriented JSON formats the client uses (e.g. JSONEachRow), sending a UUID string such as '019982cb-3abf-7e12-9668-c788a9e3639c' for a UInt128 column fails with CANNOT_PARSE_INPUT_ASSERTION_FAILED. Use one of two patterns instead.

Pattern 1 — convert the UUID on the client and send it as a decimal string (recommended). A JS number cannot hold 128 bits without precision loss, so always pass UInt128 as a string:

import * as crypto from "node:crypto";

function uuidToUInt128(uuid: string): string {
  // 8-4-4-4-12 hex digits → 32 hex digits → BigInt → decimal string
  return BigInt("0x" + uuid.replace(/-/g, "")).toString();
}

await client.command({
  query: `
    CREATE OR REPLACE TABLE events (id UInt128, description String)
    ENGINE MergeTree ORDER BY id
  `,
});

const uuid = crypto.randomUUID();
await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [{ id: uuidToUInt128(uuid), description: "converted on the client" }],
});

UInt128 values are also too wide for a JS number when reading back — cast them with toString(id) in the SELECT to avoid precision loss.

Pattern 2 — declare the UUID column as `EPHEMERAL` and let ClickHouse populate the UInt128 column via its DEFAULT expression:

await client.command({
  query: `
    CREATE OR REPLACE TABLE events
    (
      id          UInt128 DEFAULT id_uuid,
      id_uuid     UUID EPHEMERAL,
      description String
    )
    ENGINE MergeTree ORDER BY id
  `,
});

await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [{ id_uuid: uuid, description: "populated via EPHEMERAL column" }],
  // The ephemeral column must be listed so the DEFAULT on `id` is evaluated.
  columns: ["id_uuid", "description"],
});

See reference/insert-columns.md for more on EPHEMERAL columns and why they must appear in columns.

Common pitfalls

• Using `client.insert()` for `INSERT … SELECT`. There's nothing to

upload — use client.command() with the full SQL.

• Forgetting `date_time_input_format: 'best_effort'` when inserting

Date objects (or ISO strings). The default input format does not accept ISO-8601 with the T/Z separators.

• Hand-building `VALUES` with user input. Always parameterize user data;

see reference/query-parameters.md.

• Using floats in the app and expect `Decimal` columns to store them safely. Use a proper decimal library and pass them as strings to avoid precision loss.
• Sending a UUID string for a `UInt128` column in `JSONEachRow`. The

implicit UUID → UInt128 cast only happens in the VALUES clause; in JSON formats convert the UUID to its 128-bit decimal string on the client (or use an EPHEMERAL UUID column with a UInt128 DEFAULT).

Ping the Server

Applies to: all versions. ping() returns a discriminated union

PingResult = { success: true } | { success: false, error: Error } —

it does not throw on connection failures.

Answer checklist

When answering "how do I health-check / readiness-probe ClickHouse?":

• Use await client.ping() (or ping({ select: true })) and branch on

result.success directly — do not wrap in try/catch as the only check, and do not substitute query('SELECT 1').

• For a readiness probe / "can it serve traffic", recommend

client.ping({ select: true }) so credentials and the query layer are validated, not just the socket.

• Always contrast the two forms explicitly in your answer, even when

you're recommending one: plain client.ping() hits /ping (TCP/HTTP reachability only — does not validate credentials or query processing); client.ping({ select: true }) issues a lightweight SELECT 1 (validates auth and query path). Name both and say which to use for liveness vs readiness.

• Recommend lowering request_timeout on the client used for probes so

they fail fast instead of hanging on the default timeout — pick a value comparable to the probe interval (e.g., 1500–2000 ms for a 2-second-interval probe).

Successful ping

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

const client = createClient({
  url: process.env.CLICKHOUSE_URL,
  password: process.env.CLICKHOUSE_PASSWORD,
});

const pingResult = await client.ping();
if (pingResult.success) {
  console.info("ClickHouse is reachable");
} else {
  console.error("Ping failed:", pingResult.error);
}
await client.close();

Use ping() to:

• Probe ClickHouse at application startup.
• Wake up a ClickHouse Cloud instance that may be idling (a ping is enough to

bring it out of sleep).

• Implement a /healthz / readiness endpoint.

Failure: host unreachable

ping() does not throw — it resolves with { success: false, error: Error }, so you can branch without try/catch:

import type { PingResult } from "@clickhouse/client";
import { createClient } from "@clickhouse/client";

const client = createClient({
  url: "http://localhost:8100", // non-existing host
  request_timeout: 50, // keep failure fast
});

const pingResult = await client.ping();
if (hasConnectionRefusedError(pingResult)) {
  console.info("Connection refused, as expected");
} else {
  console.error("Ping expected ECONNREFUSED, got:", pingResult);
}
await client.close();

function hasConnectionRefusedError(
  pingResult: PingResult,
): pingResult is PingResult & { error: { code: "ECONNREFUSED" } } {
  return (
    !pingResult.success &&
    "code" in pingResult.error &&
    pingResult.error.code === "ECONNREFUSED"
  );
}

Mapping to an HTTP health endpoint

app.get("/healthz", async (_req, res) => {
  const r = await client.ping();
  if (r.success) {
    res.status(200).json({ ok: true });
  } else {
    res.status(503).json({ ok: false, error: String(r.error) });
  }
});

ping() vs ping({ select: true })

The default ping() hits ClickHouse's /ping HTTP endpoint — it verifies network connectivity but does not check credentials or query processing. A server that is reachable but has a bad password (or a broken query pipeline) will still return { success: true } from a plain ping().

Pass { select: true } to run a lightweight SELECT 1 instead:

const r = await client.ping({ select: true });
// success only if the server is reachable AND auth is correct AND it can run queries

When to use which:

• Liveness probe (is the process alive?) — plain ping() is fine.
• Readiness probe (can it serve traffic?) — use ping({ select: true })

so the probe fails if credentials are wrong or the query layer is broken.

• Waking a ClickHouse Cloud idle instance — plain ping() is enough.

Common pitfalls

• Do not wrap `ping()` in `try/catch` as your only check. It resolves on

failure; the success boolean is the source of truth.

• Lower `request_timeout` if you want pings to fail fast (the example

above uses 50 ms). The default is high enough to be unsuitable for liveness probes.

• Plain `ping()` does not check credentials. If auth is part of what you

want to verify, use ping({ select: true }).

• For ping that times out specifically, see the troubleshooting skill.
• Only ping the ClickHouse server in your app's liveness probe if the app

has to be restarted to recover from a ClickHouse outage. If the app can recover the connection to ClickHouse without a restart, put the ping in a readiness probe instead so the app doesn't get killed unnecessarily.

Query Parameter Binding

Applies to: all versions. NULL parameter binding fixed in 0.0.16.

Special-character (tab/newline/quote/backslash) binding >= 0.3.1.

TupleParam and JS Map parameters >= 1.9.0. Boolean formatting in

Array/Tuple/Map parameters fixed in >= 1.13.0. BigInt query

parameters >= 1.15.0.

Answer checklist

When the user passes user-controlled values into SQL:

• Use ClickHouse {name: Type} placeholders and a query_params object.
• **Your response must explicitly name template-literal / string

interpolation of user input as a SQL injection risk** — even when the user only asked "how do I bind values" and did not mention security. This is non-negotiable: the security framing is part of the right answer, not an optional aside.

• Do not suggest PostgreSQL/MySQL-style $1, ?, or :name placeholders.
• Pick the placeholder type to match the ClickHouse column type (String,

Date, DateTime, Nullable(T), etc.).

Syntax: {name: Type}

ClickHouse uses {name: Type} placeholders — not $1, ?, or :name.

await client.query({
  query: "SELECT plus({a: Int32}, {b: Int32})",
  format: "JSONEachRow",
  query_params: { a: 10, b: 20 },
});

The Type must be a valid ClickHouse type (Int32, String, Date, Array(UInt32), Tuple(Int32, String), Map(K, V), Nullable(T), etc.).

⚠️ Never use template literals for user values

Interpolating user input into the SQL string bypasses server-side escaping and opens the door to SQL injection:

const userId = req.params.id;

// ❌ Dangerous — never do this with user-controlled values
await client.query({ query: `SELECT * FROM users WHERE id = ${userId}` });

// ✓ Safe — parameterized
await client.query({
  query: "SELECT * FROM users WHERE id = {id: UInt32}",
  query_params: { id: userId },
});

This is the most common mistake for users coming from PostgreSQL/MySQL. Call it out explicitly when the user shows template-literal interpolation.

Common types

import { TupleParam } from "@clickhouse/client";

await client.query({
  query: `
    SELECT
      {var_int: Int32}                     AS var_int,
      {var_float: Float32}                 AS var_float,
      {var_str: String}                    AS var_str,
      {var_array: Array(Int32)}            AS var_array,
      {var_tuple: Tuple(Int32, String)}    AS var_tuple,
      {var_map: Map(Int, Array(String))}   AS var_map,
      {var_date: Date}                     AS var_date,
      {var_datetime: DateTime}             AS var_datetime,
      {var_datetime64_3: DateTime64(3)}    AS var_datetime64_3,
      {var_datetime64_9: DateTime64(9)}    AS var_datetime64_9,
      {var_decimal: Decimal(9, 2)}         AS var_decimal,
      {var_uuid: UUID}                     AS var_uuid,
      {var_ipv4: IPv4}                     AS var_ipv4,
      {var_null: Nullable(String)}         AS var_null
  `,
  format: "JSONEachRow",
  query_params: {
    var_int: 10,
    var_float: "10.557",
    var_str: "hello",
    var_array: [42, 144],
    var_tuple: new TupleParam([42, "foo"]), // >= 1.9.0
    var_map: new Map([
      [42, ["a", "b"]],
      [144, ["c", "d"]],
    ]), // >= 1.9.0
    var_date: "2022-01-01",
    var_datetime: "2022-01-01 12:34:56", // or a Date
    var_datetime64_3: "2022-01-01 12:34:56.789", // or a Date
    var_datetime64_9: "2022-01-01 12:34:56.123456789", // string for ns precision
    var_decimal: "123.45", // string to avoid precision loss
    var_uuid: "01234567-89ab-cdef-0123-456789abcdef",
    var_ipv4: "192.168.0.1",
    var_null: null, // fixed in 0.0.16
  },
});

Type-by-type tips

• Decimals — pass as strings to avoid JS number precision loss.
• `DateTime64(>3)` — pass as a string; JS Date only has millisecond

precision and will lose sub-millisecond digits.

• `DateTime64` — strings can also be UNIX timestamps, including

fractional ones (e.g., '1651490755.123456789').

• `BigInt` — supported in query_params since >= 1.15.0. On older

clients, pass as a string.

• `Tuple(...)` — wrap in new TupleParam([...]) (>= 1.9.0); on older

clients, build the literal manually as a string.

• `Map(K, V)` — pass a JS Map (>= 1.9.0); on older clients, build

it manually.

• `Nullable(T)` — pass null directly (>= 0.0.16).

Special characters in string parameters (>= 0.3.1)

Tabs, newlines, carriage returns, single quotes, and backslashes are escaped automatically by the client — just pass the JS string as-is:

await client.query({
  query: `
    SELECT
      'foo_\t_bar'  = {tab: String}             AS has_tab,
      'foo_\n_bar'  = {newline: String}         AS has_newline,
      'foo_\\'_bar' = {single_quote: String}    AS has_single_quote,
      'foo_\\_bar'  = {backslash: String}       AS has_backslash
  `,
  format: "JSONEachRow",
  query_params: {
    tab: "foo_\t_bar",
    newline: "foo_\n_bar",
    single_quote: "foo_'_bar",
    backslash: "foo_\\_bar",
  },
});

Common pitfalls

• `$1` / `?` / `:name` placeholders. None work — use {name: Type}.
• Forgetting the type in the placeholder. {id} is a syntax error;

it must be {id: UInt32}.

• Stringifying tuples/maps manually on `>= 1.9.0`. Use TupleParam

and Map — both serialize correctly and respect special characters.

• Boolean array/tuple/map elements before `1.13.0`. Boolean formatting

was fixed in 1.13.0 — earlier versions may misformat them.

Select Data Formats

Applies to: all versions. JSONEachRowWithProgress requires client

>= 1.7.0; see the in-repo performance examples under

examples/node/performance/.

Default choice: JSONEachRow → .json<T>()

Right answer for ~90% of selects when the result fits in memory.

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

interface Row {
  number: string;
}

const client = createClient();
const rows = await client.query({
  query: "SELECT number FROM system.numbers LIMIT 5",
  format: "JSONEachRow",
});
const result = await rows.json<Row>(); // Row[]
result.forEach((r) => console.log(r));
// { number: '0' }
// { number: '1' }
// ...
await client.close();

UInt64/Int64 and other 64-bit integers are returned as strings when output_format_json_quote_64bit_integers=1, to avoid JS precision loss. If that setting is 0, they may be returned as unquoted JSON numbers instead. Note that in ClickHouse >= 25.8, this setting can default to 0; see the troubleshooting skill for ways to control that.

Single-document JSON format with metadata

Use JSON (or JSONCompact) when you need ClickHouse's response envelope (rows + meta + statistics + row count). Type the result with ResponseJSON<T>:

import { createClient, type ResponseJSON } from "@clickhouse/client";

const client = createClient();
const rows = await client.query({
  query: "SELECT number FROM system.numbers LIMIT 2",
  format: "JSON",
});
const result = await rows.json<ResponseJSON<{ number: string }>>();
console.info(result.meta, result.data, result.rows, result.statistics);
await client.close();

JSON, JSONCompact, JSONStrings, JSONCompactStrings,

JSONColumnsWithMetadata, JSONObjectEachRow are single-document

formats — they cannot be streamed. Use a *EachRow variant if you want

to stream.

Selecting raw text (CSV / TSV / CustomSeparated)

Use .text() (not .json()) for raw textual formats:

const rs = await client.query({
  query: "SELECT number, number * 2 AS doubled FROM system.numbers LIMIT 3",
  format: "CSVWithNames",
});
console.log(await rs.text());

Streaming raw text/Parquet line-by-line belongs in `examples/node/performance/` — in particular, Parquet exports use client.exec() and pipe the raw response stream rather than ResultSet.stream() (see `select_parquet_as_file.ts`).

Format chooser

ResultSet methods

Common pitfalls

• **Calling .json() on a JSON (single-doc) result and expecting an

array.** You get a ResponseJSON<T> object; the rows are under .data. Use JSONEachRow if you want a flat array.

• Leaving a `stream()` half-consumed. This is a top cause of

ECONNRESET on the _next_ request — fully iterate the stream or call resultSet.close() (synchronous — no await). (Diagnosis details live in the troubleshooting skill.)

• Reaching for `.json()` on a CSV/TSV result. Use .text() (or

.stream() for large results).

Sessions and Temporary Tables

Applies to: all versions. session_id is a server-level concept; the

client just forwards it on every request that names it.

Answer checklist

When answering "temp table disappears between calls" / "how do I share a session" / anything involving session_id:

• State plainly that **temporary tables and session-scoped state are tied

to a session_id** — without a stable session_id across calls, every request gets a fresh server-side session and the temp table is gone.

• Set session_id via crypto.randomUUID() either on createClient or

per-call.

• Warn that **session_id on a global / module-static client is an

anti-pattern** in any concurrent app (Express, server actions, workers, etc.) — concurrent requests will share the same session and trip "Session is locked by a concurrent client". Recommend a short-lived per-workflow client or per-call session_id instead.

• If session_id is set on the client, also set max_open_connections: 1

to serialize calls and avoid the concurrent-session error.

• For ClickHouse Cloud or any load-balanced deployment: **explicitly

recommend replica-aware routing or a single-node hostname** as the primary remedy when sessions are needed. Sessions are pinned to one node; behind an LB, consecutive requests may land on different nodes and the temp table will appear to vanish. "Just collapse the workflow into one handler" or "use a non-temporary table" are valid fallbacks but secondary — name the routing fix first.

When you need a session

Use a session_id whenever multiple calls must share server-side state:

• CREATE TEMPORARY TABLE (the table only exists within its session).
• SET <setting> = <value> to apply for subsequent queries on the same

session.

• Any other server feature scoped per session (e.g., session-scoped

variables in newer ClickHouse versions).

⚠️ session_id and concurrency

ClickHouse rejects concurrent queries within the same session — if two requests arrive at the server at the same time sharing the same session_id, the second one gets an error like "Session is locked by a concurrent client". This has two practical implications:

1. Do not set `session_id` on a global / module-static client that handles concurrent requests (e.g., an Express app's shared client). Every in-flight request would share the same session and collide under load. 2. If you do set `session_id` on a client, restrict its concurrency: set max_open_connections: 1 so at most one request is in flight at a time, turning the pool into a serial queue. This is fine for a dedicated per-workflow client but wrong for a shared application client.

The right pattern for application code: create a short-lived client (or use per-request session_id) scoped to a single logical workflow, not to the entire process.

Per-client session_id

Appropriate when one client handles exactly one sequential workflow (a script, a background job, a single user's session that you've already manually serialized in the code).

import { createClient } from "@clickhouse/client";
import * as crypto from "node:crypto";

const client = createClient({
  session_id: crypto.randomUUID(),
  max_open_connections: 1, // safeguard against concurrent-session errors
});

await client.command({
  query: "CREATE TEMPORARY TABLE temporary_example (i Int32)",
});

await client.insert({
  table: "temporary_example",
  values: [{ i: 42 }, { i: 144 }],
  format: "JSONEachRow",
});

const rs = await client.query({
  query: "SELECT * FROM temporary_example",
  format: "JSONEachRow",
});
console.info(await rs.json());
await client.close();

Session-level SET commands

SET only persists within a session. With session_id defined on the client, every subsequent call inherits the change.

import { createClient } from "@clickhouse/client";
import * as crypto from "node:crypto";

const client = createClient({
  session_id: crypto.randomUUID(),
  max_open_connections: 1, // safe-guard against concurrent-session errors
});

await client.command({
  query: "SET output_format_json_quote_64bit_integers = 0",
  clickhouse_settings: { wait_end_of_query: 1 }, // ack before next call
});

const rs1 = await client.query({
  query: "SELECT toInt64(42)",
  format: "JSONEachRow",
});
// → 64-bit integers come back as numbers in this query

await client.command({
  query: "SET output_format_json_quote_64bit_integers = 1",
  clickhouse_settings: { wait_end_of_query: 1 },
});

const rs2 = await client.query({
  query: "SELECT toInt64(144)",
  format: "JSONEachRow",
});
// → 64-bit integers come back as strings again

await client.close();

`wait_end_of_query: 1` matters here. Without it, a SET on one

connection in the pool may not yet be applied when the next query lands

on the same socket.

Per-request session_id

You can also pass session_id on a single query() / insert() / command() call to override (or set) it for that one request.

⚠️ Sessions and load balancers / ClickHouse Cloud

Sessions are bound to a specific ClickHouse node. If a load balancer in front of ClickHouse routes consecutive requests to different nodes, the temporary table / SET won't be visible — you'll get UNKNOWN_TABLE / surprising results.

Mitigations (in order of preference):

• **For ClickHouse Cloud, use [replica-aware

routing](https://clickhouse.com/docs/manage/replica-aware-routing)** so consecutive requests in the same session land on the same node. This is the right primary fix when you need sessions in a Cloud deployment.

• Talk to a single node directly (e.g., a node-pinned hostname) when

routing isn't an option.

• As a fallback only: avoid sessions for cross-node workflows and persist

intermediate state in a regular (non-temporary) table instead. This trades the session requirement away rather than fixing it — use it only if replica-aware routing / single-node connections aren't available.

Common pitfalls

• **Forgetting session_id and being surprised that

CREATE TEMPORARY TABLE "disappears."** Without a session, every request may land on a different connection / server context.

• Setting `session_id` on a shared application client. Under concurrent

load, two in-flight requests will share the same session and one will fail with "Session is locked by a concurrent client". Use per-request session_id or a dedicated short-lived client instead.

• Reusing the same `session_id` across unrelated workflows. A second

session-using consumer will trip over your temporary tables and SET values. Generate a fresh UUID per logical session.

• Leaving session state pinned for the lifetime of the process. If

long-lived clients accumulate SET / temp-table state, consider creating a short-lived sub-client with its own session_id for the unit of work.

• Skipping `wait_end_of_query: 1` on `SET` — race conditions between

SET and the next query can show up under load.