Setting the file. One moment.
Skill 07 · Clickhouse Js Node Troubleshooting
Subchapter 7.5
reference/query-format-clause.mdMarkdown3 KBView on GitHub
query() FORMAT Clause Errors (incl. SHOW [ROW] POLICIES)Applies to: all versions.
is for statements that return a result set (such as ). It to the end of the string, where comes from the option (default ). You should therefore include a clause in the yourself.
client.query()SELECTFORMAT <format>query<format>formatJSONFORMATquery// What you write:
await client.query({ query: "SELECT 1", format: "JSONEachRow" });
// What the client actually sends:
// SELECT 1
// FORMAT JSONEachRowIf the query string already contains a FORMAT clause, the client still appends another one, producing a duplicate FORMAT and a server-side syntax error. This is intended behavior.
// ❌ Wrong — ends up as `... FORMAT CSV FORMAT JSON` → syntax error
await client.query({ query: "SELECT 1 FORMAT CSV" });
// ✓ Correct — let the client append FORMAT via the option
await client.query({ query: "SELECT 1", format: "CSV" });If you genuinely need to write the full SQL yourself (including the FORMAT clause), or you’re running a statement where the appended FORMAT suffix is not supported, use client.exec() instead of client.query(). Use client.insert() for data insertion and client.command() for DDLs.
Some statements are not parsed by the server with a trailing FORMAT clause, so the FORMAT suffix that query() always appends triggers a syntax error. The most common case is the short form of SHOW POLICIES / SHOW ROW POLICIES — at the server SQL parser level it does not accept the FORMAT suffix.
// ❌ Fails — query() appends `FORMAT JSON`, which the short SHOW POLICIES syntax rejects
await client.query({ query: "SHOW POLICIES", format: "JSON" });
await client.query({ query: "SHOW ROW POLICIES", format: "JSON" });Fix: use the full syntax SHOW POLICIES ON * (or SHOW POLICIES ON db.table), which the parser accepts together with the appended FORMAT:
// ✓ Works — full syntax accepts the appended FORMAT clause
await client.query({ query: "SHOW POLICIES ON *", format: "JSON" });Alternatively, run the short statement through client.exec(), where you control the full SQL and no FORMAT suffix is appended.
This behavior is easy to misdiagnose because the error looks like a generic syntax error rather than a client/format problem. See the upstream issue: https://github.com/ClickHouse/ClickHouse/issues/105899 (opens in a new tab)