ch-migrate manages SQL-first ClickHouse migrations across environments: author SQL files, bootstrap databases and roles, inspect migrations and dependencies, and compare schema snapshots. Alembic owns revision history; ClickHouse's official clickhouse-connect[alembic] integration owns the dialect and DDL compilation. ch-migrate supplies the operational layer above them, without implying an endorsement from ClickHouse.
Background: ClickHouse migrations with Alembic.
uv tool install ch-migrate-cli
# Or:
pip install ch-migrate-cli
ch-migrate --versionThe command is ch-migrate, the PyPI package is ch-migrate-cli (PyPI treats ch-migrate as the same name as the existing, unrelated chmigrate), and migrations import from ch_migrate. Versions up to 0.4.1 were published as clickhouse-alembic with the import package clickhouse_alembic; that import still works with a deprecation warning until 1.0, so existing migration files keep running. Replace clickhouse_alembic with ch_migrate in them when convenient. For migrations/env.py, run ch-migrate upgrade-env only if you never edited it; if you did (for example to add connection settings), change its clickhouse_alembic imports to ch_migrate by hand instead, because upgrade-env replaces the whole file.
To switch an existing install, remove the old package first, because both install the ch-migrate command and the clickhouse_alembic folder: uv tool uninstall clickhouse-alembic && uv tool install ch-migrate-cli, or pip uninstall clickhouse-alembic && pip install ch-migrate-cli. In a project that lists clickhouse-alembic as a dependency, replace it with ch-migrate-cli.
This README describes the source checkout, which may be ahead of PyPI. To try an unreleased checkout locally, run uv tool install . in the repository. For development without installing a global tool, use uv run --locked ch-migrate.
0.9 requires Python 3.10+; 0.5.x is the last line supporting Python 3.9.
A loose requirement such as ch-migrate-cli or ~=0.5 upgrades to 0.9; pin
ch-migrate-cli==0.5.1 until each project has run the command below.
After upgrading the package, run this in each migration project:
ch-migrate upgrade-envinit and upgrade-env write a thin version-2 environment that delegates to the
package. The old file is saved as migrations/env.py.bak; repeating the command
on the current shim leaves that backup intact. Review any old customizations.
Existing Python migrations and version tables continue to work without edits.
up, down, status, and history refuse old environments before connecting
and tell you to run upgrade-env.
One HTTP session spans a migration run, including later revisions in that run.
A standalone SET therefore carries into following revisions. Prefer a
statement's SETTINGS clause when the change should apply only to that query.
session_timeout in an environment or defaults sets the idle timeout in
seconds (default 1800). If that session expires, the next request fails rather
than silently recreating a session with default settings.
On ClickHouse Cloud a session lives on one replica, and Cloud routes each TCP connection, not
each session, to a replica. ch-migrate keeps a run on one dedicated connection. If that
connection drops, for example after a migration pauses client-side for more than Cloud's
10-second keep-alive, the run stops with SESSION_NOT_FOUND instead of continuing without
your settings.
Use a dedicated ClickHouse test server. The example uses local HTTP; replace the host and port with your server's address. For HTTPS, set secure: true and the HTTPS port (usually 8443). Never run a trial migration against a shared or production database.
mkdir my-clickhouse-project
cd my-clickhouse-project
ch-migrate init --name my_projectReplace config.yaml with:
project:
name: my_project
defaults:
port: 8123
secure: false
admin_user: default
environments:
dev:
host: 127.0.0.1
database: my_project_dev
migration_user: migration_devCreate .env.local in this project directory. Replace both values: the admin password is your server's existing password; the migration password is the password to give the new migration user.
CH_DEV_ADMIN_PASSWORD=your-admin-password
CH_DEV_MIGRATION_PASSWORD=your-migration-passwordKeep .env.local out of git. init creates an ignore entry for it.
ch-migrate bootstrap dev --dry-run
ch-migrate bootstrap dev
ch-migrate new dev add_status --table logsThe last command creates a revision plus two files under migrations/sql/history/tables/logs/. Their names include a timestamp and revision ID. Replace the contents of the generated .up.sql with:
CREATE TABLE IF NOT EXISTS {db}.logs (id UInt64)
ENGINE = MergeTree ORDER BY id;
ALTER TABLE {db}.logs ADD COLUMN IF NOT EXISTS status String;Replace the contents of its .down.sql with:
DROP TABLE IF EXISTS {db}.logs;Do not edit the generated revision file. The example downgrade drops the table and its data; only use it in this empty test project.
ch-migrate up dev
ch-migrate status dev
ch-migrate history dev
ch-migrate down devAfter up, the logs table has id and status columns and status reports one applied revision. After down, the example table is gone.
project/
├── config.yaml
├── .env.local # Secrets, ignored by git
├── alembic.ini
└── migrations/
├── env.py # Generated Alembic environment
├── script.py.mako
├── versions/ # Revision graph; generated Python adapters
└── sql/
├── bootstrap/ # Optional bootstrap SQL
└── history/
├── tables/<name>/
├── views/<name>/
├── dictionaries/<name>/
└── other/ # No named object
new creates <YYYY_MM_DD_HHMM>_<revision>_<slug>.up.sql and .down.sql. The message slug is at most 40 characters. Object directories are created when needed. Only one of --table, --view, and --dict may be supplied.
run_sql runs one statement per request. Semicolons inside strings, quoted identifiers, comments, or heredocs do not split statements. Empty or comment-only files fail instead of recording an unfilled migration as applied. Execution stops at the first failed statement; ClickHouse DDL is not transactional, so earlier changes remain.
| Placeholder | Value |
|---|---|
{db} |
Environment database |
{cluster} |
Configured cluster, or an empty string |
{on_cluster} |
ON CLUSTER <cluster>, or an empty string |
Keyword arguments to run_sql add or override substitutions. All other braces remain literal, including JSON and ClickHouse parameters such as {id:UInt64}. Doubled braces are not format escapes. Write statements that are safe to repeat where possible, such as CREATE ... IF NOT EXISTS and DROP ... IF EXISTS.
To render without executing, set CH_ENVIRONMENT and run alembic upgrade head --sql. Existing projects need ch-migrate upgrade-env to use the packaged environment. The package requires Alembic 1.18 or later and the official clickhouse-connect[alembic] integration.
New alembic_version tables use ORDER BY version_num. The runtime inspects
system.databases.engine and the environment's cluster setting:
| Deployment | New version table |
|---|---|
| Shared database on ClickHouse Cloud | MergeTree; Cloud supplies its shared implementation |
Self-hosted Replicated database |
ReplicatedMergeTree() with server-default arguments |
Atomic database with cluster configured |
ReplicatedMergeTree created ON CLUSTER, with a Keeper path containing the database name and shard macro |
| Single server | MergeTree |
Version advancement remains insert the new row, then delete the old row;
there is no asynchronous version UPDATE. Online up journals that bookkeeping
and polls its owned DELETE rather than holding an HTTP request open with
mutations_sync. If interrupted, both rows can remain. A subsequent up
finishes the proven bookkeeping without running the revision again. Overlapping
heads without an owned journal receipt are still refused. Failed version
mutations report their reason and an operator-only KILL statement; ch-migrate
never kills them itself.
Before reading replicated heads, the tool catches up replicated database
metadata and inserted version parts. The table barrier uses
SYSTEM SYNC REPLICA ... LIGHTWEIGHT,
which does not wait for held mutation tasks. Failure checks cover reachable
hosts in the configured cluster, or the
Replicated database's automatically named cluster.
An unavailable host is not evidence that its state is healthy.
Self-hosted replicas need Keeper and suitable {shard}/{replica} macros.
Configure authentication for a Replicated database's automatic cluster through
its collection_name setting; the named collection uses cluster_username,
cluster_password, and optionally cluster_secret/cluster_secure_connection.
Keep those values in server-side secret configuration, not migration files.
Provision the migration user on each node. Re-run bootstrap when upgrading to
grant the system-table reads and available CLUSTER, remote-read, and replica
synchronization privileges used by these checks.
Existing 0.4.1 ReplacingMergeTree ORDER BY updated tables are never converted
automatically. upgrade-env remains offline: it prints a conditional advisory
instead of probing every configured environment. status ENV performs the live
check and warns when existing state is not replicated but the deployment is.
Do not route migrations across nodes until that state is reconciled.
For a manual conversion:
- Stop every migration runner. Back up the old table's DDL and rows on each node, and inspect unfinished version mutations.
- Reconcile one authoritative set of completed revision heads with the
revision graph. Do not infer it from the newest timestamp or blindly use
FINAL: the old timestamp key can collapse heads written in the same second. - Keep the old tables as backups. On an Atomic cluster, rename each existing old table on its own node, then create the replacement on the cluster. In a Replicated database, issue schema changes once and let its DDL log propagate them.
- Use
MergeTree ORDER BY version_numon a single server, or the replicated engine from the table above. For an Atomic cluster, use a fresh shared Keeper path containing the database and the appropriate replica macros. - Copy the reconciled, distinct heads once into the empty replacement,
synchronize replicas, and compare
statusthrough every routing endpoint. Resume runners only after they agree. Keep backups until the result is verified.
Offline SQL cannot inspect a live database engine: it uses configured cluster
information or the single-server/Cloud default for version-table DDL. Review
that DDL before using it for a self-hosted Replicated database. Cloud qualification
is separate from the local suite; the local replicated harness has one shard.
up waits for the mutations its statements create before completing a revision.
It uses the same checked ClickHouse session for submission and polling, so prior
SET statements stay effective. op.execute, raw op.get_bind() execution, and
run_sql all pass through the cursor boundary. Upgrades run in the CLI process:
killing that process cannot leave a child Alembic runner advancing revisions.
ch-migrate up dev # No default waiting timeout
ch-migrate up dev --timeout 120 # One waiting budget across the invocationProgress names the table, mutation ID, host, remaining parts and elapsed time.
A terminal uses a live line; CI gets plain state changes and periodic summaries
on stderr. Zero remaining parts alone does not count as completion: every
required replica must report its owned mutation finished. Configure cluster
for self-hosted replicated tables, or use a Replicated database's automatic
cluster. Missing replicas or missing evidence are never treated as success.
An active foreign mutation ahead of ours is reported and waited before more work is submitted. A failed predecessor stops the run. Foreign work queued after our completion barrier is not claimed as ours and does not hold our revision open.
Ownership is persisted in _ch_migrate_journal before SQL submission. The
journal follows the version table's deployment policy and uses the same session.
It records revision/statement identities, table UUIDs and progress receipts.
Run only one migration runner per database, using a CI concurrency key or an
external lock: this journal is not a distributed lock. Do not delete or restore
it independently of the database state it describes.
After a timeout, dropped connection or killed process, ClickHouse may still be
working. Run the same up again: it skips proven completed statements and
reattaches to its known unfinished mutation instead of issuing it again.
Successful downgrade starts a fresh journal generation so a later upgrade
actually executes. A changed unresolved statement or replaced table is refused.
An explicitly rejected repeat-safe metadata statement can be repaired and retried;
transport errors do not establish rejection.
Unknown outcome: if ownership/completion evidence is missing or expired,
up exits nonzero, does not reissue the statement and does not complete the
migration. The error names the journal key, token and recorded UUIDs and provides
read-only inspection queries. Operator recovery is deliberately not a retry flag:
- Stop migration runners and quiesce other writers as needed. Back up the affected journal key and relevant data before making changes.
- Establish whether the original statement took effect using independent data, schema and server evidence on every required replica. An absent mutation row is not proof either way.
- Reconcile that specific statement and journal key in a reviewed manual
operation. For a proven applied operation, its completed receipt must carry
the actual post-operation UUID map (
after_target.uuids, or null for a dropped target). For a proven unapplied operation, remove only that key's attempt records after restoring any partial effects, and wait for that journal change on every replica. Preserve the other completed statement receipts. - Recheck the recorded heads and rerun
up. Never blindly stamp the revision, delete the whole journal, or replay a non-idempotent statement to guess its outcome.
Failed mutation: latest_fail_reason stops the run and prints a scoped
KILL MUTATION statement for the operator. The tool does not execute it. Fix the
cause and reattach, or review a cancellation and reconcile its effects; killing
a mutation is not proof that it completed.
MODIFY TTL has a separate metadata/materialization boundary: when materialization
is enabled, the runtime journals the metadata change with automatic materialization
disabled, then submits a separately owned MATERIALIZE TTL. This avoids confusing
TTL's comma-separated rules with ALTER actions. Session and per-query
materialize_ttl_after_modify = 0 are honored. Parameter batches are executed
sequentially through the same single-statement path, with a receipt and completion
barrier for each parameter set; intermediate TTL effects are not collapsed.
Lightweight DELETE keeps its row-mask semantics; lightweight UPDATE is synchronous
patch work, not a fabricated system.mutations record.
For ON CLUSTER statements, the same journal first records a unique distributed
DDL marker. up submits without a server-side synchronous DDL wait, then polls
system.distributed_ddl_queue. Every configured target host must report
Finished and exception code zero. A failed host is named with its error;
a down host remains pending until it returns or the shared --timeout expires.
The tool neither deletes the queue entry nor resubmits the DDL on rerun.
The queue entry is found by its recorded log_comment, not by matching SQL text
(the server can insert UUIDs into that text). Caller log comments are preserved
in the journal. A lost acknowledgement can reattach to the existing entry;
missing/expired queue evidence follows the unknown-outcome procedure above.
Mutation-producing distributed ALTERs must satisfy both barriers: DDL execution
on all hosts and completion of their owned mutations. TTL metadata and explicit
materialization retain separate queue receipts.
Single-node statements without ON CLUSTER do not consult that queue. Ordinary
Cloud DDL does not need ON CLUSTER; Cloud qualification remains separate from
the local harness. Re-run bootstrap when upgrading a restricted migration user
to obtain the available cluster and distributed-queue inspection grants.
up statically checks pending upgrade statements before Alembic executes any of
them. Idempotency and standalone-SET errors refuse the whole run and print the source file, line,
statement, and suggested fix. There is no command-line bypass; --skip-mv-check
does not skip this gate. Other findings are warnings in up; standalone lint
retains their configured severities.
Static lint rejects a standalone SET when migrations/env.py lacks the
CH_MIGRATE_ENV_VERSION = 2 marker: that statement would be ignored on the old
connection. Run ch-migrate upgrade-env, or put the setting in the relevant
statement's SETTINGS clause. Version-2 projects allow SET; SETTINGS clauses
and the word SET inside string literals are not flagged. standalone_set is
a gate rule whose severity cannot be lowered.
Use IF NOT EXISTS for CREATE and ALTER ... ADD, and IF EXISTS for DROP,
ALTER ... DROP, and RENAME COLUMN, where supported. This includes tables,
views, materialized views, dictionaries, databases, users, roles, row policies,
settings profiles, quotas, functions, and named collections. Column, index,
projection, and constraint changes are checked separately. CREATE OR REPLACE
is accepted. ATTACH and DETACH need their matching IF form.
Inserts, exchanges, table/dictionary/database renames, updates/deletes,
ATTACH PARTITION ... FROM, and MOVE PARTITION ... TO TABLE need a reasoned
waiver. Put it directly above the statement, without a separating blank line:
-- ch-migrate: allow-non-idempotent This backfill targets a reviewed deduplicating table
INSERT INTO {db}.target SELECT id FROM {db}.source;For inline Python SQL, use the same directive in a # comment directly above
op.execute. Empty reasons remain errors. Waivers print as INFO with their
reasons in lint and up; they do not make the statement idempotent.
MODIFY, MATERIALIZE, CLEAR COLUMN, TRUNCATE, OPTIMIZE, SYSTEM, grants,
revokes, REPLACE PARTITION, and comments are not flagged by this rule.
The reviewable classification corpus is tests/corpus/idempotency.yaml.
Recovery: fix the cause of a partial failure, then run ch-migrate up ENV
again. Earlier idempotent statements can run again; the version advances only
when the revision completes. Before retrying waived operations, review what the
earlier attempt actually changed.
Existing history: upgrade-env records the current local script head(s) in
config.yaml as lint.gate_baseline, preserving YAML comments. It records
script history, not the deployed database head. The baseline and its ancestors
are exempt in every environment, including where they have not yet run.
Multiple heads are stored as a list. init writes no baseline, so new projects
gate every revision.
Review and commit the baseline diff. Running upgrade-env again updates it to
the current script heads; do not use that operation to hide new findings.
Static lint checks revisions after the baseline, and live lint ENV intersects
that scope with pending revisions. Lowering a gate rule through lint.rules
is an error: use a reviewed baseline or an explicit statement waiver instead.
ch-migrate new dev drop_legacy --table logs --irreversible "Drops legacy data"This creates only an upgrade file. Its revision has an irreversible reason and raises IrreversibleMigration in its downgrade. An empty reason is rejected.
down reads markers statically, without importing migration files. It refuses an entire known range if any revision is irreversible: no preceding reversible downgrade runs first. It understands -N on linear history, full or unique-prefix IDs, and base. If a range is unknown, including a relative target across a merge point, it prints a note and relies on the migration's exception. Direct Alembic calls rely on the same backstop.
There is no override flag. To revert past a marked revision, implement its downgrade and remove the marker in a reviewed change. irreversible = True is accepted as "(no reason given)".
Use new --python for logic that cannot be expressed as SQL files. It retains the Python template and, with an object option, a single SQL history file. Existing Python migrations continue to work.
from ch_migrate import get_db, run_sql
def upgrade():
run_sql("history/tables/logs/001_add_status.up.sql", db=get_db())get_db() returns the environment database. read_sql(path, **values) still returns a string using Python str.format; unlike run_sql, callers must escape literal braces and execute the returned SQL themselves. For a single-statement file, the existing pattern remains valid:
from alembic import op
from ch_migrate import get_db, read_sql
def upgrade():
op.execute(read_sql("history/tables/users/001_create.sql", db=get_db()))A hand-written irreversible Python revision uses both the marker and backstop:
from ch_migrate import IrreversibleMigration
irreversible = "Drops legacy data"
def downgrade():
raise IrreversibleMigration(revision, irreversible)new --exchange --table NAME is deprecated. It still generates the legacy shadow-table, copy, exchange, and drop scaffold, and prints a warning pointing to --rebuild. Rows written during the legacy copy can be lost: coordinate or pause writers if you deliberately retain that workflow. For online changes, use the guarded --rebuild workflow below. The legacy scaffold remains irreversible because it drops the old table.
The generated copy and exchange statements require explicit, reasoned waivers
before up accepts them. The generator does not waive them automatically.
For a controlled change, the underlying pattern is:
CREATE TABLE IF NOT EXISTS {db}.users_shadow
(id UInt64, email String, phone String) ENGINE = MergeTree ORDER BY id;
INSERT INTO {db}.users_shadow SELECT id, email, '' FROM {db}.users;
EXCHANGE TABLES {db}.users AND {db}.users_shadow;
DROP TABLE IF EXISTS {db}.users_shadow;EXCHANGE TABLES requires a supporting database engine. Neither the copy nor the exchange is automatically idempotent.
The dictionary helper retains automatic SELECT grants for a configured dictionary reader:
from ch_migrate import create_dictionary
def upgrade():
create_dictionary("history/dictionaries/dict_users/001_create.sql")op.rebuild_table() builds a replacement while writers keep inserting, then
swaps it into the original name. Its guarantee is no acknowledged row is lost,
and a small number may be duplicated. Duplicates can come from rows committed
or flushed between copying-view creation and snapshot completion, in server time.
ReplacingMergeTree variants may collapse those copies on merge; other engines
can retain them.
The exception is unacknowledged async inserts: rows queued with
async_insert = 1, wait_for_async_insert = 0 at the swap may be rejected by
ClickHouse. Such writers require allow_unacknowledged_async_loss=True in the
migration file. There is no command-line bypass. Writers that wait for
acknowledgement should retry error 741 (TABLE_UUID_MISMATCH) and other
transient rebuild errors. Writers must not ignore materialized-view errors.
One race ClickHouse leaves open is detected, not prevented. An insert that
resolves the table just before EXCHANGE and builds its materialized-view chain
just after it writes only to the old storage, which becomes the rollback table;
the copying view never sees it. In synthetic trials with eight busy writers, it
hit async inserts, including wait_for_async_insert = 1, in about 2% of swaps.
Synchronous inserts were never hit, but that does not prove they are immune.
After the swap, rebuild_table
reads system.part_log for parts created in the old storage by queries that
wrote nothing to the new table. If it finds any, the run stops with the row
count and query IDs and keeps its lock; see
Recover rows stranded at the swap.
Preflight refuses when a host has no system.part_log.
On ClickHouse Cloud the rebuild runs too, with three differences:
- Declare your writers. Cloud's compute-compute separation hides other services' inserts
from this one, and an insert of theirs could be missing from the rebuilt table. Once every
writer to the table uses the service you migrate through, set
writers_on_this_service_only=Truein therebuild_tablecall;new --rebuildwrites it asFalsefor you to change. Until thenplanreports, anduprefuses with,cloud_writers_undeclared. After the copying view reaches every replica, the rebuild waitsgrace_seconds(default 10) before draining inserts. - No forced queue flush. Cloud forbids
SYSTEM FLUSH ASYNC INSERT QUEUE;EXCHANGEdrains the old table's queue itself. The guarantee is unchanged. The report of rejected fire-and-forget inserts is a lower bound ("complete": false) that names the replicas it read. - Ownership by token. Cloud rejects chosen table UUIDs, so helpers carry an owner token in
their
COMMENTuntil the rebuild reads back and records their UUID. Do not edit helper comments: a changed token stops the rebuild for operator reconciliation.
Generate the rebuild from the environment's current DDL:
ch-migrate new dev reorder_events --table events --rebuildThe command writes an .up.sql file under migrations/sql/history/tables/events/
and an irreversible revision that calls rebuild_table. The command prints the
exact paths; you do not edit Python. Edit only the replacement CREATE in the SQL
file, keeping the source table's name. For example:
CREATE TABLE {db}.events (
id UInt64,
ts DateTime,
k UInt8,
payload String
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (k, ts, id);Inspect and apply it:
ch-migrate plan dev
ch-migrate up devThe operation generates its own helper names, UUIDs and replication paths. down refuses this revision; reverse it with another forward rebuild migration. If live DDL cannot be fetched, new writes a commented placeholder, not an executable empty table. Supply one complete CREATE TABLE before running up.
Advanced Python migrations can call op.rebuild_table("events", "events-rebuild.sql") or import rebuild_table from ch_migrate.
For a row transformation, select= is a projection expression list in target
column order, not a full SELECT query. It must preserve partition identity.
The default copies explicit insertable target columns, excluding MATERIALIZED
and ALIAS columns. Internal steps are guarded by durable state and do not need
idempotency waivers. plan reads the replacement SQL and runs the same preflight.
Before starting, stop mutations and partition DDL against the source for the
whole rebuild. Ordinary inserts continue. Sharded/Distributed sources,
partition-key changes, unfinished mutations, and incompatible physical transfers
are refused. Budget roughly 2–3× the table's disk space: the snapshot pins old
parts and the replacement stores a second copy. Helpers stay in the same database.
The snapshot and stage helpers are plain MergeTree without TTL, in the source's
replication family, so their merges keep every row they hold, even after a server
restart. Sorting key, partition key and physical columns match across each
transfer. An intentional engine change happens through row copy, not mismatched
ATTACH/MOVE.
Keeper helper roots are siblings of the original root, not children that could be
removed with it. Name-dependent path macros are resolved before the swap.
For Replicated tables in an Atomic database, configure a cluster covering every
source replica. An incomplete replica scope is refused rather than rebuilt on
only the connected node. Existing per-replica table UUIDs are retained and checked
individually.
For a Replicated database, helper CREATE statements set
database_replicated_allow_explicit_uuid=1 and
database_replicated_allow_replicated_engine_arguments=1 for that query only.
These documented settings
preserve the journaled UUIDs and isolated Keeper paths; value 2 would replace
them and is not used. A settings profile that forbids these overrides must be
reconciled before the rebuild. The tool does not change server/profile defaults.
The rebuild inspects dependents in the same database before creating its copying
view. It creates owned empty replacement helpers, then uses EXPLAIN and
zero-row result headers to validate source views, materialized views, dictionary
projections, and views that write TO the table against the replacement.
Compatible server-side conversions remain allowed; these checks cannot predict
invalid values that a future writer might supply.
A missing/renamed column or incompatible view expression names the failing dependent and refuses before any copying or swap. The tool removes its fresh validation helpers and releases its own lock; the source remains unchanged. Repairing the replacement SQL and rerunning starts a fresh attempt from that known rejection. A failure after copying begins does not use this cleanup path: it retains ownership/state for operator reconciliation.
Materialized views follow the original table name across the swap, including
views with an inner engine and views that write TO the rebuilt table. The tool
does not re-point or recreate application views. Snapshot/staging copies do not
replay the source view's historical aggregate: a source-to-aggregate difference
can therefore equal the duplicate rows inside the recorded rebuild window.
After the swap, the tool reloads each discovered dependent ClickHouse dictionary.
Reload errors name the dictionary and fail the migration rather than declaring
completion with an old cache. This needs the global SYSTEM RELOAD DICTIONARY
privilege; bootstrap grants it only where the administrator's current grants
allow it, while the runtime reloads only the discovered names.
Distributed routes to the source are conservatively refused even when the route has only one shard. Cross-database views and dictionaries that the scoped dependency inspection cannot see are a known limitation: inspect and manage them explicitly before rebuilding. Do not make concurrent schema/dependency changes while a rebuild is running.
Progress is stored in the database, not on the runner's filesystem. A completed partition is skipped; an incomplete copy is cancelled before its stage is reset. If the swap already happened, recovery finishes cleanup rather than swapping again.
An existing lock always refuses another runner—even when its heartbeat expires.
ClickHouse cannot fence a paused runner's later EXCHANGE using a client-side lease.
Do not delete a lock merely because it looks stale.
- Establish that the previous runner has stopped and cannot resume, including any suspended process or automated restart.
- Reconcile its running copy/DDL queries, the
_ch_migrate_journalentry, helper tables, and source/replacement UUIDs on every replica. Do not stamp the revision or delete its journal to suppress an unknown-outcome error. - Explicitly release
_chm_rebuild_lock_<table>only after that reconciliation. On a single server, for example:DROP TABLE analytics._chm_rebuild_lock_events SYNC. For a configured cluster, release on every host with the matchingON CLUSTER; for a Replicated database, wait for its replicated DROP to converge. - Run
ch-migrate up ENVagain. It acquires a new lock and resumes the recorded work, killing any recorded orphan copy before resetting the staging partition.
A stop that names rows reaching only <table>__chm_old_<revision> happens after the
swap: the live table is already the rebuilt one. The query IDs are those in
system.part_log. For an asynchronous insert this is the flush query; find the
writers' own query IDs through flush_query_id in system.asynchronous_insert_log.
- Copy the stranded rows into the live table, for example by selecting them from the rollback table by key and excluding keys the live table already has.
- Release
_chm_rebuild_lock_<table>as in step 3 above. - Run
ch-migrate up ENVagain. The run treats the release as your acknowledgement, finishes, and records"stranded"with"acknowledged": truein the completion report.
After the all-replica swap and copying-view barrier, the original table is retained
as <table>__chm_old_<revision>. Keep it for at least seven days, or your
organization's longer recovery window; there is no automatic deletion job.
Snapshot, staging, copying-view, and successful-run lock helpers are removed.
The retained table is not a continuously updated backup. Before rolling back, quiesce writers and reconcile rows accepted after the swap; blindly exchanging it back can remove those newer rows from the live table. After verifying the new table and completing the retention window, explicitly DROP the retained table, using the appropriate replicated/cluster scope.
The completion report includes the duplicate window, old/new UUIDs, rollback
table, old-UUID async rejection count, bytes, and query IDs, and the stranded-row
check ("stranded": rows, query IDs, whether an operator acknowledged them). On ClickHouse 26.3,
the old UUID is recorded in the error-741 exception text—not a table_uuid log
column. Reporting drains the old storage UUID through the retained rollback
table before flushing logs and applying flush-time bounds. Flushing only the
new live name would miss the old queue. Missing inspection privileges or log
evidence are errors, not invented zero-error results.
After applying and verifying migrations in an environment, capture its expected schema:
ch-migrate snapshot dev
ch-migrate diff dev --snapshot-dir migrations/sql/snapshots/20261002_120000 --jsonReplace the example timestamp with the directory reported by snapshot. Review
and commit that directory alongside the migration head. Do not regenerate the
snapshot automatically in the drift job: doing so would accept an out-of-band
change as the new expected schema. Re-snapshot after an intentional migration or
an explicitly reviewed reconciliation, not merely to turn a failed check green.
Copy the GitHub Actions example into the
consumer repository's .github/workflows/. It runs on a daily schedule and
same-repository pull requests; fork PRs are excluded because they have no database
secret. It does not use pull_request_target. All actions are pinned by commit.
The checkout must contain config.yaml and the committed snapshot, and the runner
must be able to reach the selected server.
Configure these repository variables:
| Variable | Value |
|---|---|
CH_MIGRATE_PACKAGE |
An approved, pinned ch-migrate-cli requirement that supports diff --json. There is no fallback to an older release. |
CH_MIGRATE_ENV |
An environment name in config.yaml, such as dev. |
CH_MIGRATE_SNAPSHOT_DIR |
That environment's committed snapshot directory. |
Set the repository secret DRIFT_MIGRATION_PASSWORD to the password for that
environment's configured user. Prefer a dedicated schema-inspection account,
not an admin account. The job writes a private .env.local without printing the
value and removes the file even when comparison fails.
diff --json exits 0 for matching schemas, 1 for drift, and 2 for a
configuration/connection error. The workflow saves drift.json, uploads it even
on comparison failure, then fails the job for either nonzero result. The report
names changed tables and fields. The example's install and shell steps are tested
locally against the source checkout; that does not publish the package or prove
that a previously released version contains JSON support.
Without --snapshot-dir, diff sorts entries in migrations/sql/snapshots/ by
name and chooses the last one. It does not select a snapshot by environment or
recorded migration revision. Use an explicit path in CI, especially when one
repository holds snapshots for several environments.
ch_migrate.classify.classify(statement, live_schema=None) accepts SQL or
an extracted migration statement. It returns Classification(kind, table, detail);
live_schema is the existing introspection Schema. Classification does not run
SQL or change lint's gate rules.
metadata: no background mutation, such as ADD COLUMN or a default-only change.mutation: a background mutation of parts, including DROP INDEX/PROJECTION and RENAME COLUMN, not just UPDATE/DELETE.rebuild: the requested key or engine change cannot be made by that in-place ALTER.other: non-schema operations or work not covered by the classifier.
Without a live column type, MODIFY COLUMN is conservatively a mutation if the
type changes. MODIFY TTL materializes existing parts unless the statement sets
materialize_ttl_after_modify = 0. Lightweight DELETE creates a mutation;
lightweight UPDATE writes synchronous patch parts and is explicitly other,
not a nonexistent background mutation. A sorting-key extension is metadata only
when it preserves the old key and appends columns added by the same ALTER.
The classification corpus is checked against fresh seeded MergeTree tables: every ALTER must create the predicted mutation, create none, or be rejected as an unsupported in-place change. The server proof, not the apparent SQL verb, defines the classification.
Run ch-migrate plan dev before up. It reads pending upgrade statements in
apply order, classifies them against the live schema, reports rewrite bytes and
dependent materialized views/dictionaries, and includes every lint finding.
It does not import revision modules, execute SQL from migrations, or run
SYSTEM SYNC. Python-generated SQL that cannot be extracted statically is not
included. The result describes current live state, not a simulation of each
preceding pending statement.
ch-migrate plan dev
ch-migrate plan dev --jsonFor example, these two pending statements produce different size labels:
ALTER TABLE analytics.events MODIFY COLUMN value UInt32;
-- ch-migrate: allow-non-idempotent reviewed one-time correction
ALTER TABLE analytics.events UPDATE value = value + 1 WHERE id = 1;- The column change reports exact compressed/uncompressed column bytes and
distinct active parts from
system.parts_columnswhen column sizes are available. - The UPDATE reports ceiling: up to the active table's compressed/uncompressed
bytes and part count. An explicit partition ID or a simple equality on
_partition_id, an unsigned identity partition key, or a supported date-bucket partition expression narrows the ceiling. Other predicates remain table-wide. - Compact parts share a data file and report zero per-column byte counters. These use a whole-part ceiling, not an exact zero-byte claim.
- The waiver appears with its reason. It removes the UPDATE's gate blocker, not its size or dependency warnings.
Rebuild-required statements also run shared read-only preflight. It reports Distributed/sharded layouts, changed partition keys, unfinished mutations, incompatible physical-transfer definitions, and unacknowledged async writers. Storage and part counts use the maximum across inspected replicas, not the sum of replica copies. Disk-space and partition-part warnings identify each host. The insert rate comes from completed initial INSERTs in the last 60 minutes.
Writer inspection includes rotated query_log_N tables and inherited
user/profile/role settings, with a bounded log scan. Missing privileges, absent
logs, or a scan exceeding its bound are errors, not evidence of safe writers.
Unknown effective settings refuse a rebuild. A quiet or unflushed log does not
prove that no writers exist. The shared RebuildRequest API accepts
allow_unacknowledged_async_loss=True only for an explicitly reviewed
migration-file opt-in; plan has no command-line bypass.
Only ReplacingMergeTree variants can collapse identical sorting-key copies on merge. Other targets may retain duplicates, including Collapsing and VersionedCollapsing tables with identical positive rows.
Rebuild-classified statements include an exact suggested
ch-migrate new ENV rebuild_TABLE --table TABLE --rebuild command. Human output
shows the command; JSON adds the optional suggested_command field without
changing schema version 1.
The plan JSON schema covers both success and
error documents. Human and JSON output use the same facts. Exit codes are 0
for a successful inspection, including warnings/preflight refusals; 1 when
the idempotency/standalone-SET gate would refuse up; and 2 for an inspection
error. Preflight refusals remain explicit in each statement's rebuild findings.
Read-only plans do not synchronize replicas or protect against concurrent changes.
Every command accepts --help. Top-level ch-migrate --version reports the installed package version. ENV below names an entry in config.yaml.
Output lines start with → for a step, ✓ for a result, ! for a warning and ✗ for an error; warnings and errors go to stderr. Colour is dropped when output is not a terminal or NO_COLOR is set, and lines are never wrapped, so paths and SQL can be copied or grepped.
status, history, plan, lint, and diff accept --json. JSON mode writes one document to stdout; diagnostics go to stderr. Every document includes "schema_version": 1 and "command". From 1.0 this is a public interface: breaking changes require a major version. Errors include an error string rather than inventing a successful empty result. The schemas linked below use JSON Schema draft 2020-12.
ch-migrate init [PATH] [-n NAME] initializes the current directory by default. -n/--name sets the project name; otherwise it uses the directory name.
Example: ch-migrate init analytics --name analytics
ch-migrate bootstrap ENV [--dry-run] [-v] creates the database, roles, and configured users. --dry-run prints SQL without executing it; -v/--verbose prints statements during execution. Requires admin and migration credentials.
Example: ch-migrate bootstrap dev --dry-run
ch-migrate new ENV NAME [--table T | --view V | --dict D] [--irreversible REASON | --python | --exchange | --rebuild] creates SQL-first migrations by default. Object-option aliases are -t, -v, and -d. --python keeps the Python template. --rebuild and deprecated --exchange require --table; each conflicts with the other authoring-mode options. Conflicts fail before a revision is written.
Example: ch-migrate new dev add_status --table logs
ch-migrate up ENV [-r REV] [--timeout SECONDS] [--skip-mv-check] [--verbose] applies migrations to head by default after the idempotency gate passes, printing one line per migration. -r/--revision selects a target. --timeout is a finite positive waiting limit; omitting it waits without a deadline. --skip-mv-check skips nonblocking materialized-view declaration checks, not the idempotency gate; use it only after reviewing those findings. If a migration fails, up names it, the SQL file, the statement and its line, and ClickHouse's error; --verbose adds the Python traceback.
Example: ch-migrate up dev --revision abc123
ch-migrate down ENV [-r REV] [--verbose] reverts one revision by default (-1). -r/--revision accepts another target. Known ranges containing irreversible revisions are refused. Failures are reported as for up.
Example: ch-migrate down dev --revision base
ch-migrate status ENV [--json] shows connection information, applied/pending counts, and head status, and names the up command when migrations are pending. Status is a report: human output exits 0 when the database is unreachable (with a warning) or migrations are pending, and exits 1 only when the configuration or migrations/versions/ is missing, so CI can run it as a non-blocking check. JSON mode uses nonzero exits for unreachable or pending state.
Example: ch-migrate status dev
With --json, the status schema includes
database, current_heads, script_heads, pending, applied, and at_head.
applied includes ancestors resolved through the revision graph, not just stored
heads. JSON exits 0 when the head sets match exactly, 1 when pending or diverged,
and 2 when configuration or database state cannot be read. Human-mode exits are
unchanged.
ch-migrate status dev --json | jq -e .current_heads
# Check the actual head set, not the last element of applied:
ch-migrate status dev --json | jq -e '.current_heads == ["PINNED_REVISION"]'Replace PINNED_REVISION with the required revision. In a shell pipeline, enable
set -o pipefail if the caller must also preserve ch-migrate's exit code.
ch-migrate history ENV [--json] displays the revision graph and applied state.
Example: ch-migrate history dev
ch-migrate history dev --json follows the history schema.
Each entry in revisions includes its revision, all down_revisions (including
merge parents), description, create date, path, applied status, and irreversible
reason. A reversible revision has irreversible: null. If database state is
unavailable, applied is null, the document includes error, and JSON exits 2.
An unknown database head also makes applied status unknown instead of claiming
that every local revision is unapplied.
ch-migrate plan ENV [--json] inspects pending upgrades without executing them.
See Plan pending migrations for byte precision,
rebuild checks, limitations, exit codes, and an annotated example.
ch-migrate lint [ENV] [--json] analyzes upgrade statements, not downgrade SQL. Without ENV, it checks revisions after the gate baseline statically without credentials or a connection. With an environment, it checks only pending revisions in that scope and adds live dependency checks. Use plan for rewrite sizes. If it cannot determine the pending set, it fails rather than silently checking a different scope. Errors exit nonzero; warnings and waiver INFO lines alone do not.
Example: ch-migrate lint
ch-migrate lint --json follows the lint schema.
findings include rule, severity, source file/line, message, blocking, and
waived. blocking means the finding currently blocks up; a waived finding
has its written reason and does not block. counts reports total, error, warning,
info, blocking, and waived findings. JSON and human output use the same exit
codes: 1 for lint/configuration errors, otherwise 0.
Findings name the project-relative SQL file and statement line. Inline Python SQL
points to its op.execute call. Extraction reads run_sql/read_sql file
references and literal or f-string op.execute arguments without importing
revisions. It preserves placeholders and adjacent comments; arbitrary Python
expressions are not evaluated. Materialized-view declaration and companion-grant
validation still uses the complete migration batch, with lint findings limited
to selected upgrade statements.
ch-migrate deps ENV [-v PATH] reads the live materialized-view and dictionary dependency graph. -v/--validate PATH checks a SQL file against it.
Example: ch-migrate deps dev --validate migrations/sql/history/tables/logs/change.up.sql
ch-migrate diff ENV [-s PATH] [--json] compares the live schema with the latest snapshot. -s/--snapshot-dir PATH chooses another snapshot. Exit code 0 means no drift; 1 means drift. JSON errors exit 2; human-mode execution errors retain exit 1.
Example: ch-migrate diff dev --snapshot-dir migrations/sql/snapshots/20261002_120000
ch-migrate diff dev --json follows the diff schema.
objects identifies each object's type, name, status, and structural details
(field, local value, remote value, and message). in_sync is true only when every
object matches. Use the exit code as the drift gate; an error document does not
contain a fabricated in_sync: true.
ch-migrate snapshot ENV [-e GLOB] [-f GLOB] writes CREATE statements to a timestamped snapshot directory. -e/--exclude and -f/--filter accept repeated glob patterns for excluded and included objects.
Example: ch-migrate snapshot dev --exclude 'temp_*' --filter 'logs*'
ch-migrate rebase ENV [--onto REV] [--dry-run] rewrites dangling local revision branches onto the deployed head. --onto selects an explicit target; --dry-run previews changes. Review the preview before rewriting migration history; do not rewrite deployed revisions.
Example: ch-migrate rebase dev --onto abc123 --dry-run
ch-migrate upgrade-env replaces migrations/env.py with the installed version, backs up the old file as env.py.bak, and records current script heads as lint.gate_baseline while preserving YAML comments. No command-specific options. Review the baseline diff. It does not merge: any local customizations (connection settings, session pins, hooks) are dropped from the new file. Reapply them from the backup, or skip upgrade-env and edit a customized env.py by hand. An unchanged shim is not backed up again.
Example: ch-migrate upgrade-env
ch-migrate skill [--user | --project] installs the bundled Claude skill. --user is the default (~/.claude/skills/ch-migrate/); --project writes ./.claude/skills/ch-migrate/.
Example: ch-migrate skill --project
defaults are merged with each environment. Environment fields override defaults. The project name controls role names. A Cloud/HTTPS example:
project:
name: analytics
defaults:
port: 8443
secure: true
admin_user: default
# cluster: my_cluster
# dict_reader_name: dict_reader
# mcp_user_name: mcp_reader
environments:
dev:
host: your-service.clickhouse.cloud
database: analytics_dev
migration_user: migration_dev
aws_region: us-east-1
ssm:
admin_password: /analytics/dev/admin_password
migration_password: /analytics/credentials#passwordChoose .env.local (or exported environment variables) or per-environment SSM paths. When an SSM path is configured, it is used for that secret. A #key suffix extracts a JSON key from the parameter. SSM access requires AWS credentials and permission to read the specified parameters; aws_region is optional.
| Variable | Purpose |
|---|---|
CH_<ENV>_MIGRATION_PASSWORD |
Required migration password |
CH_<ENV>_ADMIN_PASSWORD |
Admin password for bootstrap |
CH_<ENV>_DICT_READER_PASSWORD |
Password when a dictionary reader is configured |
CH_<ENV>_MCP_PASSWORD |
Password when a read-only MCP user is configured |
The legacy CH_<ENV>_PASSWORD remains supported. Never commit credentials or pass them in migration SQL that will be logged.
Top-level hooks run SQL on the migration connection. pre_migrate runs before the
migration batch; post_migrate runs after each revision body before its version
is completed, so hook mutations cannot escape the waiting barrier. A resumed
upgrade reuses its unfinished batch's receipts instead of repeating completed
hook writes. {db} is substituted. Hooks execute as SQLAlchemy text, not through
the SQL-file splitter; supply one statement per entry. Hook SQL is logged, so do
not put secrets in it.
hooks:
pre_migrate:
- "SELECT 1"
post_migrate:
- "SYSTEM RELOAD DICTIONARY {db}.dict_regions"Only configure the dictionary hook when that dictionary exists at every revision where the hook runs.
Non-gate rule severities can be error, warn, or off. Gate rules must remain error. mv_validation_cutoff can exclude older revisions from materialized-view declaration checks.
lint:
mv_validation_cutoff: "2026-01-01"
rules:
destructive_changes: warn
idempotency: error
reserved_words: warnReview findings rather than treating a successful command as a guarantee that a migration is safe. DDL and mutations are not transactional.
large_table_mutation and large_table_threshold are retired. Existing entries
are ignored with one deprecation warning pointing to ch-migrate plan.
Bootstrap creates {project}_migration_role for schema/data operations and introspection, including explicit system.grants, system.databases, system.tables, and system.mutations access. It grants available cluster, remote-read, and synchronization rights through CURRENT GRANTS. Optional users add {project}_readonly_role (SELECT/SHOW) and {project}_dict_role (dictionary sources). Bootstrap uses explicit grants rather than GRANT ALL for Cloud compatibility.
Plan also needs readable system.parts, system.parts_columns, and, for rebuild
preflight, disks, MergeTree settings, user/profile/role metadata, and query logs.
Bootstrap grants these where the admin's current grants permit. Existing
deployments may need to rerun bootstrap or grant missing access explicitly,
including readable rotated query logs. A denied inspection fails rather than
silently omitting evidence.
Queued async inserts whose logged settings are missing are matched to active
system.processes entries by host and query ID. If neither source proves the
writer's acknowledgement settings, preflight still refuses without the
unacknowledged-loss opt-in.
Use standard table engine names such as MergeTree and ReplacingMergeTree; ClickHouse Cloud supplies its shared variants. Cloud usually uses HTTPS port 8443; local HTTP usually uses 8123.
Run unit tests without starting Docker:
uv run --locked pytest -qRun the opt-in real-server suite:
uv run --locked pytest -q -m integrationThe single-server fixture starts clickhouse/clickhouse-server:26.3 in its own chm-it-* container on a random loopback port. Each test uses a separate database. Finalizers clean up on success, failure, and handled interrupts; a forced process kill cannot run finalizers. Docker-unavailable runs skip with a reason.
CH_MIGRATE_IT_IMAGE overrides the image tag. CH_MIGRATE_IT_URL selects a dedicated test server instead of starting Docker. It is an HTTP(S) URL with credentials supplied only through the environment. Tests create and drop databases there: never select a shared or production server, and never commit the URL.
For the single-server suite only:
uv run --locked pytest -q -m "integration and not cluster"For the replicated harness:
uv run --locked --python 3.12 pytest -q -m "integration and cluster" -k harnessclickhouse_cluster owns a Docker network and two nodes, with Keeper embedded
in node 1. it_cluster has one shard and two replicas. cluster_project creates
an Atomic database on both nodes with ON CLUSTER; replicated tables use
ReplicatedMergeTree with a shared Keeper path and per-node replica macros.
The fixture exposes clients[1] and clients[2], plus stop_node(2) and
start_node(2). Read clients[2] again after a restart; its connection is renewed.
Tests prove row replication and both hosts' distributed-DDL completion. The
node-down scenario uses a five-second distributed-DDL timeout, inspects the
unfinished host, then proves catch-up after restart. Its HTTP response is
buffered with wait_end_of_query=1 so the timeout is not obscured by a truncated
chunked response.
Cluster fixtures use the same image-tag override, remove their own containers,
anonymous volumes, and network, and never operate on external servers. Cluster
tests skip when CH_MIGRATE_IT_URL is set. The integration CI job runs cluster
tests on Python 3.12 only, and single-server tests on 3.10 and 3.14.
Point CH_MIGRATE_IT_URL at a dedicated Cloud test service
(https://<user>:<url-encoded password>@<service>:8443), loading the password
from your secret store at run time. Use a user that can create and drop it_*
databases, users and roles. The fixture waits up to 300 seconds for an idle
service to wake.
uv run --locked pytest -q -m "integration and not cluster" # the full single-server suite
uv run --locked pytest -q -m "integration and cloud" # Cloud-only checksTests marked cloud run only when the URL is a Cloud service (Shared
databases); they check that a migration's SET reaches every later statement
whichever replica serves it, version-table round trips on SharedMergeTree, and
mutation waiting. Cluster tests skip. Cloud grants no user SYSTEM STOP MERGES,
so tests that hold a mutation pending skip with that reason; the self-hosted
suite still covers them. Each test drops its own database. After an interrupted
run, scripts/cloud_sweep.py [--dry-run] drops it_* databases no query has
touched for 24 hours, with their test users and roles.
Run all five local variants five times, stopping on the first failure:
for run in 1 2 3 4 5; do
echo "Continuous-insert run $run"
env -u CH_MIGRATE_IT_URL uv run --locked pytest -q -s \
-m integration -k continuous_insert || exit 1
doneThe variants cover synchronous writes, process termination and operator-confirmed
resume, writes through node 1 checked on both replicas, and async writes with
wait_for_async_insert set to either 1 or 0. Each writer spans three
partitions and continues after the swap. Real HTTP barriers hold snapshot and
exchange requests; they do not replace ClickHouse operations.
Each CONTINUOUS_INSERT output line reports sent IDs, duplicate IDs, lost IDs,
and error-741 retries. Acknowledged writes must have no lost IDs. Duplicate
attribution uses server query intervals for synchronous inserts and actual
asynchronous_insert_log flush timestamps for async inserts. The fire-and-forget
variant first proves refusal without opt-in, then requires the missing IDs to
match the rebuild's old-UUID FlushError report exactly. The recovery variant
proves an expired lock still refuses takeover and that resume skips the already
moved partition after operator reconciliation.
These tests use only owned containers, not CH_MIGRATE_IT_URL. CI includes
them through the existing integration markers; the replicated variant runs in
the cluster job.
MIT License — see LICENSE.
Dan Young