Skip to content

Schema DSL Reference

Schema files export definitions using functions from @chkit/core (TypeScript) or chkit (Python, via chkit-py). All exported definitions are collected when chkit loads schema files matched by the schema glob in your configuration. The two implementations share every field’s semantics — pick your language once and the whole page follows.

import { schema, table, view, materializedView, dictionary } from '@chkit/core'

Groups definitions into a single array for export.

export default schema(users, events)

Any exported value with a valid kind is also discovered automatically.

Creates a table definition.

Minimal example:

import { schema, table } from '@chkit/core'
const users = table({
database: 'app',
name: 'users',
columns: [
{ name: 'id', type: 'UInt64' },
{ name: 'email', type: 'String' },
],
engine: 'MergeTree',
primaryKey: ['id'],
orderBy: ['id'],
})
export default schema(users)

Comprehensive example (all features):

const events = table({
database: 'analytics',
name: 'events',
columns: [
{ name: 'id', type: 'UInt64' },
{ name: 'org_id', type: 'String' },
{ name: 'source', type: 'LowCardinality(String)' },
{ name: 'payload', type: 'String', nullable: true },
{ name: 'received_at', type: 'DateTime64(3)', default: { expression: 'now64(3)' } },
{ name: 'status', type: 'String', default: 'pending', comment: 'Event processing status' },
],
engine: 'MergeTree',
primaryKey: ['id'],
orderBy: ['org_id', 'received_at', 'id'],
partitionBy: 'toYYYYMM(received_at)',
ttl: 'received_at + INTERVAL 90 DAY',
settings: { index_granularity: 8192 },
indexes: [
{ name: 'idx_source', expression: 'source', type: 'set', maxRows: 0, granularity: 1 },
],
projections: [
{ name: 'p_recent', query: 'SELECT id ORDER BY received_at DESC LIMIT 10' },
],
comment: 'Raw ingested events',
})
FieldTypeDescription
databasestringClickHouse database name
namestringTable name
columnsColumnDefinition[]Column definitions (see Columns)
enginestringEngine clause, e.g. 'MergeTree', 'ReplacingMergeTree(ver)'
orderBystring[]ORDER BY columns or expressions, e.g. ['toStartOfHour(ts)', 'id']

orderBy is required only on engines that support sort order. An omitted primaryKey defaults to orderBy, as in ClickHouse MergeTree. Omit both for Distributed tables and other engines without keys.

For Kafka tables, omit primaryKey and orderBy. Kafka also rejects storage clauses (partitionBy, uniqueKey, ttl, indexes, projections, constraints) and column defaults. Its setting strings are escaped SQL literals.

FieldTypeDescription
primaryKeystring[]Primary key columns or expressions, e.g. ['toDate(ts)', 'id']; defaults to orderBy when omitted
partitionBystringPartition expression, e.g. 'toYYYYMM(created_at)'
uniqueKeystring[]Unique key columns
ttlstringTTL expression, e.g. 'created_at + INTERVAL 90 DAY'
settingsRecord<string, string | number | boolean>Table-level settings
indexesSkipIndexDefinition[]Skip indexes (see Skip indexes)
projectionsProjectionDefinition[]Projections (see Projections)
constraintsTableConstraintDefinition[]CHECK and ASSUME constraints (see Constraints)
commentstringTable comment
renamedFrom{ database?: string; name: string }Previous identity for rename tracking (see Rename support)
pluginsTablePluginsPer-table plugin configuration (see Plugin configuration)

A Distributed table stores no rows. It routes queries and inserts to a local table on every shard of a cluster. Define the local table and the Distributed table as two separate tables in the schema:

import { schema, table } from '@chkit/core'
const columns = [
{ name: 'id', type: 'UInt64' },
{ name: 'ts', type: 'DateTime' },
]
const eventsLocal = table({
database: 'analytics',
name: 'events_local',
engine: "ReplicatedMergeTree('/clickhouse/tables/{shard}/analytics/events_local', '{replica}')",
columns,
orderBy: ['id'],
partitionBy: 'toYYYYMM(ts)',
})
const events = table({
database: 'analytics',
name: 'events',
engine: 'Distributed(my_cluster, analytics, events_local, rand())',
columns,
})
export default schema(eventsLocal, events)

Set clickhouse.cluster so every statement runs ON CLUSTER.

  • No keys. ClickHouse rejects PARTITION BY, PRIMARY KEY, and ORDER BY on a Distributed table, so chkit renders none, and validation fails with engine_unsupported_clause when one is set. Put the keys on the local table. The same applies to Memory, Null, Log, Buffer, Merge, and other engines without sort order.
  • Two tables, two ALTERs. chkit does not link the Distributed table to its local table. To add a column, add it to both definitions; chkit generate plans one ALTER TABLE ... ADD COLUMN per table, and both run in the same migration.
  • Engine arguments. ClickHouse stores the cluster, database, and table arguments as string literals, so Distributed(my_cluster, analytics, events_local) and Distributed('my_cluster', 'analytics', 'events_local') are the same engine to drift and generate. Write the database name literally: an expression such as currentDatabase() reads back as its value and shows as drift.
  • Recreate risk. Changing the engine arguments (cluster, local table, sharding key) recreates the Distributed table. The drop is risk=caution, not danger: the table holds no rows, and DROP sends inserts still queued for the shards first (flush_on_detach, on by default). With settings: { flush_on_detach: 0 } queued inserts are lost, so the drop stays risk=danger.
  • ObsessionDB. ObsessionDB has no shards. The ObsessionDB plugin rejects Distributed tables when targeting it; use the local table directly.

Each entry in the columns array is a ColumnDefinition.

Column name.

Any ClickHouse type string. Parameterized types like DateTime64(3), Decimal(18, 4), Enum8('a' = 1, 'b' = 2), and FixedString(32) are supported.

In TypeScript, type also takes a t type such as t.lowCardinality(t.string()). It renders to the same type string, so a query param can share the column’s type.

Primitive types recognized by the DSL type system: String, UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256, Float32, Float64, Bool, Boolean, Date, DateTime, DateTime64.

chkit passes the type string through to ClickHouse verbatim — it does not rewrite it. ClickHouse itself accepts standard SQL type aliases and stores them as its native types, so a table declared with aliases like BIGINT or TEXT is created successfully:

SQL aliasClickHouse native type
TINYINTInt8
SMALLINTInt16
INTEGER / INTInt32
BIGINTInt64
FLOAT / REALFloat32
DOUBLEFloat64
TEXT / VARCHAR / CHARString
TIMESTAMPDateTime

See the ClickHouse data types reference for the complete alias list.

When true, the column type is wrapped in Nullable(...) in the generated SQL.

An inline nullable type (type: 'Nullable(String)') and type: 'String', nullable: true are equivalent in migration generation and drift checks. Use either form; setting nullable: true on an already nullable type adds another wrapper.

{ name: 'payload', type: 'String', nullable: true }
// SQL: `payload` Nullable(String)

default (string | number | boolean | SQLExpression, optional)

Section titled “default (string | number | boolean | SQLExpression, optional)”

The value of the column’s DEFAULT clause, or of the clause that defaultKind names: a literal value, or a SQL expression that ClickHouse evaluates for each row.

ValueMeaningExampleRenders
stringLiteral, single-quoted, with quotes and backslashes escapeddefault: 'pending'DEFAULT 'pending'
number / booleanLiteral, as writtendefault: 0DEFAULT 0
{ expression: string }SQL expressiondefault: { expression: 'now64(3)' }DEFAULT now64(3)

Use { expression } for anything ClickHouse evaluates: function calls, arithmetic, casts, and references to other columns.

A string is the value itself, so do not quote it as SQL: default: "'standard'" renders DEFAULT '''standard''' and stores the quotes as part of the value. chkit generate and chkit check warn, without failing, when a plain string default is one quoted SQL string literal, and name both fixes: default: 'standard' for the value, or default: { expression: "'standard'" } for SQL. The same applies to dictionary attribute defaults, which accept only literals.

columns: [
{ name: 'status', type: 'String', default: 'pending' },
// SQL: `status` String DEFAULT 'pending'
{ name: 'received_at', type: "DateTime64(3, 'UTC')", default: { expression: 'now64(3)' } },
// SQL: `received_at` DateTime64(3, 'UTC') DEFAULT now64(3)
]

An expression may span lines and contain SQL comments. chkit removes the comments when it writes the clause, so a -- comment cannot hide the rest of the column definition, and keeps the rest of the text as written. An unterminated string, quoted identifier, or block comment would swallow the rest of the migration, so chkit generate rejects it with column_default_invalid, as it does a # that is not followed by a space or !, which ClickHouse cannot parse. chkit/meta/snapshot.json stores the expression with its comments, so editing only a comment plans a MODIFY COLUMN that sets the same default.

chkit drift and chkit check compare defaults with the live default_expression token by token, ignoring whitespace, comments, outer parentheses, and quotes around plain identifiers. A string stays a literal, so default: 'now()' drifts against a live DEFAULT now().

The fn: prefix. A string that starts with fn: is the original spelling of an expression default and keeps working: default: 'fn:now64(3)' is equivalent to default: { expression: 'now64(3)' }. chkit/meta/snapshot.json stores both as "fn:now64(3)", so switching between them generates no migration. Because of the prefix, a string literal cannot start with fn:; to store such a value, write the quoted literal as an expression: default: { expression: "'fn:abc'" }.

Choose DEFAULT (the implicit default), MATERIALIZED, ALIAS, or EPHEMERAL. Python also accepts default_kind. The default field holds the value or expression for every kind: strings remain SQL literals, and { expression } marks a SQL expression.

KindBehavior
DEFAULTStored; the expression applies when the insert omits the value.
MATERIALIZEDComputed on insert and stored; cannot be supplied in a normal insert.
ALIASComputed when explicitly selected; neither stored nor insertable.
EPHEMERALInput for other column expressions; neither stored nor selectable.
columns: [
{ name: 'ts', type: 'DateTime' },
{ name: 'day', type: 'Date', defaultKind: 'MATERIALIZED', default: { expression: 'toDate(ts)' } },
{ name: 'label', type: 'String', defaultKind: 'ALIAS', default: { expression: 'toString(day)' } },
{ name: 'raw', type: 'String', defaultKind: 'EPHEMERAL' },
{ name: 'size', type: 'UInt64', default: { expression: 'length(raw)' } },
]

MATERIALIZED and ALIAS compute their value, so they require a default, and a plain string fails with column_expression_requires_fn: default: 'toDate(ts)' would render the quoted literal MATERIALIZED 'toDate(ts)' instead of SQL. Write { expression: 'toDate(ts)' }, or { expression: "'text'" } for a constant string (fn:toDate(ts) and fn:'text' in the legacy spelling). Numbers and booleans render as written. EPHEMERAL may omit default and keeps a plain string as a literal. ClickHouse normally excludes all three from SELECT * and accepts EPHEMERAL values only through an explicit insert column list. Keep the base type in type; do not embed MATERIALIZED ... in the type string.

Changing a DEFAULT or MATERIALIZED expression, or switching between those two kinds, emits MODIFY COLUMN with a warning that stored values are not rewritten. Removing an expression emits REMOVE DEFAULT or REMOVE MATERIALIZED as its own statement, before any type change in the same migration. Converting a column to or from ALIAS or EPHEMERAL fails chkit generate with column_kind_change_unsupported instead of dropping and recreating the column.

ClickHouse stores no data for ALIAS and EPHEMERAL columns, so validation rejects them where a stored column is required. Use a MATERIALIZED column instead.

CodeRejected use
column_kind_not_storedNamed directly in orderBy, primaryKey, partitionBy, or an engine argument such as ReplacingMergeTree(ver); for EPHEMERAL, also a skip index on the bare column.
column_ephemeral_in_projectionAn EPHEMERAL column read by a projection. ClickHouse can accept the table and then fail every insert.
column_kind_codec_unsupportedA codec on an ALIAS column, or on an EPHEMERAL column without a value or comment.

References inside expressions, such as toStartOfDay(day) or a TTL, are left to ClickHouse to report at migrate time.

Codegen emits separate read and insert types for these tables (see Tables with generated columns), and the backfill plugin checks the target’s column kinds before an automatic backfill (see Target safety checks).

Column-level comment rendered in SQL. See Comments.

Previous column name for rename tracking. See Rename support.

Sets the column compression codec, rendered as a CODEC(...) clause. A codec is an object with a kind, or an array forming a chain. Kinds are case-sensitive.

columns: [
{ name: 'ts', type: 'DateTime64(3)', codec: { kind: 'DoubleDelta' } },
{ name: 'amount', type: 'Float64', codec: { kind: 'ZSTD', level: 3 } },
// chain: transform, then general codec → CODEC(Delta, LZ4HC(9)), stored as Delta(8)
{ name: 'seq', type: 'UInt64', codec: [{ kind: 'Delta' }, { kind: 'LZ4HC', level: 9 }] },
]

General codecs (at most one per chain; only encryption codecs may follow it):

kindArgsRendersClickHouse stores
LZ4—CODEC(LZ4)LZ4
LZ4HClevel?: number (any integer, default 9; ClickHouse keeps its low 32 bits, and chkit compares those)CODEC(LZ4HC(9))as written; a bare LZ4HC as LZ4HC(0), the default
ZSTDlevel?: number (its signed low 32 bits must be at most 22, default 1; negative levels are faster), windowLog?: number (0 or 10–31)CODEC(ZSTD(3)), CODEC(ZSTD(3, 24))as written; a bare ZSTD as ZSTD(1)
Default—CODEC(Default)Default, the server’s default compression
NONE—CODEC(NONE)NONE. It stores data uncompressed and must be the only codec of its column

Transforms and compression codecs (placed before the general codec, or used without one):

kindArgsColumn typesRenders and stores
Deltasize?: 1 | 2 | 4 | 8With explicit size: any type. Bare Delta: fixed-size values of 1, 2, 4 or 8 bytes: integers up to 64 bits, floats, BFloat16, Decimal32/Decimal64, dates and times, Enum, Bool, IPv4, FixedString(N)CODEC(Delta) without size; ClickHouse stores the byte width of the values (Delta(8) on UInt64, Delta(4) on DateTime, Delta(2) on Date), and a bare Delta on a Tuple or Map of mixed widths. An explicit size is kept as written. Only transforms: the chain needs a codec that compresses
GCD—integers, Decimal, Date, Date32, DateTime, DateTime64, BoolGCD. Only transforms, like Delta
DoubleDeltasize?: 1 | 2 | 4 | 8numeric values of 1, 2, 4 or 8 bytesDoubleDelta or DoubleDelta(size); ClickHouse keeps the size only in verbatim chains
Gorillasize?: 1 | 2 | 4 | 8With explicit size: any leaf types, with a reachable Float32, Float64 or BFloat16 leaf. Bare Gorilla: leaf widths of 1, 2, 4 or 8 bytes, with a reachable float leafCODEC(Gorilla) without size, stored with the value width (Gorilla(8) on Float64)
T64mode?: 'byte' | 'bit'integers up to 64 bits, Decimal32/Decimal64, dates and times, Enum, Bool, IPv4T64 ('byte', the default) or T64('bit')
FPClevel?: number (1–28, default 12), floatSize?: 4 | 8Float32, Float64FPC(level); ClickHouse drops the float size except in the mixed-width chains described below
ALP—Float32, Float64ALP. Experimental: see below

Per-leaf type checks and value widths apply inside Nullable, Array, LowCardinality and SimpleAggregateFunction(f, T), and to each element of a Tuple or Map. LowCardinality(Nullable(T)) is one leaf with no fixed width or numeric family. T64, FPC, ALP, GCD and DoubleDelta require each modelled leaf to qualify. A bare Delta or Gorilla requires leaf widths of 1, 2, 4 or 8 bytes. An explicit size bypasses the per-leaf check, including the fixed-size requirement: Tuple(String, Float64) and Tuple(UUID, Float64) reject bare Gorilla and accept Gorilla(8).

Gorilla, FPC and ALP also require a float leaf reachable through Nullable, Array and Tuple. LowCardinality and Map are opaque for this reachability check, so a float inside them does not count. Tuple(LowCardinality(Float64), Float64) accepts FPC and ALP because the second leaf is reachable and both leaves satisfy their type rules. Tuple(LowCardinality(UInt64), Float64) accepts Gorilla but rejects FPC on the UInt64 leaf. Map(Float64, Float64) and Tuple(LowCardinality(Float64)) reject all three float-series codecs. An explicit Gorilla size still requires a reachable float. Numeric LowCardinality types require the server setting allow_suspicious_low_cardinality_types = 1.

An unsized Enum(...) has width 1 when its assigned values fit in −128–127, otherwise width 2. Explicit values accept signed decimal, 0x hex and 0b binary integer literals, with _ between digits, like codec arguments. An omitted value is the previous decoded value plus one, starting at 1: Enum('a', 'b') uses one byte, while Enum('a' = 0x7f, 'b') uses two. Codec comparisons recognize whitespace before type arguments, such as Tuple (Float32, Float64), and quoted Tuple field names, including escaped backticks.

On leaves of different fixed widths, a Delta, Gorilla or DoubleDelta without an explicit size makes ClickHouse store the whole chain as written. T64, with or without a mode, also triggers verbatim storage when leaf type IDs differ, even at the same width: Tuple(UInt32, Int32) retains DoubleDelta(4), T64, ZSTD. For this rule, Bool has the same type ID as UInt8. On Tuple(Float32, Float64), both Gorilla, FPC(12, 4) and FPC(12, 4), DoubleDelta, ZSTD keep FPC’s float size; changing it to 8 plans a column modification. On Tuple(Float64, Float64), the latter chain stores as FPC(12), DoubleDelta, ZSTD(1). When none of these codecs triggers verbatim storage, ClickHouse drops FPC’s float size, even on mixed-width leaves.

chkit preserves and renders an explicit DoubleDelta size in both typed and raw codecs. ClickHouse retains that size only in verbatim chains: on Tuple(UInt32, UInt64), Delta, DoubleDelta(4) differs from Delta, DoubleDelta(8). Outside those chains, the server drops the size and chkit ignores it during comparison. JSON and Dynamic contribute a value width of 1; their per-leaf codec validation stays unchecked. The geo types count as what they stand for: Point as Tuple(Float64, Float64), Ring and LineString as Array(Point), Polygon and MultiLineString as arrays of those, and MultiPolygon as Array(Polygon). Types chkit does not model keep their existing comparison behavior.

ZSTD keeps an explicit zero window: ZSTD(3, 0) and ZSTD(3) are different stored forms.

Raw escape hatch — for codecs without a typed form, such as the encryption codecs, pass the inner expression through verbatim:

{ name: 'secret', type: 'String', codec: { kind: 'raw', expression: 'ZSTD, AES_128_GCM_SIV' } }
// → CODEC(ZSTD, AES_128_GCM_SIV)

The atoms of a raw codec that name a typed codec are validated and compared like the typed form, so codec.raw('ZSTD') equals { kind: 'ZSTD' }. The encryption codecs need encryption_codecs keys in the server configuration before an insert succeeds.

Rules. generate rejects a codec ClickHouse would reject at migrate time (see Validation rules):

  • NONE is the only codec of its column.
  • A chain has at most one general codec, and only encryption codecs follow it. A chain needs no general codec: T64, DoubleDelta, Gorilla, FPC and ALP compress by themselves, and transforms and compression codecs may repeat in any order before the general codec.
  • A chain of only Delta and GCD (with or without encryption) compresses nothing.
  • Gorilla, FPC and ALP cannot follow Delta or DoubleDelta. ALP cannot follow Gorilla, FPC or ALP: ClickHouse creates the column, but every insert fails.
  • Each codec accepts only the column types in the table above, and the parameter ranges shown. An explicit Delta(n) bypasses the type check; bare Delta on String is rejected. ZSTD windowLog is 0 or an integer from 10 through 31.

Changing codecs. generate compares codecs in the form ClickHouse stores them, so respelling a codec (ZSTD and ZSTD(1), FPC(12, 8) and FPC outside a mixed-width chain with bare Delta/Gorilla, [x] and x) plans nothing. A changed codec is a MODIFY COLUMN ... CODEC(...); a removed one is MODIFY COLUMN ... REMOVE CODEC, its own operation before any other change to the column (a MODIFY COLUMN without a codec keeps the stored one). A new codec applies to the parts written after it: chkit runs no mutation, and existing parts keep their codec until ClickHouse merges them.

ALP is experimental. ClickHouse rejects it unless allow_experimental_codecs is 1, and generate warns about every operation that sets it. Pass the setting to migrate:

Terminal window
chkit migrate --apply --setting allow_experimental_codecs=1

On ObsessionDB (ClickHouse 26.8), the other replicas of a table with an ALP column do not apply later ALTER statements on that table, so migrate stops waiting for them. Avoid ALP on replicated tables.

Each entry in the indexes array is a SkipIndexDefinition. The shared base fields are:

FieldTypeDescription
namestringIndex name
expressionstringIndexed expression
type'minmax' | 'set' | 'bloom_filter' | 'tokenbf_v1' | 'ngrambf_v1' | 'text' | 'vector_similarity' | 'raw'Index type
granularitynumberRequired for most indexes; optional for vector_similarity (defaults to 100000000); optional and ignored for text, which always uses 100000000

Type-specific fields:

TypeRequired fieldsOptional fieldsNotes
minmax——No arguments
setmaxRows: number—maxRows: 0 stores all unique values (ClickHouse 26+ requires set(0) rather than bare set)
bloom_filter—falsePositiveRate: numberDefaults to 0.025 when omitted
tokenbf_v1sizeBytes, hashFunctions, randomSeed (all number)—Maps to tokenbf_v1(size_bytes, n_hash, seed)
ngrambf_v1ngramSize, sizeBytes, hashFunctions, randomSeed (all number)—Maps to ngrambf_v1(n, size_bytes, n_hash, seed)
vector_similaritymethod, distanceFunctiondimensions, quantization, hnswMaxConnectionsPerLayer, hnswCandidateListSizeForConstructionSee Vector similarity indexes
rawrawType: string—Any other index type, rendered verbatim after TYPE (see Raw indexes)

Use type: 'text' on ClickHouse 26.2 or newer. tokenizer is a required SQL expression, such as splitByNonAlpha, ngrams(3), or splitByString([' ', ';']). Quoted whitespace, Unicode, and escaped characters retain their meaning through generation, pull, and drift checks. Granularity is automatic: ClickHouse indexes an entire part and ignores any supplied granularity.

FieldTypeMeaning
tokenizerstringRequired SQL tokenizer
preprocessorstringOptional SQL expression applied before tokenization
postprocessorstringOptional SQL expression applied to each token; requires server support
supportPhraseSearchbooleanStore token positions; requires server support and the table setting allow_experimental_text_index_phrase_search: 1
dictionaryBlockSizenumberPositive integer dictionary block size
dictionaryBlockFrontcodingCompressionbooleanEnable or disable dictionary front coding
postingListBlockSizenumberPositive integer posting-list block size
postingListCodec'none' | 'bitpacking'Posting-list compression
indexes: [{
name: 'idx_body',
expression: 'body',
type: 'text',
tokenizer: "splitByString([' ', ';'])",
preprocessor: 'lower(body)',
}]

Python accepts the same dictionary fields, or SkipIndexText(name="idx_body", expression="body", tokenizer="splitByNonAlpha"). Snake-case names such as posting_list_codec are also accepted.

Basic text indexes and tuning options are tested against ClickHouse 26.3 and 26.8. The newer postprocessor and supportPhraseSearch options are exercised on 26.8; 26.2 availability of the index does not imply availability of every later option. ClickHouse remains responsible for validating tokenizer/function availability and server-specific parameter limits. Pull keeps a text index with parameters it does not know as a raw index instead of silently discarding them.

Avoid column names that are also SQL literals (true, false, inf, infinity, or nan) in text-index expressions on older servers. ClickHouse 26.3 can remove their required identifier quotes from index metadata, preventing a lossless pull. chkit treats meaningful quote differences as drift; it does not assume a column reference and a literal are equivalent. Ordinary identifier quoting and switching between backticks and double quotes do not require an index rebuild.

Adding or changing an index does not automatically index historical parts. Run ALTER TABLE database.table MATERIALIZE INDEX idx_body when historical data must be indexed; materialization consumes database resources. Existing rows remain queryable before materialization.

indexes: [
{ name: 'idx_source', expression: 'source', type: 'set', maxRows: 0, granularity: 1 },
{ name: 'idx_ts', expression: 'received_at', type: 'minmax', granularity: 3 },
{
name: 'idx_body',
expression: 'body',
type: 'tokenbf_v1',
sizeBytes: 256,
hashFunctions: 2,
randomSeed: 0,
granularity: 1,
},
]

type: 'vector_similarity' declares an approximate nearest-neighbour (HNSW) index on an Array(Float32), Array(Float64) or Array(BFloat16) column. It renders vector_similarity(method, distanceFunction[, dimensions][, quantization, hnswMaxConnectionsPerLayer, hnswCandidateListSizeForConstruction]).

FieldTypeMeaning
method'hnsw'Required. The only method ClickHouse supports
distanceFunction'L2Distance' | 'cosineDistance' | 'dotProduct'Required. dotProduct needs ClickHouse 26.4+
dimensionsnumberVector length. Required on ClickHouse 25.4+; omit it on older servers, which take no dimensions argument
quantization'f64' | 'f32' | 'f16' | 'bf16' | 'i8' | 'b1'Stored vector precision. b1 (binary, ClickHouse 25.8+) requires cosineDistance and dimensions divisible by 8
hnswMaxConnectionsPerLayernumber0 (library default) or at least 2
hnswCandidateListSizeForConstructionnumberNon-negative integer; 0 is the library default
granularitynumberDefaults to 100000000, one index per part, as ClickHouse recommends

The three tuning fields are positional arguments in ClickHouse, so set all three or none; omitted, ClickHouse uses bf16, 32 and 128.

indexes: [{
name: 'embedding_hnsw',
expression: 'embedding',
type: 'vector_similarity',
method: 'hnsw',
distanceFunction: 'cosineDistance',
dimensions: 512,
// optional, all three or none:
quantization: 'bf16',
hnswMaxConnectionsPerLayer: 32,
hnswCandidateListSizeForConstruction: 128,
}]

Vector search is generally available from ClickHouse 25.8. On older servers ClickHouse rejects the index unless the setting allow_experimental_vector_similarity_index = 1 (25.7: enable_vector_similarity_index) is active for the connection. chkit never sets experimental settings itself: enable it in the settings profile of the user chkit connects as. The TypeScript CLI also passes clickhouse_setting_<name>=<value> query parameters on clickhouse.url through to the server, e.g. http://host:8123/?clickhouse_setting_allow_experimental_vector_similarity_index=1. chkit-py ignores URL parameters, so use the profile there.

A changed vector index is dropped and re-added. As with other skip indexes, the new index covers new parts only until you run MATERIALIZE INDEX.

type: 'raw' declares an index type chkit has no structured form for, such as sparse_grams or an option a newer ClickHouse adds. rawType is rendered verbatim after TYPE, and granularity is required.

indexes: [{ name: 'idx_grams', expression: 'body', type: 'raw', rawType: 'sparse_grams(3, 20, 256, 2, 0)', granularity: 1 }]

chkit pull and chkit drift read every index type. A type, or an argument list, that the structured forms cannot express exactly (for example set without a row limit, or a text index parameter chkit does not know) comes back as a raw index carrying ClickHouse’s type_full, so pull never rewrites an index into a different one. Drift compares the type the way ClickHouse formats it, so a structured index and the same type written raw do not drift.

Each entry in the constraints array is a TableConstraintDefinition with a name and exactly one of check or assume.

FieldTypeDescription
namestringConstraint name
checkstringBoolean SQL expression. ClickHouse rejects every INSERT with a row for which it is false
assumestringBoolean SQL expression ClickHouse never checks; with optimize_using_constraints the optimizer trusts it
constraints: [
{ name: 'dimension', check: 'length(embedding) = 512' },
{ name: 'positive_id', assume: 'id > 0' },
]

Constraints render inside CREATE TABLE after the indexes. A new constraint is ALTER TABLE ... ADD CONSTRAINT, a removed one DROP CONSTRAINT, and a changed expression or kind drops and re-adds it. Every constraint operation has risk=caution and carries a warning: ClickHouse does not validate existing rows against a new CHECK, but rejects later INSERTs that violate it, and a wrong ASSUME returns wrong query results. In a migration that also drops a column, chkit drops the constraints and skip indexes first, because ClickHouse refuses to drop a column they read.

ClickHouse stores the expression in its own formatting (s IN ('a','b') AND (id+1)*2 > 0 is stored as (s IN ('a', 'b')) AND (((id + 1) * 2) > 0)). chkit drift formats both sides through ClickHouse, so a different spelling of the same expression is not drift.

Each entry in the projections array is a ProjectionDefinition, which takes one of two forms.

A SELECT projection stores a rewritten copy of the data.

FieldTypeDescription
namestringProjection name
querystringProjection SELECT query

An index-only projection stores no SELECT body. It reorders parts by a secondary key so lookups on that key prune instead of scanning.

FieldTypeDescription
namestringProjection name
indexstringExpression list to order by, e.g. receiver, sender
typestringProjection index type. ClickHouse currently accepts basic
projections: [
{ name: 'p_recent', query: 'SELECT id ORDER BY received_at DESC LIMIT 10' },
{ name: 'by_receiver', index: 'receiver, sender', type: 'basic' },
]

The index expression is rendered the way ClickHouse normalizes it: a single expression is emitted bare (INDEX receiver), several are emitted as a tuple (INDEX (receiver, sender)), redundant parentheses are dropped, and a space follows every argument separator. Writing '(receiver)' and 'receiver' therefore produce the same table, and neither reads as drift.

A projection must be exactly one of the two kinds. Setting both query and index on the same entry is a projection_ambiguous_kind validation error, and an empty index is a projection_empty_index error.

Creates a view definition.

FieldTypeRequiredDescription
databasestringyesDatabase name
namestringyesView name
asstringyesSELECT query; may span lines and contain comments (see SQL fragments)
commentstringnoView comment
import { view } from '@chkit/core'
const activeUsers = view({
database: 'app',
name: 'active_users',
as: 'SELECT id, email FROM app.users WHERE active = 1',
})

A view can read tables, other views, materialized views, and dictionaries. chkit generate creates a view after the objects it reads and drops it before them, whatever their names. Write qualified names (app.users) in as so every reference is detected. See Operation order.

Creates a materialized view definition. In Python the factory is materialized_view().

FieldTypeRequiredDescription
databasestringyesDatabase name
namestringyesMaterialized view name
to{ database: string; name: string }yesTarget table for the view
refreshMaterializedViewRefreshnoRefresh schedule — see Refreshable materialized views
asstringyesSELECT query; may span lines and contain comments (see SQL fragments)
commentstringnoView comment
import { materializedView } from '@chkit/core'
const eventCounts = materializedView({
database: 'analytics',
name: 'event_counts_mv',
to: { database: 'analytics', name: 'event_counts' },
as: 'SELECT org_id, count() AS total FROM analytics.events GROUP BY org_id',
})

For a refreshable (scheduled) materialized view, add the refresh field:

const dailyReport = materializedView({
database: 'analytics',
name: 'daily_report_mv',
to: { database: 'analytics', name: 'daily_report' },
refresh: { every: '1 DAY', offset: '2 HOUR' },
as: 'SELECT toDate(ts) AS day, count() AS total FROM analytics.events GROUP BY day',
})

See Refreshable materialized views for the full refresh field reference, including APPEND mode, DEPENDS ON, and the ClickHouse rules that chkit validates.

Creates a ClickHouse dictionary definition — a key-value lookup structure backed by an external or in-database source, queried with dictGet().

import { dictionary } from '@chkit/core'
const usersDict = dictionary({
database: 'default',
name: 'users_dict',
attributes: [
{ name: 'id', type: 'UInt64' },
{ name: 'name', type: 'String' },
{ name: 'email', type: 'String', default: '' },
],
primaryKey: ['id'],
source: `MYSQL(host 'db' port 3306 user 'reader' password '${process.env.MYSQL_PASSWORD}' db 'app' table 'users')`,
layout: `HASHED()`,
lifetime: `300`,
comment: 'User lookup dictionary',
})
FieldTypeDescription
databasestringClickHouse database name
namestringDictionary name
attributesDictionaryAttribute[]Attribute definitions (see Dictionary attributes)
primaryKeystring[]Key attribute name(s) — every entry must name a declared attribute
sourcestringRaw SOURCE(...) body, e.g. `MYSQL(host '...' password '...' ...)`
layoutstringRaw LAYOUT(...) body, e.g. `HASHED()` or `COMPLEX_KEY_HASHED()`
lifetimestringRaw LIFETIME(...) body, e.g. `300` or `MIN 300 MAX 360`. generate compares it in the form ClickHouse stores, so 300 and MIN 0 MAX 300 are the same lifetime
FieldTypeDescription
range{ min: string; max: string }RANGE(MIN ... MAX ...) — required by RANGE_HASHED / COMPLEX_KEY_RANGE_HASHED layouts. Both min and max must name declared attributes
settingsRecord<string, string | number>Raw SETTINGS(...) key/value pairs, e.g. { dictionary_use_async_executor: 1 }
commentstringDictionary comment
renamedFrom{ database?: string; name: string }Previous identity for rename tracking

Each entry in the attributes array is a DictionaryAttribute.

FieldTypeDescription
namestringAttribute name
typestringClickHouse type
defaultstring | number | booleanDEFAULT value for missing keys. A literal: ClickHouse accepts no expression here. Mutually exclusive with expression
expressionstringEXPRESSION computed from source columns. Mutually exclusive with default
hierarchicalbooleanMarks the attribute HIERARCHICAL
bidirectionalbooleanMarks the attribute BIDIRECTIONAL — enables parent/child lookups in both directions. Only valid alongside hierarchical
injectivebooleanMarks the attribute INJECTIVE
isObjectIdbooleanMarks the attribute IS_OBJECT_ID (MongoDB sources)

Inline credentials in source (e.g. a MySQL/PostgreSQL password '...') should be interpolated from environment variables at schema-authoring time, the same way you’d handle any other secret in a config file:

source: `MYSQL(host 'db' password '${process.env.MYSQL_PASSWORD}' ...)`,

ClickHouse redacts inline passwords back to [HIDDEN] on introspection (SHOW CREATE DICTIONARY, system.dictionaries). A real password change diffs and migrates like any other field change. The one exception is a source that still carries the literal [HIDDEN] placeholder written by chkit pull — chkit never knows the real value in that case, so it excludes source from the diff entirely rather than risk rendering [HIDDEN] into DDL — see Pull: credential handling.

ClickHouse has no ALTER DICTIONARY — every structural change to a dictionary is rendered as a single CREATE OR REPLACE DICTIONARY statement (atomic, dependency-safe). See Structural vs. alterable properties. A pure rename (renamedFrom with no other change) is the one exception — it renders as RENAME DICTIONARY, not a replace; see Dictionary rename.

Several fields hold ClickHouse SQL that chkit copies into the DDL it generates: as on views and materialized views, partitionBy and ttl on tables, skip index expression, projection query and index, and dictionary source, layout, and lifetime. These fields accept SQL that spans several lines and contains comments:

import { view } from '@chkit/core'
const meetingCompany = view({
database: 'crm',
name: 'meeting_company',
as: `
WITH people_by_email AS (
SELECT person_id, company_id, arrayJoin(emails) AS email FROM crm.person_identity
)
-- The attendee's company, through their person record.
SELECT email, company_id /* one row per address */
FROM people_by_email
`,
})

Before chkit compares a fragment, stores it in snapshot.json, or writes it into a migration, it removes the comments and collapses each run of whitespace to one space, so the generated CREATE VIEW holds the query on one line. Comment syntax follows ClickHouse: --, //, #!, and # followed by a space start a comment that runs to the end of the line, and /* */ comments can nest. Comment markers inside string literals ('--') and quoted identifiers are text and stay in place. An unterminated block comment or string literal is left as written, so ClickHouse reports it when the migration runs.

Editing the text of a comment does not produce a migration, and neither does adding or removing a comment that whitespace separates from the rest of the SQL. Whitespace inside string literals collapses too: each run of spaces, tabs, or newlines inside a literal becomes one space.

Full-text indexes (type: 'text') are the exception: chkit reads their SQL with a separate parser that keeps whitespace inside string literals and recognizes only -- and non-nested /* */ comments.

The codegen plugin maps ClickHouse types to TypeScript types using these rules (the Python codegen plugin emits Pydantic models with the analogous Python types — string → str, number → int/float, T[] → list[T], and so on):

CategoryClickHouse TypesTypeScript Type
String-likeString, FixedString, Date, Date32, DateTime, DateTime64, UUID, IPv4, IPv6, Enum8, Enum16, Decimal*string
NumberInt8, Int16, Int32, UInt8, UInt16, UInt32, Float32, Float64, BFloat16number
Large integersInt64, Int128, Int256, UInt64, UInt128, UInt256string (default) or bigint
BooleanBool, Booleanboolean
WrappersNullable(T)T | null
WrappersLowCardinality(T)same as T
CompositeArray(T)T[]
CompositeMap(K, V)Record<K, V>
CompositeTuple(T1, T2, ...)[T1, T2, ...]
AggregateSimpleAggregateFunction(fn, T)same as T
JSONJSONRecord<string, unknown>

Parameterized types like DateTime('UTC'), Decimal(18, 4), and Enum8('a' = 1) are supported. The bigintMode option in the codegen plugin controls whether large integers map to string or bigint.

chkit tracks renames to avoid destructive drop-and-recreate operations.

Set renamedFrom on a table definition to rename a table:

const users = table({
database: 'app',
name: 'accounts', // new name
renamedFrom: { name: 'users' }, // old name
// ...
})

The database field in renamedFrom is optional and defaults to the table’s current database.

Set renamedFrom on a column definition to rename a column:

columns: [
{ name: 'user_email', type: 'String', renamedFrom: 'email' },
]

Set renamedFrom on a dictionary definition to rename a dictionary. This emits a single RENAME DICTIONARY IF EXISTS ... TO ... statement instead of a drop_dictionary + create_dictionary pair:

const lookupDict = dictionary({
database: 'app',
name: 'lookup_dict', // new name
renamedFrom: { name: 'users_dict' }, // old name
// ...
})

The database field in renamedFrom is optional and defaults to the dictionary’s current database.

Table, column, and dictionary renames can all be overridden by CLI flags: --rename-table, --rename-column, and --rename-dictionary.

The plugins field on a table definition provides per-table configuration for plugins. In TypeScript, each plugin that supports table-level config augments the TablePlugins interface via declaration merging; in Python it is a plain dict.

import { table } from '@chkit/core'
const events = table({
database: 'app',
name: 'events',
columns: [
{ name: 'event_time', type: 'DateTime' },
{ name: 'id', type: 'UInt64' },
],
engine: 'MergeTree',
orderBy: ['event_time', 'id'],
primaryKey: ['event_time', 'id'],
plugins: {
backfill: { timeColumn: 'event_time' },
},
})

Currently supported plugin keys:

KeyPluginFieldsDescription
backfill@chkit/plugin-backfilltimeColumn?: stringTime column for backfill WHERE clauses

The plugins field is ignored by the diff engine — it does not affect migration planning or SQL generation.

chkit validates schema definitions and throws a ChxValidationError if any issues are found:

  • Duplicate object names — two definitions with the same kind, database, and name
  • Duplicate column names — repeated column name within a table
  • Duplicate index names — repeated index name within a table
  • Duplicate projection names — repeated projection name within a table
  • Duplicate constraint names (duplicate_constraint_name) — repeated constraint name within a table
  • Invalid constraint (constraint_invalid) — a constraint sets both check and assume, neither, or an empty expression
  • Invalid vector similarity index (vector_index_invalid_parameters) — an unknown method, distanceFunction or quantization, a non-positive dimensions or granularity, only some of the three tuning fields, hnswMaxConnectionsPerLayer: 1, or b1 quantization without cosineDistance or with dimensions not divisible by 8
  • Raw index without a type (raw_index_invalid_type) — a raw index with an empty rawType
  • Ambiguous projection kind (projection_ambiguous_kind) — a projection sets both query and index; use one or the other (see Projections)
  • Empty projection index (projection_empty_index) — an index-only projection whose index expression is empty
  • Primary key references missing column — primaryKey includes a bare column name not in columns (function expressions like toDate(ts) are passed through to ClickHouse unchecked)
  • Order by references missing column — orderBy includes a bare column name not in columns (function expressions like toStartOfHour(ts) are passed through to ClickHouse unchecked)
  • Empty codec chain (codec_chain_empty) — a codec array with no steps; provide at least one codec or omit the field
  • Unknown codec (codec_unknown) — a codec atom without a kind, a kind chkit does not know (kinds are case-sensitive: Zstd is not ZSTD), or a raw atom such as zstd that names a codec in the wrong case
  • Invalid codec parameter (codec_parameter_invalid) — a field the codec does not take, or a value out of range: a non-integer level, an LZ4HC or ZSTD level outside -9007199254740991 to 9007199254740991, ZSTD level above 22 or windowLog other than 0 or an integer in 10–31, FPC level outside 1–28, a size other than 1, 2, 4 or 8, a floatSize other than 4 or 8, a T64 mode other than 'byte' or 'bit', an empty raw expression, or a raw atom such as Delta(3) or ZSTD(1.0) whose arguments ClickHouse rejects (raw arguments are integers written as ClickHouse reads them: decimal, 0x hex or 0b binary, with _ between digits, and at most 2^53 - 1 in size)
  • NONE combined (codec_none_combined) — NONE with any other codec
  • Multiple general codecs (codec_chain_multiple_general) — more than one of LZ4, LZ4HC, ZSTD and Default in a chain
  • Codec after the general codec (codec_chain_must_end_with_general) — a transform or compression codec after the general codec; only encryption codecs may follow it
  • Codec after encryption (codec_chain_encryption_not_last) — any codec but another encryption codec after AES_128_GCM_SIV or AES_256_GCM_SIV
  • Chain without compression (codec_chain_no_compression) — a chain of only Delta and GCD, with or without encryption
  • Float codec order (codec_chain_float_order) — Gorilla, FPC or ALP after Delta or DoubleDelta, or ALP after Gorilla, FPC or ALP
  • Codec type unsupported (codec_type_unsupported) — a codec on a column type it does not accept, such as T64 on Float64 or Delta on String (see the codec tables in codec)
  • Column expression required (column_expression_required) — a MATERIALIZED or ALIAS column has no default, or a default expression is empty: { expression: '' }, an expression made only of comments, or a bare 'fn:'
  • Invalid column kind (column_default_kind_invalid) — defaultKind is not DEFAULT, MATERIALIZED, ALIAS, or EPHEMERAL (Python rejects it when the column is constructed)
  • Default looks like an expression (column_default_looks_like_expression) — the plain string default of a DEFAULT or EPHEMERAL column starts with a function call, such as 'now64(3)', and the column type cannot hold a string; use { expression: 'now64(3)' } (see default)
  • Plain string expression (column_expression_requires_fn) — a MATERIALIZED or ALIAS column has a plain string default, which renders as a quoted literal instead of SQL; use { expression: 'toDate(ts)' }, or { expression: "'text'" } for a constant string (see defaultKind)
  • Invalid default (column_default_invalid) — default is an object other than { expression: string }, an expression that keeps the legacy prefix, such as { expression: 'fn:now()' }, or an expression with an unterminated string, quoted identifier, or block comment, such as { expression: 'now() /* set on insert' }, or with a # that starts no comment, such as { expression: 'now() #' }
  • Key on an engine without sort order (engine_unsupported_clause) — partitionBy, primaryKey, or orderBy is set on an engine that rejects them, such as Distributed, Memory, or Null (see Distributed tables)
  • Unstored column in a key (column_kind_not_stored) — an ALIAS or EPHEMERAL column is named directly in orderBy, primaryKey, partitionBy or an engine argument, or an EPHEMERAL column is a skip index expression
  • EPHEMERAL column in a projection (column_ephemeral_in_projection) — a projection reads an EPHEMERAL column
  • Codec on an unstored column (column_kind_codec_unsupported) — an ALIAS column, or an EPHEMERAL column without a default or comment, has a codec
  • Dictionary missing primary key (dictionary_missing_primary_key) — a dictionary’s primaryKey is empty
  • Dictionary primary key references missing attribute (dictionary_primary_key_missing_attribute) — a primaryKey entry doesn’t name a declared attribute
  • Dictionary missing source/layout/lifetime (dictionary_missing_source, dictionary_missing_layout, dictionary_missing_lifetime) — one of these raw-string fields is empty
  • Dictionary attribute default/expression exclusive (dictionary_attribute_default_expression_exclusive) — an attribute sets both default and expression
  • Dictionary range references missing attribute (dictionary_range_missing_attribute) — range.min/range.max doesn’t name a declared attribute
  • Dictionary bidirectional requires hierarchical (dictionary_bidirectional_requires_hierarchical) — an attribute sets bidirectional without hierarchical

The table rules below apply to MergeTree-family tables. Kafka changes require an explicit replacement; chkit refuses generic ALTERs. A Distributed table follows the same rules, but its recreate drop is risk=caution because it holds no rows.

When a property changes, chkit determines whether the table can be altered in place or must be dropped and recreated.

Structural (drop + recreate): engine, primaryKey, orderBy, partitionBy, uniqueKey, and the settings ClickHouse refuses to change on an existing table: index_granularity, index_granularity_bytes, enable_mixed_granularity_parts, table_disk, the add_minmax_index_for_* settings, and on ObsessionDB also allow_tuple_element_aggregation and share_nested_offsets

Alterable (ALTER in place): columns, indexes, projections, constraints, other settings, TTL, comment

A MergeTree table that does not declare index_granularity is treated as having the server’s default, the value ClickHouse stores for it: 8192, or clickhouse.indexGranularity when the server uses another. CREATE TABLE writes SETTINGS index_granularity = 8192, and a schema with or without it plans nothing. Setting values compare by value: 8192 and "8192", 2.0 and 2., 1e6 and 1000000 are the same, and true and false equal 1 and 0. Numbers compare exactly, so two large integers that differ only past the 15th digit are different values.

What chkit pull reads back plans nothing against the schema it came from:

  • A column type compares in the spacing ClickHouse stores: Decimal(10,2) and Decimal(10, 2) are the same type. Text in quotes, such as an Enum label or a time zone, compares as written. chkit writes the type as you declared it.
  • A default that is one number or string literal compares by value: 2.0 and 2., 1e21 and 1e+21, 'it\'s' and "it's" are the same default. A string escape whose meaning chkit does not know for certain (such as \N or \u0041) compares as text, so it can show a change that is not one, but never hides one.
  • Indexes, projections, constraints and columns compare whatever order their keys are written in.
  • A key (orderBy, primaryKey, uniqueKey) compares token by token as chkit renders it: whitespace, how a name is quoted, and how an expression is split across entries (['cityHash64(a', 'b)']) don’t matter. A quoted name keeps its commas and spaces, so a key on a column named a, b is not the key on columns a and b.

The auto_minmax_index_* skip indexes ClickHouse creates for the add_minmax_index_for_* settings are not part of the schema.

Views and materialized views use drop + recreate for a change to the query (or a materialized view’s TO target or refresh mode). A comment-only change is an ALTER TABLE ... MODIFY COMMENT in place; see Comments.

Dictionaries have no ALTER for their definition: any change to attributes, primaryKey, layout, lifetime, or source (including a password change) renders as a single CREATE OR REPLACE DICTIONARY (risk=caution), which carries the new comment. A comment-only change is an ALTER TABLE ... MODIFY COMMENT. A source still carrying the [HIDDEN] introspection placeholder is excluded from the diff entirely (see Credentials in source). Removing a dictionary from schema emits DROP DICTIONARY (risk=danger, requires --allow-destructive).

comment on a table, column, view, materialized view or dictionary is stored as written: chkit escapes backslashes and quotes, so the text C:\new ('C:\\new' in TypeScript) reaches system.tables.comment (or system.columns.comment) as C:\new, not with a newline. Leading and trailing whitespace is trimmed, and an empty comment is the same as none, so switching between comment: '' and no comment plans nothing.

ChangePlanned statementRisk
Table, view, materialized view or dictionary comment added or changedALTER TABLE <object> MODIFY COMMENT '<text>'safe
Same, comment removedALTER TABLE <object> MODIFY COMMENT ''safe
Column comment added or changed, nothing elseALTER TABLE <table> COMMENT COLUMN <column> '<text>'safe
Column comment removed, nothing elseALTER TABLE <table> MODIFY COLUMN <column> REMOVE COMMENTsafe
Column comment and type or default changedMODIFY COLUMN with the new COMMENT; a removed comment runs REMOVE COMMENT firstcaution

A comment change never recreates a view, materialized view or dictionary: recreating a materialized view would lose the rows its source receives in between. A change that recreates the object anyway, such as a new query, carries the new comment in its CREATE.

On ObsessionDB, MODIFY COMMENT currently changes only the replica that runs it (#407), so chkit drift may report a comment_mismatch for a table, view, materialized view or dictionary until the server fix ships. Column comments are not affected.