Proposal: chdb-node 3.x layered API plan: native reliability, ClickHouse client compatibility, and TypeScript fluent query layer #39

Summary

This issue proposes a layered direction for the next major version of chdb-node.

The goal is not to turn chDB Node into another thin query(sql) wrapper. The goal is to make chDB a reliable embedded ClickHouse engine for the Node / Bun / TypeScript ecosystem.

Proposed layers:

Layer 3: Fluent SDK / query authoring
  - SQL-first, not ORM-first
  - Kysely-shaped fluent API for common analytical queries
  - chdb.sql`...` escape hatch for full ClickHouse SQL
  - explicit .execute() / .stream() / .compile()

Layer 2: @clickhouse/client-style compatibility layer
  - embedded-only client for chDB
  - target: local dev, CI, tests, agent sandboxes
  - remote ClickHouse should still use official @clickhouse/client

Layer 1: hardened native binding
  - existing query / Session compatibility
  - prebuilt N-API binaries
  - streaming, Arrow IPC, prepared parameters, safer lifecycle

This issue is meant to collect feedback before splitting the work into smaller implementation issues.

Related existing issues

Layer 1 should directly address several existing community reports:

• chdb-node Streaming of query result #31: Streaming of query result — users need row-by-row or chunked streaming instead of buffering all rows into one string.
• chdb-node Each call to session.query() takes at least 13ms #29: Each call to session.query() takes at least 13ms — important for test suites that may run tens of thousands of queries.
• chdb-node Insert query hangs process #26: Insert query hangs process — shows that insert paths and complex ClickHouse types need more robust execution / error handling.
• chdb-node Cleanup should rmSync only if isTemp== true #30: cleanup should rmSync only if isTemp == true — session lifecycle should avoid deleting user-supplied data directories.
• chdb-node Question: Do we need any logic on shutdown? #17: Do we need shutdown logic? — we need safer lifecycle handling for SIGINT / SIGTERM and long-running work.
• chdb-node When no DB used we get: Code: 57. DB::Exception: Directory for table data data/default/testtable/ already exists. (TABLE_ALREADY_EXISTS) #18: Default DB / table directory conflict — session / data directory behavior should be easier to reason about.
• chdb-node Question: Do we need a result CREATE FUNCTION query #20: Empty result for CREATE FUNCTION with JSON output — command result semantics should be documented and easier to handle.

Related chDB core issues:

• chdb #473: Add chunk size in streaming function — Node streaming API should align with configurable chunked streaming support in core.
• chdb #492: Enable remote ClickHouse access via chDB capabilities in clickhouse-connect — this is related to exposing chDB's federated capabilities, though this proposal keeps remote HTTP client behavior out of Layer 2.

Layer 1: hardened native binding

Layer 1 is the foundation. Before adding higher-level APIs, npm i chdb and the native runtime path should become boring and reliable.

Proposed Layer 1 work

1. Ship N-API prebuilt binaries.

Target behavior:

• npm i chdb should not compile on supported platforms.
• No node-gyp / CMake / compiler requirement during normal install.
• Platform subpackages can be used, similar to the esbuild-style npm package model.
• Unsupported platforms should fail with a clear message and source-build instructions.

2. Keep existing query and Session compatibility.

Existing users should not be forced to move to a new API.

3. Add streaming APIs.

Possible shape:

for await (const row of session.queryStream(
  "SELECT * FROM file('data.parquet', Parquet)",
  "JSONEachRow"
)) {
  // process row
}

This should avoid buffering the entire response into a single string.

4. Add Arrow IPC / binary result path.

This should reduce string materialization overhead and improve interop with JS data tooling.

5. Add prepared / parameterized execution.

Possible shape:

session.queryBind(
  "SELECT * FROM events WHERE user_id = {user_id:UInt64}",
  { user_id: 42 },
  "JSONEachRow"
)

6. Improve insert reliability and large input handling.

This should include better error propagation for complex ClickHouse types and avoid process hangs.

7. Improve session lifecycle.

This should cover cleanup safety, shutdown behavior, temp directory ownership, and cancellation.

Layer 1 acceptance criteria

• Supported platforms install without local compilation.
• Existing query and Session examples still work.
• Large result sets can be streamed without loading all rows into memory.
• Parameterized execution is available.
• Session cleanup never deletes user-owned directories unexpectedly.
• Errors are returned as JS errors instead of silent hangs where possible.

Layer 2: embedded @clickhouse/client-style compatibility

Layer 2 is for users who already use ClickHouse from JavaScript or TypeScript.

The user story:

I use @clickhouse/client in production. For local development, CI, tests, or agent sandboxes, I want a similar client shape backed by embedded chDB instead of a remote ClickHouse server.

Scope

Layer 2 should be embedded-only.

import { createClient } from "chdb/client"

const client = createClient({
  url: "chdb://./tmp/chdb"
})

const result = await client.query({
  query: "SELECT * FROM numbers(10)",
  format: "JSONEachRow"
})

Remote ClickHouse HTTP transport should remain the responsibility of the official @clickhouse/client.

This avoids making chDB Node a second remote ClickHouse JS client.

Non-goals

• Do not claim "100% @clickhouse/client compatible".
• Do not implement a competing remote HTTP ClickHouse client.
• Do not hide unsupported APIs.

Proposed compatibility promise

A safer wording:

chdb/client provides an @clickhouse/client-style embedded client for local chDB execution, with a documented compatibility matrix.

Layer 2 acceptance criteria

• createClient, query, insert, command-like execution, and common result materialization are supported.
• Unsupported remote-only options fail with clear guidance.
• Result shape follows common @clickhouse/client usage where possible.
• Errors are wrapped into a ClickHouse-like error shape while preserving the original cause.
• Compatibility tests compare supported behavior against @clickhouse/client.

Layer 3: SQL-first fluent SDK for TypeScript and agents

Layer 3 is not intended to be an ORM.

The goal is a TypeScript SQL authoring layer that is friendly to humans, IDEs, and LLM-generated analytical queries.

Design principles

• SQL-first, not ORM-first.
• Kysely-shaped fluent API for common query authoring.
• Always provide chdb.sql for full ClickHouse SQL.
• Explicit execution: .compile(), .execute(), .stream().
• Layer 3 compiles SQL and calls Layer 1 directly. It should not depend on Kysely runtime execution.

Example:

const q = db
  .selectFrom("events")
  .select(["country"])
  .where("event_time", ">=", cutoff)
  .groupBy("country")
  .orderBy("revenue", "desc")

const sql = q.compile()
const rows = await q.execute()

For advanced ClickHouse features:

const rows = await chdb.sql`
  SELECT country, sum(revenue) AS revenue
  FROM events
  PREWHERE event_time >= ${cutoff}
  GROUP BY country
  ORDER BY revenue DESC
  SETTINGS max_threads = 4
`.execute()

Why this layer exists

Layer 3 targets:

• AI agents generating analytical queries.
• TypeScript users who want safer query composition.
• Local analytics over files, Arrow data, snapshots, and remote sources.
• Users who want ClickHouse SQL power without hiding it behind a full ORM.

Layer 3 acceptance criteria

• Common analytical queries can be written fluently.
• Full ClickHouse SQL remains reachable through chdb.sql.
• Queries are lazy until explicit materialization.
• .compile() is available for dry-run, logging, explain, and agent workflows.
• .stream() is available for large results.
• ClickHouse-specific features such as PREWHERE, SETTINGS, FINAL, SAMPLE, ARRAY JOIN, and LIMIT BY are either first-class or clearly documented as SQL-tag use cases.
• Complex ClickHouse types such as Nullable, LowCardinality, Tuple, Map, Enum8, IPv4, and IPv6 have documented TypeScript mapping behavior.

Open questions

• Does the three-layer split make sense for current chdb-node users?
• Should Layer 2 live in the same chdb npm package or a subpath such as chdb/client?
• Which subset of @clickhouse/client should Layer 2 support first?
• What should the first streaming API look like: async row iterator, chunk iterator, or both?