# Roll Back the Database After Each pytest Test in a FastAPI App

Yes. An agent gives your pytest suite a per-test transaction that the app's requests join and roll back after each test, tightens loose assertions, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-transactional-test-fixtures/pytest/). 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 suite talks to the database today. In a FastAPI app laid out like fastapi/full-stack-fastapi-template, `backend/tests/conftest.py` has one `db` fixture with `scope="session"` and `autouse=True` that opens a single `Session(engine)`, calls `init_db` to create the first superuser, yields that session to every test in the run, and only at the very end runs `delete(Item)` and `delete(User)` and commits. The app itself never sees that session: `get_db` in `backend/app/api/deps.py` opens its own `Session(engine)` per request and the routes commit inside it, so a `POST /items/` made through the module-scoped `TestClient` writes a row that stays until the session teardown. The tests have grown around that: `test_read_items` creates two items and asserts `len(content["data"]) >= 2`, because it cannot know how many rows earlier tests left behind.

It replaces that with a transaction per test. The new `db` fixture is function-scoped: it takes a connection from `engine`, begins a transaction on it, opens a `Session` bound to that connection with `join_transaction_mode="create_savepoint"`, yields it, and on teardown rolls the outer transaction back and returns the connection. In that mode the `session.commit()` calls inside `crud.create_user`, `crud.create_item` and the routes release a savepoint instead of ending the transaction, so a test can call code that commits and still have everything undone. The app joins the same transaction through `app.dependency_overrides[get_db]`, set in a fixture to yield the test's session, so a request made with the `TestClient` reads what the test wrote and vice versa, and nothing either of them wrote survives the test.

Seed data moves to where it belongs. `init_db` and the test user that `authentication_token_from_email` in `tests/utils/user.py` creates for `settings.EMAIL_TEST_USER` have to exist before any transaction starts, because the login in `user_authentication_headers` goes through the client and a user created inside a rolled-back transaction would vanish between tests; the agent keeps a session-scoped fixture that commits those two users once, and the function-scoped `superuser_token_headers` and `normal_user_token_headers` log in against them. The `delete(Item)` and `delete(User)` teardown goes, since there is nothing left to delete.

Then it fixes what the leak was hiding. `test_read_items` asserts an exact count of two, and the other tests under `tests/api/routes` and `tests/crud` that create rows get the same treatment where a `>=` or a `first()` was covering for state from another test. It runs `bash scripts/test.sh` against a PostgreSQL it installs in the sandbox, with `DATABASE_URL` pointed at it and `alembic upgrade head` run first; then it runs each test file on its own, which is the check that no test still depends on rows another one left; then `coverage report --fail-under=90`, the floor `.github/workflows/test-backend.yml` enforces.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Make this pytest suite roll back the database after every test. Read backend/pyproject.toml, backend/tests/conftest.py, backend/tests/utils/user.py, backend/tests/utils/item.py, backend/tests/utils/utils.py, backend/tests/api/routes/test_items.py, backend/tests/api/routes/test_users.py, backend/tests/crud/test_user.py, backend/app/api/deps.py, backend/app/core/db.py, backend/app/crud.py, backend/scripts/test.sh and .github/workflows/test-backend.yml first. Replace the session-scoped db fixture with a function-scoped one that begins a transaction on an engine connection, binds a Session to it with join_transaction_mode set to create_savepoint, and rolls back on teardown; override the app's get_db dependency so requests through the TestClient use that session. Keep a session-scoped fixture that commits the first superuser and the EMAIL_TEST_USER once, before any test transaction, and make the token header fixtures function-scoped. Remove the delete-all teardown. Tighten assertions that relied on rows left by other tests, starting with the >= 2 in test_read_items. Install PostgreSQL in the sandbox, run alembic upgrade head, bash scripts/test.sh, then each test file on its own, and coverage report --fail-under=90, 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

What comes back is a pull request that rewrites `backend/tests/conftest.py`, touches the fixtures in `backend/tests/utils` where a scope changed, and tightens assertions in `backend/tests/api/routes/test_items.py` and any other file that leaned on leaked rows. The description shows the new fixture, the dependency override, and the pytest output for the full suite and for each file run alone.

The run's record keeps every test run, so a reviewer sees that `test_read_items` passes with an exact count and that the files pass in isolation rather than reads that they should.

Before merging, a reviewer checks one thing the pull request names: any test that deliberately exercises a commit followed by a read on a second connection, since such a test cannot share one transaction and needs to be marked to use the engine directly. The agent lists any it found and how it handled each.

## Run it on every pull request that adds a test

Once the fixture is in place, the thing to catch is a new test that writes with its own `Session(engine)` and commits outside the transaction, which brings the leak back. In the Project for the repository, add an automation with a `github` trigger on `pull_request.opened` and `pull_request.synchronize` and a prompt that reads the changed test files, flags any session opened outside the `db` fixture or any assertion that only holds with rows from another test, and leaves a review comment with the file and line. The trigger adds the pull request after the prompt and fills in no placeholder.

A team with several FastAPI services on the same test layout sends this body once per repository from its own server, each with its 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 and opens no second pull request.

## Why run it on Rush?

A suite that leaks rows passes in one order and fails in another, and the failure appears on the day someone adds a test, not the day the leak was introduced. Finding the tests that depend on each other means running files alone and in combinations, which is exactly the kind of long, mechanical experiment the agent runs in its sandbox with a real Postgres while the record keeps each run and its result.

The change touches only test code, so merging it carries no runtime risk, and every service on the same template gets the same fix from the same body, each with its own sandbox, its own database and its own pull request showing the suite green both ways.

## Frequently asked questions

### Why create_savepoint rather than patching commit?

The routes and `crud.py` call `session.commit()` and `session.refresh()` as part of their normal work. With `join_transaction_mode="create_savepoint"` a commit releases a savepoint inside the outer transaction and refresh reads the row back on the same connection, so the application code runs unmodified and the fixture's final rollback still undoes everything.

### Does the TestClient really see the test's uncommitted rows?

Yes, because the override makes `get_db` yield the same session, bound to the same connection, that the test holds. A row the test added is visible to the request, and a row the request added is visible to the test's next query, all inside one transaction.

### What about the superuser and the test user?

They are created once per test session and committed for real, before any test transaction starts, because the login goes through the HTTP client and the user has to exist for every test. Nothing else is committed; a test that changes the test user's password does so inside its transaction and the change is rolled back.

### Will the suite get slower?

Each test now begins and rolls back a transaction, which is cheaper than the row deletes that the old teardown ran once at the end but also cheaper than the leaked state that made assertions loose. The agent includes the pytest timing summary from before and after in the pull request so the reviewer can see the difference rather than guess.

## More pytest jobs

- [Fix "Fixtures are not meant to be called directly" in a pytest Suite](https://getrush.ai/run/fix-fixtures-are-not-meant-to-be-called-directly/pytest/)
- [Fix pytest Tests That Pass Once and Fail on the Next Run](https://getrush.ai/run/fix-flaky-tests/pytest/)
- [Fix "AttributeError: module 'pytest' has no attribute 'config'" After Upgrading pytest](https://getrush.ai/run/fix-pytest-config-attribute-error/pytest/)

## More jobs like this: Write the missing tests

- [Add Unit Tests to a dbt Project](https://getrush.ai/run/add-unit-tests/dbt/)
- [Add Visual Regression Tests With Playwright](https://getrush.ai/run/add-visual-regression-tests/playwright/)
- [Add Visual Regression Tests to Storybook Stories](https://getrush.ai/run/add-visual-regression-tests/storybook/)

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