> Complete content index: https://sirannon.sondelali.com/docs/0.3/llms.txt

# Distributed replication

> Replicate a SQLite database across nodes over gRPC, bootstrap new replicas automatically, choose write concerns, and fail over through etcd.

URL: https://sirannon.sondelali.com/docs/0.3/distributed-replication
Section: Distributed
Version: 0.3 (@delali/sirannon-db@0.3.4, supported)

Sirannon replicates a SQLite database from one primary node to its replicas, where each node keeps its own database file. The primary sends checksummed batches of changes to the replicas over gRPC, so each node needs only a disk of its own.

The primary accepts every write, while each replica serves reads and can forward a write to the primary. In coordinator mode, the nodes store the group's authority in etcd, which lets the group fail over automatically. Every change carries a <Tooltip tip="A Hybrid Logical Clock combines a node's physical clock with a logical counter, which gives events across nodes timestamps that respect cause and effect even when the clocks disagree.">Hybrid Logical Clock</Tooltip> timestamp, which gives every node the same order for the changes even when the node clocks disagree. This page builds `primary.ts` and `replica.ts` on one machine. Every code block contains the whole file, with the lines that changed marked.

## Install the transport packages

The gRPC transport imports three packages that Sirannon lists as optional peer dependencies, so install them alongside the core package and its driver:

```bash pm
pnpm add -E @delali/sirannon-db better-sqlite3
pnpm add -E @grpc/grpc-js @bufbuild/protobuf grpc-health-check
```

## Create the certificates the nodes present to each other

The nodes authenticate each other over <Tooltip tip="Mutual TLS means both sides of a connection present certificates, so the server verifies the client and the client verifies the server.">mutual TLS</Tooltip>, so each node needs a key, a certificate, and the certificate of the authority that signed them. Enter these commands in the directory that you start the nodes from, so that the `./certs` and `./data` paths in the files below resolve. Keep the keys that they write out of your repository.

```bash
mkdir -p certs data

openssl req -x509 -newkey rsa:2048 -nodes -days 3650 \
  -subj "/CN=Sirannon replication CA" \
  -keyout certs/ca.key -out certs/ca.crt

for node in primary-us-east-1 replica-eu-west-1 replica-ap-south-1; do
  printf 'basicConstraints = CA:FALSE\nkeyUsage = digitalSignature, keyEncipherment\nextendedKeyUsage = serverAuth, clientAuth\nsubjectAltName = DNS:localhost\n' > "certs/$node.ext"
  openssl req -newkey rsa:2048 -nodes -subj "/CN=$node" \
    -keyout "certs/$node.key" -out "certs/$node.csr"
  openssl x509 -req -in "certs/$node.csr" -days 825 \
    -CA certs/ca.crt -CAkey certs/ca.key -CAcreateserial \
    -extfile "certs/$node.ext" -out "certs/$node.crt"
done
```

Each certificate's common name is the node's `nodeId`, because a node refuses the replication stream of a peer whose certificate common name differs from the `nodeId` that the peer announces. The `extendedKeyUsage` line marks each certificate for both `serverAuth` and `clientAuth`, since every node accepts connections from its peers as well as opening connections to them. The subject alternative name `localhost` matches the address that the nodes on this page dial, so replace it with each node's host name once you deploy the nodes to separate machines.

The loop also creates a certificate for `replica-ap-south-1`, the third node that you add for failover. `openssl verify -CAfile certs/ca.crt certs/primary-us-east-1.crt certs/replica-eu-west-1.crt certs/replica-ap-south-1.crt` prints `OK` for each of the three.

## Start the primary

`primary.ts` opens a writer connection to its database file and creates the two tables that the group replicates. It watches both tables with a `ChangeTracker`, because the tracker records changes only on a table that it watches. The engine, in turn, sends the replicas only the changes that the tracker records. Then `primary.ts` opens the same database through a `Sirannon` registry and starts a `ReplicationEngine` in the primary role, with a gRPC transport listening on port 4200.

```ts title="primary.ts"
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const dbPath = './data/orders.db'
const driver = betterSqlite3()

const writerConn = await driver.open(dbPath)
await writerConn.exec(`
  CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL DEFAULT 0);
  CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
`)

const tracker = new ChangeTracker()
await tracker.watch(writerConn, 'accounts')
await tracker.watch(writerConn, 'orders')

const sirannon = new Sirannon({ driver })
const db = await sirannon.open('orders', dbPath)

const transport = new GrpcReplicationTransport({
  host: '0.0.0.0',
  port: 4200,
  tlsCert: './certs/primary-us-east-1.crt',
  tlsKey: './certs/primary-us-east-1.key',
  tlsCaCert: './certs/ca.crt',
})

const engine = new ReplicationEngine(db, writerConn, {
  nodeId: 'primary-us-east-1',
  topology: new PrimaryReplicaTopology('primary'),
  transport,
  snapshotConnectionFactory: () => driver.open(dbPath, { readonly: true }),
  changeTracker: tracker,
})

await engine.start()

await engine.executeBatch('INSERT OR IGNORE INTO accounts (id, holder, balance) VALUES (?, ?, ?)', [
  [1, 'Helen Carter', 5000],
  [2, 'Daniel Park', 2000],
  [3, 'Carlos Mendes', 7500],
])
```

The engine applies and records changes through the writer connection. `snapshotConnectionFactory` opens the read-only connection that the primary streams a first sync from, so writes continue while a new replica copies the database. `executeBatch` inserts the three accounts that the replica copies in the next section. Because the statement uses `INSERT OR IGNORE`, SQLite skips those rows when you start the file again.

Start the primary with `npx tsx primary.ts`, and keep that terminal open.

## Point a replica at the primary

`replica.ts` opens a database file of its own and dials the primary at the address in `transportConfig.endpoints`. Its transport takes only the certificate paths, because a replica outside coordinator mode listens on no port. `writeForwarding: true` makes the replica send a write that it receives on to the primary. The loop at the end polls the engine every 500 milliseconds until the first sync reaches `ready`, after which the file prints the sync state and the accounts that the replica copied.

```ts title="replica.ts"
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const replicaPath = './data/orders-replica.db'
const driver = betterSqlite3()

const replicaConn = await driver.open(replicaPath)
const replicaTracker = new ChangeTracker()

const sirannon = new Sirannon({ driver })
const replicaDb = await sirannon.open('orders', replicaPath)

const replicaTransport = new GrpcReplicationTransport({
  tlsCert: './certs/replica-eu-west-1.crt',
  tlsKey: './certs/replica-eu-west-1.key',
  tlsCaCert: './certs/ca.crt',
})

const replicaEngine = new ReplicationEngine(replicaDb, replicaConn, {
  nodeId: 'replica-eu-west-1',
  topology: new PrimaryReplicaTopology('replica'),
  transport: replicaTransport,
  transportConfig: { endpoints: ['localhost:4200'] },
  writeForwarding: true,
  changeTracker: replicaTracker,
})

await replicaEngine.start()

for (;;) {
  const { syncState } = replicaEngine.status()
  if (syncState) {
    const { phase, completedTables, totalTables } = syncState
    console.log(`sync ${phase}: ${completedTables.length}/${totalTables} tables copied`)
    if (phase === 'ready') break
  }
  await new Promise((resolve) => setTimeout(resolve, 500))
}

console.log(replicaEngine.status().syncState)
console.log(await replicaEngine.query('SELECT id, holder, balance FROM accounts ORDER BY id'))
```

Start `replica.ts` in a second terminal with `npx tsx replica.ts`. On its first start, it printed this:

```ts result
sync pending: 0/0 tables copied
sync ready: 2/2 tables copied
{
  phase: 'ready',
  sourcePeerId: 'primary-us-east-1',
  snapshotSeq: 3n,
  completedTables: [ 'accounts', 'orders' ],
  totalTables: 2,
  startedAt: 1789490213522,
  error: null
}
[
  { id: 1, holder: 'Helen Carter', balance: 5000 },
  { id: 2, holder: 'Daniel Park', balance: 2000 },
  { id: 3, holder: 'Carlos Mendes', balance: 7500 }
]
```

## First sync

A new replica copies the whole database before it applies any later change. The primary streams the schema and each table in checksummed batches and then sends a manifest, which the replica checks its copy against. After that, the replica applies the writes that reached the primary during the copy. Until its phase reaches `ready`, the replica refuses reads.

The engine moves `phase` from `pending` through `syncing` and `catching-up` to `ready`. The primary copied both small tables between two polls of the loop, which is why the loop printed `pending` and then `ready` with no phase between them.

`snapshotSeq` is the change-log position at which the primary took the copy, while `startedAt` is the time when the sync started, in milliseconds since the Unix epoch. Both values therefore differ on your machine. The file prints the state with `console.log`, because `JSON.stringify` cannot serialise the `bigint` in `snapshotSeq`.

A replica that already holds a copy skips the sync when you start it again, so it prints `sync ready: 0/0 tables copied` and a `null` source. For a database too large to copy over the network, copy the file yourself, and then start the replica with `initialSync: false` and with `resumeFromSeq` set to the change-log position at which you copied the file.

## Write concerns

`writeConcern` on `engine.execute` sets how many nodes must acknowledge a write before the call returns. It takes the levels `'local'`, `'majority'`, and `'all'`. When too few nodes acknowledge a majority write before `timeoutMs` passes, the call fails with `WRITE_CONCERN_ERROR`. `primary.ts` therefore waits in a loop until the replica connects, and only then does it place the order.

```ts title="primary.ts"
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const dbPath = './data/orders.db'
const driver = betterSqlite3()

const writerConn = await driver.open(dbPath)
await writerConn.exec(`
  CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL DEFAULT 0);
  CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
`)

const tracker = new ChangeTracker()
await tracker.watch(writerConn, 'accounts')
await tracker.watch(writerConn, 'orders')

const sirannon = new Sirannon({ driver })
const db = await sirannon.open('orders', dbPath)

const transport = new GrpcReplicationTransport({
  host: '0.0.0.0',
  port: 4200,
  tlsCert: './certs/primary-us-east-1.crt',
  tlsKey: './certs/primary-us-east-1.key',
  tlsCaCert: './certs/ca.crt',
})

const engine = new ReplicationEngine(db, writerConn, {
  nodeId: 'primary-us-east-1',
  topology: new PrimaryReplicaTopology('primary'),
  transport,
  snapshotConnectionFactory: () => driver.open(dbPath, { readonly: true }),
  changeTracker: tracker,
})

await engine.start()

await engine.executeBatch('INSERT OR IGNORE INTO accounts (id, holder, balance) VALUES (?, ?, ?)', [
  [1, 'Helen Carter', 5000],
  [2, 'Daniel Park', 2000],
  [3, 'Carlos Mendes', 7500],
])
// [!code ++:10]

while (!engine.status().peers.some((peer) => peer.connected)) {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

const placed = await engine.execute('INSERT INTO orders (total) VALUES (?)', [4999], {
  writeConcern: { level: 'majority', timeoutMs: 5000 },
})

console.log(placed)
```

Leave `replica.ts` running, stop `primary.ts`, and start the new version. While the primary is down, the replica process stays up and dials it again, first after 250 milliseconds and then after twice the previous wait, up to 5,000 milliseconds, so it reconnects once the new primary listens. The primary printed this:

```ts result
{ changes: 1, lastInsertRowId: 1 }
```

In static mode, `engine.execute` returns after the local commit when you pass no write concern. In coordinator mode, however, it waits for `'majority'`.

A replica that forwards a write sends your write concern along with it, and the primary waits for that concern before it answers, so the replica's `execute` returns only once enough nodes hold the write. `engine.executeBatch` commits every parameter set in one transaction on the primary, whether a replica forwarded it or not, and it waits once for the whole batch. The replica stops waiting after the transport's `forwardDeadlineMs`, 30,000 milliseconds by default, whatever `timeoutMs` you set.

When the primary refuses a forwarded write with a Sirannon code, such as `STALE_PRIMARY` or `WRITE_CONCERN_ERROR`, the replica raises a `SirannonError` with that same code. Match on `err.code` there, because the error is not an instance of `WriteConcernError` or `StalePrimaryError`. A write that SQLite rejects, such as one that repeats a primary key, fails with `QUERY_ERROR` on the primary, and a replica that forwarded it receives that same code. A write to one of Sirannon's own `_sirannon` tables fails with `FORBIDDEN_SQL` on every node.

## Read concern

A read concern sets how current a read must be. A node in coordinator mode enforces it and treats a read that names no level as `'majority'`. A node in static mode ignores it, which is why the linearizable read below succeeds on these two nodes.

```ts title="primary.ts"
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const dbPath = './data/orders.db'
const driver = betterSqlite3()

const writerConn = await driver.open(dbPath)
await writerConn.exec(`
  CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL DEFAULT 0);
  CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
`)

const tracker = new ChangeTracker()
await tracker.watch(writerConn, 'accounts')
await tracker.watch(writerConn, 'orders')

const sirannon = new Sirannon({ driver })
const db = await sirannon.open('orders', dbPath)

const transport = new GrpcReplicationTransport({
  host: '0.0.0.0',
  port: 4200,
  tlsCert: './certs/primary-us-east-1.crt',
  tlsKey: './certs/primary-us-east-1.key',
  tlsCaCert: './certs/ca.crt',
})

const engine = new ReplicationEngine(db, writerConn, {
  nodeId: 'primary-us-east-1',
  topology: new PrimaryReplicaTopology('primary'),
  transport,
  snapshotConnectionFactory: () => driver.open(dbPath, { readonly: true }),
  changeTracker: tracker,
})

await engine.start()

await engine.executeBatch('INSERT OR IGNORE INTO accounts (id, holder, balance) VALUES (?, ?, ?)', [
  [1, 'Helen Carter', 5000],
  [2, 'Daniel Park', 2000],
  [3, 'Carlos Mendes', 7500],
])

while (!engine.status().peers.some((peer) => peer.connected)) {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

const placed = await engine.execute('INSERT INTO orders (total) VALUES (?)', [4999], {
  writeConcern: { level: 'majority', timeoutMs: 5000 },
})

console.log(placed)
// [!code ++:6]

const rows = await engine.query('SELECT id, total FROM orders WHERE id = ?', [placed.lastInsertRowId], {
  readConcern: { level: 'linearizable' },
})

console.log(rows)
```

Restart `primary.ts` while `replica.ts` keeps running, as in the previous section. The primary printed this:

```ts result
{ changes: 1, lastInsertRowId: 2 }
[ { id: 2, total: 4999 } ]
```

At each higher level, the node must prove more before it answers.

| Level | What the node must prove |
| --- | --- |
| `local` | The node proves nothing, because the read returns its local state, which a later failover can quarantine. |
| `majority` | The node is in the in-sync set and is neither draining nor repairing. |
| `linearizable` | The node is the current primary, and it confirms its authority for the term with the coordinator before it reads. |

A read that cannot meet its level fails with a code that names the reason. A node refuses a majority read with `NODE_NOT_IN_SYNC` when it is outside the in-sync set, and with `READ_CONCERN_ERROR` when it is draining or repairing. It refuses a linearizable read with `STALE_PRIMARY` when it is not the current primary. A majority or a linearizable read fails with `COORDINATOR_UNAVAILABLE` when the node cannot load the group state from the coordinator.

`GET /db/:id/cluster` lists the levels that each node serves at that moment, which the [topology-aware client](/docs/0.3/topology-routing) reads to pick a node for each read.

## Conflict resolution

One primary per replication group puts the ordinary writes in order, yet a node can still find the target row present when it applies a change. The node then passes the local and the incoming versions of that row to a resolver.

| Strategy | Class | Behaviour |
| --- | --- | --- |
| Last-Writer-Wins | `LWWResolver` | It applies a remote delete whatever the timestamps say, so a delete wins over a concurrent update. For any other change, it keeps the version with the higher HLC timestamp and breaks a tie by node ID. |
| Field-Level Merge | `FieldMergeResolver` | It merges the columns that only one side changed and uses per-column HLC metadata for a column that both sides changed. Without column metadata, it resolves the whole row by last-writer-wins. |
| Primary Wins | `PrimaryWinsResolver` | It keeps the version that the configured primary node wrote, and it resolves any other conflict by last-writer-wins. |

The engine resolves every table by last-writer-wins unless you choose another resolver. Pass `defaultConflictResolver` in the engine configuration to change it for every table, or `conflictResolvers` to give named tables a resolver of their own. A custom resolver is a class with a `resolve(ctx)` method.

## Coordinator-backed failover

In coordinator mode, the nodes store the primary's authority, each node's session, the group state, and the in-sync set in etcd, so a surviving node can still read which node may write after the primary fails. The etcd adapter imports `etcd3`, so install it:

```bash pm
pnpm add -E etcd3
```

You change both files in this step. Each node now listens on a gRPC port of its own and dials the other nodes, because the group can promote any node to primary. Each node also creates and watches the replicated tables when it starts, as the primary did. The replica waits until the group counts it in sync before it reads, because a node in coordinator mode refuses a majority read with `NODE_NOT_IN_SYNC` until then.

The three `credentials` files come from your etcd cluster, which has a certificate authority of its own. Each node checks the certificate that etcd presents against `etcd-ca.crt`, and etcd accepts `orders-node.crt` with `orders-node.key` as the client certificate of these nodes. Put the client URLs of that cluster in `hosts`; this page uses an etcd on `localhost`.

<CodeGroup defaultTab="primary.ts">

```ts title="primary.ts"
// [!code ++:1]
import { readFileSync } from 'node:fs'
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
// [!code ++:1]
import { createEtcdCoordinator } from '@delali/sirannon-db/replication/coordinator/etcd'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const dbPath = './data/orders.db'
const driver = betterSqlite3()

const writerConn = await driver.open(dbPath)
await writerConn.exec(`
  CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL DEFAULT 0);
  CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
`)

const tracker = new ChangeTracker()
await tracker.watch(writerConn, 'accounts')
await tracker.watch(writerConn, 'orders')

const sirannon = new Sirannon({ driver })
const db = await sirannon.open('orders', dbPath)

const transport = new GrpcReplicationTransport({
  host: '0.0.0.0',
  port: 4200,
  tlsCert: './certs/primary-us-east-1.crt',
  tlsKey: './certs/primary-us-east-1.key',
  tlsCaCert: './certs/ca.crt',
})
// [!code ++:10]

const coordinator = createEtcdCoordinator({
  hosts: ['https://localhost:2379'],
  keyPrefix: '/sirannon/orders',
  credentials: {
    rootCertificate: readFileSync('./certs/etcd-ca.crt'),
    privateKey: readFileSync('./certs/orders-node.key'),
    certChain: readFileSync('./certs/orders-node.crt'),
  },
})

const engine = new ReplicationEngine(db, writerConn, {
  nodeId: 'primary-us-east-1',
  topology: new PrimaryReplicaTopology('primary'),
  transport,
  // [!code ++:2]
  transportConfig: { endpoints: ['localhost:4201', 'localhost:4202'] },
  writeForwarding: true,
  snapshotConnectionFactory: () => driver.open(dbPath, { readonly: true }),
  changeTracker: tracker,
  // [!code ++:8]
  coordinator: {
    clusterId: 'commerce-production',
    groupId: 'orders',
    endpoint: 'http://localhost:9876/db/orders',
    coordinator,
    votingDataBearingNodeIds: ['primary-us-east-1', 'replica-eu-west-1', 'replica-ap-south-1'],
    controller: true,
  },
})

await engine.start()
// [!code ++:4]

while (!engine.status().health.canWrite) {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

await engine.executeBatch('INSERT OR IGNORE INTO accounts (id, holder, balance) VALUES (?, ?, ?)', [
  [1, 'Helen Carter', 5000],
  [2, 'Daniel Park', 2000],
  [3, 'Carlos Mendes', 7500],
])

while (!engine.status().peers.some((peer) => peer.connected)) {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

const placed = await engine.execute('INSERT INTO orders (total) VALUES (?)', [4999], {
  writeConcern: { level: 'majority', timeoutMs: 5000 },
})

console.log(placed)

const rows = await engine.query('SELECT id, total FROM orders WHERE id = ?', [placed.lastInsertRowId], {
  readConcern: { level: 'linearizable' },
})

console.log(rows)
```

```ts title="replica.ts"
// [!code ++:1]
import { readFileSync } from 'node:fs'
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
// [!code ++:1]
import { createEtcdCoordinator } from '@delali/sirannon-db/replication/coordinator/etcd'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const replicaPath = './data/orders-replica.db'
const driver = betterSqlite3()

const replicaConn = await driver.open(replicaPath)
// [!code ++:5]
await replicaConn.exec(`
  CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL DEFAULT 0);
  CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
`)

const replicaTracker = new ChangeTracker()
// [!code ++:2]
await replicaTracker.watch(replicaConn, 'accounts')
await replicaTracker.watch(replicaConn, 'orders')

const sirannon = new Sirannon({ driver })
const replicaDb = await sirannon.open('orders', replicaPath)

const replicaTransport = new GrpcReplicationTransport({
  // [!code ++:2]
  host: '0.0.0.0',
  port: 4201,
  tlsCert: './certs/replica-eu-west-1.crt',
  tlsKey: './certs/replica-eu-west-1.key',
  tlsCaCert: './certs/ca.crt',
})
// [!code ++:10]

const coordinator = createEtcdCoordinator({
  hosts: ['https://localhost:2379'],
  keyPrefix: '/sirannon/orders',
  credentials: {
    rootCertificate: readFileSync('./certs/etcd-ca.crt'),
    privateKey: readFileSync('./certs/orders-node.key'),
    certChain: readFileSync('./certs/orders-node.crt'),
  },
})

const replicaEngine = new ReplicationEngine(replicaDb, replicaConn, {
  nodeId: 'replica-eu-west-1',
  topology: new PrimaryReplicaTopology('replica'),
  transport: replicaTransport,
  // [!code --:1]
  transportConfig: { endpoints: ['localhost:4200'] },
  // [!code ++:1]
  transportConfig: { endpoints: ['localhost:4200', 'localhost:4202'] },
  writeForwarding: true,
  // [!code ++:1]
  snapshotConnectionFactory: () => driver.open(replicaPath, { readonly: true }),
  changeTracker: replicaTracker,
  // [!code ++:8]
  coordinator: {
    clusterId: 'commerce-production',
    groupId: 'orders',
    endpoint: 'http://localhost:9877/db/orders',
    coordinator,
    votingDataBearingNodeIds: ['primary-us-east-1', 'replica-eu-west-1', 'replica-ap-south-1'],
    controller: true,
  },
})

await replicaEngine.start()

for (;;) {
  const { syncState } = replicaEngine.status()
  if (syncState) {
    const { phase, completedTables, totalTables } = syncState
    console.log(`sync ${phase}: ${completedTables.length}/${totalTables} tables copied`)
    if (phase === 'ready') break
  }
  await new Promise((resolve) => setTimeout(resolve, 500))
}
// [!code ++:4]

while (replicaEngine.status().health.reason !== 'in-sync') {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

console.log(replicaEngine.status().syncState)
console.log(await replicaEngine.query('SELECT id, holder, balance FROM accounts ORDER BY id'))
```

</CodeGroup>

Automatic failover needs at least three voting data-bearing nodes, because the survivor of a two-node group cannot prove a majority. `votingDataBearingNodeIds` therefore names a third node, `replica-ap-south-1`. Create its file as a copy of `replica.ts` that changes six values: the node ID and the certificate paths to `replica-ap-south-1`, the database path to `./data/orders-replica-ap-south-1.db`, the gRPC port to 4202, the endpoints to `localhost:4200` and `localhost:4201`, and the client endpoint to port 9878.

Start etcd first, then `primary.ts`, and then both replicas. In a sample run on one machine, the primary printed its new order, while each replica printed the three accounts once the group counted it in sync. When the primary's process stopped, the controller made `replica-eu-west-1` the primary for term 2.

`engine.stop()` deregisters the node's session in etcd, so a node that you stop that way can start again at once. A node whose process is killed, however, leaves that session in etcd until its lease expires, and that wait is `sessionTtlMs`, which defaults to 10 seconds. A start inside that window fails with `COORDINATOR_UNAVAILABLE`.

Stop each engine from a signal handler so that a redeploy never waits out a lease. `engine.stop()` releases the session before it closes the transport, and it forces the gRPC close once 2,000 milliseconds pass. The call that releases the session has no deadline unless you set `defaultCallTimeoutMs` on the coordinator, however, so arm a backstop that exits the process anyway. Give the backstop longer than those 2,000 milliseconds, so that it never cuts a graceful close short.

```ts
const stopCleanly = async (): Promise<void> => {
  setTimeout(() => process.exit(0), 5000)
  await engine.stop()
  await sirannon.shutdown()
  process.exit(0)
}

process.once('SIGINT', stopCleanly)
process.once('SIGTERM', stopCleanly)
```

Add those lines to the end of `primary.ts`, and to `replica.ts` with `replicaEngine` in place of `engine`.

A primary accepts a write only while it can prove its authority through its term and its lease. Replicas refuse a stale batch or a stale forwarded write, and the controller promotes only a replica from the in-sync set. Only the node that holds the controller lease acts as the controller. Every other node that sets `controller` watches that lease in the coordinator and bids for it as soon as the lease has no holder. When the coordinator offers no such watch, the node bids every `tickIntervalMs`. With fewer than three voting nodes, no survivor can prove a majority, so the group refuses writes and no <Tooltip tip="A split brain happens when two nodes each believe they are the primary and both accept writes, leaving the copies with conflicting histories.">split brain</Tooltip> can form.

<Note>
  Sirannon quarantines a returning former primary that holds writes only it committed, and an operator must rebuild or
  restore that node before it rejoins the group.
</Note>

## Schema changes

A node applies a replicated <Tooltip tip="DDL (data definition language) covers the statements that change a database's schema, such as CREATE TABLE and DROP INDEX.">DDL</Tooltip> statement only when the statement is `CREATE TABLE`, `ALTER TABLE ... ADD COLUMN`, `DROP TABLE`, `CREATE INDEX`, or `DROP INDEX`. The node refuses such a statement when it contains a semicolon, a `SELECT`, `ATTACH`, a Sirannon system table, or one of the functions `load_extension`, `readfile`, `writefile`, `randomblob`, `zeroblob`, and `fts3_tokenizer`.

## Report the cluster to clients and to your operators

A [topology-aware client](/docs/0.3/topology-routing) reads `GET /db/:id/cluster` to find the primary and the nodes that it may read from, while `GET /health/ready` reports the node's replication figures to your operators. The server answers both routes from functions that you pass to it. `toClusterStatusInfo` and `toReplicationStatusInfo` build what those functions return from the engine's status. The server imports uWebSockets.js, so install it as [Getting started](/docs/0.3/getting-started#grow-into-a-networked-service) shows before you add this step to `primary.ts`.

```ts title="primary.ts"
import { readFileSync } from 'node:fs'
// [!code --:1]
import { ChangeTracker, Sirannon } from '@delali/sirannon-db'
// [!code ++:1]
import { ChangeTracker, RequestDeniedError, readBearerToken, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
// [!code --:1]
import { PrimaryReplicaTopology, ReplicationEngine } from '@delali/sirannon-db/replication'
// [!code ++:6]
import {
  PrimaryReplicaTopology,
  ReplicationEngine,
  toClusterStatusInfo,
  toReplicationStatusInfo,
} from '@delali/sirannon-db/replication'
import { createEtcdCoordinator } from '@delali/sirannon-db/replication/coordinator/etcd'
// [!code ++:1]
import { createServer } from '@delali/sirannon-db/server'
import { GrpcReplicationTransport } from '@delali/sirannon-db/transport/grpc'

const dbPath = './data/orders.db'
const driver = betterSqlite3()

const writerConn = await driver.open(dbPath)
await writerConn.exec(`
  CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL DEFAULT 0);
  CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL);
`)

const tracker = new ChangeTracker()
await tracker.watch(writerConn, 'accounts')
await tracker.watch(writerConn, 'orders')

const sirannon = new Sirannon({ driver })
const db = await sirannon.open('orders', dbPath)

const transport = new GrpcReplicationTransport({
  host: '0.0.0.0',
  port: 4200,
  tlsCert: './certs/primary-us-east-1.crt',
  tlsKey: './certs/primary-us-east-1.key',
  tlsCaCert: './certs/ca.crt',
})

const coordinator = createEtcdCoordinator({
  hosts: ['https://localhost:2379'],
  keyPrefix: '/sirannon/orders',
  credentials: {
    rootCertificate: readFileSync('./certs/etcd-ca.crt'),
    privateKey: readFileSync('./certs/orders-node.key'),
    certChain: readFileSync('./certs/orders-node.crt'),
  },
})

const engine = new ReplicationEngine(db, writerConn, {
  nodeId: 'primary-us-east-1',
  topology: new PrimaryReplicaTopology('primary'),
  transport,
  transportConfig: { endpoints: ['localhost:4201', 'localhost:4202'] },
  writeForwarding: true,
  snapshotConnectionFactory: () => driver.open(dbPath, { readonly: true }),
  changeTracker: tracker,
  coordinator: {
    clusterId: 'commerce-production',
    groupId: 'orders',
    endpoint: 'http://localhost:9876/db/orders',
    coordinator,
    votingDataBearingNodeIds: ['primary-us-east-1', 'replica-eu-west-1', 'replica-ap-south-1'],
    controller: true,
  },
})

await engine.start()

while (!engine.status().health.canWrite) {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

await engine.executeBatch('INSERT OR IGNORE INTO accounts (id, holder, balance) VALUES (?, ?, ?)', [
  [1, 'Helen Carter', 5000],
  [2, 'Daniel Park', 2000],
  [3, 'Carlos Mendes', 7500],
])

while (!engine.status().peers.some((peer) => peer.connected)) {
  await new Promise((resolve) => setTimeout(resolve, 500))
}

const placed = await engine.execute('INSERT INTO orders (total) VALUES (?)', [4999], {
  writeConcern: { level: 'majority', timeoutMs: 5000 },
})

console.log(placed)

const rows = await engine.query('SELECT id, total FROM orders WHERE id = ?', [placed.lastInsertRowId], {
  readConcern: { level: 'linearizable' },
})

console.log(rows)
// [!code ++:26]

const operatorToken = process.env.SIRANNON_OPERATOR_TOKEN
if (!operatorToken) throw new Error('Set SIRANNON_OPERATOR_TOKEN before you start the node')

const endpoints = {
  'primary-us-east-1': 'http://localhost:9876/db/orders',
  'replica-eu-west-1': 'http://localhost:9877/db/orders',
  'replica-ap-south-1': 'http://localhost:9878/db/orders',
}

const server = createServer(sirannon, {
  port: 9876,
  authenticate: (ctx) => {
    if (readBearerToken(ctx) !== operatorToken) {
      throw new RequestDeniedError(401, 'UNAUTHORIZED', 'This node serves its operators only')
    }
    return { role: 'operator' }
  },
  authorizeClusterStatus: (ctx) => readBearerToken(ctx) === operatorToken,
  resolveExecutionTarget: (databaseId) => (databaseId === 'orders' ? engine : null),
  getReplicationStatus: () => toReplicationStatusInfo(engine.status()),
  getClusterStatus: (databaseId) =>
    databaseId === 'orders' ? toClusterStatusInfo(engine.status(), { databaseId, endpoints }) : null,
})

await server.listen()
```

Start `primary.ts` with `SIRANNON_OPERATOR_TOKEN` set in its environment, and start both replicas as before. `authenticate` refuses a request without the operator token with `401 UNAUTHORIZED`. Only an operator therefore reaches the cluster route, where `authorizeClusterStatus` checks the same token. `resolveExecutionTarget` sends each database request through the engine, which enforces primary authority and write concern on the server's routes.

In a sample run with all three nodes in sync, `GET /db/orders/cluster` named `primary-us-east-1` as the primary for term `1` and listed all three nodes under `readEndpoints`, each serving `local` and `majority` reads. `GET /health/ready` answered without the token, because the server calls no hook for the health routes, and it listed both replicas as in sync.

`readEndpoints` contains one entry for each voting node that is neither quarantined, draining, nor repairing. A node in the in-sync set serves `local` and `majority` reads, while a node that falls behind serves `local` reads alone. The reporting node also watches the node sessions in the coordinator, so a node drops out of `readEndpoints` once its process stops the engine or its session lease expires. A reporting node that has heard nothing from the coordinator within `sessionTtlMs` lists every voting node from the group's membership, which keeps the list intact through a coordinator outage. Sirannon records node IDs, while `toClusterStatusInfo` reads each client address from your `endpoints` map, so keep that map current. A node missing from the map appears with an empty endpoint.

The [configuration page](/docs/0.3/configuration#replication) lists the batch sizes, sync timeouts, flow control, and coordinator options, as does the package's own [configuration reference](https://github.com/assetcorp/sirannon-db/blob/main/docs/configuration.md). The [distributed entitlements example](https://github.com/assetcorp/sirannon-db/tree/main/packages/ts/examples/distributed-entitlements) starts a three-node coordinator-backed cluster with etcd, mutual TLS, and failure injection through Toxiproxy. A group that uses coordinator-backed failover can also give its [backups](/docs/0.3/backups-in-a-cluster) to one of its nodes, which reads the same membership from the same coordinator.
