/clickhouse-js-node-troubleshooting

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

Inserting a UUID into a UInt128 column fails (CANNOT_PARSE_INPUT_ASSERTION_FAILED)

Applies to: all versions. This is a ClickHouse input-parsing behavior, not a client bug.

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.

Fix it with one of two patterns:

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) {
  // 8-4-4-4-12 hex digits → 32 hex digits → BigInt → decimal string
  return BigInt("0x" + uuid.replace(/-/g, "")).toString();
}

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

Read UInt128 back with toString(id) in the SELECT to avoid the same precision loss.

Pattern 2 — declare the UUID column as EPHEMERAL and let ClickHouse populate the UInt128 column via its DEFAULT expression. The ephemeral column must be listed in columns so the DEFAULT is evaluated:

// CREATE TABLE events (id UInt128 DEFAULT id_uuid, id_uuid UUID EPHEMERAL, description String) ...
await client.insert({
  table: "events",
  format: "JSONEachRow",
  values: [{ id_uuid: uuid, description: "populated via EPHEMERAL column" }],
  columns: ["id_uuid", "description"],
});

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