Clickhouse Js Node Troubleshooting

ClickHouse Node.js Client Troubleshooting

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. Identify the issue — match symptoms to the Issue Index below and read the corresponding reference file. 2. Lead with the diagnosis — explain what's likely causing the issue before giving the fix. 3. Note version constraints — flag if a fix requires a minimum client version and check it against what the user provided. 4. Ask only what's missing — if the fix is version-dependent and you don't know their version, ask; otherwise help immediately.

---

Issue Index

Identify the user's issue from the list below and read the corresponding reference file for detailed troubleshooting steps.

---

Still Stuck?

• JS client source + full examples
• ClickHouse JS client docs
• ClickHouse supported formats

Compression Not Working

Applies to: all versions. Response compression was enabled by default in < 1.0.0 and disabled by default since `>= 1.0.0` — you must explicitly enable it. Request compression has always been opt-in.

Both request and response compression are supported. Only GZIP is supported (via zlib).

import { createClient } from '@clickhouse/client'
const client = createClient({
  compression: {
    response: true,
    request: true,
  },
})

Compression enabled but getting an error?

If you enable compression.response: true and get a ClickHouse settings error, you are likely connecting as a readonly=1 user. Response compression requires the enable_http_compression setting, which read-only users cannot change.

See `reference/readonly-users.md` for the fix.

Compression enabled but response doesn't seem compressed?

• Verify your version-specific defaults — response compression was enabled by default in < 1.0.0 and is disabled by default in >= 1.0.0, so on newer versions you must enable compression.response: true explicitly.
• Check that the ClickHouse server has HTTP compression enabled (enable_http_compression = 1 in server config). By default this is enabled on ClickHouse Cloud and most self-hosted setups.
• Request compression (compression.request: true) compresses the request body sent to ClickHouse. It has no effect on the response.

Data Type Mismatches

Large integers returned as strings

Applies to: all versions. The output_format_json_quote_64bit_integers ClickHouse setting is server-side and can be passed via clickhouse_settings in any client version.

UInt64, Int64, UInt128, Int128, UInt256, Int256 are serialized as strings in JSON* formats to prevent overflow (they exceed Number.MAX_SAFE_INTEGER).

To receive them as numbers (use with caution — precision loss possible):

const resultSet = await client.query({
  query: 'SELECT toUInt64(9007199254740993)',
  format: 'JSONEachRow',
  clickhouse_settings: { output_format_json_quote_64bit_integers: 0 },
})

Tip (`>= 1.15.0`): BigInt values are now supported in query parameters, so you can safely pass large integers as bind params without string workarounds.

Decimals losing precision on read

Applies to: all versions (this is a ClickHouse JSON serialization behavior). For custom JSON parse/stringify (e.g., using a BigInt-safe parser), see >= 1.14.0 which added configurable json.parse and json.stringify functions.

ClickHouse returns Decimals as numbers by default in JSON* formats. Cast to string in the query:

const resultSet = await client.query({
  query: `
    SELECT toString(my_decimal) AS my_decimal
    FROM my_table
  `,
  format: 'JSONEachRow',
})

When inserting, always use the string representation to avoid precision loss:

await client.insert({
  table: 'my_table',
  values: [{ dec64: '123456789123456.789' }],
  format: 'JSONEachRow',
})

Format Selection Quick Reference

⚠️ JSON and JSONCompact return a single object and cannot be streamed.

Date/DateTime insertion fails or produces wrong values

Applies to: all versions. Note that >= 0.2.1 changed Date object serialization to use time-zone-agnostic Unix timestamps instead of timezone-naive datetime strings, which fixed timezone mismatch issues between client and server.

• Date / Date32 columns accept strings only (e.g., '2024-01-15').
• DateTime / DateTime64 columns accept strings or JS Date objects. To use Date objects, set:

import { createClient } from '@clickhouse/client'
const client = createClient({
  clickhouse_settings: { date_time_input_format: 'best_effort' },
})

Logging Not Showing Anything

Requires: >= 0.2.0 (explicit log.level config option introduced in 0.2.0, replacing the CLICKHOUSE_LOG_LEVEL env var from 0.0.11). Custom LoggerClass also available since >= 0.2.0. In >= 1.18.1, the default changed from OFF to WARN and logging became lazy (messages only constructed if the log level matches). In >= 1.18.1, structured context fields (connection_id, query_id, request_id, socket_id) are available in logger args.

The default log level is OFF (for < 1.18.1) or WARN (for >= 1.18.1). Enable it explicitly:

import { ClickHouseLogLevel, createClient } from '@clickhouse/client'

const client = createClient({
  log: {
    level: ClickHouseLogLevel.DEBUG, // TRACE | DEBUG | INFO | WARN | ERROR
  },
})

To use a custom logger (e.g., to pipe to your observability stack), implement the Logger interface:

import { ClickHouseLogLevel, createClient } from '@clickhouse/client'
import type { Logger } from '@clickhouse/client'

class MyLogger implements Logger {
  debug({ module, message, args }) {
    /* ... */
  }
  info({ module, message, args }) {
    /* ... */
  }
  warn({ module, message, args, err }) {
    /* ... */
  }
  error({ module, message, args, err }) {
    /* ... */
  }
  trace({ module, message, args }) {
    /* ... */
  }
}

const client = createClient({
  log: { LoggerClass: MyLogger, level: ClickHouseLogLevel.INFO },
})

Proxy / Pathname URL Confusion

Requires: >= 1.0.0 (the pathname config option and URL-based configuration were introduced in 1.0.0). For < 1.0.0, a partial fix for pathname handling in the host parameter was shipped in 0.2.5.

Symptom: Wrong database is selected, or requests fail when ClickHouse is behind a proxy with a path prefix (e.g., http://proxy:8123/clickhouse_server).

Cause: Passing the pathname in url makes the client treat it as the database name.

Fix: Use the pathname option separately:

import { createClient } from '@clickhouse/client'

const client = createClient({
  url: 'http://proxy:8123',
  pathname: '/clickhouse_server', // leading slash optional; multiple segments supported
})

For proxies that require custom auth headers:

Requires: >= 1.0.0 (http_headers config option; replaces the deprecated additional_headers from >= 0.2.9). Per-request http_headers overrides are available since >= 1.11.0.

import { createClient } from '@clickhouse/client'

const client = createClient({
  http_headers: {
    'My-Auth-Header': 'secret',
  },
})

Query Parameters Not Interpolated

Applies to: all versions. NULL parameter binding was fixed in 0.0.16. Tuple support via TupleParam wrapper and JS Map as a query parameter were added in >= 1.9.0. BigInt values in query parameters are supported since >= 1.15.0. Boolean formatting in Array/Tuple/Map params was fixed in >= 1.13.0.

Use the {name: type} syntax in the query string and pass values via query_params:

await client.query({
  query: 'SELECT plus({val1: Int32}, {val2: Int32})',
  format: 'CSV',
  query_params: { val1: 10, val2: 20 },
})

Never use template literals for user values

When $1/? don't work, a common instinct is to interpolate values directly with a template literal. Don't — this bypasses ClickHouse's server-side escaping and opens the door to SQL injection:

// ❌ Dangerous — never do this with user-controlled values
const userId = req.params.id
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 },
})

Always bring this up when answering query-params questions, especially when the user is coming from another database (PostgreSQL, MySQL, etc.) — they're the most likely to reach for template literals as a fallback.

Common mistake: wrong parameter syntax

The ClickHouse JS client uses ClickHouse's native {name: type} syntax — not $1/?/:name placeholders from other databases:

// ❌ Wrong — these don't work
await client.query({
  query: 'SELECT * FROM t WHERE id = $1',
  query: 'SELECT * FROM t WHERE id = ?',
  query: 'SELECT * FROM t WHERE id = :id',
  query_params: { id: 42 },
})

// ✓ Correct
await client.query({
  query: 'SELECT * FROM t WHERE id = {id: UInt32}',
  query_params: { id: 42 },
})

Array parameters

await client.query({
  query: 'SELECT * FROM t WHERE id IN {ids: Array(UInt32)}',
  format: 'JSONEachRow',
  query_params: { ids: [1, 2, 3] },
})

Tuple parameters (>= 1.9.0)

Use the TupleParam wrapper to pass a tuple:

import { TupleParam, createClient } from '@clickhouse/client'

const client = createClient({
  url: 'http://localhost:8123',
})

await client.query({
  query: 'SELECT {t: Tuple(UInt32, String)}',
  format: 'JSONEachRow',
  query_params: { t: new TupleParam([42, 'hello']) },
})

Map parameters (>= 1.9.0)

Pass a JS Map directly:

await client.query({
  query: 'SELECT {m: Map(String, UInt32)}',
  format: 'JSONEachRow',
  query_params: { m: new Map([['key', 1]]) },
})

NULL parameters

Pass null directly — binding fixed in 0.0.16:

await client.query({
  query: 'SELECT {val: Nullable(String)}',
  format: 'JSONEachRow',
  query_params: { val: null },
})

Read-Only User Errors

Applies to: all versions. In >= 1.0.0, compression.response was changed to disabled by default specifically to avoid this confusing error for read-only users. If you are on < 1.0.0, response compression was enabled by default and you must explicitly disable it.

Symptom: Error when using compression: { response: true } with a readonly=1 user.

Cause: Response compression requires the enable_http_compression setting, which readonly=1 users cannot change. Note: request compression (compression: { request: true }) is unaffected by this restriction — only response compression triggers the error.

Fix: Remove response compression for read-only users:

import { createClient } from '@clickhouse/client'

// Don't do this with a readonly=1 user:
// compression: { response: true }

const client = createClient({
  username: 'my_readonly_user',
  password: '...',
  // compression omitted, or explicitly set to false
  compression: {
    response: false,
  },
})

Socket Hang-Up / ECONNRESET

Symptom: socket hang up or ECONNRESET errors, often intermittent.

Root cause: The server or load balancer closes the Keep-Alive connection before the client detects it and stops reusing the socket.

Quick triage:

• Errors on every request → likely dangling stream (Step 1–2)
• Errors only after idle periods → Keep-Alive timeout mismatch (Step 3)
• Errors on long-running queries (INSERT FROM SELECT, etc.) → load balancer idle timeout (Step 4)
• Can't diagnose → disable Keep-Alive as a last resort (Step 5)

Step 1 — Enable WARN-level logging to find dangling streams

Requires: >= 0.2.0 (logging support with log.level config option). In >= 1.18.1, the default log level changed from OFF to WARN, so this step may already be active. In >= 1.18.2, the client auto-emits a WARN log with Keep-Alive troubleshooting hints when an ECONNRESET is detected. In >= 1.12.0, a warning is logged when a socket is closed without fully consuming the stream.

import { createClient, ClickHouseLogLevel } from '@clickhouse/client'

const client = createClient({
  log: { level: ClickHouseLogLevel.WARN },
})

Look for log lines about unconsumed or dangling streams — these are a common hidden cause. A dangling stream is a query response stream that was never fully consumed or explicitly closed with ResultSet.close(). Because the Node.js client reuses sockets (Keep-Alive), leaving a stream open corrupts the socket and causes the _next_ request to fail with ECONNRESET. Errors on every request strongly suggest dangling streams rather than a Keep-Alive timeout mismatch.

Common dangling stream patterns:

// ❌ Wrong — result stream never consumed; socket is left open
const resultSet = await client.query({ query: 'SELECT ...' })
// result is abandoned without calling .json(), .text(), .stream(), or .close()

// ❌ Wrong — stream created but not fully piped/iterated
const resultSet = await client.query({
  query: 'SELECT ...',
  format: 'JSONEachRow',
})
const stream = resultSet.stream()
// stream is never iterated and resultSet is never closed

// ✓ Correct — consume via .json()
const resultSet = await client.query({ query: 'SELECT ...' })
const data = await resultSet.json()

// ✓ Correct — consume via async iteration
const resultSet = await client.query({
  query: 'SELECT ...',
  format: 'JSONEachRow',
})
for await (const rows of resultSet.stream()) {
  // process rows
}

// ✓ Correct — explicitly close; this destroys the underlying socket immediately
const resultSet = await client.query({ query: 'SELECT ...' })
resultSet.close()

Step 2 — Check your ESLint setup

Add the `no-floating-promises` ESLint rule. Unhandled promises leave streams dangling, which can cause the server to close the socket.

Even with await, if the returned ResultSet is not consumed (no .json(), .text(), .close(), or full stream iteration), the socket is left open. The ESLint rule catches the promise case; code review is needed for the "awaited but unconsumed result" case.

Step 3 — Find the server's Keep-Alive timeout

curl -v --data-binary "SELECT 1" <your_clickhouse_url>

Check the response headers:

< Connection: Keep-Alive
< Keep-Alive: timeout=10

Requires: >= 0.3.0 (keep_alive.idle_socket_ttl was introduced in 0.3.0 with a default of 2500 ms, replacing the older keep_alive.socket_ttl from 0.1.1 which was removed in 0.3.0).

The default idle_socket_ttl in the client is 2500 ms, which is safe for servers with a 3 s timeout (common in ClickHouse < 23.11). If your server has a higher timeout (e.g., 10 s), you can safely increase:

const client = createClient({
  keep_alive: {
    idle_socket_ttl: 9000, // stay ~500ms below the server's timeout
  },
})

⚠️ If you still get errors after increasing, lower the value, not raise it.

Tip (`>= 1.18.3`): Enable keep_alive.eagerly_destroy_stale_sockets: true to proactively destroy sockets that have been idle longer than idle_socket_ttl before each request. This helps when event loop delays prevent the idle timeout callback from firing on time.

Step 4 — Long-running queries with no data in/out (INSERT FROM SELECT, etc.)

Requires: >= 1.0.0 (request_timeout default was fixed to 30 000 ms in 0.3.0; url-based configuration including request_timeout via URL params available since 1.0.0).

Load balancers may close idle connections mid-query. Force periodic progress headers:

const client = createClient({
  request_timeout: 400_000, // e.g. 400s for long queries
  clickhouse_settings: {
    send_progress_in_http_headers: 1,
    http_headers_progress_interval_ms: '110000', // string — UInt64 type; set ~10s below LB idle timeout
  },
})

⚠️ Node.js caps total received headers at ~16 KB. After ~70–80 progress headers, an exception is thrown. For a query running longer than roughly http_headers_progress_interval_ms * 75, this limit will be hit — use a longer interval or the fire-and-forget approach below.

Alternatively — fire-and-forget (mutations only): Mutations (INSERT ... SELECT, OPTIMIZE, ALTER) are not cancelled on the server when the client connection is lost. You can send the mutation and immediately close the connection, then poll system.query_log or system.mutations for status. See the client repo examples for a concrete implementation.

Step 5 — Disable Keep-Alive entirely (last resort)

Requires: >= 0.1.1 (Keep-Alive disable option introduced in 0.1.1).

Adds overhead (new TCP connection per request) but eliminates all Keep-Alive issues:

const client = createClient({
  keep_alive: { enabled: false },
})

TLS / Certificate Errors

Requires: >= 0.0.8 (basic and mutual TLS support added in 0.0.8). For custom HTTP agent with TLS, see >= 1.2.0 (http_agent option); note that when using a custom agent, the tls config option is ignored.

Basic TLS (CA certificate only)

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

const client = createClient({
  url: 'https://<hostname>:<port>',
  username: '<user>',
  password: '<pass>',
  tls: {
    ca_cert: fs.readFileSync('certs/CA.pem'),
  },
})

Mutual TLS (client certificate + key)

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

const client = createClient({
  url: 'https://<hostname>:<port>',
  username: '<user>',
  tls: {
    ca_cert: fs.readFileSync('certs/CA.pem'),
    cert: fs.readFileSync('certs/client.crt'),
    key: fs.readFileSync('certs/client.key'),
  },
})

Tip (`>= 1.2.0`): If you need a custom HTTP(S) agent, use the http_agent option. Only set set_basic_auth_header: false if you must avoid sending the basic-auth Authorization header (for example, due to a header conflict); in that case, provide alternative auth headers such as X-ClickHouse-User / X-ClickHouse-Key via http_headers.

Common TLS errors

UNABLE_TO_VERIFY_LEAF_SIGNATURE / UNABLE_TO_GET_ISSUER_CERT_LOCALLY

Scenario A — Private/internal CA (most common for self-hosted): The server's certificate was issued by a private CA that Node.js doesn't trust. Pass the CA certificate explicitly:

tls: {
  ca_cert: fs.readFileSync('certs/CA.pem'),
}

Scenario B — ClickHouse Cloud: The CA is a well-known public CA; this error typically means the system CA bundle is outdated or the URL/hostname is wrong. Updating Node.js or the system certificates usually resolves it.

self signed certificate / self signed certificate in certificate chain

The server uses a self-signed cert (the certificate is its own CA). Options in order of preference:

1. Pass the self-signed cert as the CA:

   tls: {
     ca_cert: fs.readFileSync('certs/server.crt')
   }

2. For development only — disable verification via a custom agent (>= 1.2.0):

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

   const client = createClient({
     url: 'https://<hostname>:<port>',
     username: '<user>',
     password: '<pass>',
     http_agent: new https.Agent({ rejectUnauthorized: false }),
     // Optional: only disable the basic-auth Authorization header if you need to
     // provide alternative auth headers instead.
     set_basic_auth_header: false,
     http_headers: {
       'X-ClickHouse-User': '<user>',
       'X-ClickHouse-Key': '<pass>',
     },
   })

⚠️ Never use rejectUnauthorized: false in production — it disables all certificate verification.

ERR_SSL_WRONG_VERSION_NUMBER / ECONNREFUSED on HTTPS URL

The client is connecting with HTTPS but the server is listening on plain HTTP. Change the URL scheme to http:// or enable TLS on the ClickHouse server.