All jobs

Add Read Replicas to a Drizzle App

Yes. An agent wraps your Drizzle client with withReplicas, sends reads to a replica and writes to the primary, keeps read-after-write on the primary, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent starts with how the database is reached. In an app laid out like nextjs/saas-starter, lib/db/drizzle.ts builds one postgres(process.env.POSTGRES_URL) client and exports db = drizzle(client, { schema }); drizzle.config.ts reads the same POSTGRES_URL for drizzle-kit, and .env.example lists it as the only database variable. Every query in lib/db/queries.ts goes through that db: getUser selects by id from the session cookie, getTeamByStripeCustomerId and getUserWithTeam select, getActivityLogs selects the last ten rows, getTeamForUser uses the relational db.query.teamMembers.findFirst API, and updateTeamSubscription updates. The server actions in app/(login)/actions.ts insert a user and .returning() it, then insert the team and membership, and lib/db/seed.ts inserts the first user and team. The project pins drizzle-orm ^0.43.1 and postgres ^3.4.5.

It adds the replica where the client is made. drizzle.ts reads POSTGRES_REPLICA_URLS, a comma-separated list, builds one postgres() client per URL, and wraps the primary with withReplicas from drizzle-orm/pg-core, so db.select() and db.query.* go to a replica chosen at random and insert, update, delete and transaction go to the primary. When the variable is empty the export is the primary alone, so a laptop with one database runs unchanged. The schema option is passed to every drizzle() call, because the relational API getTeamForUser uses needs it on the replica too.

Then it finds the reads that must not lag. Sign-up inserts a user and immediately setSession on the returned row, and the next request's getUser reads it back; on a replica a few hundred milliseconds behind, that read returns nothing and the new account sees the sign-in page. So getUser and the sign-up and invitation paths call db.$primary for the select that follows a write in the same flow, and getActivityLogs, getTeamByStripeCustomerId and getTeamForUser stay on the replica. The Stripe webhook in app/api/stripe/webhook/route.ts reads the team by customer id and then updates it; the agent keeps that read on $primary as well, because a webhook that arrives before the replica has the row would drop the subscription. drizzle.config.ts and seed.ts are left on the primary URL.

In its sandbox the agent starts two Postgres containers, the second a streaming replica of the first, writes POSTGRES_URL and POSTGRES_REPLICA_URLS, and runs pnpm db:migrate and pnpm db:seed with the Stripe steps stubbed. It then writes a script under lib/db/ that inserts an activity log row and reads it back once through db and once through db.$primary, logging which connection answered through postgres's debug callback, and runs pnpm build to show the types still check.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add read replica support to this Drizzle app. Read lib/db/drizzle.ts, drizzle.config.ts, lib/db/queries.ts, lib/db/seed.ts, app/(login)/actions.ts, app/api/stripe/webhook/route.ts, .env.example and package.json first. In drizzle.ts read POSTGRES_REPLICA_URLS as a comma-separated list, build a postgres client per URL, and wrap the primary with withReplicas from drizzle-orm/pg-core, passing schema to every drizzle() call; when the variable is empty export the primary alone. Keep reads that follow a write in the same flow on db.$primary: getUser, the sign-up and invitation paths, and the Stripe webhook's team lookup. Leave drizzle.config.ts and seed.ts on the primary. Add the variable to .env.example. In the sandbox run a primary and a streaming replica, migrate and seed with the Stripe steps stubbed, and write a script that shows a read going to the replica and a $primary read going to the primary using the postgres debug callback. Run pnpm build. Open a pull request.",
  "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 touching lib/db/drizzle.ts, lib/db/queries.ts, app/(login)/actions.ts, app/api/stripe/webhook/route.ts, .env.example and a new script under lib/db/. The description lists every query by name with the connection it now uses and why, quotes the script's output showing the replica host answering the plain read and the primary answering the $primary read, and the pnpm build summary.

The run's record keeps both Postgres containers' logs and the replication setup, so a reviewer can see the replica was really streaming from the primary and not a second independent database. The seed's Stripe calls were stubbed, and the pull request says so.

Before merging, a reviewer checks the list of $primary reads against flows the agent could not see, such as a background job that reads right after a webhook writes, and adds any that need it. They also pick the replica URLs for each environment; a preview deployment can leave the variable empty.

Run it on every database alert

This is the job to run when the primary's CPU or connection count alert fires and the reads need somewhere else to go. In the Project for the repository, an automation with the prompt above and a webhook trigger takes the alert body from the database provider or Grafana; the handler sends the trigger's URL an Idempotency-Key made from the alert id, so a re-fired alert does not start a second run.

The first run adds the replica wiring. A later run on a repository that already has it reads the alert, finds the query the alert names in lib/db/queries.ts, and opens a pull request moving that one read, each on its own branch and linked to the alert in the description.

The use case this job follows, with its automation and trigger: Fix a bug from an alert.

Why run it on Rush?

withReplicas is a one-line wrap, and the whole risk is in which reads stay on the primary: a sign-up that reads its own row from a replica a moment behind looks like a random login failure that no test on one database can reproduce. In a Rush sandbox the agent runs a real streaming replica beside the primary, routes the reads, and shows which host answered each one before anyone deploys it.

The per-query list stays with the run and in the pull request, so the next person adding a query knows the rule and the reason. Across many apps on the same template the job runs in parallel, one sandbox each, and every pull request carries its own evidence of where the reads went.

Frequently asked questions

What if I only have one database today?

Leave POSTGRES_REPLICA_URLS unset. drizzle.ts then exports the plain primary client, exactly as before, and db.$primary is not needed anywhere; the code paths that use it keep working because withReplicas is only applied when a replica URL exists and the agent exports a typed shim with $primary pointing at the same client otherwise.

Why does getUser stay on the primary?

Because it runs on the first request after sign-up, which inserted that user milliseconds earlier. A replica behind by even a little returns no row and the new account is sent back to sign in. Reads that are not preceded by a write in the same flow, like the activity log, go to the replica.

Does drizzle-kit need the replica URLs?

No. drizzle.config.ts keeps POSTGRES_URL for generate, migrate and studio; migrations run on the primary and replicate from there. The agent leaves that file alone.

How does the agent prove a read hit the replica?

The postgres client takes a debug callback per connection; the script tags each client with its host and logs the tag for every query it runs. The pull request quotes one plain read answered by the replica host and one $primary read answered by the primary.

Can reads be split by table instead of by call?

withReplicas chooses per query, not per table, and the choice of replica is random unless you pass a getReplica function. Say so in the prompt and the agent adds one that pins a given table's reads to one replica, which helps when replicas differ in lag.

More Drizzle jobs

The same job on other stacks

More jobs like this: Fix a bug from an alert

Keep reading