# Add Alembic Migrations to an aiohttp App

Yes. An agent adds Alembic's async template to your aiohttp app, generates the first revision from your models, makes the tests apply it and adds a drift check.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-migrations/aiohttp/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads how the schema is created today before adding Alembic. In an app like the `polls` demo in aio-libs/aiohttp-demos, `aiohttpdemo_polls/db.py` declares a SQLAlchemy 2.0 `DeclarativeBase` with two models, `Question` (`id`, `question_text` as `String(200)`, `pub_date`) and `Choice` (`id`, `choice_text`, `votes` with `server_default` 0, and `question_id` as a foreign key with `ondelete` CASCADE), and a `pg_context` cleanup context that builds an async engine with `asyncpg` from the `postgres` section of the YAML config and stores an `async_sessionmaker` on the app. `init_db.py` connects as the `postgres` superuser to drop and recreate the database and role from `config/polls.yaml`, then calls `Base.metadata.create_all` and inserts one question with three choices. `tests/conftest.py` reuses those functions: a module-scoped `db` fixture creates the test database, and a `tables_and_data` fixture calls `create_tables` and `sample_data` before each test and `drop_tables` after. `requirements-dev.txt` pins `aiohttp` 3.13.5, `asyncpg` 0.32.0 and `SQLAlchemy` 2.0.54 with no `alembic`, and the repository's own `docs/preparations.rst` lists a migration tool as one of the ways to prepare the database without providing one.

The agent runs `alembic init -t async migrations` inside the demo, which writes `alembic.ini`, `migrations/env.py` built around `async_engine_from_config` and `connection.run_sync`, and `script.py.mako`. It edits `env.py` to import `Base` from `aiohttpdemo_polls.db` as `target_metadata`, and to take the database URL from the same `get_config` and `-c` path the app uses instead of a second copy in `alembic.ini`, so the app and the migrations can never point at different databases. It installs PostgreSQL in its sandbox, creates the `aiohttpdemo_polls` database and user from the YAML, and runs `alembic revision --autogenerate -m "question and choice"`. It reads the revision before keeping it: `op.create_table` for `question`, then `choice` with the foreign key and its `ondelete`, the `server_default` on `votes`, and a `downgrade` that drops them in reverse order.

Then it makes the migration the only way tables are created. `create_tables` in `init_db.py` stops calling `create_all` and runs the upgrade instead; because the async template calls `asyncio.run` and the tests already run inside an event loop, the agent follows Alembic's documented connection-sharing pattern: `env.py` checks `config.attributes` for an existing connection, and the `tables_and_data` fixture passes its connection in through `run_sync` and calls `command.upgrade(cfg, "head")` on it, so every test applies the real revision rather than a parallel schema from the models. `drop_tables` becomes `command.downgrade(cfg, "base")` the same way, which proves the `downgrade` path on every test run. It adds `alembic` to `requirements-dev.txt` and corrects `setup.py`, whose `install_requires` still names `aiopg[sa]` although the code moved to `asyncpg` and SQLAlchemy's async engine.

Finally it guards against drift: a test that runs `alembic check`, which fails when the models and the migrations disagree, so a changed column without a revision fails `pytest` before it reaches the shared database. It runs the demo's `pytest tests` against the sandbox PostgreSQL, the `flake8` target from the demo's `Makefile`, and `python init_db.py` followed by `python -m aiohttpdemo_polls` to show the app boots on a database built only from the migration.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add Alembic migrations to this aiohttp app and open a pull request. Read requirements-dev.txt, setup.py, pytest.ini, Makefile, config/polls.yaml, config/polls_test.yaml, init_db.py, aiohttpdemo_polls/db.py, aiohttpdemo_polls/main.py, aiohttpdemo_polls/settings.py, tests/conftest.py and tests/test_integration.py first. Initialise Alembic with the async template, point env.py at the declarative Base's metadata, and make it read the database URL from the app's own YAML config and -c option rather than alembic.ini. Install PostgreSQL in your sandbox, create the database and user the config names, autogenerate the first revision and read it. Replace create_all in init_db.py with the upgrade, and make the test fixtures apply the migration and downgrade it through Alembic's connection-sharing pattern so they work inside the running event loop. Add alembic to the dev requirements, fix the stale install_requires in setup.py, and add a test that runs alembic check. Run pytest, flake8, init_db.py and the app once, and open a pull request with the generated revision and the test output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

The pull request adds `alembic.ini`, `migrations/env.py`, `migrations/script.py.mako` and `migrations/versions/<revision>_question_and_choice.py`, and changes `init_db.py`, `tests/conftest.py`, `requirements-dev.txt` and `setup.py`. Its description pastes the `upgrade` and `downgrade` bodies, the `alembic check` output saying no new operations are detected, and the full `pytest` run with the four existing tests and the new one passing against a database built from the revision.

From now on a model change is a revision file a reviewer reads, the tests apply and roll back that file on every run, and a missing revision fails CI through `alembic check` before anyone touches a real database. `init_db.py` keeps its role as the one-command local setup, now through the same migration path production will use.

Before merging, a reviewer decides how the upgrade runs in deployment, as a release step or at application start before `web.run_app`, checks that the production role may create the `alembic_version` table, and confirms the revision's `ondelete` and `server_default` match what the running database already has if it was created by `create_all` earlier.

## Run it on every service still creating tables from models

A team with several aiohttp services sends this body once per repository to `POST /o/{org}/sessions`, each with its own repository in `resources` and a `request_id` that is a UUID v5 of the repository's full name, so a retried call returns the run already started. Each run reads its own models and config loader and generates its own first revision, and the key's `webhook_url` receives one `run.completed` per repository with the pull request in `result`.

Once Alembic is in place, the recurring job is the next revision. An automation with a `linear` trigger on the label `schema` starts a run for each issue that asks for a column or a table, with this prompt adapted to add a revision rather than the first; the trigger appends the issue's title and description after the prompt, and an automation run has its own `max_runs_per_hour` cap.

## Why run it on Rush?

Adding Alembic to an async app has two traps the agent handles on purpose: the async template cannot be invoked from inside a running event loop, which is where pytest-aiohttp fixtures live, and a second copy of the database URL in `alembic.ini` drifts from the app's config. The agent uses Alembic's connection-sharing pattern for the first and the app's own config loader for the second, and proves both by running the real suite against a real PostgreSQL in its own sandbox.

The generated revision, the `alembic check` result, the test run and the app start all stay with the run and are pasted into the pull request, so review is about deployment choices rather than whether the migration matches the models. Several services can be migrated at once, each with its own database.

## Frequently asked questions

### Why does env.py read the app's YAML instead of alembic.ini?

So there is one source of truth. The app builds its DSN from the `postgres` section of the file passed with `-c`, and `env.py` calls the same `get_config` and overrides `sqlalchemy.url` with the result, which also means the test config selects the test database without any extra setting.

### How do the tests apply a migration from inside the event loop?

The async template's `run_migrations_online` calls `asyncio.run`, which fails in a running loop. Alembic documents passing an existing connection through `config.attributes`; the fixture opens an async connection, calls `run_sync` with a function that sets that attribute and invokes `command.upgrade`, and `env.py` uses the connection when present.

### What does alembic check add?

It compares the models' metadata with the migration history and exits non-zero when a new revision would contain operations. Run as a test, it fails the suite when someone edits a model without generating a revision.

### What about a database that create_all already built?

The first revision creates the same tables, so running it there would fail. The pull request shows the `alembic stamp head` command that records the revision as applied without executing it, for databases that already match.

## More aiohttp jobs

- [Add a Dockerfile to an aiohttp App](https://getrush.ai/run/add-dockerfile/aiohttp/)
- [Add Health Checks to an aiohttp App](https://getrush.ai/run/add-health-checks/aiohttp/)
- [Add Input Validation to an aiohttp App](https://getrush.ai/run/add-input-validation/aiohttp/)

## The same job on other stacks

- [Add Database Migrations to a CodeIgniter 4 App](https://getrush.ai/run/add-database-migrations/codeigniter/)
- [Add Database Migrations to a Drizzle App](https://getrush.ai/run/add-database-migrations/drizzle/)
- [Add Versioned Database Migrations to an Echo App](https://getrush.ai/run/add-database-migrations/echo/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
