Prisma Read Replica Routing with the Read Replicas Extension

Problem statement: Your Prisma-backed Node.js service points a single DATABASE_URL at the primary, read traffic is throttling writes, and you want Prisma to send reads to replicas and writes to the primary automatically — with a clean escape hatch for read-your-writes.

Symptom Identification

These signals mean it is time to add replica routing to a Prisma client:

• The primary saturates on SELECT throughput while a provisioned replica idles — the Prisma client has one datasource url and no routing layer.
• Connection-pool exhaustion surfaces as Timed out fetching a new connection from the connection pool because reads and writes compete for the same connection_limit.
• Hand-rolled routing — two PrismaClient instances, one per URL, chosen manually at call sites — leads to inconsistent usage and doubled pool footprints that are hard to reason about.
• Read-your-writes bugs: prisma.order.create() succeeds, the follow-up prisma.order.findUnique() reads a replica that has not yet applied the insert, and the UI shows nothing — a direct consequence of replication lag.
• After adding a replica by hand, a $transaction accidentally lands on the read-only replica and fails with cannot execute INSERT in a read-only transaction.

Root Cause Analysis

A stock PrismaClient connects to exactly one database through the datasource url in your schema. Every query — read or write — uses that single connection pool, so there is no path to a replica without either running multiple clients by hand or adding a routing layer. Prisma’s official answer is @prisma/extension-read-replicas, a client extension that wraps PrismaClient and transparently splits traffic by operation type.

The extension classifies each Prisma operation. Model reads (findMany, findFirst, findUnique, count, aggregate, groupBy) and the raw read helper $queryRaw are dispatched to a replica connection. Model writes (create, update, delete, upsert, createMany, updateMany, deleteMany), the raw write helpers $executeRaw/$executeRawUnsafe, and every $transaction block are dispatched to the primary. This is Prisma’s implementation of the operation-level classification described in the parent guide on ORM middleware for automatic query routing — but done for you, at the client boundary, rather than hand-written.

Two design consequences follow. First, because a transaction can contain writes and must run on one consistent connection, the extension pins entire transactions to the primary — you cannot split a transaction’s reads onto a replica. Second, replica selection is random with no lag awareness: with multiple replica URLs, each read picks one at random, so a lagging replica can still serve stale data unless you exclude it upstream. The escape hatch for correctness-sensitive reads is prisma.$primary(), which forces a single query back onto the write connection.

Architecture: Extension Routing at the Client Boundary

Step-by-Step Resolution

Step 1: Install the extension

Add the extension package to a project already using @prisma/client.

npm install @prisma/extension-read-replicas

Keep your schema.prisma datasource pointing at the primary — the extension supplies replica URLs at runtime, not in the schema.

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")   // primary
}

Inline verification: npm ls @prisma/extension-read-replicas shows the installed version, and DATABASE_URL resolves to the primary host.

Step 2: Extend the client with replica URLs

Wrap PrismaClient with readReplicas. Pass a single replica URL, or an array for several replicas.

// db.ts
import { PrismaClient } from '@prisma/client'
import { readReplicas } from '@prisma/extension-read-replicas'

export const prisma = new PrismaClient().$extends(
  readReplicas({
    url: process.env.REPLICA_URL!,   // e.g. postgresql://app_ro@replica-lb.internal:5432/app
  })
)

The extended client keeps the full Prisma API. From here, read operations route to the replica and writes to the primary with no further changes at call sites.

Inline verification: await prisma.$queryRaw\SELECT pg_is_in_recovery()`returnstrue(served by the replica); a raw call through$primary()returnsfalse`.

Step 3: Let the extension auto-route reads and writes

No call-site changes are needed. The extension inspects the operation and dispatches it.

// Reads -> replica automatically
const users = await prisma.user.findMany({ where: { active: true } })
const count = await prisma.order.count()
const report = await prisma.$queryRaw`SELECT sum(total) FROM "Order"`

// Writes -> primary automatically
const created = await prisma.user.create({ data: { email: 'a@b.com' } })
await prisma.order.updateMany({ where: { status: 'draft' }, data: { status: 'sent' } })
await prisma.$executeRaw`UPDATE "Order" SET archived = true WHERE created_at < now() - interval '1 year'`

findMany, findFirst, findUnique, count, aggregate, groupBy, and $queryRaw are reads. create, update, delete, upsert, the *Many variants, $executeRaw, and $executeRawUnsafe are writes.

Inline verification: log application_name per connection; findMany appears on the replica backend, create on the primary backend.

Step 4: Force reads onto the primary with $primary()

For read-your-writes, call $primary() to run a single read on the write connection right after a mutation.

// Write, then read the same row back consistently
const order = await prisma.order.create({ data: { userId, total } })

// The replica may not have applied the insert yet — read from the primary
const confirmed = await prisma.$primary().order.findUnique({
  where: { id: order.id },
})

$primary() returns a client scoped to the primary for that call chain only; subsequent normal reads return to routing across replicas. Keep the $primary() window as short as the write’s causal requirement — do not blanket every read with it, or you lose the read-scaling benefit entirely.

Inline verification: await prisma.$primary().$queryRaw\SELECT pg_is_in_recovery()`returnsfalse`.

Step 5: Add multiple replicas

Pass an array. Each read picks one replica at random.

// db.ts
export const prisma = new PrismaClient().$extends(
  readReplicas({
    url: [
      process.env.REPLICA_1_URL!,
      process.env.REPLICA_2_URL!,
    ],
  })
)

There is no lag awareness or weighting in the extension itself. If you need to exclude a lagging replica, front the replicas with a single load-balancer or proxy VIP that health-checks lag, and give the extension that one URL — the exclusion logic then lives in the proxy, as described in routing queries based on data freshness requirements.

Inline verification: over many reads, inet_server_addr() sampled via $queryRaw shows both replica IPs appearing roughly evenly.

Step 6: Size the connection pools per endpoint

Each URL gets its own Prisma connection pool. Set connection_limit on each so reads and writes no longer compete.

// db.ts — explicit per-endpoint pool sizing via URL query params
export const prisma = new PrismaClient({
  datasources: { db: { url: `${process.env.DATABASE_URL}?connection_limit=10` } },
}).$extends(
  readReplicas({
    url: `${process.env.REPLICA_URL}?connection_limit=20`,
  })
)

Because the primary and replicas have separate pools, size the primary pool for write concurrency and the replica pool for read fan-out. Keep the sum of all pools below each backend’s max_connections budget, exactly as you would when sizing a connection pool for read replicas.

Inline verification: SELECT count(*) FROM pg_stat_activity WHERE usename = 'app' on the replica stays at or below the replica connection_limit.

Configuration Snippet

A complete, production-shaped module with multiple replicas and per-pool sizing:

// db.ts
import { PrismaClient } from '@prisma/client'
import { readReplicas } from '@prisma/extension-read-replicas'

const primaryUrl = `${process.env.DATABASE_URL}?connection_limit=10&pool_timeout=10`

export const prisma = new PrismaClient({
  datasources: { db: { url: primaryUrl } },
}).$extends(
  readReplicas({
    url: [
      `${process.env.REPLICA_1_URL}?connection_limit=20`,
      `${process.env.REPLICA_2_URL}?connection_limit=20`,
    ],
  })
)

// Helper for read-your-writes flows
export async function createThenRead<T>(
  write: (c: typeof prisma) => Promise<{ id: T }>,
  read: (c: ReturnType<typeof prisma.$primary>, id: T) => Promise<unknown>,
) {
  const { id } = await write(prisma)
  return read(prisma.$primary(), id)   // consistent read on the primary
}

Verification and Rollback

Confirm the split at runtime:

// Reads land on a standby
const onReplica = await prisma.$queryRaw<{ pg_is_in_recovery: boolean }[]>`
  SELECT pg_is_in_recovery()`
console.assert(onReplica[0].pg_is_in_recovery === true)

// $primary() lands on the primary
const onPrimary = await prisma.$primary().$queryRaw<{ pg_is_in_recovery: boolean }[]>`
  SELECT pg_is_in_recovery()`
console.assert(onPrimary[0].pg_is_in_recovery === false)

Roll back by removing the $extends(readReplicas(...)) wrapper — export the plain PrismaClient, and all traffic returns to the primary with no call-site changes:

// db.ts — kill switch during a replica incident
export const prisma = new PrismaClient()

Because every read call still uses the standard Prisma API, unwrapping the extension is a one-line, zero-risk revert. Redeploy, and reads collapse onto the primary until replicas recover.

Edge Cases and Gotchas

Transactions always run on the primary

Both interactive (prisma.$transaction(async (tx) => { ... })) and sequential (prisma.$transaction([...])) transactions run entirely on the primary, including any reads inside them. You cannot route a transaction’s reads to a replica. If a bulk read-only report is running inside a $transaction purely for snapshot consistency, move it out of the transaction so it can use a replica — or accept the primary load. This is the correct behavior for write safety but surprises teams expecting read fan-out inside transactions.

No lag awareness in replica selection

The extension chooses replicas uniformly at random with no knowledge of each replica’s replication lag. A replica that is 30 seconds behind is just as likely to be picked as a fresh one. For freshness-sensitive workloads, either front the replicas with a lag-aware proxy behind one URL, or reach for $primary() on the specific reads that cannot tolerate staleness. Do not assume the extension protects you from stale reads.

$primary() scope is per call, not per request

$primary() returns a primary-scoped client for the chained operation only. It does not set a request-wide sticky mode, so a later prisma.user.findMany() in the same request still goes to a replica. For request-scoped read-your-writes across many reads, thread a $primary()-scoped client through your request context deliberately, mirroring the sticky-after-write patterns used elsewhere.

FAQ

Related

← Back to ORM Middleware for Automatic Query Routing

• Read/Write Splitting in SQLAlchemy with Multiple Binds — the Python equivalent, where you write the routing logic in get_bind() yourself.
• Configuring Django Database Routers for Read Replicas — how Django expresses the same split through DATABASE_ROUTERS.
• Connection Pool Architecture for Read Replicas — sizing the per-endpoint pools the extension creates so you stay within each backend’s connection budget.
• Routing Queries Based on Data Freshness Requirements — how to add the lag awareness the extension lacks, using a proxy in front of the replica URL.