ClickHouse Migrations That Catch Schema Drift: ch-migrate 0.8
2026-11-11 - 9 min read
A hand-run ALTER in prod, then a migration with IF NOT EXISTS that succeeds and does nothing. ch-migrate 0.8 catches that ClickHouse schema drift in CI.
ClickHouse migrations assume each database looks the way the last migration left it. Schema drift, any difference between an environment's live schema and the one its migrations describe, breaks that assumption without raising an error.
During an incident, someone adds a column to production by hand:
ALTER TABLE shop_prod.orders ADD COLUMN region String;A week later, the planned change lands as a migration. It's written to be safe to run twice, as ch-migrate's idempotency gate asks:
ALTER TABLE {db}.orders
ADD COLUMN IF NOT EXISTS region LowCardinality(String);I ran that sequence on a local ClickHouse 26.3 server, with dev and prod side by side. ch-migrate up applied the migration to both and reported each one at head. On prod, IF NOT EXISTS found a column named region and stopped there, because it doesn't compare types. In system.columns, dev's region is LowCardinality(String) and prod's is String. The two environments now disagree, and nothing says so: ch-migrate status prod reports prod at head, and it's right to.
The version table records which migrations ran. It says nothing about what the schema looks like.
ch-migrate is my open-source ClickHouse migration tool. Its diff command has compared a live database with a schema snapshot since 0.3, but only as text for a person to read. 0.8 adds --json to diff and three other commands, and ships a GitHub Actions workflow that runs the comparison once a day and on pull requests. In the story above, the next day's run fails, a week before the migration hides the hotfix. (New to the tool? Start with ClickHouse migrations with Alembic, then what changed in 0.7.)
What's new in 0.8
- A drift gate for GitHub Actions: it compares an environment with its committed snapshot every day and on pull requests, keeps a JSON report, and fails the job on drift.
--jsononstatus,history,lintanddiff: one versioned document on stdout, with published schemas.- Exit codes a script can branch on: with
--json,statusexits 1 when migrations are pending and 2 when it can't read the database. - Coming from 0.7? Nothing changes unless you pass
--json. See upgrading from 0.7.
Building it in public
ch-migrate is MIT-licensed and built in the open, one release at a time, each with a post and a short demo. I want it to be useful to any team running ClickHouse in more than one environment, on ClickHouse Cloud or self-hosted, not only the teams I work with.
I still haven't settled whether the CLI is for a person at a terminal or for a coding agent. 0.7 gave each its own progress output. 0.8 is the first release built for a reader that isn't a person: a CI job, a deploy script or an agent gets one JSON document with a version number instead of text to scrape. If you automate around ClickHouse migrations, I'd like to hear what you need in those documents. Bug reports and ideas are welcome on GitHub.
A schema drift check for ClickHouse migrations
The check compares the schema you expect, committed to the repository as a snapshot, with the schema the environment has right now.
ch-migrate snapshot prod
mv migrations/sql/snapshots/20261110_175408 migrations/sql/snapshots/prod
ch-migrate diff prod --snapshot-dir migrations/sql/snapshots/prod --jsonsnapshot writes one CREATE statement per table, view, materialized view and dictionary into a new timestamped folder. I move it to a fixed name per environment, so the workflow's setting never changes and the next snapshot shows up in review as a diff against this one. You review that folder and commit it with the migrations that produced it. diff reads the live schema and compares the two field by field: each column's type, default and codec, and each table's engine, sorting key, partition key, TTL and settings.
Here's the report for the hotfix above, taken against the snapshot committed before it:
{
"schema_version": 1,
"command": "diff",
"objects": [
{
"type": "table",
"name": "_ch_migrate_in_progress",
"status": "in_sync",
"details": []
},
{
"type": "table",
"name": "orders",
"status": "modified",
"details": [
{
"field": "column 'region'",
"local": null,
"remote": "String",
"message": "column 'region' String is in the database but not in the snapshot"
}
]
}
],
"in_sync": false
}The first object is _ch_migrate_in_progress, the small table where up notes each migration while it runs. With --json, diff exits 0 when every object matches, 1 on drift, and 2 when it can't read the configuration or the database, so a connection failure never looks like a clean result.
The job only compares. It never takes a snapshot, because a snapshot taken by the job would record the hotfix as the expected schema.
A drift job that takes its own snapshot will always pass.
When the job fails
There are two ways to clear it, and both go through review.
- Make prod match the migrations. Write the migration so it lands the same way on every environment. For
region, follow theADD COLUMN IF NOT EXISTSwithMODIFY COLUMN region LowCardinality(String). Where the column already has that type, ClickHouse changes nothing and starts no mutation. On prod, it rewrites the column as a mutation, which ClickHouse finishes before it answers. Both statements pass the idempotency gate as written. Then refresh prod's snapshot and commit it with the migration, as for any schema change, and the check passes again. - Keep the change. Run
ch-migrate snapshot prodnow, replacesnapshots/prodwith the new folder and commit it, so a reviewer seesregion Stringappear in the snapshot diff. Then write the next migration knowing it's there.
Take a new snapshot after an intentional migration or a reviewed decision to keep a change, never just to turn a failed check green.
The GitHub Actions workflow
The repository ships the workflow as docs/examples/github-actions-drift.yml. Copy it into .github/workflows/, then set three repository variables and one secret:
| Name | What it holds |
|---|---|
CH_MIGRATE_PACKAGE | A pinned requirement, such as ch-migrate-cli==0.8.0, the first release with diff --json |
CH_MIGRATE_ENV | The config.yaml environment to check, such as prod |
CH_MIGRATE_SNAPSHOT_DIR | That environment's committed snapshot folder |
DRIFT_MIGRATION_PASSWORD (secret) | The password for that environment's migration_user in config.yaml |
It runs once a day at 06:17 UTC and on pull requests from the same repository. Pull requests from forks are skipped, because they can't read the secret, and the workflow never uses pull_request_target to get around that. Every action is pinned to a commit.
The comparison step can fail without stopping the job, so the report gets uploaded before the last step decides the result:
- name: Compare committed schema with live schema
id: compare
continue-on-error: true
run: |
"$UV_TOOL_BIN_DIR/ch-migrate" diff "$CH_MIGRATE_ENV" \
--snapshot-dir "$CH_MIGRATE_SNAPSHOT_DIR" --json > drift.json
- name: Preserve drift report even when comparison fails
if: always()
uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4.6.2
with:
name: schema-drift
path: drift.json
if-no-files-found: warn
- name: Fail on drift or comparison errors
id: gate
if: always()
env:
DRIFT_OUTCOME: ${{ steps.compare.outcome }}
run: |
# compare exits 1 on drift and 2 on errors; neither is a clean result.
test "$DRIFT_OUTCOME" = successThe password goes into a private .env.local that the job writes without printing and removes at the end, even when the comparison fails.
The example is tested like the rest of the tool. ch-migrate's test suite reads the workflow file and runs its shell steps in order against a real ClickHouse server: once with a matching schema and once after an out-of-band ALTER. It checks each step's outcome, validates the report against the published schema, and runs actionlint over the file.
Here's the hotfix story at half speed. The drift check against prod's committed snapshot passes, a column is added by hand, and the next check fails with the report. Then, as if nobody had looked, the IF NOT EXISTS migration applies to both environments, leaves prod's column a plain String, and status still says prod is at head.
JSON output for scripts and agents
status, history, lint and diff take --json. Each prints one document to stdout with schema_version and command, and sends warnings to stderr, so a pipe into jq never sees them. An error comes back as a document with an error field, never as an empty result that looks like success.
| Command | What the document holds | Exit code with --json |
|---|---|---|
status | Current and script heads, pending and applied revisions, at_head | 0 at head, 1 pending or diverged, 2 unreadable |
history | Every revision with its parents, applied state and irreversible reason | 0, or 2 when the database can't be read |
lint | Each finding's rule, severity, file and line, blocking and waived, plus counts | 1 for errors, otherwise 0 |
diff | Each object's status and field-level details, plus in_sync | 0 in sync, 1 drift, 2 error |
status is the one that changes meaning. Without --json, it still exits 0 when migrations are pending or the database can't be reached. Some teams run it in CI as a report that shouldn't fail a build, and I'm keeping that promise. A deploy step that must stop when prod is behind asks for JSON:
ch-migrate status prod --json > status.json # 1 if prod is behind, 2 if unreachablelint --json says which findings would stop up (blocking) and carries the written reason for each waived statement (waived), so a review bot can quote the reason instead of re-parsing SQL comments. For an agent, the difference is reading "at_head": true instead of finding Head status: At head inside a bordered terminal panel.
The schemas live in docs/schemas/, in JSON Schema draft 2020-12. This is version 1 of the format. It can still change before 1.0, and every document says which version it is.
Tested on ClickHouse Cloud
As with 0.5, 0.6 and 0.7, I ran the integration suite against my ClickHouse Cloud test service as well as local containers.
- The workflow's steps pass on Cloud. The drift test ran the example's install, credential, comparison and exit steps against the Cloud service, for a matching schema and for an out-of-band column.
- The JSON documents follow live state. On the two-replica service,
status,historyanddiffdocuments tracked a migration and a hand-made change, andlintreported a waived statement and a blocking one, each validated against its schema.
705 unit tests pass on each of Python 3.10 through 3.14. On ClickHouse Cloud, the integration suite passed 55 tests and skipped 12 that hold a mutation still with SYSTEM STOP MERGES, which Cloud doesn't grant, and the 4 Cloud-only checks passed. Against local ClickHouse 26.3 containers, it passed 63 on a single server (4 Cloud-only tests skipped) and 16 on a two-replica cluster with Keeper.
Upgrading from 0.7
Without --json, nothing changes: output, exit codes, the environment file and the version table are the same as in 0.7, and there's no upgrade-env.
1. Install 0.8.
uv tool install ch-migrate-cli==0.8.0
ch-migrate --versionWith pip, use pip install ch-migrate-cli==0.8.0. A loose requirement such as ch-migrate-cli upgrades to 0.8 on its own; pin ch-migrate-cli==0.7.0 to stay on 0.7.
2. Snapshot what you expect. For each environment you want to check, run ch-migrate snapshot ENV once it's at head and matches your migrations, move the new folder to a fixed name such as snapshots/ENV, review it, and commit it.
3. Add the workflow. Copy the example, set the three variables and the secret, and pass the snapshot folder explicitly. Without --snapshot-dir, diff picks the last folder by name, whichever environment it came from.
4. Move scripts to --json. If anything parses the human output of status or diff, switch it to the JSON documents, which are the format meant for scripts.
The CHANGELOG lists every change.
Try it
Try 0.8 on a test server with uv tool install ch-migrate-cli==0.8.0, then follow the README's drift detection section. And whatever runs your migrations, compare one environment's live schema with what your migrations say it should be. If you'd like help getting a migration workflow in place, get in touch.