ClickHouse dialect

ClickHouse dialect ClickHouse is a columnar OLAP database built for real-time analytics over very large datasets — aggregations, rollups, and dashboards scanning billions of rows, not row-by-row transactional CRUD. Reach for this dialect when your workload is "append a lot of events, then run fast aggregate queries over them," and not "many small transactional reads/writes with strict per-row consistency." Because ClickHouse is architecturally unlike the OLTP dialects (Postgres/MySQL/MariaDB/SQLite/MSSQL/Oracle), several assumptions the rest of this ORM makes about "a SQL database" simply do not hold. ClickHouseDialect (src/dialects/clickhouse/index.ts) is deliberately explicit about where the behavior genuinely differs — throwing on unsupported capabilities and warning where an option is silently ignored — rather than papering over the gaps. Read Key differences from an OLTP database before you write any code against it. Overview | | dialect | 'clickhouse' | Driver | @clickhouse/client (promise-native, official) | Wire protocol | ClickHouse HTTP interface | Default port | 8123 (HTTP), 8443 (HTTPS) | Transactions | None (single INSERT is atomic at the block level) | Constraints | CHECK only; no enforced PRIMARY KEY/UNIQUE/FOREIGN KEY | Primary key | No auto-increment; the MergeTree ORDER BY (sorting key) stands in | Status | Experimental | Connection Connect over the HTTP interface. dialect: 'clickhouse' selects this dialect; the driver builds a ${protocol}://${host}:${port} URL from your options. ClickHouseDialectOptions also accepts: engine — default ENGINE = ... clause used by createTable() when a call doesn't specify one. Default: 'MergeTree()'. orderBy — default ORDER BY column(s) used when a table has no primaryKey column and no per-call orderBy is given. requestTimeout — request timeout in ms, forwarded to @clickhouse/client. clickhouseSettings — arbitrary ClickHouse session settings applied to every request (e.g. { maxexecutiontime: 60 }). Under the hood the dialect routes SELECT/SHOW/DESCRIBE/EXPLAIN through the driver's query() (reading rows as JSONEachRow) and everything else — DDL, INSERT, and the ALTER TABLE ... UPDATE/DELETE mutations — through command(), which returns no row data. Key differences from an OLTP database None of these are bugs. They are how ClickHouse works, and they will surprise you if you treat this dialect like a normal RDBMS. CREATE TABLE requires an ENGINE and (for MergeTree) an ORDER BY There is no default storage engine. MergeTree-family engines also require an ORDER BY — the sorting key, which stands in for a traditional primary key. It controls physical sort/merge order, not uniqueness. createTable()/Model.sync() default the engine to MergeTree() and infer ORDER BY in this priority order: 1. an explicit orderBy passed on the table options, 2. any column(s) marked primaryKey, 3. the dialect-level orderBy default, 4. the first declared column, 5. tuple() ("no sorting key") if nothing else is available. Note the column defaults are inverted from standard SQL: ClickHouse columns are NOT NULL unless you opt into Nullable(T). This dialect wraps a column in Nullable(...) automatically unless allowNull: false is set (or the type is already Array/Nullable/LowCardinality/Map/Tuple). No PRIMARY KEY / UNIQUE / FOREIGN KEY enforcement ClickHouse will happily store duplicate "keys." A column's primaryKey flag only feeds the ORDER BY inference above; unique and references are ignored (with a console warning). addForeignKey() throws outright. Only CHECK constraints map to real syntax (and are validated on INSERT only). If you need uniqueness or referential integrity, enforce it in your application, or model "latest row per key" with a ReplacingMergeTree engine plus FINAL reads (see Upserts). No multi-statement transactions There is no BEGIN/COMMIT/ROLLBACK spanning more than one statement — only a single INSERT is atomic (at the block level). startTransaction(), commitTransaction(), and rollbackTransaction() all throw rather than silently no-op, so application code can't mistakenly believe a rollback undid earlier work. Don't wrap ClickHouse work in prorm.transaction(...). Savepoints throw for the same reason. UPDATE/DELETE are asynchronous background mutations buildUpdateQuery()/buildIncrementQuery() emit ALTER TABLE ... UPDATE, and buildDeleteQuery() emits ALTER TABLE ... DELETE (or TRUNCATE TABLE when deleting everything). Both return as soon as the mutation is scheduled — the actual rewrite happens later, in the background, with progress visible only in system.mutations. Do not expect affected rows to be immediately gone/changed on the next SELECT; there is a window where reads still see the pre-mutation data. Consequently, rowCount after a mutation is always 0 — it reflects "rows this HTTP request returned," not "rows the mutation will touch." Data types DataTypes map onto ClickHouse's native types: ORM DataTypes | ClickHouse type | STRING, CHAR, TEXT | String | INTEGER | Int32 (UInt32 if unsigned) | BIGINT | Int64 (UInt64 if unsigned) | FLOAT / DOUBLE | Float32 / Float64 | DECIMAL(p, s) | Decimal(p, s) | BOOLEAN | Bool | DATE | DateTime (DateTime64(p) when a precision is given) | DATEONLY | Date | ENUM(...) | Enum8 (≤127 values) or Enum16 | UUID | UUID | INET / INET({version:6}) | IPv4 / IPv6 | JSON / JSONB | String (serialized JSON; query with JSON functions) | ARRAY(T) | Array(T) | TIME, BLOB | String (no dedicated type) | You can always pass a raw ClickHouse type string as the column type (e.g. 'LowCardinality(String)', 'AggregateFunction(sum, UInt64)') when the abstraction has no dedicated variant. There is no native auto-increment. A column marked autoIncrement warns and is ignored — use a DEFAULT generateUUIDv4() on a UUID column, or an application-maintained counter, instead. A gotcha: string-typed aggregates The HTTP driver reads results as JSONEachRow, and ClickHouse serializes 64-bit integers (UInt64/Int64) as JSON strings to avoid precision loss. That means count(), sum(), and any UInt64/Int64 column come back as strings, not JS numbers: Coerce with Number(...)/BigInt(...) on the way out. Values that fit in Float64/Int32 come back as JS numbers as expected. Creating tables and MergeTree engines Beyond the plain MergeTree() default, the dialect can build and validate the whole MergeTree family via a typed mergeTreeEngine option on the table options. Each variant's required columns are checked against your schema before any SQL reaches ClickHouse: Supported mergeTreeEngine variants: { type: 'MergeTree' } { type: 'ReplacingMergeTree', version?, isDeleted? } — isDeleted requires version. { type: 'SummingMergeTree', columns? } — columns must be numeric. { type: 'AggregatingMergeTree' } — requires at least one AggregateFunction(...)/SimpleAggregateFunction(...) column. { type: 'CollapsingMergeTree', sign } — sign must be an Int8 column. { type: 'VersionedCollapsingMergeTree', sign, version } — sign Int8, version numeric. If a required column is missing or the wrong type, createTable() throws a descriptive error instead of letting ClickHouse fail opaquely at insert/merge time. A raw engine: 'GraphiteMergeTree(...)' string is also honored for engines without a typed helper. Other MergeTree table options (read as loosely-typed extension fields, since the shared TableOptions doesn't declare them): orderBy — the sorting key (string or string[]). partitionBy — a raw PARTITION BY expression, e.g. 'toYYYYMM(createdat)'. Partitions materialize automatically the first time a row with a given partition value is inserted; you cannot pre-declare an empty one. ttl — automatic expiry / tiered storage, e.g. 'createdat + INTERVAL 30 DAY' or '... TO VOLUME \'cold\''. Also available per-column. projections — inline PROJECTION name (SELECT ...) alternate layouts. Inserts and streaming bulkCreate A single INSERT is the atomic unit in ClickHouse, and bulk loading is where it shines. bulkInsert() (backing Model.bulkCreate()) does not build one giant literal INSERT ... VALUES (...), (...) string — that risks blowing ClickHouse's max query size and holds the whole batch in memory as one string. Instead it uses the driver's native streaming insert() with JSONEachRow, which chunks/streams the encoding: Date values are converted to ClickHouse's YYYY-MM-DD HH:MM:SS[.fff] wire format (not JS ISO-8601) so DateTime/DateTime64 columns accept them. Upserts (ReplacingMergeTree + FINAL) ClickHouse has no synchronous UPSERT/ON CONFLICT/MERGE. The idiomatic pattern is: insert into a ReplacingMergeTree table and let background merges asynchronously drop older rows sharing the same ORDER BY key. buildUpsertQuery() reflects this honestly — it only ever emits a plain INSERT (with a console warning); conflictFields/updateOnDuplicate are accepted but have no effect. The critical gotcha: those merges are asynchronous and eventual — they can run minutes later, or effectively never on a lightly-written table. Until then, duplicate rows for the same key coexist, and a plain SELECT can silently return duplicates or a stale row, with no error. Pass final: true to buildSelectQuery() to force merge-time dedup at query time: FINAL is opt-in on purpose: this dialect doesn't track per-table engine metadata, so it can't know a table is a ReplacingMergeTree, and FINAL forces a slower merge-on-read that doesn't scale well on tables with many parts. Ask for it explicitly on every read that needs deduplicated/latest data. For very large tables where FINAL is too slow, hand-write an argMax(...)/GROUP BY dedup query instead — this dialect won't build one for you. Analytics helpers ClickHouse's approximate aggregate functions are surfaced as typed helpers that return a Literal (raw-SQL escape hatch) you drop straight into attributes: Available: uniq, uniqCombined([precision]), uniqHLL12, uniqExact, quantile(level), quantiles(levels[]), quantileExact, quantileTDigest. Quantile levels must be in [0, 1]. buildSelectQuery() also supports ARRAY JOIN/LEFT ARRAY JOIN (to unnest Array columns into rows) via arrayJoin/leftArrayJoin options, and SELECT EXCEPT (...) via attributes.exclude. Materialized views createMaterializedView(name, query, options) emits ClickHouse's real insert-triggered CREATE MATERIALIZED VIEW ... TO <target> AS SELECT .... Every time a row lands in the source table(s) in query's FROM, ClickHouse runs the query against just the new block and appends the result to the target. It is not recomputed at read time, and does not backfill existing rows unless you pass populate: true (which races with concurrent inserts, so it's opt-in). Target either an existing table ({ to: 'rollup' }) or an implicit hidden one ({ engine, orderBy } without to). Known limitations No transactions/savepoints — the methods throw (see above). No enforced PK/UNIQUE/FK constraints — only CHECK; addForeignKey() throws. No auto-increment — use generateUUIDv4() or an app counter. No stored procedures — no procedural language; use materialized views or CREATE FUNCTION UDFs. createStoredProcedure() throws. No Foreign Data Wrappers — a Postgres concept; use ClickHouse table functions (mysql(), postgresql(), s3()) directly. FDW methods throw. No extensions — a Postgres concept; the methods throw. Indexes are data-skipping indexes (minmax/set/bloomfilter), not B-Trees — they let scans skip granules, not guarantee point-lookup speed. No per-column CODEC(...), no Distributed/ON CLUSTER, no dictionaries, no Kafka/S3 table engines, no SAMPLE clause — out of scope for this single-node-oriented dialect. Query parameters are inlined as escaped literals — the HTTP client sends one opaque SQL string per request, so replacements/bindings are substituted as escaped literals (the same as SQLite/PostgreSQL do), not native binds. See also clickhouse — the source-level walkthrough this guide is grounded in. clickhouse — the dialect implementation. </invoke> Related reading Running clickhouse in Docker — get one going locally All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL