Backups

A backup copies a database while that database keeps serving reads and writes. SQLite moves the pages in steps on the connection that writes, and it yields to the event loop between one step and the next, which lets a write commit in the gaps. This page builds one file, backup.ts, step by step. Ask what your runtime can copy, take one copy to a local file, read what that copy moved, and then put the same copy on a cron schedule that clears older files off disk.

The pages after this one cover sending a copy to storage you supply, the chain that follows a full copy with only what changed, rebuilding a database from a moment you name, and choosing which node of a replication group takes the backups.

backup refuses a destination that already holds a file, and every figure below assumes an empty directory, which is why backup.ts clears ./data before it opens anything.

What your runtime can copy

A backup needs two capabilities from the runtime underneath it. It needs SQLite's stepped backup interface, which moves an open database page by page, and it needs a driver that supplies a backup engine. db.backupCapabilities() reports whether this runtime has both. It reads what the driver declares and touches no disk, and you can call it at start-up and decide from the answer. Start backup.ts here.

import { mkdirSync, rmSync } from 'node:fs'
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
 
rmSync('./data', { recursive: true, force: true })
mkdirSync('./data', { recursive: true })
 
const db = await sirannon.open('orders', './data/orders.db')
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])
 
console.log(JSON.stringify(db.backupCapabilities(), null, 2))
 
await sirannon.shutdown()

The bun, wa-sqlite, and expo drivers each report fullCopy: false, since none of the three supplies a backup engine. A browser database and a device database therefore take their copies through the device sync snapshot, and a call to db.backup on one of them fails with BACKUP_UNSUPPORTED. The driver table states what each runtime reports.

Copy the database to a file

db.backup(destPath) writes the copy and returns a report of what it moved. The call takes the writer lock to start the copy and gives it back as soon as the first step finishes, because SQLite copies no pages while a transaction is already open on the source connection. It handles a transaction that opens once the copy is under way. Every write after that first step commits between two steps of the copy.

Replace the capability check with the copy itself.

import { mkdirSync, rmSync } from 'node:fs'
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
 
rmSync('./data', { recursive: true, force: true })
mkdirSync('./data', { recursive: true })
 
const db = await sirannon.open('orders', './data/orders.db')
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])
 
console.log(JSON.stringify(db.backupCapabilities(), null, 2)) 
const report = await db.backup('./data/backups/orders.db')
 
console.log(
  JSON.stringify(
    {
      databaseId: report.databaseId,
      sourcePath: report.sourcePath,
      pageCount: report.pageCount,
      pageSize: report.pageSize,
      byteLength: report.byteLength,
      restarts: report.restarts,
    },
    null,
    2,
  ),
)
 
await sirannon.shutdown()

The panel prints the fields that stay the same on every machine. The report also names runId, which Sirannon mints for each copy, and destPath, the absolute path Sirannon resolved, which returns the full path to a caller that passed a relative one. startedAt, finishedAt, and durationMs differ on every run.

restarts counts the times SQLite returned the copy to page one. SQLite does that whenever another connection writes to the source file or runs a RESTART or TRUNCATE checkpoint on it, and a restarts figure above zero therefore means another connection wrote to that file or checkpointed it.

Sirannon creates the parent directory of destPath recursively, and it refuses three kinds of path before it touches the filesystem. A path holding a null byte or a control character, a path holding a .. segment, and a path whose file already exists each fail with BACKUP_ERROR. The second of those reports Backup path must not contain directory traversal segments, and the third names the destination: Backup destination './data/backups/orders.db' already exists. Copy over a previous backup by removing that file yourself, so no call of Sirannon's can destroy a copy you still hold.

Repeat the copy on a schedule

One copy is a manual step. A service takes copies on a timetable and clears the older ones off disk, which is what db.scheduleBackup does. Swap the one-off call for a schedule, and take the file each finished copy produces through onBackup.

import { mkdirSync, rmSync } from 'node:fs'
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
 
rmSync('./data', { recursive: true, force: true })
mkdirSync('./data', { recursive: true })
 
const db = await sirannon.open('orders', './data/orders.db')
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])
 
const report = await db.backup('./data/backups/orders.db')
 
console.log(
  JSON.stringify(
    {
      databaseId: report.databaseId,
      sourcePath: report.sourcePath,
      pageCount: report.pageCount,
      pageSize: report.pageSize,
      byteLength: report.byteLength,
      restarts: report.restarts,
    },
    null,
    2,
  ),
)
 
await sirannon.shutdown()
db.scheduleBackup({
  cron: '*/5 * * * * *',
  destDir: './data/backups',
  maxFiles: 3,
  timezone: 'Europe/London',
  onBackup: (copy) => {
    console.log(`${copy.databaseId}: ${copy.pageCount} pages, ${copy.byteLength} bytes`)
  },
  onBackupTimeoutMs: 60_000,
  onError: (err) => console.error('Scheduled backup failed:', err.message),
})

Each finished copy reaches onBackup with the same report db.backup returns, and the line above therefore prints orders: 2 pages, 8192 bytes.

The cron expression here has six fields, and */5 * * * * * therefore fires every five seconds. A service uses the five-field form, where 0 */6 * * * takes a copy every six hours.

scheduleBackup returns nothing and holds no handle on the event loop, because its timer is unreferenced. The schedule therefore fires for as long as your own service keeps its process alive, and closing the database cancels it.

Each copy is named backup-{ISO timestamp}.db with the colons and full stops replaced by hyphens, which is how the rotation finds every copy the schedule wrote and leaves anything else in that directory alone.

onBackup is where you move the file somewhere durable. Sirannon waits for the promise it returns, and it clears the older files only once that promise settles, which leaves every copy your upload is still reading in place. A failure inside onBackup reaches onError, and Sirannon clears the older files either way.

onBackupTimeoutMs bounds that wait, because a callback left waiting on a socket that never answers would hold the schedule still for good. Past the deadline Sirannon reports a BACKUP_ERROR through onError naming the database and the file, and it counts that copy among the ones it may delete from then on. Your callback keeps running. Set the deadline longer than your slowest upload takes. A deadline of zero leaves the wait unbounded.

The scheduler checks the clock on a recurring tick and backfills nothing, which settles four cases:

• The scheduler skips a scheduled time the host slept through.
• A backward clock step repeats no copy until real time passes the last finished one.
• A daylight saving jump forward skips the missing hour.
• A time inside a repeated hour fires once.

When a copy cannot finish

Three codes separate the ways a copy stops, and each one points at a different cause.

• BACKUP_RESTARTED means SQLite returned the copy to page one more than three times, or the copy reached no new page across 256 steps. Another connection is writing to the source file, or the source is growing faster than the copy moves it. Route that connection's writes through Sirannon, or take the copy when the write rate is lower.
• BACKUP_STALLED means no step ran for 30 seconds. SQLite steps the copy once per turn of the event loop, and a caller that never lets the loop reach its timers therefore stalls the copy.
• BACKUP_ERROR covers everything else, including the three refused paths above.

Sirannon removes the partial file a failed copy was writing, which leaves no half-written database at that path. SQLite offers no way to cancel a copy already under way. Where a copy passes its stall deadline, Sirannon therefore reports the failure to you at once and removes the file only once that copy stops on its own.

db.backup uses those three limits at their defaults. db.backupTo, on the destinations page, takes restartLimit, stallTimeoutMs, noProgressStepLimit, and pagesPerStep as options, which lets a host with a slow disk or a busy source widen them.