ClickHouse Migrations in Plain SQL: What Changed in ch-migrate 0.5

ClickHouse migrations in plain SQL: ch-migrate 0.5 drops the Python glue each schema change used to need. Write an upgrade file and a downgrade file.

ch-migrate is an open-source ClickHouse migration tool. We started it while building IoT analytics infrastructure on ClickHouse Cloud. At the time, our schema "system" was a folder of SQL files, a spreadsheet of what had been applied where, and Slack threads asking whether anyone had run that ALTER on staging. We introduced the tool in ClickHouse migrations with Alembic.

Since we introduced it, we've hit each of these problems on production systems. A migration tool built for Postgres doesn't expect any of them:

• An ALTER that returns isn't an ALTER that finished. Type changes, UPDATE, and DELETE start a mutation that rewrites data in the background, and it can still fail after the migration is recorded as applied.
• Some changes can't be made in place. A new sorting key or engine means building a new table and swapping it in, and rows written during the swap are easy to lose.
• Materialized views are insert triggers. Changing a table underneath one can break inserts into the source table, not just the view.
• There are no transactions. A migration that fails halfway leaves the schema between two revisions, and the version table doesn't say where.
• Some statements are silently ignored. Over HTTP without a session, a standalone SET applies to nothing.

ClickHouse now maintains an official Alembic integration in clickhouse-connect, which turns Alembic operations into ClickHouse DDL. ch-migrate doesn't compete with it. We want it to be the layer on top: the part that knows what that DDL will do to a live cluster, and runs it safely.

Building it in public

We're building ch-migrate in the open, one release at a time, with a post and a short demo for each. It's MIT-licensed, and the goal is a tool that's useful to any team running ClickHouse in more than one environment, on ClickHouse Cloud or self-hosted, not only the teams we work with.

One thing we haven't settled is who the CLI is for. Today it's built for a person at a terminal. A coding agent running the same migration wants output it can parse, a read-only way to preview what will happen, and errors it can act on without guessing. ch-migrate already ships a skill command that installs instructions for Claude, but past that we're torn, and we haven't picked a direction. If you're pointing an agent at your ClickHouse schema, we'd like to hear what it needs.

Each release is tested against a real ClickHouse server before the next one starts. Bug reports and ideas are welcome on GitHub.

ClickHouse migrations in plain SQL

Since its first release, ch-migrate has kept schema changes in SQL files. ch-migrate new dev add_status --table logs creates a .sql file under migrations/sql/history/tables/logs/, and the DDL goes there. Up to 0.4, though, nothing connected that file to the migration. You still wrote a Python revision by hand for every change.

Version 0.5 drops that step. You write the SQL, and ch-migrate generates the revision that runs it.

Here's the whole 0.5 flow, run against a local ClickHouse server and slowed to half speed so you can read along: create a migration, see where its files go, apply it, check its status, then watch down refuse to undo an irreversible change.

What a migration took in 0.4

In 0.4, new created the SQL file plus a stub revision with the code commented out. To make it run, you finished the revision yourself:

def upgrade() -> None:
    db = get_db()
    op.execute(read_sql("history/tables/logs/2026_10_08_1700_9e232daad963.sql", db=db))


def downgrade() -> None:
    db = get_db()
    op.execute(f"DROP TABLE IF EXISTS {db}.logs")

def upgrade() -> None:
    db = get_db()
    op.execute(read_sql("history/tables/logs/2026_10_08_1700_9e232daad963.sql", db=db))


def downgrade() -> None:
    db = get_db()
    op.execute(f"DROP TABLE IF EXISTS {db}.logs")

That came with three catches:

• Every migration was a Python edit. You copied the generated file name into the revision by hand, and the downgrade lived in Python, not SQL.
• One statement per file. op.execute sends one request, and ClickHouse runs one statement per request. Adding a column and an index meant two files and two calls.
• Braces had to be escaped. read_sql() runs the file through Python's str.format. JSON or a ClickHouse parameter such as {id:UInt64} in your SQL broke unless you doubled every brace.

It meant writing and reviewing Python for changes that were entirely SQL, and the glue code was where mistakes crept in.

Writing a ClickHouse migration in 0.5

new now writes an upgrade file, a downgrade file, and a revision that's already wired to both. You edit the two SQL files and leave the revision alone.

To try it, install the tool and create a project. Point config.yaml at a test server and put the admin and migration passwords in .env.local; the README quick start shows both files. Then bootstrap and create a migration:

uv tool install ch-migrate-cli==0.5.1

mkdir my-clickhouse-project && cd my-clickhouse-project
ch-migrate init --name my_project
# edit config.yaml and create .env.local, then:
ch-migrate bootstrap dev --dry-run
ch-migrate bootstrap dev
ch-migrate new dev add_status --table logs

uv tool install ch-migrate-cli==0.5.1

mkdir my-clickhouse-project && cd my-clickhouse-project
ch-migrate init --name my_project
# edit config.yaml and create .env.local, then:
ch-migrate bootstrap dev --dry-run
ch-migrate bootstrap dev
ch-migrate new dev add_status --table logs

The last command creates two files under migrations/sql/history/tables/logs/, named with a timestamp and the revision ID. Fill in the upgrade. Two statements in one file is fine now:

-- Create the table if this environment doesn't have it yet.
CREATE TABLE IF NOT EXISTS {db}.logs
(
    id UInt64
)
ENGINE = MergeTree
ORDER BY id;

-- Then add the new column.
ALTER TABLE {db}.logs
    ADD COLUMN IF NOT EXISTS status String;

-- Create the table if this environment doesn't have it yet.
CREATE TABLE IF NOT EXISTS {db}.logs
(
    id UInt64
)
ENGINE = MergeTree
ORDER BY id;

-- Then add the new column.
ALTER TABLE {db}.logs
    ADD COLUMN IF NOT EXISTS status String;

And the downgrade:

-- Undo both statements above.
DROP TABLE IF EXISTS {db}.logs;

-- Undo both statements above.
DROP TABLE IF EXISTS {db}.logs;

Then apply, inspect, and revert:

ch-migrate up dev
ch-migrate status dev
ch-migrate down dev

ch-migrate up dev
ch-migrate status dev
ch-migrate down dev

{db} becomes the environment's database, so the same file runs against dev, staging, and production. --view and --dict group views and dictionaries the same way. When a change needs real Python logic, new --python keeps the old template, and existing Python migrations keep working.

How multi-statement files run

The generated revision calls a new helper, run_sql(), which splits the file and sends each statement as its own request, in order.

• Splitting understands SQL. Semicolons inside strings, quoted identifiers, comments, and heredocs don't split a statement.
• Your literals stay literal. JSON, % signs, colons, and ClickHouse query parameters reach the server unchanged, both online and in offline --sql output.
• It stops at the first failure. The revision isn't recorded as applied, and an empty or comment-only file fails instead of passing silently.

Clearer output

0.5 also reworks what ch-migrate prints. up and down show one line per migration instead of Alembic's log lines, and every command marks its lines the same way: → for a step, ✓ for a result, ! for a warning and ✗ for an error. Colour goes away when the output is piped, and long lines aren't wrapped, so paths and SQL can be copied or searched.

The biggest difference shows when a migration fails. Here's the same mistake, an ALTER on a table that doesn't exist yet, run by 0.4.1 and by 0.5. 0.4.1 printed 86 lines, and ClickHouse's error is near the bottom:

INFO  [alembic.runtime.migration] Context impl ClickhouseImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 1ba2b926438b, add_status
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  ⋮  (77 more lines of Python traceback)
clickhouse_sqlalchemy.exceptions.DatabaseException: Orig exception: Code: 60. DB::Exception: Could not find table: logs. (UNKNOWN_TABLE) (version 26.3.39.7 (official build))
Alembic command failed with exit code 1

INFO  [alembic.runtime.migration] Context impl ClickhouseImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 1ba2b926438b, add_status
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  ⋮  (77 more lines of Python traceback)
clickhouse_sqlalchemy.exceptions.DatabaseException: Orig exception: Code: 60. DB::Exception: Could not find table: logs. (UNKNOWN_TABLE) (version 26.3.39.7 (official build))
Alembic command failed with exit code 1

0.5 names the migration, the SQL file, the statement and its line, and the error, then says what to do next:

→ Applying faefdbcb  add_status
✗ faefdbcb  add_status failed
  migrations/sql/history/tables/logs/2026_10_09_1519_faefdbcbb823_add_status.up.sql (statement 1 of 1, line 2): Code: 60. DB::Exception: Could not find table: logs. (UNKNOWN_TABLE)
It was not recorded as applied. Anything it ran before the failure stays in place, so make the SQL safe to re-run, then run ch-migrate up dev again.
Add --verbose to see the full traceback.

→ Applying faefdbcb  add_status
✗ faefdbcb  add_status failed
  migrations/sql/history/tables/logs/2026_10_09_1519_faefdbcbb823_add_status.up.sql (statement 1 of 1, line 2): Code: 60. DB::Exception: Could not find table: logs. (UNKNOWN_TABLE)
It was not recorded as applied. Anything it ran before the failure stays in place, so make the SQL safe to re-run, then run ch-migrate up dev again.
Add --verbose to see the full traceback.

Other commands follow the same pattern. lint prints one finding per line with the file and line first, and diff prints one line per object that has drifted. When down refuses a range, it lists every migration in it and marks which ones are irreversible.

Some migrations shouldn't have a down

A migration that drops a column can't put the data back, so a down for it would only pretend to. 0.5 lets you say so up front:

ch-migrate new dev drop_legacy --table logs --irreversible "Drops legacy data"

ch-migrate new dev drop_legacy --table logs --irreversible "Drops legacy data"

This writes only an upgrade file and marks the revision with your reason. down checks the whole range before running anything. If any revision in it is irreversible, nothing runs, not even the reversible migrations after it. Direct alembic downgrade calls hit the same wall, because the generated downgrade raises IrreversibleMigration.

There's no override flag. To go back past an irreversible migration, write its downgrade and remove the marker in a reviewed change.

The --exchange scaffold, which copies a table into a new schema and swaps it in, is now marked irreversible too, because it drops the old table.

We ran it on ClickHouse Cloud

For 0.5 we also ran the integration suite against a ClickHouse Cloud service, not just the local container that CI uses. Our test service has two replicas and scales to zero when idle. That run turned up two bugs our local runs hadn't caught.

• bootstrap couldn't grant user management. ClickHouse Cloud manages its own SQL console user, and even the default user can't pass on ALTER USER for it. The broad GRANT ... ON *.* WITH GRANT OPTION failed, so bootstrap stopped. It now uses GRANT CURRENT GRANTS(CREATE USER, ALTER USER, DROP USER ON *.*), which passes on only the requested privileges that the admin is allowed to grant.
• new --exchange crashed on Replicated and Shared engines. The scaffold copies the live SHOW CREATE TABLE into a SQL file, and on ClickHouse Cloud that DDL contains engine macros such as {uuid} and {replica}. The file is read with read_sql(), the same str.format problem described above, so it failed with KeyError: 'uuid'. The braces are now escaped, so the macros reach the server unchanged. Self-hosted ReplicatedMergeTree tables had the same bug.

Two more findings changed our tests rather than the tool. They're worth knowing if you write tests or tools for ClickHouse Cloud:

• Memory tables live on one replica. A row inserted through one replica wasn't there when the next query was routed to the other. Tests that check data now use MergeTree.
• An idle service needs time to wake. A run that started cold took 177 seconds, against about 50 seconds warm. The first test errored because it connected before the service answered. The test setup now retries a cheap query for up to five minutes first.

Upgrading from 0.4

Up to 0.4.1 the package was clickhouse-alembic. We renamed it to ch-migrate-cli in 0.5, because Alembic is how ch-migrate stores revisions, not what it is. (PyPI treats ch-migrate itself as the same name as an unrelated project.) The command is still ch-migrate.

Your existing migrations don't change. Revisions written for 0.4 run the same way on 0.5, and up picks up where the version table says you are.

1. Swap the package. Uninstall clickhouse-alembic before you install ch-migrate-cli, because both install the ch-migrate command.

uv tool uninstall clickhouse-alembic
uv tool install ch-migrate-cli
ch-migrate --version

uv tool uninstall clickhouse-alembic
uv tool install ch-migrate-cli
ch-migrate --version

With pip, use pip uninstall clickhouse-alembic and then pip install ch-migrate-cli. If CI installs clickhouse-migrate from git at a pinned commit, replace that with ch-migrate-cli==0.5.1, and change any cache key that includes the old commit. Otherwise a cached runner keeps 0.4.

2. Leave your imports alone, or update them when convenient. Code that imports clickhouse_alembic keeps working until 1.0, with a deprecation warning that names the file and line. To clear it, replace clickhouse_alembic with ch_migrate in your revisions and anything else that imports it.

3. Update migrations/env.py the right way for your project. If you never edited it, run ch-migrate upgrade-env. If you did, for example to add connection settings, change its imports by hand instead, because upgrade-env replaces the whole file. It keeps a backup as env.py.bak.

4. Check alembic.ini for a black hook. Projects created before 0.5 run black on every new revision, and black isn't a dependency. If ch-migrate new fails with Could not find entrypoint console_scripts.black, install black or delete the [post_write_hooks] section.

5. Update anything that reads the output. The clearer output above is a change for scripts too. Exit codes are the same with one exception: lint ENV now fails when it can't reach the database, where 0.4 quietly fell back to the offline checks. status still exits 0 when the database can't be reached, so it can keep running as a non-blocking CI check. The CHANGELOG lists every change.

Coming in 0.6

0.6 moves ch-migrate onto ClickHouse's official dialect in clickhouse-connect. That one change closes the two traps 0.5 still has.

• SET will reach the statements after it. Each migration runs in one ClickHouse session, so a SET at the top of a file applies to everything below it. Today a standalone SET applies to nothing, because each statement is its own request with no session.
• A migration can't run twice. 0.5 records the applied version with a background update, so up can return before that update is visible, and a second up in that window runs the migration again. 0.6 inserts the new version first and waits for the old one to be deleted.
• Checks before anything runs. lint flags a standalone SET, and up refuses a statement that isn't safe to run twice, such as a CREATE without IF NOT EXISTS, unless the file explains why it's fine.

Until then, check that ch-migrate status shows the new revision before you run up again, and never run two ups at once.

0.6 will need Python 3.10 or later. The 0.5 line is the last to support Python 3.9.

Try it

Try 0.5 on a test server with uv tool install ch-migrate-cli, then follow the README quick start. If you'd like help getting a migration workflow in place, get in touch.