Setting the file. One moment.
Skill 07 · Clickhouse Js Node Troubleshooting
Subchapter 7.2
reference/data-types.mdMarkdown5 KBView on GitHub
Applies to: all versions. The
output_format_json_quote_64bit_integersClickHouse setting is server-side and can be passed viaclickhouse_settingsin 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.
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.0which added configurablejson.parseandjson.stringifyfunctions.
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",
});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"],
});| Use case | Recommended format | Min version |
|---|---|---|
| Insert/select JS objects | JSONEachRow | all |
| Bulk insert arrays | JSONEachRow | all |
| Stream large result sets | JSONEachRow, JSONCompactEachRow | all |
| CSV file streaming | CSV, CSVWithNames | all |
| Parquet file streaming | Parquet | >= 0.2.6 |
| Single JSON object response | JSON, JSONCompact | JSON all; JSONCompact >= 0.0.14 |
| Stream with progress | JSONEachRowWithProgress | >= 1.7.0 |
⚠️
JSONandJSONCompactreturn a single object and cannot be streamed.
Applies to: all versions. Note that
>= 0.2.1changed 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" },
});