All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Hand off a long task.

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

The same job on other stacks

Keep reading