Metadata-Version: 2.5
Name: clickhouse-alembic
Version: 0.4.1
Summary: Alembic-based migrations for ClickHouse, optimized for ClickHouse Cloud
Project-URL: Homepage, https://github.com/DRYCodeWorks/clickhouse-migrate
Project-URL: Repository, https://github.com/DRYCodeWorks/clickhouse-migrate
Project-URL: Issues, https://github.com/DRYCodeWorks/clickhouse-migrate/issues
Project-URL: Guide, https://www.drycodeworks.com/articles/dev-guides/clickhouse-migrations-with-alembic
Author: Dan Young
License: MIT
License-File: LICENSE
Keywords: alembic,clickhouse,clickhouse-cloud,database,migrations,schema,schema-migrations
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Requires-Python: >=3.9
Requires-Dist: alembic>=1.13.0
Requires-Dist: boto3>=1.42.24
Requires-Dist: click>=8.1.0
Requires-Dist: clickhouse-connect>=0.7.0
Requires-Dist: clickhouse-sqlalchemy>=0.3.0
Requires-Dist: python-dotenv>=1.0.0
Requires-Dist: pyyaml>=6.0
Requires-Dist: rich>=13.0.0
Provides-Extra: dev
Requires-Dist: black>=23.0.0; extra == 'dev'
Requires-Dist: isort>=5.12.0; extra == 'dev'
Requires-Dist: mypy>=1.5.0; extra == 'dev'
Requires-Dist: pytest-cov>=4.1.0; extra == 'dev'
Requires-Dist: pytest>=7.4.0; extra == 'dev'
Requires-Dist: types-pyyaml; extra == 'dev'
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9.0; extra == 'postgres'
Description-Content-Type: text/markdown

# 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):
```bash
# 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):
```bash
uv add clickhouse-alembic
uv run ch-migrate --version

# Or with pip
pip install clickhouse-alembic
```

**Pin to a specific version**:
```bash
uv tool install clickhouse-alembic==0.4.1
```

**Unreleased changes from `main`**:
```bash
uv tool install git+https://github.com/DRYCodeWorks/clickhouse-migrate.git
```

### Initialize a Project

```bash
# 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:

```yaml
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**
```bash
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)
```yaml
# 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

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

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

### Create and Run Migrations

```bash
# 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`:

```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:

```python
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:

```python
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

```python
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

```bash
# 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

```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

| Variable | Required | Description |
|----------|----------|-------------|
| `CH_<ENV>_MIGRATION_PASSWORD` | Yes | Migration user password |
| `CH_<ENV>_ADMIN_PASSWORD` | For bootstrap | Admin password for creating users |
| `CH_<ENV>_DICT_READER_PASSWORD` | If dict_reader enabled | dict_reader user password |
| `CH_<ENV>_MCP_PASSWORD` | If mcp_user enabled | MCP user password |

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

## Roles and Users

Bootstrap creates the following roles:

| Role | Purpose |
|------|---------|
| `{project}_migration_role` | Full migration access, including explicit `system.grants` introspection (required) |
| `{project}_readonly_role` | Read-only for MCP tools (optional) |
| `{project}_dict_role` | Dictionary source access (optional) |

## 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](LICENSE) file for details.

## Author

Dan Young
