# Migrate a Fastify API From MySQL to PostgreSQL

Yes. An agent swaps the Knex client, rewrites the SQL migrations and database scripts for Postgres, moves the test and CI services over, and opens a PR.

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

## What the agent does

The agent starts by listing every place the database engine leaks into the code. In a Fastify API shaped like fastify/demo, `package.json` has Fastify 5 with `knex`, `mysql2` and `postgrator`, and `npm run test` seeds the database before running the suite with `tsx --test` under `c8`. `src/plugins/external/knex.ts` builds the Knex instance with `client: 'mysql2'` from the `MYSQL_HOST`, `MYSQL_USER`, `MYSQL_PASSWORD`, `MYSQL_DATABASE` and `MYSQL_PORT` values that `src/plugins/external/env.ts` validates with `@fastify/env`. The four scripts in `scripts/` open their own `mysql2/promise` connections, `migrations/*.sql` use `INT AUTO_INCREMENT PRIMARY KEY` and `ON UPDATE CURRENT_TIMESTAMP`, `docker-compose.yml` runs `mysql:8.4` with a `mysqladmin ping` health check, and `.github/workflows/ci.yml` starts the same image as a service for Node 22 and 24. `@types/node/environment.d.ts` and `.env.example` repeat the variable names.

It then replaces the driver and the schema. `pg` replaces `mysql2` in `package.json`, and because `tasks-repository.ts` exposes `createStream()` through Knex's `.stream()`, `pg-query-stream` comes in beside it, since Knex needs that package to stream from Postgres. The Knex client becomes `pg`, and the variables are renamed to `DATABASE_HOST`, `DATABASE_PORT`, `DATABASE_USER`, `DATABASE_PASSWORD` and `DATABASE_NAME` in `env.ts`, `environment.d.ts`, `.env.example`, `docker-compose.yml` and `ci.yml` in one pass, so no file still names MySQL. In the migrations, `id INT AUTO_INCREMENT PRIMARY KEY` becomes an identity column, backticks go, and the `updated_at` columns on `users` and `tasks` lose `ON UPDATE CURRENT_TIMESTAMP`, which Postgres does not have; `001.do.users.sql` gains a `set_updated_at()` trigger function and each table with the column gets a `BEFORE UPDATE` trigger, with matching drops in the `.undo.` files. Postgrator's driver in `scripts/migrate.ts` becomes `pg` and keeps the `schemaversion` table.

The code paths that silently assume MySQL get fixed one by one. `tasksRepository.create` reads the new id as `const [id] = await knex('tasks').insert(newTask)`, which only MySQL returns; with Postgres it needs `.returning('id')` and reads `[{ id }]`, and the `createUser` and `createTask` helpers at the top of `test/routes/api/tasks/tasks.test.ts` have the same shape. `paginate` already wraps `count(*) OVER() as total` in `Number()`, which matters now because Postgres returns that bigint as a string. In `scripts/seed-database.ts`, `SHOW TABLES` becomes a query on `pg_tables` for the `public` schema, `SET FOREIGN_KEY_CHECKS` and the per-table truncates become one `TRUNCATE ... RESTART IDENTITY CASCADE`, and the `insertId` reads become `RETURNING id`. `scripts/create-database.ts` checks `pg_database` before `CREATE DATABASE`, since Postgres has no `IF NOT EXISTS` there, and the `schemaversion` table is left out of the truncate list so a reseed does not forget which migrations ran.

Before it opens anything, the agent runs what `ci.yml` runs against a Postgres it starts in its own sandbox: `npm run db:create`, `npm run db:migrate`, then `npm run test`, which seeds and runs every file under `test/` with coverage, plus `npm run lint` with the `neostandard` ESLint config and `npm run build -- --noEmit`. It confirms the `rate-limit.test.ts` and `auth.test.ts` suites still pass unchanged, since sessions and rate limiting never touched the driver, and that the CSV download in `routes/api/tasks/index.ts` still streams through the new `pg-query-stream`. It updates the Database section of `README.md` for the new variable names and the `docker compose up` instructions.

## Start it with one API call

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

```json
{
  "agent": "codex",
  "input": "Migrate this Fastify API from MySQL to PostgreSQL and open a pull request. Read package.json, src/plugins/external/knex.ts, src/plugins/external/env.ts, @types/node/environment.d.ts, .env.example, docker-compose.yml, .github/workflows/ci.yml, every file in scripts/ and migrations/, src/plugins/app/tasks/tasks-repository.ts, src/plugins/app/users/users-repository.ts, test/helper.ts and test/routes/api/tasks/tasks.test.ts first. Replace mysql2 with pg and add pg-query-stream because tasks-repository.ts streams through Knex. Set the Knex client to pg. Rename the MYSQL_* variables to DATABASE_HOST, DATABASE_PORT, DATABASE_USER, DATABASE_PASSWORD and DATABASE_NAME everywhere they appear. Rewrite migrations/*.sql for Postgres: identity columns instead of AUTO_INCREMENT, no backticks, and a set_updated_at() trigger function with BEFORE UPDATE triggers on users and tasks in place of ON UPDATE CURRENT_TIMESTAMP, with drops in the undo files. Switch Postgrator to the pg driver. Make every insert that reads the new id use .returning('id') in the repositories and the test helpers. Rewrite scripts/create-database.ts, drop-database.ts and seed-database.ts for pg: check pg_database before creating, truncate with RESTART IDENTITY CASCADE from pg_tables, use RETURNING id, and never truncate schemaversion. Change docker-compose.yml and the CI service to a postgres image with a pg_isready health check. Start Postgres, run npm run db:create, npm run db:migrate, npm run test, npm run lint and npm run build -- --noEmit, and paste the output in the pull request. Update the Database section of README.md. Do not change any route, schema or plugin that does not touch the database.",
  "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 touches `package.json` and `package-lock.json` (`pg` and `pg-query-stream` in, `mysql2` out), `src/plugins/external/knex.ts`, `src/plugins/external/env.ts`, `@types/node/environment.d.ts`, `.env.example`, every `.do.` and `.undo.` file under `migrations/`, the four scripts under `scripts/`, `tasks-repository.ts`, the test helpers that insert rows, `docker-compose.yml`, `.github/workflows/ci.yml` and `README.md`. The description lists each MySQL construct it found and what replaced it, and pastes the output of the migrate, test, lint and build commands from the sandbox.

The CI workflow runs on the pull request with its new `postgres` service on both Node versions, so the suite that seeds through `npm run test` is the proof that migrations, seed and queries agree. The agent pushes one commit for the driver and migrations, one for the repositories and tests, and one for the scripts, Compose file, workflow and README, so each can be read on its own.

A reviewer checks that every `.insert()` whose id is used has `.returning('id')`, that the `updated_at` trigger fires on both tables and the undo files drop it, that the `schemaversion` table survives a reseed, that no `MYSQL_` name is left in any file, and that the health check in Compose and CI waits for `pg_isready` rather than a fixed sleep. Then they plan the data move for production, which this pull request deliberately leaves alone.

## Run it on every service still on MySQL

A team with several Fastify services on the same MySQL instance starts one run per repository with `POST /o/{org}/sessions`, this prompt and that repository in `resources`. Each run reads its own `knex.ts`, migrations and scripts, so a service that already uses `.returning()` or has no streaming export gets a smaller diff, and each ends in its own pull request. A `request_id` in the body makes a retried call return the run it already started instead of opening a second pull request.

The key's `webhook_url` receives `run.completed` for each run it starts, with `session_url` and the final answer, so a script can post the pull request links to the team channel as they land and flag any run that ended without one.

## Why run it on Rush?

A database engine swap is the kind of change that looks finished when the code compiles and fails on the first insert. In its own sandbox the agent runs a real Postgres, applies the rewritten migrations in order, seeds through the project's own script and runs the whole suite, and every command and its output stays in the transcript. The `.returning('id')` and `pg-query-stream` changes are found by running the tests, not by guessing.

Nothing in the change depends on the reader's laptop having MySQL, Postgres or Docker set up side by side. The result passes the repository's own CI with its service container switched over, the pull request lists every construct it replaced, and the same request works unchanged across every Fastify service the team wants to move.

## Frequently asked questions

### Does it move my data too?

No. The pull request changes the schema, the driver, the scripts and the test setup. Copying rows from a production MySQL into Postgres is a separate, supervised step, and the agent says so in the description rather than reaching for your data. The migrations it rewrites give you the target schema to load into.

### Why a trigger for updated_at?

MySQL's `ON UPDATE CURRENT_TIMESTAMP` has no equivalent in Postgres. The alternatives are to set `updated_at` in every `update()` call in the repositories or to let the database do it. A `BEFORE UPDATE` trigger keeps the behaviour the current schema promises without touching each repository method, and the undo migration drops it.

### Can I keep the MYSQL_ variable names?

You can ask for that in the prompt and the diff shrinks, but `env.ts` validates them, `environment.d.ts` types them and the README documents them, so names that say MySQL while pointing at Postgres will mislead the next person. The agent renames them everywhere in one commit so it is easy to review.

### Does the CSV export still stream?

Yes. `tasksRepository.createStream()` uses Knex's `.stream()`, which with the `pg` client needs the `pg-query-stream` package. The agent adds it and runs the CSV test in `tasks.test.ts`, which gunzips the download and checks the rows.

### What about the file uploads and sessions?

They do not touch the driver. `@fastify/session` keeps its cookie store, `tasks-file-manager.ts` writes to the uploads directory, and `rate-limit.ts` has no database. Their tests run unchanged and the agent cites that in the description.

## More Fastify jobs

- [Review Postgrator Migrations in Fastify Pull Requests](https://getrush.ai/run/review-database-migrations/fastify/)
- [Review Pull Requests Automatically in a Fastify API](https://getrush.ai/run/review-pull-requests/fastify/)
- [Run a Security Audit on a Fastify API](https://getrush.ai/run/security-audit/fastify/)

## The same job on other stacks

- [Migrate a SQLAlchemy App From MySQL to PostgreSQL](https://getrush.ai/run/migrate-mysql-to-postgres/sqlalchemy/)

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