Read replicas

ReplicaManager routes read queries across a set of replica databases, tracks
how far each one is behind the primary, and takes unhealthy replicas out of
rotation. It is a standalone component — you send queries to it explicitly, so
nothing is silently routed away from the primary behind your back.

import { ReplicaManager } from 'ts-prorm-orm';

const replicas = new ReplicaManager({
  enabled: true,
  defaultRoutingStrategy: 'lowest-lag',
  autoFailover: true,
  lagDetection: {
    enabled: true,
    interval: 5_000,          // check every 5s
    maxAcceptableLag: 2_000,  // 2s behind is too far
    maxFailures: 3,           // 3 failed checks in a row -> unhealthy
    timeout: 1_000,
  },
});

await replicas.addReplica({
  name: 'eu-west-1a',
  dialect: 'postgres',
  host: 'replica-1.internal', port: 5432,
  database: 'app', username: 'reader', password: process.env.PG_PASSWORD!,
  pool: { min: 2, max: 10 },
});

replicas.startLagMonitor();

const { rows } = await replicas.queryReplica("SELECT id, email FROM users WHERE status = 'active'");

Each replica gets its own connection pool, sized independently — a reporting
replica can hold a bigger pool than a latency-sensitive one.

queryReplica(sql, options) passes positional replacements or bind to
the driver as the statement's values, and timeout as the per-query timeout
(query_timeout on PostgreSQL):

await replicas.queryReplica('SELECT id, email FROM users WHERE status = $1', { replacements: ['active'] });

Named replacements ({ id: 1 } for :id) are refused with

named
replacements are not supported

. Before 4.9.2, queryReplica sent the SQL
text only, so a query with placeholders failed on the replica.

Replicas are PostgreSQL or MySQL/MariaDB: those are the dialects
ReplicaPool opens pools for and knows how to measure. Any other dialect
gets no pool, and its queries throw Unsupported dialect for replica <name>.

Setting it up end to end

Put the endpoints in the environment

DB_HOST=primary.internal
DB_NAME=app
DB_USER=app
DB_PASSWORD=change-me
REPLICA_HOSTS=replica-1.internal,replica-2.internal
REPLICA_USER=reader
REPLICA_PASSWORD=change-me-too

The reader account only needs SELECT; if it cannot write, a misrouted write
fails loudly instead of being silently lost.

Create the primary connection and the manager

import { Prorm, ReplicaManager } from 'ts-prorm-orm';

export const prorm = new Prorm({
  dialect: 'postgres',
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

export const replicas = new ReplicaManager({
  enabled: true,
  defaultRoutingStrategy: 'lowest-lag',
  lagDetection: { interval: 5_000, maxAcceptableLag: 2_000 },
});

for (const [i, host] of (process.env.REPLICA_HOSTS ?? '').split(',').filter(Boolean).entries()) {
  await replicas.addReplica({
    name: `replica-${i + 1}`,
    dialect: 'postgres',
    host,
    port: 5432,
    database: process.env.DB_NAME!,
    username: process.env.REPLICA_USER!,
    password: process.env.REPLICA_PASSWORD!,
    pool: { max: 10 },
  });
}

Start monitoring and wait for a healthy replica

A replica starts unhealthy and joins the rotation after its first lag
check comes back under maxAcceptableLag. Wait for that before sending
traffic, or accept that the first reads will go to whichever replica
round-robin picks:

import { once } from 'node:events';

await prorm.connect();
replicas.startLagMonitor();
await Promise.race([
  once(replicas, 'replica:healthy'),
  new Promise((resolve) => setTimeout(resolve, 10_000)), // start anyway, reads fall back to the primary
]);

Route reads and writes

Writes and anything inside a transaction go to prorm; reads that can
tolerate lag go to replicas. See Using it in an application.

Shut down both

process.on('SIGTERM', async () => {
  replicas.stopLagMonitor();
  await replicas.close();
  await prorm.close();
  process.exit(0);
});

Routing strategies

Strategy	Picks
`round-robin` (default)	The next replica in rotation, skipping known-unhealthy ones.
`least-connections`	The healthy replica with the fewest in-flight queries.
`lowest-lag`	The healthy replica with the smallest measured lag.

least-connections and lowest-lag both fall back to round-robin when no
replica has usable stats yet — before the first monitor cycle, for instance — so
selection never throws just because monitoring has not warmed up.

replicas.setRoutingStrategy('least-connections');
replicas.getRoutingStrategy();

Where each query goes

Everything inside a transaction goes to the primary, including its reads —
otherwise a read could miss a write made moments earlier in the same
transaction. Outside one, a read immediately after a write may land on a
replica that has not caught up.

Lag detection

checkLag(name) measures how far one replica trails, in milliseconds:

Dialect	Query	Source
MySQL / MariaDB	`SHOW SLAVE STATUS`	`Seconds_Behind_Master × 1000`
PostgreSQL	`SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000 AS lag`	replay timestamp
Anything else	—	returns `null` and logs a warning

startLagMonitor() runs that check for every replica on the configured
interval and maintains health. A check that returns a lag at or under
maxAcceptableLag marks the replica healthy; a lag over it, a null lag, or
takes it out of rotation at once (the replica answered, it is just behind). A
check that throws counts towards maxFailures, and only that many failures
in a row take a healthy replica out of rotation, so one dropped packet does
not; a good check resets the count. Before 4.9.2, maxFailures was stored
but not consulted, and one failed check was enough.
stopLagMonitor() ends the loop — call it during shutdown or the process will
not exit.

Pointing a replica at a port with nothing listening shows the loop from the
outside: every cycle logs Error checking lag for replica r1: to
console.error, and all-replicas-unhealthy is emitted once, when every
replica has been checked and none is healthy. replica:unhealthy does not
fire, because the replica was never healthy to begin with.

[!CAUTION]
On PostgreSQL the lag is now() - pg_last_xact_replay_timestamp(): the time
since the last replayed transaction. When the primary is idle, nothing is
replayed and the number keeps growing, so a perfectly caught-up replica can
cross maxAcceptableLag overnight and leave rotation. Pointed at the primary
itself, the function returns NULL and the node is always unhealthy. Keep a
little write traffic on the primary (a heartbeat row updated every few
seconds) if you rely on lag for health.

const lag = await replicas.checkLag('eu-west-1a'); // ms, or null
replicas.setLagDetectionConfig({ maxAcceptableLag: 5_000 });

Events

ReplicaManager is an EventEmitter. These are the hooks for metrics and
alerting:

Event	Fired when
`replica:added` / `replica:removed`	Membership changes.
`replica:healthy` / `replica:unhealthy`	A replica's health flips.
`replica:lag-detected`	Every successful lag measurement, with the value.
`replica:selected`	A query picked a replica.
`all-replicas-unhealthy`	Nothing is left to route to. Emitted once per outage; it can fire again after a replica has recovered. Before 4.9.2 it fired on every monitor cycle.
`monitor:started` / `monitor:stopped`	Monitor lifecycle.

replicas.on('replica:unhealthy', (name, status) =>
  logger.warn('replica out of rotation', { name, lag: status.lag, error: status.error }));

replicas.on('all-replicas-unhealthy', () => {
  useReplicas = false;   // send reads to the primary until one recovers
});
replicas.on('replica:healthy', () => {
  useReplicas = true;
});

With autoFailover: true and a master connection in the options, a
queryReplica() call made while every replica has been checked and none is
healthy goes to the master instead, and replica:selected names the master.
Without a master (or with autoFailover off), reads keep going to a replica
and the listener above is the failover. Before 4.9.2, autoFailover was
accepted but rerouted nothing.

const replicas = new ReplicaManager({
  autoFailover: true,
  master: { name: 'primary', dialect: 'postgres', host: 'primary.internal', port: 5432, database: 'app', username: 'app', password: process.env.DB_PASSWORD! },
  lagDetection: { enabled: true, interval: 5_000, maxAcceptableLag: 2_000, maxFailures: 3, timeout: 1_000 },
});

Inspecting state

Call	Returns
`getReplicaStats()`	`Map<name, ReplicaStatus>` — `healthy`, `lag`, `activeConnections`, `lastCheck`, `error`.
`getReplicaStat(name)`	One replica's status.
`getHealthyReplicas()`	Names currently in rotation.
`getReplicaCount()` / `getReplicaNames()`	Membership.
`isReplicationEnabled()`	Whether routing is on.
`getReplica(name)`	The underlying `ReplicaPool` — `getConnection()`, `releaseConnection()`, `query()`, `close()`.

Using it in an application

queryReplica() takes raw SQL and positional parameters. Model finders always
go to the connection they were defined on, so the usual shape is a small helper
that decides which side a read belongs on.

Parameterised reads

queryReplica(sql, { replacements }) is enough for most reads. When you need
the driver's own client (a cursor, COPY), getReplica(name) returns the
ReplicaPool, and getConnection() on it returns the driver's client (pg's
PoolClient for PostgreSQL). selectReplica() applies the routing strategy:

import type { PoolClient } from 'pg';

async function readReplica<T>(sql: string, params: unknown[]): Promise<T[]> {
  const { pool } = await replicas.selectReplica();
  const client = (await pool.getConnection()) as PoolClient;
  try {
    const result = await client.query(sql, params);
    return result.rows as T[];
  } finally {
    await pool.releaseConnection(client);
  }
}

const active = await readReplica<{ id: number; email: string }>(
  'SELECT id, email FROM users WHERE status = $1 AND created_at > $2',
  ['active', new Date(Date.now() - 86_400_000)],
);

With that, the helper that picks a side:

async function readOnly<T>(sql: string, params: unknown[]): Promise<T[]> {
  if (replicas.getHealthyReplicas().length === 0) {
    return (await prorm.query(sql, { replacements: params })).rows;   // primary
  }
  return readReplica<T>(sql, params);
}

selectReplica() counts the pick towards the replica's activeConnections
and only queryReplica() counts it back down (also when the query throws), so
combine this helper with round-robin or lowest-lag rather than
least-connections, or use queryReplica() with replacements instead.

Reading your own writes

After a user writes, their next reads should see the write. Pin that user's
reads to the primary for longer than the replication lag you tolerate:

const pinnedUntil = new Map<number, number>();
const PIN_MS = 5_000;

async function updateProfile(userId: number, bio: string) {
  await prorm.query('UPDATE profiles SET bio = $1 WHERE user_id = $2', {
    replacements: [bio, userId],
  });
  pinnedUntil.set(userId, Date.now() + PIN_MS);
}

async function loadProfile(userId: number) {
  const sql = 'SELECT * FROM profiles WHERE user_id = $1';
  if ((pinnedUntil.get(userId) ?? 0) > Date.now() || replicas.getHealthyReplicas().length === 0) {
    return (await prorm.query(sql, { replacements: [userId] })).rows;
  }
  return readReplica(sql, [userId]);
}

The pin must outlast maxAcceptableLag: a replica is allowed to be that far
behind while still in rotation.

Two rules worth keeping:

Never send writes to a replica. They are read-only; the driver will error,
and on some setups a write would be silently lost.

Do not read your own write from a replica. After a write, read from the
primary until you know replication has caught up — replication lag is real
even when it is small. checkLag() gives you the number to reason with.

For routing to genuinely different databases rather than replicas of one, see
Multiple databases.

Shutdown

replicas.stopLagMonitor();
await replicas.close();   // closes every replica pool

Connection pooling

Multiple databases

Logging — query and slow-query events

Setting it up end to end#

Put the endpoints in the environment#

Create the primary connection and the manager#

Start monitoring and wait for a healthy replica#

Route reads and writes#

Shut down both#

Routing strategies#

Where each query goes#

Lag detection#

Events#

Inspecting state#

Using it in an application#

Parameterised reads#

Reading your own writes#

Shutdown#

Related reading#