Insert Values

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

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.

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.

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:

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

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:

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:

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).