{"id":8896,"library":"clickhouse-migrations","title":"ClickHouse Migrations","description":"clickhouse-migrations is a Python library designed for managing file-based database migrations in ClickHouse. It supports multi-statement SQL files, cluster deployments, and ensures migration state consistency across nodes. The project is actively maintained, with the current version being 0.9.1, and receives regular updates.","status":"active","version":"0.9.1","language":"en","source_language":"en","source_url":"https://github.com/zifter/clickhouse-migrations","tags":["clickhouse","migrations","database","ddl"],"install":[{"cmd":"pip install clickhouse-migrations","lang":"bash","label":"Install stable version"}],"dependencies":[],"imports":[{"symbol":"ClickhouseCluster","correct":"from clickhouse_migrations.clickhouse_cluster import ClickhouseCluster"}],"quickstart":{"code":"import os\nfrom clickhouse_migrations.clickhouse_cluster import ClickhouseCluster\n\ndb_host = os.environ.get('CH_DB_HOST', 'localhost')\ndb_user = os.environ.get('CH_DB_USER', 'default')\ndb_password = os.environ.get('CH_DB_PASSWORD', '')\ndb_name = os.environ.get('CH_DB_NAME', 'test_db')\nmigrations_home = os.environ.get('CH_MIGRATIONS_HOME', './migrations')\n\n# Ensure migrations directory exists for the example\nos.makedirs(migrations_home, exist_ok=True)\n\n# Create a dummy migration file for the example\nwith open(f\"{migrations_home}/1_create_test_table.sql\", \"w\") as f:\n    f.write(\"CREATE TABLE IF NOT EXISTS my_table (id UInt64, name String) ENGINE = MergeTree() ORDER BY id;\")\n\ncluster = ClickhouseCluster(\n    db_host=db_host,\n    db_user=db_user,\n    db_password=db_password\n)\n\ntry:\n    print(f\"Applying migrations to {db_host}/{db_name} from {migrations_home}...\")\n    cluster.migrate(\n        db_name=db_name,\n        migrations_home=migrations_home,\n        create_db_if_no_exists=True,\n        multi_statement=True\n    )\n    print(\"Migrations applied successfully.\")\nexcept Exception as e:\n    print(f\"An error occurred during migration: {e}\")\n","lang":"python","description":"This example demonstrates how to programmatically connect to a ClickHouse instance and apply migrations from a specified directory. It creates a dummy migration file for illustration. Ensure `CH_DB_HOST`, `CH_DB_USER`, `CH_DB_PASSWORD`, `CH_DB_NAME`, and `CH_MIGRATIONS_HOME` environment variables are set or default values are appropriate for your ClickHouse instance."},"warnings":[{"fix":"Upgrade Python to version 3.9 or newer (e.g., Python 3.9, 3.10, 3.11, 3.12, 3.13).","message":"Python 3.7 support was dropped in version 0.5.0. Ensure your environment uses Python 3.9 or higher.","severity":"breaking","affected_versions":"0.5.0 and greater"},{"fix":"Design migrations to be idempotent where possible (e.g., using `IF NOT EXISTS` for `CREATE TABLE` or `ADD COLUMN`). Manually inspect the database state after a failed migration and either manually apply missing parts or revert changes if necessary.","message":"ClickHouse does not support transactions for DDL operations. If a migration fails mid-way, the schema may be left in a partially migrated and inconsistent state, which will not be automatically rolled back.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Avoid semicolons within string literals or comments in multi-statement migration files. If programmatic usage, ensure `multi_statement=True` is passed.","message":"When using multi-statement migration files, the library splits SQL statements by semicolons. This can lead to issues if semicolons are present within string literals or comments in your SQL queries.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always include `IF NOT EXISTS` in `ALTER TABLE ... ADD COLUMN` statements when running migrations on a ClickHouse cluster (e.g., `ALTER TABLE my_table ON CLUSTER default ADD COLUMN IF NOT EXISTS new_col String;`).","message":"For ClickHouse cluster deployments, `ALTER TABLE ... ADD COLUMN` statements might fail on subsequent nodes if they don't include `IF NOT EXISTS`, leading to 'DUPLICATE_COLUMN' errors.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure that special characters in ClickHouse passwords are URL-encoded when used in connection strings, CLI arguments, or environment variables. Consider using simpler passwords for development or secret management systems for production.","message":"ClickHouse passwords containing special characters (like `@`, `&`, `#`, `!`, `%`, `?`, `$`, `:`) may cause authentication failures when passed via CLI or environment variables if not properly URL-encoded.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"First, investigate the failed migration (version X) to determine its actual state. If it was partially applied, manually complete or revert changes. Then, update the `_migrations` table to mark version X as clean (e.g., `INSERT INTO _migrations (version, dirty) VALUES (X, 0);`) or delete its entry if not applied. Backup your data before manual modifications.","cause":"A previous migration failed and left the internal `_migrations` table in a 'dirty' state, preventing further migrations.","error":"Dirty database version X. Fix and force version. Applying clickhouse migrations failed. This is mostly caused by the database being unavailable. Exiting.."},{"fix":"Ensure that if you are using multi-statement SQL files, you pass `multi_statement=True` when calling `cluster.migrate()`. If using the CLI, the tool attempts to split statements by semicolon, so avoid semicolons within string literals or comments in your SQL.","cause":"ClickHouse client (or the library's internal client) received multiple SQL statements in a single query without the multi-statement mode enabled, or semicolons are misinterpreted.","error":"Error: code: 62, message: Syntax error (Multi-statements are not allowed): failed at position X (end of query) (line Y, col Z): ; select 2;"},{"fix":"If the password contains special characters, ensure they are URL-encoded when provided via environment variables (`CH_DB_PASSWORD`) or directly in connection strings. For example, `P@ssword!` should be `P%40ssword%21`.","cause":"The provided ClickHouse password contains special characters that are not correctly parsed or URL-encoded by the underlying connection mechanism.","error":"error: failed to open database: code: 516, message: <db_name>: Authentication failed: password is incorrect, or there is no user with such name."},{"fix":"Modify your migration SQL to include `IF NOT EXISTS` when adding columns, especially in clustered environments: `ALTER TABLE my_table ON CLUSTER default ADD COLUMN IF NOT EXISTS new_column String;`.","cause":"An `ALTER TABLE ... ADD COLUMN` statement was executed on a ClickHouse cluster where the column already existed on some nodes, leading to a duplicate column error.","error":"DB::Exception: There is already a column with name 'new_column' in table 'my_table'"}]}