clickhouse-js-node-coding/reference/query-parameters.md

Query Parameter Binding

Applies to: all versions. NULL parameter binding fixed in 0.0.16. Special-character (tab/newline/quote/backslash) binding >= 0.3.1. TupleParam and JS Map parameters >= 1.9.0. Boolean formatting in Array/Tuple/Map parameters fixed in >= 1.13.0. BigInt query parameters >= 1.15.0.

Answer checklist

When the user passes user-controlled values into SQL:

• Use ClickHouse {name: Type} placeholders and a query_params object.
• Your response must explicitly name template-literal / string interpolation of user input as a SQL injection risk — even when the user only asked "how do I bind values" and did not mention security. This is non-negotiable: the security framing is part of the right answer, not an optional aside.
• Do not suggest PostgreSQL/MySQL-style $1, ?, or :name placeholders.
• Pick the placeholder type to match the ClickHouse column type (String, Date, DateTime, Nullable(T), etc.).

Syntax: {name: Type}

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

⚠️ Never use template literals for user values

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.

Common types

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

Type-by-type tips

• Decimals — pass as strings to avoid JS number precision loss.
• 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).

Special characters in string parameters (>= 0.3.1)

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

Common pitfalls

• $1 / ? / :name placeholders. None work — use {name: Type}.
• Forgetting the type in the placeholder. {id} is a syntax error; it must be {id: UInt32}.
• Stringifying tuples/maps manually on >= 1.9.0. Use TupleParam and Map — both serialize correctly and respect special characters.
• Boolean array/tuple/map elements before 1.13.0. Boolean formatting was fixed in 1.13.0 — earlier versions may misformat them.