# Write Tests That Run Alembic Migrations Up and Down

Yes. An agent adds pytest tests that run your Alembic chain up from empty, back down, up again, and compare the result with your models, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/write-migration-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 chain and the test setup before it writes a test, because a migration test has to share the suite's database without breaking the suite. In a backend laid out like fastapi/full-stack-fastapi-template, `backend/alembic.ini` points `script_location` at `app/alembic`, and `app/alembic/env.py` sets `target_metadata = SQLModel.metadata` from `app.models`, reads the URL from `settings.DATABASE_URL`, and passes `compare_type=True` in both offline and online mode. `app/alembic/versions/` holds five revisions in a single chain: `e2412789c190` creates `user` and `item` with integer ids and a unique index on `user.email`, `9c0a54914c78` adds string lengths, `d98dd8ec85a3` replaces the integer ids with UUIDs, `1a31ce608336` makes `item.owner_id` non-null and recreates its foreign key with `ondelete='CASCADE'`, and `fe56fa70289e` adds a nullable `created_at` to both tables. The models in `app/models.py` are `User` and `Item`, both `table=True`, with UUID primary keys, `created_at`, and a `cascade_delete=True` relationship. `tests/conftest.py` has a session-scoped `db` fixture that calls `init_db` and deletes `Item` and `User` rows at the end, and `scripts/test.sh` runs `coverage run -m pytest tests/` followed by `coverage report`; `test-backend.yml` brings up `postgres:18` with `docker compose up -d --wait db mailpit`, runs `scripts/prestart.sh`, which is `alembic upgrade head` then `initial_data.py`, runs the tests, and fails under 90% coverage. `pyproject.toml` excludes `alembic` from `ruff` and `mypy`, pins `alembic>=1.19.1` and `sqlmodel>=0.0.46`, and requires Python 3.14. Nothing in `tests/` touches a migration.

The agent writes `tests/alembic/test_migrations.py` around a module-scoped fixture that creates its own throwaway database on the same Postgres server, `CREATE DATABASE` from a connection to the configured one, so the suite's database, already at head after `prestart.sh`, is never downgraded under the other tests. It builds an Alembic `Config` from `alembic.ini` and sets `sqlalchemy.url` to the scratch database, then uses `alembic.command` rather than a subprocess so failures carry a Python traceback. The first test runs `upgrade head` from an empty database and asserts `ScriptDirectory.get_current_head()` equals the revision stamped in `alembic_version`, which also proves there is exactly one head. The second runs `downgrade base` and asserts no table but `alembic_version` remains, which catches a downgrade that drops a constraint under a name it never created. The third runs `upgrade head` again, then inserts one `User` and one `Item` through the models and deletes the user, asserting the item is gone, which is what `1a31ce608336`'s `ondelete='CASCADE'` promises and the models' `cascade_delete=True` assumes.

The fourth test is the one that matters most as the models grow: with the scratch database at head, it calls Alembic's autogenerate comparison, `compare_metadata` from `alembic.autogenerate` against `SQLModel.metadata`, and asserts the result is empty, so a model change without a revision fails the suite with the missing operation named, the same check `alembic check` makes from the command line but inside pytest with the suite's own output. Because `env.py` uses `compare_type=True`, the test passes the same flag, so a type change counts. A fifth test walks the chain one revision at a time, `upgrade +1` from base to head, so a revision that only works when applied together with its neighbour is caught. The fixture drops the scratch database at the end, and the tests are marked so `coverage` reports them without counting the migration files, which `pyproject.toml` already excludes from linting.

To check it, the agent installs Docker in its sandbox first (`sudo apt-get install -y docker.io`, then starts `dockerd`), starts the `postgres:18` container that `docker-compose.yml` describes with `docker run` and the same environment, since the `docker compose` plugin is not in that package, and runs `uv run bash scripts/prestart.sh` as the workflow does, then `uv run bash scripts/tests-start.sh` so the new tests run with the rest and the coverage threshold is still met, and `uv run bash scripts/lint.sh` so `mypy`, `ty`, `ruff check` and `ruff format` accept the new file. It then adds a temporary column to `Item`, runs the suite again to show the drift test fails naming the column, and reverts it.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Write tests for the Alembic migrations of this backend. Read backend/alembic.ini, backend/app/alembic/env.py, every file under backend/app/alembic/versions, backend/app/models.py, backend/app/core/db.py, backend/tests/conftest.py, backend/scripts/prestart.sh, backend/scripts/test.sh, backend/scripts/lint.sh, backend/pyproject.toml and .github/workflows/test-backend.yml first. Add backend/tests/alembic/test_migrations.py with a module-scoped fixture that creates and drops a scratch database on the configured Postgres server so the suite's database is never downgraded. Using alembic.command, test that upgrade head from empty lands on the single head, that downgrade base leaves only alembic_version, that upgrade head again works and a cascade delete from user to item behaves as the models promise, that compare_metadata against SQLModel.metadata with compare_type is empty at head, and that the chain applies one revision at a time. Install Docker, start the Postgres container the compose file describes, run prestart.sh, tests-start.sh and lint.sh, prove the drift test fails on a temporary model column and revert it, and open a pull request with the 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 `backend/tests/alembic/__init__.py` and `backend/tests/alembic/test_migrations.py` and nothing else. Its description pastes the pytest output with the five new tests, the coverage report line showing the threshold still met, the lint script's output, and the failing run from the temporary column with the drift test's message naming it.

The `Test Backend` workflow runs on the pull request with the new tests inside the same `tests-start.sh` step, so a reviewer sees them pass on a fresh `postgres:18` after `prestart.sh`, and the `pre-commit` workflow shows `ruff`, `mypy` and `ty` accept the file. The run's record keeps the `CREATE DATABASE` and `DROP DATABASE` statements the fixture issued.

Before merging, a reviewer decides whether the scratch database should also be used for the revision-by-revision walk or whether that test may stay on the same database as the others, whether the cascade test belongs here or with the CRUD tests, and whether to run the migration tests in their own workflow step so their failure is easy to tell from the API tests.

## Run it on every Sunday night

Create an automation in the Project with a `schedule` trigger, a `cron` such as `0 2 * * 0` with your `timezone`, and a prompt that asks for tests on whatever the week added under `app/alembic/versions` and the models it touched. Each run opens one pull request on its own branch, so the first revision of the week that lacks a downgrade or drifts from the models is found before Monday rather than during a deploy.

A run that finds nothing new under `versions/` and no model change ends with a short note and no pull request. A retried slot is deduped, so one week's schedule cannot open two pull requests, and the automation has its own `max_runs_per_hour` cap.

## Why run it on Rush?

Migrations are the one part of a backend that runs once against production and never again locally, so a downgrade that was never tried or a model that drifted from its last revision stays invisible until it is expensive. The agent brings up the same Postgres the workflow uses, in its own sandbox, and runs the chain up, down and up again with the output kept.

The pytest run, the coverage line and the deliberate failure on a temporary column stay with the run and are pasted into the pull request, so the review is reading evidence rather than trusting a description. Several services can get the same tests at once, each against its own chain.

## Frequently asked questions

### Will the tests interfere with the existing suite's database?

No. The fixture creates a scratch database on the same server, points Alembic's `sqlalchemy.url` at it, and drops it afterwards, so the database that `prestart.sh` migrated and the session-scoped `db` fixture uses is never touched. The existing tests run exactly as before.

### How is this different from running alembic check in CI?

The drift test calls the same comparison, but inside pytest, with the suite's output and the suite's database lifecycle, and it sits beside four tests that `alembic check` does not make: the downgrade, the re-upgrade, the cascade behaviour and the revision-by-revision walk. A team with a drift step in CI keeps it; the tests add the rest.

### What if a downgrade is not meant to be reversible?

Then the test says which revision refuses, and the pull request asks you whether to mark that revision as the floor of the downgrade test. The agent does not edit any revision; it reports what the chain does today.

### Does this slow the suite down?

By a few upgrades and downgrades of a five-revision chain on a local Postgres, which is small next to the API tests that already run. The revision-by-revision walk is the slowest of the five, and the pull request names it if you would rather keep it out of the default run.

## More Alembic jobs

- [Add Database Indexes with an Alembic Migration](https://getrush.ai/run/add-database-indexes/alembic/)
- [Add an Alembic Migration Drift Check to CI](https://getrush.ai/run/add-migration-check-to-ci/alembic/)
- [Make CI Fail When SQLModel Models Change Without an Alembic Migration](https://getrush.ai/run/add-migration-drift-check/alembic/)

## More jobs like this: Write the missing tests

- [Write System Tests for a Rails App](https://getrush.ai/run/write-minitest-system-tests/rails/)
- [Write Molecule Tests for an Ansible Role](https://getrush.ai/run/write-molecule-tests/ansible/)
- [Write node:test Tests for a Fastify API](https://getrush.ai/run/write-node-test-tests/fastify/)

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