# Migrate a SQLAlchemy App From MySQL to PostgreSQL

Yes. An agent swaps the MySQL drivers for PostgreSQL ones, fixes what the ORM does not hide, runs migrations and tests on a real Postgres, and opens a PR.

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

## What the agent does

SQLAlchemy hides most of the dialect, so the agent goes looking for the places it does not. In an app like teamhide/fastapi-boilerplate the first is the drivers: `pyproject.toml` lists `aiomysql` for the async engine and `pymysql` for synchronous work, and `core/config.py` builds `WRITER_DB_URL` and `READER_DB_URL`, plus the test pair, as `mysql+aiomysql://` URLs on port 3306. Two files derive a synchronous URL by string replacement: `migrations/env.py` and `tests/support/test_db_coordinator.py` both call `.replace("aiomysql", "pymysql")`. The agent swaps the packages for `asyncpg` and a synchronous PostgreSQL driver with Poetry, changes the URLs, and replaces both string edits with one helper that sets the driver name on a parsed URL.

The second place is raw SQL. The only table is named `user`, a reserved word in PostgreSQL. The ORM quotes it, but `TestDbCoordinator.truncate_all` formats `TRUNCATE TABLE {table}` as text, which PostgreSQL rejects for that name. The same statement hides a subtler difference: MySQL's `TRUNCATE` resets the auto-increment counter and PostgreSQL's keeps the sequence unless told to restart it. `test_get_users` inserts two users and asks for rows with an id below 12, which holds on MySQL because every test starts again at 1. On PostgreSQL the ids keep climbing across tests, so that assertion comes to depend on how many rows earlier tests inserted. The agent quotes the identifier through the dialect and adds `RESTART IDENTITY`.

Then it runs the schema on a real server. Docker is not in the sandbox, so it installs PostgreSQL from system packages, creates the `fastapi` role and the `fastapi` and `fastapi_test` databases, and runs `alembic upgrade head`, the downgrade and the upgrade again. The one revision uses `op.create_table` with generic types (`BigInteger`, `Unicode`, `Boolean`, `Float`, `DateTime`), so it should apply as written, and the agent checks the resulting columns instead of assuming. While in `migrations/env.py` it fixes a name clash: the file binds `config` to Alembic's context and then imports the app's `config` over it, so offline mode's `config.get_main_option` cannot work. With that fixed, `alembic upgrade head --sql` prints the PostgreSQL DDL for a person to read before a cutover.

Last it looks for behaviour that changes without an error. MySQL 8 compares strings case-insensitively by default, so the unique constraints on `email` and `nickname` treat `A@b.c` and `a@b.c` as one value; PostgreSQL treats them as two, and `User.email == email` becomes case-sensitive. The agent writes a test that shows the difference and reports the options without choosing. It also notes that `docker-compose.yml` runs MySQL with `TZ` set to `Asia/Seoul` while `created_at` and `updated_at` are filled by `func.now()` into columns with no time zone. It rewrites the compose service and `docker/init.sql` for PostgreSQL, updates the README and runs `make test`.

## Start it with one API call

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

```json
{
  "agent": "codex",
  "input": "Migrate this SQLAlchemy app from MySQL to PostgreSQL and open a pull request. Read pyproject.toml, core/config.py, core/db/session.py, migrations/env.py, alembic.ini, every file under migrations/versions, tests/conftest.py, tests/support/test_db_coordinator.py, docker/docker-compose.yml, docker/init.sql, the Makefile and the README first, and list every place that names MySQL, a MySQL driver, port 3306 or MySQL-only SQL. Docker is not available: install PostgreSQL and Redis from system packages and create the role and both databases. With Poetry, remove aiomysql and pymysql and add asyncpg and a synchronous PostgreSQL driver, keeping the lockfile in step. Change the URLs in core/config.py. Replace the string edits that turn the async URL into a sync one with a single helper used by migrations/env.py and the test database helper. Fix the name clash in migrations/env.py so offline mode works, and include the output of alembic upgrade head --sql. Run alembic upgrade head, downgrade base and upgrade head again on an empty database and describe the resulting table. In the test helper, quote table names through the dialect and restart identities on truncate. Rewrite the compose service and init.sql for PostgreSQL and update the README; say that the compose file was edited and not started. Run make test. Then check what changes silently: write tests for case sensitivity of the unique columns and of lookups by email, report the result with the options, and report how timestamps are stored; do not change behaviour for either without being asked. Do not migrate data. The pull request lists each file and why, the command output, and the open decisions.",
  "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 changes `pyproject.toml` and `poetry.lock`, `core/config.py`, `migrations/env.py`, the test database helper, `docker/docker-compose.yml`, `docker/init.sql` and the README, and adds the URL helper and the case-sensitivity tests. If the existing revision applies unchanged, it is left unchanged. The description lists each file with its reason and pastes the output of the three Alembic commands, the offline DDL, the column listing of the created table and `make test`.

A section headed open decisions carries what the agent found and did not settle. Case sensitivity is first: the test shows two users whose emails differ only in case, and the options are a `citext` column, a unique index on the lowered value, or normalising in the service. Time zones are second: whether to keep naive timestamps and fix the server's zone, or move to time zone aware columns in a new revision. Neither is changed in this pull request, because each alters what the application accepts or stores.

A reviewer checks that no `mysql`, `3306` or driver name is left outside the lockfile's history, that the sync and async URLs come from one helper, and that the test suite still truncates between tests and now restarts ids. The description says the compose file was rewritten but not started, since the sandbox has no Docker, and that no data was moved.

## Run it on every service you are moving off MySQL

A team with several SQLAlchemy services on one MySQL server sends this page's body once per repository to `POST /o/{org}/sessions`, with that repository in `resources` and a `request_id` that is a UUID v5 of the repository's full name and the word postgres, so a retried script returns the runs it already started. Each run reads its own models, raw SQL and tests, so a service with JSON columns or MySQL-only functions gets a longer list of findings than one with a single table.

Give the key a `webhook_url` and each run it starts reports there when it finishes, with a `session_url` that reads the pull request and the final answer. An API key runs four at a time by default and the rest wait as `queued`. The open decisions are the same questions in every service, so it is worth answering them once and sending the answer to each run as a follow-up message, which wakes the same sandbox with its database still in place.

## Why run it on Rush?

A database migration is mostly small edits and one or two surprises, and the surprises only show up against the new server. A truncate that no longer resets ids, a reserved word in a hand-written statement and a unique constraint that changed meaning are not visible in a diff. They appear when the migrations and the whole test suite run on PostgreSQL. The agent installs one in its sandbox, runs both, and keeps the output, so the pull request arrives with evidence and a list of what it deliberately left for a person.

It is also a task that sits half done in a terminal for days, because each fix needs the database restarted and the suite run again. Handed off, it runs to the end without your machine: your local MySQL setup keeps working for the branch you are on, and the run's transcript holds every command if you want to see how a conclusion was reached.

## Frequently asked questions

### Does it move the data too?

No. This job changes the application so it runs on PostgreSQL and proves it on an empty database. Copying rows is a separate operation with its own downtime plan and tooling. The offline DDL in the pull request is the schema to load data into.

### Do the Alembic migrations need rewriting?

Often not. A revision written with `op.create_table` and generic SQLAlchemy types is rendered for whichever dialect runs it. Revisions that call `op.execute` with MySQL syntax, or use types from `sqlalchemy.dialects.mysql`, do need changes, and the agent finds those by running every revision on PostgreSQL.

### Which PostgreSQL driver does it pick?

`asyncpg` for the async engines, since the app uses `create_async_engine`, and a synchronous driver for Alembic and the test helper, which both build a plain engine today. If you would prefer one driver for both, say so in the prompt and the agent uses `psycopg` in its async and sync modes.

### What happens to the reader and writer routing?

It stays. `RoutingSession.get_bind` chooses an engine by statement type and is independent of the dialect. Both URLs change to PostgreSQL, and in the sandbox both point at the same server, so the routing is exercised but replication is not.

### Why not change the email columns to be case-insensitive right away?

Because there are three reasonable ways to do it and each has a cost: an extension, an index that every query must match, or a rule in the service that existing rows may not satisfy. The agent shows the difference with a test and lists the options. Reply to the run with your choice and it adds the revision and updates the test.

## More SQLAlchemy jobs

- [Review Pull Requests in a SQLAlchemy App](https://getrush.ai/run/review-pull-requests/sqlalchemy/)
- [Upgrade a FastAPI App from SQLAlchemy 1.4 to 2.0](https://getrush.ai/run/upgrade-sqlalchemy/sqlalchemy/)
- [Add Connection Pool Settings to a Flask and SQLAlchemy App](https://getrush.ai/run/add-connection-pooling/sqlalchemy/)

## The same job on other stacks

- [Migrate a Fastify API From MySQL to PostgreSQL](https://getrush.ai/run/migrate-mysql-to-postgres/fastify/)

## More jobs like this: Hand off a long task

- [Replace node-fetch With Native fetch in a Node.js Library](https://getrush.ai/run/migrate-node-fetch-to-native-fetch/node-fetch/)
- [Replace nodemon With node --watch in an Express and Mongoose API](https://getrush.ai/run/migrate-nodemon-to-node-watch/nodemon/)

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