clickhouse-js-node-coding/reference/sessions.md

Sessions and Temporary Tables

Applies to: all versions. session_id is a server-level concept; the client just forwards it on every request that names it.

Answer checklist

When answering "temp table disappears between calls" / "how do I share a session" / anything involving session_id:

• State plainly that temporary tables and session-scoped state are tied to a session_id — without a stable session_id across calls, every request gets a fresh server-side session and the temp table is gone.
• Set session_id via crypto.randomUUID() either on createClient or per-call.
• Warn that session_id on a global / module-static client is an anti-pattern in any concurrent app (Express, server actions, workers, etc.) — concurrent requests will share the same session and trip "Session is locked by a concurrent client". Recommend a short-lived per-workflow client or per-call session_id instead.
• If session_id is set on the client, also set max_open_connections: 1 to serialize calls and avoid the concurrent-session error.
• For ClickHouse Cloud or any load-balanced deployment: explicitly recommend replica-aware routing or a single-node hostname as the primary remedy when sessions are needed. Sessions are pinned to one node; behind an LB, consecutive requests may land on different nodes and the temp table will appear to vanish. "Just collapse the workflow into one handler" or "use a non-temporary table" are valid fallbacks but secondary — name the routing fix first.

When you need a session

Use a session_id whenever multiple calls must share server-side state:

• CREATE TEMPORARY TABLE (the table only exists within its session).
• SET <setting> = <value> to apply for subsequent queries on the same session.
• Any other server feature scoped per session (e.g., session-scoped variables in newer ClickHouse versions).

⚠️ session_id and concurrency

ClickHouse rejects concurrent queries within the same session — if two requests arrive at the server at the same time sharing the same session_id, the second one gets an error like "Session is locked by a concurrent client". This has two practical implications:

1. Do not set session_id on a global / module-static client that handles concurrent requests (e.g., an Express app's shared client). Every in-flight request would share the same session and collide under load.
2. If you do set session_id on a client, restrict its concurrency: set max_open_connections: 1 so at most one request is in flight at a time, turning the pool into a serial queue. This is fine for a dedicated per-workflow client but wrong for a shared application client.

The right pattern for application code: create a short-lived client (or use per-request session_id) scoped to a single logical workflow, not to the entire process.

Per-client session_id

Appropriate when one client handles exactly one sequential workflow (a script, a background job, a single user's session that you've already manually serialized in the code).

import { createClient } from "@clickhouse/client";
import * as crypto from "node:crypto";

const client = createClient({
  session_id: crypto.randomUUID(),
  max_open_connections: 1, // safeguard against concurrent-session errors
});

await client.command({
  query: "CREATE TEMPORARY TABLE temporary_example (i Int32)",
});

await client.insert({
  table: "temporary_example",
  values: [{ i: 42 }, { i: 144 }],
  format: "JSONEachRow",
});

const rs = await client.query({
  query: "SELECT * FROM temporary_example",
  format: "JSONEachRow",
});
console.info(await rs.json());
await client.close();

Session-level SET commands

SET only persists within a session. With session_id defined on the client, every subsequent call inherits the change.

import { createClient } from "@clickhouse/client";
import * as crypto from "node:crypto";

const client = createClient({
  session_id: crypto.randomUUID(),
  max_open_connections: 1, // safe-guard against concurrent-session errors
});

await client.command({
  query: "SET output_format_json_quote_64bit_integers = 0",
  clickhouse_settings: { wait_end_of_query: 1 }, // ack before next call
});

const rs1 = await client.query({
  query: "SELECT toInt64(42)",
  format: "JSONEachRow",
});
// → 64-bit integers come back as numbers in this query

await client.command({
  query: "SET output_format_json_quote_64bit_integers = 1",
  clickhouse_settings: { wait_end_of_query: 1 },
});

const rs2 = await client.query({
  query: "SELECT toInt64(144)",
  format: "JSONEachRow",
});
// → 64-bit integers come back as strings again

await client.close();

wait_end_of_query: 1 buffers the HTTP response. Await each operation in the same session and preserve node affinity. This is not a replacement for session serialization or a transaction guarantee.

Per-request session_id

You can also pass session_id on a single query() / insert() / command() call to override (or set) it for that one request.

⚠️ Sessions and load balancers / ClickHouse Cloud

Sessions are bound to a specific ClickHouse node. If a load balancer in front of ClickHouse routes consecutive requests to different nodes, the temporary table / SET won't be visible — you'll get UNKNOWN_TABLE / surprising results.

Mitigations (in order of preference):

• For ClickHouse Cloud, use replica-aware routing so consecutive requests in the same session land on the same node. This is the right primary fix when you need sessions in a Cloud deployment.
• Talk to a single node directly (e.g., a node-pinned hostname) when routing isn't an option.
• As a fallback only: avoid sessions for cross-node workflows and persist intermediate state in a regular (non-temporary) table instead. This trades the session requirement away rather than fixing it — use it only if replica-aware routing / single-node connections aren't available.

Common pitfalls

• Forgetting session_id and being surprised that CREATE TEMPORARY TABLE "disappears." Without a session, every request may land on a different connection / server context.
• Setting session_id on a shared application client. Under concurrent load, two in-flight requests will share the same session and one will fail with "Session is locked by a concurrent client". Use per-request session_id or a dedicated short-lived client instead.
• Reusing the same session_id across unrelated workflows. A second session-using consumer will trip over your temporary tables and SET values. Generate a fresh UUID per logical session.
• Leaving session state pinned for the lifetime of the process. If long-lived clients accumulate SET / temp-table state, consider creating a short-lived sub-client with its own session_id for the unit of work.
• Skipping wait_end_of_query: 1 on SET — race conditions between SET and the next query can show up under load.