# PyClickHouseMigrator

> SQL-first ClickHouse migration runner for Python teams. Plain `.sql` migrations, explicit `-- @stmt` blocks, rollback SQL, checksum validation, dry-run, baseline, advisory locking, and cluster-aware service tables. No ORM, no schema diff engine, no deployment platform.

## Essentials

- Package: `py-clickhouse-migrator`
- CLI: `migrator`
- Python: 3.11+
- Dependencies: `click`, `clickhouse-driver`
- License: MIT
- Repository: https://github.com/Maksim-Burtsev/PyClickHouseMigrator
- Default migrations directory: `./db/migrations`
- Default Docker migrations directory: `/migrations`

## Migration format

Migrations are `.sql` files:

```sql
-- migrator:up
-- @stmt
CREATE TABLE IF NOT EXISTS users
(
    id UInt64,
    name String
)
ENGINE = MergeTree
ORDER BY id

-- migrator:down
-- @stmt
DROP TABLE IF EXISTS users
```

Rules:

- exactly one `-- migrator:up` and one `-- migrator:down` section;
- `-- migrator:up` must come before `-- migrator:down`;
- SQL must be inside `-- @stmt` blocks;
- `up` must contain at least one non-empty block;
- `down` may be empty;
- each `-- @stmt` block is executed as one ClickHouse query;
- the migrator does not split SQL by semicolon.

## Commands

- `migrator init` — create migrations directory; offline.
- `migrator new [name]` — create timestamped `.sql` migration; offline.
- `migrator up [N]` — apply pending migrations.
- `migrator up --dry-run` — print pending SQL without executing.
- `migrator rollback [N]` — rollback applied migrations in reverse order.
- `migrator show [--all]` — display applied/pending migrations and integrity markers.
- `migrator baseline` — adopt an existing DB by marking current `.sql` files as already applied without executing SQL.
- `migrator repair` — accept intentional edit(s) to applied migration file(s) by updating stored checksums.
- `migrator lock-info` — show active lock.
- `migrator force-unlock` — manually release stuck lock.

## Configuration

Global options and env vars:

- `--url` / `CLICKHOUSE_MIGRATE_URL` — ClickHouse URL, required for DB commands.
- `--path` / `CLICKHOUSE_MIGRATE_DIR` — migrations directory, default `./db/migrations`.
- `--cluster` / `CLICKHOUSE_MIGRATE_CLUSTER` — cluster name for migrator service tables.
- `--connect-retries` / `CLICKHOUSE_MIGRATE_CONNECT_RETRIES` — startup connection retries.
- `--connect-retries-interval` / `CLICKHOUSE_MIGRATE_CONNECT_RETRIES_INTERVAL` — seconds between retries.
- `--send-receive-timeout` / `CLICKHOUSE_MIGRATE_SEND_RECEIVE_TIMEOUT` — ClickHouse client timeout.
- `-v`, `--verbose` — debug output.
- `-q`, `--quiet` — suppress INFO/WARNING logs; command output such as dry-run SQL is still printed.

## Boundaries

PyClickHouseMigrator executes migration SQL as written. It does not generate schema diffs, create rollback SQL, create the target database, sandbox SQL, inject `ON CLUSTER`, or orchestrate deployments.

## Docs

- README: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/README.md
- Migration format: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/migration-format.md
- Baseline: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/baseline.md
- Cluster mode: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/cluster-mode.md
- CI/CD: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/ci-cd.md
- Docker: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/docker.md
- Python API: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/python-api.md
- Troubleshooting: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/troubleshooting.md
- Known limitations: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/docs/known-limitations.md
- Changelog: https://raw.githubusercontent.com/Maksim-Burtsev/PyClickHouseMigrator/master/CHANGELOG.txt
