# Recipe — Alembic env.py with `mgf.common.alembic`

> **Audience:** consumers using alembic for schema migrations, especially
> on async SQLAlchemy 2 (asyncpg, aiomysql, aiosqlite, psycopg async).
> Sync drivers also work — the helper auto-detects.
> **Optional extra:** `pip install 'mgf-common[alembic]'` (pulls
> `alembic>=1.13`).

## At a glance

Replace the ~40-line `alembic/env.py` boilerplate every async-SQLAlchemy
project copies around with one call:

```python
# alembic/env.py
from mgf.common.alembic import configure_env

from myapp.config import MyAppSettings
from myapp.db.base import Base

settings = MyAppSettings()
configure_env(
    database_url=settings.database_url,
    target_metadata=Base.metadata,
)
```

That's the entire file. The helper:
- Detects async vs sync from the URL prefix.
- Wires offline mode (`alembic upgrade --sql ...`).
- Wires online mode with a single connection, disposed cleanly.
- Applies `compare_type=True` + `compare_server_default=True` for
  autogenerate (the production-leaning defaults).
- Optionally applies a `naming_convention` to your metadata.

`alembic.ini` is unchanged. Migrations directory is unchanged.
`alembic upgrade head` / `alembic revision --autogenerate` work
exactly as before.

## What it auto-detects

Async URLs (handled via `asyncio.run` + `connection.run_sync`):

- `postgresql+asyncpg://`
- `postgresql+psycopg://` (psycopg 3 in async mode)
- `mysql+aiomysql://`
- `mysql+asyncmy://`
- `sqlite+aiosqlite://`

Anything else takes the synchronous path:

- `postgresql://`, `postgresql+psycopg2://`
- `mysql+pymysql://`
- `sqlite:///`

## Naming conventions

Pass `naming_convention=` to keep auto-generated constraint names
deterministic across DBs (without this, alembic's autogenerate produces
anonymous names that vary by dialect and create noisy migration diffs):

```python
configure_env(
    database_url=settings.database_url,
    target_metadata=Base.metadata,
    naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_%(constraint_name)s",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s",
    },
)
```

The convention is applied to `target_metadata.naming_convention`
before any migration runs. Existing tables keep their original
constraint names; new constraints generated by autogenerate use the
new pattern.

## Autogenerate flags

Two flags are passed through to `alembic.context.configure`:

- `compare_type=True` (default) — autogenerate notices column-type
  changes (e.g. `Integer` → `BigInteger`).
- `compare_server_default=True` (default) — autogenerate notices
  `server_default=` changes.

Pass `compare_type=False` or `compare_server_default=False` to opt
out. Most consumers want them on.

```python
configure_env(
    database_url=settings.database_url,
    target_metadata=Base.metadata,
    compare_type=False,            # off: tolerate type changes
    compare_server_default=False,  # off: tolerate server-default changes
)
```

## Combining with `mgf.common.db`

If your service already uses `mgf.common.db.setup_db`, your alembic
env.py can read the same `database_url` from your settings module:

```python
# alembic/env.py
from mgf.common.alembic import configure_env

from myapp.config import settings
from myapp.db.base import Base

configure_env(
    database_url=settings.database_url,
    target_metadata=Base.metadata,
)
```

The two helpers don't share state — `configure_env` opens its own
single connection for the migration and disposes the engine on exit.
That's intentional: alembic runs out-of-process from your app, so
sharing the runtime engine pool would be wrong.

## Offline mode

`alembic upgrade --sql ...` (offline mode) is detected automatically
via `context.is_offline_mode()` and runs without opening a connection.
The same `database_url` is used to pick the dialect for SQL emission.

## Common patterns

### Reading the URL from settings

```python
# alembic/env.py
from mgf.common.alembic import configure_env

from myapp.config import AppSettings
from myapp.db.base import Base

configure_env(
    database_url=AppSettings().database_url,
    target_metadata=Base.metadata,
)
```

The settings object is constructed at env.py module load time —
which is fine for alembic, since alembic invokes env.py per
command. Don't import the running app — alembic runs in its own
process.

### Reading the URL from `alembic.ini`

If your `alembic.ini` already has `sqlalchemy.url = ...`, read it via
the alembic config object:

```python
# alembic/env.py
from alembic import context

from mgf.common.alembic import configure_env
from myapp.db.base import Base

config = context.config
url = config.get_main_option("sqlalchemy.url")
configure_env(
    database_url=url,
    target_metadata=Base.metadata,
)
```

### Multiple databases (`-x db=foo`)

Pass `-x` flags through alembic's CLI and pick a URL by name:

```python
# alembic/env.py
from alembic import context

from mgf.common.alembic import configure_env
from myapp.config import AppSettings
from myapp.db.base import Base

x_args = context.get_x_argument(as_dictionary=True)
db = x_args.get("db", "primary")
url = AppSettings().database_urls[db]
configure_env(
    database_url=url,
    target_metadata=Base.metadata,
)
```

Then run: `alembic -x db=replica upgrade head`.

## Anti-patterns

- **Don't share the running app's engine with alembic.** Alembic runs
  out-of-process; `configure_env` opens its own connection and
  disposes on exit. Sharing pools across processes leaks file
  descriptors and subtly breaks transaction isolation.
- **Don't call `configure_env` from inside a function.** It must run
  at module-level scope — alembic invokes env.py exactly once and
  expects side effects (`context.run_migrations()`) at top level.
- **Don't combine `configure_env` with manual `context.configure`
  calls.** The helper already calls `context.configure` once; calling
  it again silently breaks autogenerate.
- **Don't hardcode the URL in env.py.** Read it from settings or
  `alembic.ini` so dev / staging / prod differ without editing code.

## See also

- `docs/public/recipes/db.md` — `mgf.common.db` for the runtime
  engine + sessionmaker.
- Alembic docs:
  https://alembic.sqlalchemy.org/en/latest/
- SQLAlchemy 2.x async docs:
  https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html
