Setting the file. One moment.
Skill 05 · Clickhouse Js Node Coding
Subchapter 5.10
reference/query-parameters.mdMarkdown5 KBView on GitHub
Applies to: all versions. NULL parameter binding fixed in
0.0.16. Special-character (tab/newline/quote/backslash) binding>= 0.3.1.TupleParamand JS parameters . Boolean formatting in // parameters fixed in . query parameters .
Map>= 1.9.0ArrayTupleMap>= 1.13.0BigInt>= 1.15.0When the user passes user-controlled values into SQL:
{name: Type} placeholders and a query_params object.$1, ?, or :name placeholders.String,
Date, DateTime, Nullable(T), etc.).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.).
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.
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
},
});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).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",
},
});$1 / ? / :name placeholders. None work — use {name: Type}.{id} is a syntax error;
it must be {id: UInt32}.>= 1.9.0. Use TupleParam
and Map — both serialize correctly and respect special characters.1.13.0. Boolean formatting
was fixed in 1.13.0 — earlier versions may misformat them.