# Migrate a Drizzle Schema from SQLite to PostgreSQL

Yes. An agent rewrites the Drizzle schema, driver, config and tests for PostgreSQL, copies the rows across, and runs the suite against a real server.

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

## What the agent does

Drizzle keeps the dialect in the code, so moving databases is a set of edits that all have to agree, and the agent begins by finding every one. In an API like w3cj/hono-open-api-starter, `src/db/schema.ts` builds `tasks` with `sqliteTable` from `drizzle-orm/sqlite-core`: an autoincrement integer id, `done` as `integer({ mode: "boolean" })`, and `createdAt` and `updatedAt` as `integer({ mode: "timestamp" })`. `src/db/index.ts` connects through `drizzle-orm/libsql` with a URL and an auth token, `drizzle.config.ts` says `dialect: "turso"`, `src/env.ts` requires `DATABASE_AUTH_TOKEN` when `NODE_ENV` is production, and `.env.example` and `.env.test` point at `file:dev.db` and `file:test.db`. `package.json` has `drizzle-orm` ^0.44.4, `drizzle-kit` ^0.31.4 and `@libsql/client`, with a pnpm lockfile.

The schema becomes a `pgTable` from `drizzle-orm/pg-core` with real types: an identity column for the id, `boolean()` for `done`, and `timestamp()` with a time zone for the two dates. The `casing: "snake_case"` option and the drizzle-zod schemas built from the table carry over. The agent swaps the driver for `drizzle-orm/node-postgres` and `pg`, changes the config's dialect to `postgresql`, and removes the auth-token variable together with the rule in `env.ts` that demands it. The one SQLite migration under `src/db/migrations` cannot run on PostgreSQL, so the agent replaces it and its `meta` snapshot with a baseline generated by `pnpm drizzle-kit generate`, and states that the old history ends there.

Three behaviours differ between the engines, and each has a line in this codebase. The `remove` handler in `tasks.handlers.ts` decides between 204 and 404 with `result.rowsAffected`, which is the libsql result shape; node-postgres reports `rowCount`, so left alone the comparison with zero is never true and deleting an id that does not exist answers 204. The `list` handler calls `findMany()` with no ordering, which SQLite happens to return in id order and PostgreSQL does not promise, so the agent adds an explicit `orderBy`. And SQLite's timestamp mode stores whole seconds as integers, so any copied value has to be converted with `to_timestamp` and not inserted as a number.

For the data, it writes a one-off script that reads every row from the SQLite file through the existing libsql client and inserts it into PostgreSQL with its original id, then moves the identity sequence past the highest id so the next insert does not collide. It installs PostgreSQL in the sandbox with `sudo apt-get install -y postgresql`, creates a development and a test database, seeds a SQLite file with tasks, runs the script and compares row counts and a few full rows on both sides. The test file needs its own changes: `tasks.test.ts` runs `pnpm drizzle-kit push` before the suite and deletes `test.db` after it, and it expects the first created task to have id 1 and the list to hold exactly one row. A server database outlives the test run, so the agent replaces the file removal with a truncate that restarts the identity. Then it runs `pnpm typecheck`, `pnpm lint` and `pnpm test`, starts `pnpm dev` and calls each route with `curl`.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Migrate this Drizzle project from SQLite to PostgreSQL. Read package.json, drizzle.config.ts, src/db/schema.ts, src/db/index.ts, src/db/migrations, src/env.ts, .env.example, .env.test, every file under src/routes/tasks and the README first, and list in the pull request every file that names the dialect, the driver or a SQLite file. Rewrite the schema with pgTable: an identity primary key, boolean for done, timestamps with time zone. Keep the casing option and the drizzle-zod schemas. Switch the client to drizzle-orm/node-postgres with pg, set the drizzle-kit dialect to postgresql, and remove DATABASE_AUTH_TOKEN and the production rule that requires it. Replace the SQLite migration and its meta files with a baseline from pnpm drizzle-kit generate. Find every place that reads a driver-specific result, such as rowsAffected, and every query that relies on unspecified row order, and fix them. Write a one-off script that copies all rows from the SQLite file to PostgreSQL with their ids, converts second-precision integer timestamps, and resets the identity sequence. Install postgresql with apt, create dev and test databases, seed a SQLite file, run the copy and show row counts and three sample rows from both sides. Make the tests work against a database that persists between runs. Run pnpm typecheck, pnpm lint and pnpm test, then start the server and curl every route. Update .env.example and the README. Open a pull request and put a table of each SQLite column type and its PostgreSQL replacement at the top.",
  "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 touches `package.json` and `pnpm-lock.yaml`, `drizzle.config.ts`, `src/db/schema.ts`, `src/db/index.ts`, `src/env.ts`, both env files, `tasks.handlers.ts`, `tasks.test.ts` and the README, replaces the contents of `src/db/migrations`, and adds the copy script. The description leads with the type table, one line per column, followed by the list of behavioural fixes with the file and line of each: the delete check, the list ordering, the test cleanup.

Below that is the evidence from the sandbox. The copy section shows the row count in the SQLite file and in PostgreSQL, three rows printed from each side with their timestamps as dates, and the id the next insert received. The checks section has the output of typecheck, lint and the vitest run, and the `curl` calls against the running server with their status codes, including a delete of an existing id and of a missing one.

The description is explicit about what a deployment still has to do: provision the database, set `DATABASE_URL`, apply the baseline, run the copy script once during a write freeze, and only then switch traffic. A reviewer checks the type table against how the application reads each column, confirms nobody depends on the removed auth-token variable, and decides when the freeze happens.

## Run it on every migration a teammate starts

A database move is rarely one person's call, and it does not have to be one person's run. A teammate starts this in the Project from the console, or with this body on `POST /o/{org}/sessions`, and everyone in the org can open the run and read its transcript while the agent works: the file list, the type table as it forms, the first test output. Nobody needs a screen share to see which driver it picked.

The run's starter or an org admin steers it. A follow-up such as "use postgres.js instead of pg" or "keep `updatedAt` without a time zone" goes in from the console, which queues one sent mid-turn, or through `POST /o/{org}/sessions/{id}/messages` with `{"input": "..."}`. It answers 202 with the turn it opened, and the parked sandbox wakes with the databases and installed packages still in place.

## Why run it on Rush?

The type changes are the easy part to read and the row-count check is the easy part to skip. A migration is trustworthy when the old database and the new one were both running and someone compared them. In its sandbox the agent has the SQLite file, a PostgreSQL server it installed, and the application pointed at each in turn, and the transcript keeps every command with its output. The pull request therefore shows copied rows and a passing suite on PostgreSQL, where a diff alone would show only intent.

The quiet failures are the reason to run it this way. A delete that answers 204 for an id that was never there, a list whose order changes under load, a test suite that passes once and fails on the second run because ids kept counting: none of these appear until the code meets a real PostgreSQL. A laptop can do it, after installing a server and remembering to drop the databases afterwards. The sandbox starts empty, is used once and is discarded, and a teammate can read the whole attempt while it happens.

## Frequently asked questions

### Does it move my production data?

No. The agent has no access to your production database unless you give it one, and this job does not ask for it. It writes the copy script and proves it on a seeded SQLite file in the sandbox. You run the script against real data when you choose, and the pull request lists the order of steps.

### We are on Turso, not a local file. Does that change anything?

Only the source side of the copy. The script reads through the same libsql client the app already uses, so it takes your Turso URL and token from the environment when you run it. The schema, driver and test changes are identical.

### Can it target Neon, Supabase or another hosted PostgreSQL?

Yes. Those are PostgreSQL, so the schema and migrations are the same. Name the provider in the prompt if you want its serverless driver in place of `pg`; the agent then changes the client module accordingly and says which import it used.

### What happens to the existing migration history?

It is replaced by one PostgreSQL baseline. SQLite DDL cannot be applied to PostgreSQL, so keeping the old files would only make `drizzle-kit` fail. They remain in Git history, and the description says at which commit the old chain ends.

## More Drizzle jobs

- [Review Drizzle Migrations in Every Pull Request](https://getrush.ai/run/review-database-migrations/drizzle/)
- [Upgrade Drizzle ORM and Drizzle Kit](https://getrush.ai/run/upgrade-drizzle-orm/drizzle/)
- [Add Account Deletion to a Next.js App on Drizzle](https://getrush.ai/run/add-account-deletion/drizzle/)

## The same job on other stacks

- [Migrate an Echo and GORM API From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/echo/)
- [Migrate a Feathers App From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/feathers/)
- [Move a Gin API From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/gin/)

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