# Upgrade a FastAPI App from SQLAlchemy 1.4 to 2.0

Yes. The agent clears the 2.0 warnings on 1.4 first, moves the pin, rewrites models in the typed style, and proves with Alembic that the schema did not change.

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

## What the agent does

SQLAlchemy 1.4 was built as a bridge to 2.0, and the agent uses it that way: before any version changes, it asks 1.4 what will break. In an app like Donnype/fastapi-template the pin is `SQLAlchemy = "^1.4.42"` in `pyproject.toml`, locked at 1.4.54, on Python 3.10. All database code is in `api/repository.py`: a `declarative_base()` named `SQL_BASE`, one model, `TodoInDB`, with four `Column` attributes and a `# type: ignore` on its class line, and a repository that reads through `session.query(...).filter(...)`. `api/migrations/env.py` and `api/test_all.py` both import `SQL_BASE` by name. Four of the five tests need PostgreSQL, which `make test` supplies through Docker Compose. The sandbox has no Docker, so the agent installs PostgreSQL with apt, points `DB_STRING` at it, and runs `pytest` directly.

On 1.4.54 the five tests pass. Run again with `SQLALCHEMY_WARN_20=1`, three `RemovedIn20Warning` messages appear, and all three point at one line: the test fixture's teardown in `api/test_all.py`, which truncates every table through `sessionmaker(bind=engine, autocommit=True)().execute(...)` with a plain string. The application code raises none. The agent fixes that line while still on 1.4, with `engine.begin()` and `text()`, and the suite then passes under `-W error` with the warning flag on. That change can merge by itself, ahead of the upgrade.

The order matters, and the agent shows why by trying the other one. With the pin moved and the teardown untouched, SQLAlchemy 2.0.57 gives 2 failed, 3 passed and 3 errors. The errors are `ArgumentError: autocommit=True is no longer supported`. The two failures are a consequence: the truncate never ran, so later tests collide on `Key (key)=(testkey) already exists`. One line produces five red marks, and reading the first one alone would send a developer into the repository code, which is fine. With the teardown fixed, all five pass on 2.0.57 and no other change is required, because `session.query` still works in 2.0. The agent also notes which version it stopped at: when this page was written, SQLAlchemy 2.1 required Python 3.11, and this project and its `python:3.10-slim` image are on 3.10.

The second commit modernises, and it has its own proof. `SQL_BASE` becomes a `DeclarativeBase` subclass under the same name, so the two importing files do not change. The columns become `Mapped[...]` annotations with `mapped_column`, the `# type: ignore` goes, and the reads become `select()` statements run through `session.scalars`. The trap is the `done` column: it was declared `Column(Boolean, default=False)` with no `nullable=False`, so it is nullable. Written as `Mapped[bool]` it would become NOT NULL, and `alembic check` reports exactly that, a `modify_nullable` operation on `todo.done`. Written as `Mapped[bool | None]`, the check answers `No new upgrade operations detected`.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Upgrade this repository from SQLAlchemy 1.4 to 2.0. Read the dependency manifest and lockfile, every module that imports sqlalchemy, the Alembic env.py and versions directory, the tests, and how CI starts the database. Docker may not be available here: if the tests need PostgreSQL, install it with apt, start it, and run pytest directly with the connection string the code reads; say which server version you tested on and which one CI uses. Check whether the tests can run twice against the same database and reset state between runs if they cannot. Step 1, still on 1.4: run the tests with SQLALCHEMY_WARN_20=1 and list every RemovedIn20Warning by file and line. Fix each one in a way that also works on 2.0, and get the suite passing under -W error with that flag on. Commit. Step 2: choose the target. Read Requires-Python for the newest 2.x releases and compare with the Python version in the manifest and the Dockerfile; pick the newest release the project's Python supports and say what a later one would require. Move the pin, relock, run the tests under -W error, and fix anything left. Commit. Step 3: convert models to DeclarativeBase with Mapped and mapped_column, keeping the name of the base that other modules import, and convert Query usage to select(). Preserve every column's nullability, type, length, default and constraints exactly; a column with no nullable=False stays nullable. Prove it: with the database migrated to head, run alembic check and require that it detects no new operations. Do not create a migration. Commit. Open one pull request with the three commits and, in the description, the warning list, test totals at each step, and the alembic check 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

A pull request with three commits that can be reviewed, and reverted, one at a time. The first touches only the code 1.4 warned about; in an app like the one above that is one fixture in `api/test_all.py`. The second changes the pin and the lockfile and nothing else. The third rewrites `api/repository.py` in the typed style. No file under `api/migrations/versions/` is added or changed.

The description carries the evidence for each step: the three warnings with their file and line, the test totals on 1.4 before and after the fix, the totals on 2.0 with `-W error`, and the output of `alembic check` against a database migrated to head. It also shows the failed experiment, the 2 failed and 3 errors from moving the pin first, because that is what a teammate will see if they try the upgrade on an old branch.

A reviewer looks hardest at the model diff, column by column, against the migration that created the table. The `alembic check` line is the mechanical version of that review: metadata and database agree. The description names what was left for a decision: the tests ran on the PostgreSQL version apt provides, which may differ from the image CI pins, and SQLAlchemy 2.1 is available only after a Python upgrade.

## Run it on every Linear issue labelled upgrade

A major-version upgrade usually exists as an issue long before anyone starts it. An automation in the Project that holds the repository, with a `linear` trigger on the label your team uses for upgrades, starts a run when someone in your Rush org adds that label. The saved prompt is the instruction above, and Rush puts the issue after it as the event. The trigger needs a Linear connection for whoever the automation runs as, made in the console under Settings → Connections.

For a quick look before committing to the work, a state mapped to `plan` mode starts a read-only run that posts its plan as a comment on the issue: the warning list and the target version, with no branch. To start the same job from a script, send this body to `POST /o/{org}/sessions` with a `request_id` that is a UUID v5 of the issue id, so a retry returns the run it already started.

## Why run it on Rush?

This upgrade needs a real PostgreSQL, two installed versions of SQLAlchemy, and a schema comparison against a live database. On a laptop that means the developer's own database and its leftover rows; this fixture's tests fail on a second run for that reason, because one test leaves a row behind. The run's sandbox starts empty, the agent installs the server it needs, and every command with its output is kept in the transcript at `GET /o/{org}/sessions/{id}/messages`.

The proof is specific to the risk. A model rewrite that quietly changes a column to NOT NULL can pass every test and then surface in the next autogenerated migration. The agent runs the check that catches it and puts the output in the pull request, so the reviewer reads a result where they would otherwise have to trust a diff. If they want the 2.1 path explored, a follow-up to `POST /o/{org}/sessions/{id}/messages` wakes the same sandbox with the database still migrated.

## Frequently asked questions

### Do I have to rewrite session.query() calls for 2.0?

No. The Query API is labelled legacy in 2.0 but still works, and in Donnype/fastapi-template the repository passes its tests on 2.0.57 without a change. The agent converts the calls to `select()` in a separate commit so you can take the version bump without the rewrite.

### What breaks immediately on 2.0?

The things 1.4 warns about under `SQLALCHEMY_WARN_20=1`: `autocommit=True` on a session, plain strings passed to `execute` without `text()`, and statements that relied on implicit autocommit. Running 1.4 with the flag lists every occurrence by file and line, which is why the agent does that before it moves the pin.

### Will the typed models change my database schema?

They should not, and `alembic check` is how the agent proves it. `Mapped[bool]` means NOT NULL and `Mapped[bool | None]` means nullable, so a column that was nullable by default must get the optional annotation. If the check reports any operation, the agent fixes the model, not the database.

## More SQLAlchemy jobs

- [Add Connection Pool Settings to a Flask and SQLAlchemy App](https://getrush.ai/run/add-connection-pooling/sqlalchemy/)
- [Add Cursor Pagination to a SQLAlchemy API](https://getrush.ai/run/add-cursor-pagination/sqlalchemy/)
- [Set Up GitHub Actions CI for a SQLAlchemy App](https://getrush.ai/run/add-github-actions-ci/sqlalchemy/)

## More jobs like this: Turn a Linear label into a PR

- [Add Account Deletion to a Next.js App on Drizzle](https://getrush.ai/run/add-account-deletion/drizzle/)
- [Add Account Deletion to a Supabase App from a Linear Issue](https://getrush.ai/run/add-account-deletion/supabase/)
- [Add Account Lockout After Failed Logins to a Node.js App](https://getrush.ai/run/add-account-lockout/nodejs/)

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