Python CLI for ClickHouse
Plain SQL.Auditable migrations.
Version schema changes in Git, validate them before execution, and run them from the CLI or CI.
pip install py-clickhouse-migratorEvery query stays explicit.
Each -- @stmt block is sent to ClickHouse as one query. The same file carries the up and down SQL.
-- migrator:up
-- @stmt
CREATE TABLE IF NOT EXISTS events
(
id UInt64,
created_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY id
-- migrator:down
-- @stmt
DROP TABLE IF EXISTS events
A focused migration workflow
No ORM, schema diff, or framework. You write the SQL. The migrator handles ordering, checks, state, and advisory locking.
Plain SQL
Keep migrations in versioned files. Every statement boundary is explicit and reviewable in Git.
20260801120000_create_events.sql
Integrity checks
Detect edited or missing migrations that the tool previously applied.
Rollback you control
Write the down SQL yourself. The stored version is used for rollback.
Preflight validation
EXPLAIN AST runs before up and rollback by default.
Deployment locking
Reduce common CI races with a TTL-based advisory lock.
Cluster-aware state
Replicate service tables while leaving migration SQL unchanged.
Inside migrator up
By default, an apply run follows this path from connection to recorded state. Select a step to inspect it.
Connect and prepare the ledger
Check the target with SELECT 1 and ensure db_migrations exists. The database in the URL must already exist.
migrator showAcquire the advisory lock
Create or reuse _migrations_lock, then acquire a TTL-based advisory lock. By default, up retries three times.
migrator upCompare stored checksums
Compare stored SHA-256 values with the current up and down blocks. A mismatch or missing file stops the run by default.
migrator upPreflight pending SQL
Run EXPLAIN AST for every pending statement before execution. Dry-run follows the same best-effort validation path.
migrator up --dry-runExecute statement blocks
Run migrations in filename order and each -- @stmt block in file order. Each block is one ClickHouse query.
migrator upStore successful state
After each success, record its name, up SQL, down SQL, and SHA-256 checksum in db_migrations.
migrator showGuardrails before execution.
The default path checks applied history, preflights pending SQL, and coordinates migration runners.
Checksum integrity
Compares current blocks with the checksum stored at apply time.
migrator showA mismatch or missing checksummed file stops migrator up before pending SQL by default.
Preflight validation
Runs EXPLAIN AST before up or rollback execution.
migrator up --dry-runDry-run uses the same validation path. This is a best-effort check, not a safety guarantee.
Advisory locking
Uses a TTL-based lock to reduce concurrent migration races.
migrator lock-infoUp, rollback, and baseline use it by default. Keep one migration runner per deployment.
Choose your path
Pick the guide that matches your database and delivery process.