clickhouse-alembic

Alembic-based migrations for ClickHouse, optimized for ClickHouse Cloud.

What is ch-migrate?

ch-migrate is a CLI tool for managing ClickHouse schema migrations. It:

• Initializes project structure with config files
• Bootstraps databases with proper roles and users
• Runs Alembic migrations against ClickHouse

Features

• Multi-environment support - Manage dev, staging, and production with separate configs
• ClickHouse Cloud compatible - Uses standard engines (auto-upgraded to Shared* on Cloud), handles non-transactional DDL
• Role-based access control - Creates roles for migration, read-only (MCP), and dictionary access
• Object-centric SQL history - Track all versions of each table/view/dictionary
• Zero-downtime migrations - EXCHANGE TABLES pattern for safe schema changes
• Bootstrap command - One-command database and user setup (idempotent)
• SSM support - Store credentials in AWS SSM Parameter Store
• YAML + env vars - Config in version control, secrets in environment

Quick Start

Installation

The package on PyPI is clickhouse-alembic; the command it installs is ch-migrate.

For CLI usage (recommended for most users):

# Install globally - ch-migrate available everywhere
uv tool install clickhouse-alembic

# Verify installation
ch-migrate --version

As a project dependency (for importing in Python code):

uv add clickhouse-alembic
uv run ch-migrate --version

# Or with pip
pip install clickhouse-alembic

Pin to a specific version:

uv tool install clickhouse-alembic==0.4.1

Unreleased changes from main:

uv tool install git+https://github.com/DRYCodeWorks/clickhouse-migrate.git

Initialize a Project

# Create a new project
mkdir my-clickhouse-project
cd my-clickhouse-project

# Initialize with ch-migrate
ch-migrate init

# Or initialize in current directory with custom name
ch-migrate init --name my_project

This creates:

my-clickhouse-project/
├── config.yaml              # ClickHouse hosts and settings
├── .env.local.example       # Template for secrets
├── .gitignore
├── alembic.ini
└── migrations/
    ├── env.py
    ├── script.py.mako
    ├── versions/            # Migration files go here
    └── sql/
        ├── bootstrap/
        └── history/
            ├── tables/
            ├── views/
            └── dictionaries/

Configure

1. Edit config.yaml with your ClickHouse hosts:

project:
  name: my_project

defaults:
  port: 8443
  secure: true
  admin_user: default
  # Optional users (uncomment to enable):
  # dict_reader_name: dict_reader
  # mcp_user_name: mcp_reader

environments:
  dev:
    host: my-dev-instance.clickhouse.cloud
    database: my_project_dev
    migration_user: migration_dev

  production:
    host: my-prod-instance.clickhouse.cloud
    database: my_project
    migration_user: migration_prod

2. Set up credentials (choose one):

Option A: Environment file

cp .env.local.example .env.local
# Edit with your passwords:
CH_DEV_MIGRATION_PASSWORD=your-dev-password
CH_DEV_ADMIN_PASSWORD=your-dev-admin-password

Option B: AWS SSM (for production)

# In config.yaml, add ssm paths:
environments:
  production:
    host: my-prod-instance.clickhouse.cloud
    database: my_project
    migration_user: migration_prod
    ssm:
      admin_password: /my_project/prod/admin_password
      migration_password: /my_project/prod/migration_password

Bootstrap Database

# Creates database, roles, and users
ch-migrate bootstrap dev

# Preview without executing
ch-migrate bootstrap dev --dry-run

Create and Run Migrations

# Create a new migration
ch-migrate new dev create_users_table

# Run pending migrations
ch-migrate up dev

# Check status
ch-migrate status dev

# Rollback last migration
ch-migrate down dev

# Show history
ch-migrate history dev

Migration Patterns

Basic Table Creation

1. Create SQL file in migrations/sql/history/tables/users/001_<revision>.sql:

CREATE TABLE {db}.users (
    id UInt64,
    email String,
    name String,
    created_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY id

2. Reference in migration:

from alembic import op
from clickhouse_alembic import get_db, read_sql

def upgrade():
    db = get_db()
    op.execute(read_sql("history/tables/users/001_abc123.sql", db=db))

def downgrade():
    db = get_db()
    op.execute(f"DROP TABLE IF EXISTS {db}.users")

Zero-Downtime Schema Changes

Use EXCHANGE TABLES for data-preserving migrations:

def upgrade():
    db = get_db()

    # 1. Create new table with updated schema
    op.execute(read_sql("history/tables/users/002_def456.sql", db=db)
               .replace(f"{db}.users", f"{db}.users_new"))

    # 2. Migrate data with transformations
    op.execute(f"""
        INSERT INTO {db}.users_new
        SELECT id, email, name, created_at, '' as phone
        FROM {db}.users
    """)

    # 3. Atomic swap
    op.execute(f"EXCHANGE TABLES {db}.users AND {db}.users_new")

    # 4. Drop old table
    op.execute(f"DROP TABLE {db}.users_new")

Dictionary with Auto-Grant

from clickhouse_alembic import create_dictionary

def upgrade():
    # Automatically grants SELECT to dict_reader on source table
    create_dictionary("history/dictionaries/dict_users/001_abc123.sql")

CLI Reference

# Initialize new project
ch-migrate init [PATH] [--name NAME]

# Bootstrap database (create DB, roles, users)
ch-migrate bootstrap <environment> [--dry-run]

# Apply pending migrations
ch-migrate up <environment>

# Rollback last migration
ch-migrate down <environment> [--revision REV]

# Show current status
ch-migrate status <environment>

# Show migration history
ch-migrate history <environment>

# Create new migration
ch-migrate new <environment> <name>

Configuration Reference

config.yaml

project:
  name: my_project          # Project identifier

defaults:                   # Inherited by all environments
  port: 8443
  secure: true
  admin_user: default
  # dict_reader_name: dict_reader    # Uncomment to enable
  # mcp_user_name: mcp_reader        # Uncomment to enable

environments:
  dev:
    host: dev.clickhouse.cloud
    database: my_project_dev
    migration_user: migration_dev
    # SSM paths (optional, if set fetches from SSM directly):
    # ssm:
    #   admin_password: /my_project/dev/admin_password
    #   migration_password: /my_project/dev/migration_password

Environment Variables

Legacy CH_<ENV>_PASSWORD is supported for backward compatibility.

Roles and Users

Bootstrap creates the following roles:

ClickHouse Cloud Notes

• Uses standard engines (MergeTree, ReplacingMergeTree) - auto-upgraded to Shared* variants on Cloud
• Native port 9440 maps to HTTP port 8443
• No transactional DDL - migrations can't be atomically rolled back
• Each op.execute() runs one statement (no multi-statement batches)

License

MIT License - see LICENSE file for details.

Author

Dan Young