# Migrations

alchemiq ships a unified migration CLI that covers both PostgreSQL
(Alembic-backed) and ClickHouse (a custom, async runner) from a single
``alchemiq`` entry-point.  You never edit ``alembic.ini`` or write raw
``env.py`` files - all configuration lives in ``pyproject.toml``.

---

## Installation

Migrations are an optional feature.  For PostgreSQL install the
``[migrations]`` extra (Alembic) together with the ``[postgres]`` extra
(the ``asyncpg`` driver the CLI connects with):

```bash
pip install "alchemiq[postgres,migrations]"
```

ClickHouse migrations are part of the ``[clickhouse]`` extra:

```bash
pip install "alchemiq[clickhouse]"
```

---

## Configuration

Declare the ``[tool.alchemiq]`` section in your ``pyproject.toml``.  The CLI
searches upward from the working directory to find it:

```toml
[tool.alchemiq]
models = ["myapp.domain.models"]   # import paths that register your models

[tool.alchemiq.postgres]
host     = "${DB_HOST}"            # ${VAR} placeholders expand from the environment
port     = 5432                    # optional, defaults to 5432
database = "myapp"
username = "${DB_USER}"
password = "${DB_PASSWORD}"

[tool.alchemiq.clickhouse]
host     = "ch.internal"
port     = 8123                    # optional, defaults to 8123
database = "analytics"
username = "default"
password = "${CH_PASSWORD}"
secure   = false                   # optional, defaults to false
```

Either ``[tool.alchemiq.postgres]`` or ``[tool.alchemiq.clickhouse]`` (or both)
can be present.  Sections that are absent are simply skipped.

---

## PostgreSQL migrations

PostgreSQL migrations are powered by **Alembic**.  alchemiq wraps Alembic's
autogenerate pipeline so that your custom field types (``Email``, ``Money``,
``Encrypted``, etc.) map correctly to SQL column types without any extra
configuration.

Migrations are generated as standard Alembic revision files and stored under
the ``migrations/`` directory (configurable via ``migrations_dir`` in
``[tool.alchemiq]``).

---

## ClickHouse migrations

ClickHouse migrations use a custom async runner because Alembic does not
support ClickHouse.  Each migration is a Python class that subclasses
``Migration`` and declares ``revision``, ``down_revision``, ``up``, and
``down``:

```python
from alchemiq.migrations import Migration
from alchemiq.migrations.clickhouse.operations import Operations

class AddPageViews(Migration):
    revision = "0002"
    down_revision = "0001"

    def up(self, op: Operations) -> None:
        op.create_table("page_views", ...)

    def down(self, op: Operations) -> None:
        op.drop_table("page_views")
```

Migration history is tracked in a dedicated ClickHouse table so no external
state store is needed.

**Autogenerate support:** the runner can detect new tables and new columns and
generate the corresponding ``up`` / ``down`` methods automatically.  Destructive
operations (dropping columns, changing a table engine) are *not* autogenerated -
write those migrations by hand so no data loss is accidental.

---

## Unified CLI

The five sub-commands below work identically for both backends.  Omit
``--db`` to run the command against every configured database; pass
``--db postgres`` or ``--db clickhouse`` to target one:

| Command | Description |
|---|---|
| ``alchemiq makemigrations`` | Autogenerate a new migration from model changes |
| ``alchemiq migrate`` | Apply all pending migrations |
| ``alchemiq rollback`` | Roll back the last applied migration |
| ``alchemiq history`` | List applied and pending migrations |
| ``alchemiq showsql`` | Print the SQL for pending migrations without running them |

These five commands - ``makemigrations``, ``migrate``, ``rollback``,
``history``, and ``showsql`` - are the complete migration surface in v1.

### Common usage examples

```bash
# Autogenerate for both databases:
alchemiq makemigrations

# Autogenerate with a descriptive message (Postgres only):
alchemiq makemigrations -m "add user table" --db postgres

# Apply pending migrations to Postgres only:
alchemiq migrate --db postgres

# Roll back the last ClickHouse migration:
alchemiq rollback --db clickhouse

# Inspect pending SQL without running it:
alchemiq showsql

# Show migration history for both databases:
alchemiq history
```

### Exit codes

| Code | Meaning |
|---|---|
| ``0`` | Success |
| ``1`` | Runtime error (connection failure, migration error) |
| ``2`` | Configuration error (missing ``pyproject.toml``, unset environment variable) |

---

## Multiple databases

When both ``[tool.alchemiq.postgres]`` and ``[tool.alchemiq.clickhouse]`` are
configured, running a command without ``--db`` executes it against both in
sequence - Postgres first, then ClickHouse.  Each backend is independent: a
failure in one does not roll back the other.