All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Steer a run as a team.

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

The same job on other stacks

Keep reading