# Write Migration Rollback Tests for Alembic

Yes. An agent writes a stairway test that upgrades, downgrades and re-upgrades every Alembic revision on a throwaway database, round-trips rows and opens a PR.

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

## What the agent does

The agent reads the migration history before touching it. In a FastAPI project laid out like fastapi/full-stack-fastapi-template, `backend/app/alembic/versions/` holds five revisions in a single chain: `e2412789c190` creates `user` and `item` with integer ids and the `ix_user_email` index, `9c0a54914c78` sets a length of 255 on four string columns, `d98dd8ec85a3` replaces every integer id with a UUID by adding `new_id` columns, filling them with `uuid_generate_v4()` and swapping the primary and foreign keys, `1a31ce608336` makes `item.owner_id` non-nullable with `ondelete='CASCADE'`, and `fe56fa70289e` adds `created_at` to both tables. Every file has a `downgrade`, and the UUID one is the longest: it recreates integer columns, creates sequences with `CREATE SEQUENCE IF NOT EXISTS`, and rebuilds `item.owner_id` from the old user ids. Nothing runs any of those downgrades today. `scripts/prestart.sh` runs `alembic upgrade head`, the `Test Backend` workflow runs that against the compose `db` and then `scripts/tests-start.sh`, and `pyproject.toml` excludes `alembic` from ruff and mypy, so a downgrade that fails is found the day someone needs it.

The tests need their own database, because `backend/tests/conftest.py` keeps a session-scoped `Session` open on the app database and deletes `Item` and `User` rows at the end, and a downgrade to base drops those tables under it. The agent connects with `psycopg` to the server named in `DATABASE_URL`, creates `app_migrations` if it does not exist, and builds an Alembic `Config` from `backend/alembic.ini` with `sqlalchemy.url` set to that database. `env.py` ignores that option today, since `get_url()` returns `settings.DATABASE_URL`, so the agent changes it to prefer `config.get_main_option("sqlalchemy.url")` when one is set and fall back to the settings otherwise, which leaves `prestart.sh` exactly as it was.

The stairway test is one parametrised function. `ScriptDirectory.from_config(cfg).walk_revisions()` lists the revisions newest first; reversed, each one becomes a case that runs `command.upgrade(cfg, rev.revision)`, `command.downgrade(cfg, rev.down_revision or "base")` and `command.upgrade(cfg, rev.revision)` again, so a downgrade that leaves a constraint, index or sequence behind fails on the step that follows. A second test goes to `head`, inserts one user and one item with the SQLModel classes from `backend/app/models.py`, downgrades two steps to `d98dd8ec85a3`, upgrades back and asserts that the item still points at the user, which is the part of the UUID migration's downgrade most likely to rot. A third asserts that after `upgrade head` the schema matches the models, so a model change without a revision fails here too.

The new module lives at `backend/tests/alembic/test_migrations.py` and runs with the rest of the suite: `scripts/test.sh` is `coverage run -m pytest tests/`, and `[tool.coverage.run]` sets `source = ["app"]`, so test files do not lower the coverage the workflow checks with `--fail-under=90`. The agent runs the suite against the compose database the way the workflow does, with `docker compose up -d --wait db mailpit` after installing Docker in the sandbox with `sudo apt-get install -y docker.io` and the Compose v2 plugin, which that package does not include, and starting `dockerd`, quotes the per-revision results, and leaves the five migration files untouched unless a downgrade fails, in which case the fix is its own commit with the failing output above it.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Write rollback tests for the Alembic migrations. Read backend/alembic.ini, backend/app/alembic/env.py, every file under backend/app/alembic/versions/, backend/tests/conftest.py, backend/scripts/prestart.sh, backend/scripts/test.sh and .github/workflows/test-backend.yml first. Make env.py honour sqlalchemy.url from the Alembic config when it is set, falling back to settings.DATABASE_URL. Add backend/tests/alembic/test_migrations.py that creates a separate database on the same server, runs upgrade, downgrade and upgrade for every revision in order, round-trips one user and one item through the UUID migration's downgrade, and checks the head schema against the models. Run scripts/test.sh against the compose database, quote the results, fix any downgrade that fails in its own commit, and open a pull request.",
  "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

What comes back is a pull request that adds `backend/tests/alembic/__init__.py` and `backend/tests/alembic/test_migrations.py` and makes one small change to `backend/app/alembic/env.py`. The migration files are unchanged unless a downgrade failed, and then that fix is a separate commit whose message quotes the error.

The description holds the pytest output with one line per revision for the stairway cases, the round-trip test, the schema check, and the coverage table `scripts/test.sh` prints, plus a note on where the throwaway database comes from and that it is dropped and recreated on each run. The repository's `Test Backend` workflow runs the new module on the branch with everything else.

Before merging, a reviewer decides whether the `d98dd8ec85a3` downgrade is something the team would ever run in production, given that it reassigns integer ids from fresh sequences rather than restoring the originals, and whether that migration should say so in its docstring. The agent names it and leaves the file alone.

## Run it on every nightly

In the Project that holds the repository, create an automation with this page's prompt generalised to any project that has an `alembic/versions` directory and a `schedule` trigger with `every` set to `1d` and the team's `timezone`. Each night's run reads the revisions as they are that day, so a migration merged in the afternoon gets its first downgrade run before anyone depends on it, and a run whose stairway passes with nothing new to add opens no pull request and says so in its answer.

The automation's `notify_each_run` can stay off, so the nightly results arrive as one Slack message a day that each run updates, while a failed run still arrives on its own. A migration whose downgrade breaks shows up the next morning with the failing revision named, rather than during an incident.

## Why run it on Rush?

A rollback test only means something against the same Postgres the app uses, with `uuid-ossp`, sequences and `ondelete` behaviour exactly as the migrations assume, and it has to run on a database no other test holds open. The sandbox brings up the compose database, creates the throwaway one, runs every revision both ways and keeps the output, which is more than a laptop run usually records.

The run's record shows each `alembic` command and what Postgres said back, so a reviewer sees which step of which revision failed rather than a summary that the downgrades work. Across several services on this template the same request runs at once, one sandbox each, and each pull request carries that service's own revision chain.

## Frequently asked questions

### Why a separate database rather than the one the other tests use?

`backend/tests/conftest.py` opens a session-scoped `Session` on the app database and the `db` fixture is `autouse`, so dropping `user` and `item` during a downgrade would break every other module. A second database on the same server costs nothing and is created by the test module itself.

### Does this change how migrations run in production?

No. `scripts/prestart.sh` still runs `alembic upgrade head` with no `sqlalchemy.url` set, so `env.py` falls through to `settings.DATABASE_URL` as before. The config option is read only when a caller sets it, which only the tests do.

### What if a downgrade genuinely cannot be reversed?

Then the test says which revision and which statement, and the team chooses between fixing the `downgrade`, or marking that revision as forward-only by raising in `downgrade` with a message and skipping it in the stairway with a listed exception. The agent takes the first path when the fix is mechanical and otherwise asks in the pull request.

### How long does the suite take now?

Each revision runs three Alembic commands on empty tables, so the module adds a few seconds against a local Postgres. The agent quotes the pytest durations for the new module so the reviewer sees the real cost on this schema.

## More Alembic jobs

- [Add Constraint Naming Conventions to Alembic Migrations](https://getrush.ai/run/add-naming-conventions/alembic/)
- [Make a Nullable Column NOT NULL with an Alembic Migration](https://getrush.ai/run/add-not-null-constraint/alembic/)
- [Fix Alembic Multiple Heads After a Merge](https://getrush.ai/run/fix-multiple-heads/alembic/)

## More jobs like this: Write the missing tests

- [Add Mutation Testing to a .NET Solution](https://getrush.ai/run/add-mutation-testing/dotnet/)
- [Add Mutation Testing With Stryker to a Jest Suite](https://getrush.ai/run/add-mutation-testing/jest/)
- [Add Mutation Testing to a Maven Project](https://getrush.ai/run/add-mutation-testing/maven/)

## 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/)
