> Complete content index: https://sirannon.sondelali.com/llms.txt # Backups > Copy an open database to a file while it keeps serving writes, read what the copy moved, and repeat that copy on a cron schedule that clears older files off disk. URL: https://sirannon.sondelali.com/docs/backups Section: Backups Version: 0.3 (@delali/sirannon-db@0.3.0, latest) 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](/docs/backup-destinations), [the chain that follows a full copy with only what changed](/docs/backup-chains), [rebuilding a database from a moment you name](/docs/backup-restore), and [choosing which node of a replication group takes the backups](/docs/backups-in-a-cluster). `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. ```ts title="backup.ts" 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() ``` ```json result { "fullCopy": true, "streamedCopy": false, "stagedCopy": true, "localDiskRequired": "equal-to-backup", "schedule": true } ``` | Field | What it tells you | | --- | --- | | `fullCopy` | Whether this runtime copies an open database at all. Everything else on this page needs it. | | `streamedCopy` | Whether a copy to a destination you supply reaches that destination without a local file first. The [destinations page](/docs/backup-destinations) covers what turns this on. | | `stagedCopy` | Whether a copy to a destination you supply writes one local file and sends that file on. | | `localDiskRequired` | `'equal-to-backup'` where a copy needs free local disk the size of the database, and `'none'` where it needs none. | | `schedule` | Whether `scheduleBackup` repeats a copy on a timetable. It follows `fullCopy`, because a scheduled run makes a full copy. | 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](/docs/device-sync-recovery), and a call to `db.backup` on one of them fails with `BACKUP_UNSUPPORTED`. The [driver table](/docs/getting-started#install-the-package-and-a-driver) 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. ```ts title="backup.ts" 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)) // [!code --] // [!code ++:16] 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() ``` ```json result { "databaseId": "orders", "sourcePath": "./data/orders.db", "pageCount": 2, "pageSize": 4096, "byteLength": 8192, "restarts": 0 } ``` 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`. ```ts title="backup.ts" 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], ]) // [!code --:18] 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() // [!code ++:11] 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. | Option | Default | What it does | | --- | --- | --- | | `cron` | required | Five fields for minute resolution, or six to add seconds at the front. | | `destDir` | required | Directory each copy is written into. Sirannon creates it recursively. | | `maxFiles` | `5` | Copies to keep. Sirannon sorts the files matching `backup-*.db` by modification time and removes the rest. | | `timezone` | the host zone | IANA name the cron expression is read in, which also settles the daylight saving rules that apply. | | `onBackup` | none | Called with the report of every finished copy. | | `onBackupTimeoutMs` | `600000` | Milliseconds Sirannon waits for `onBackup` before it continues. | | `onError` | none | Called with every failure. Set it, because a schedule reports through no other channel. | 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](/docs/backup-destinations), takes `restartLimit`, `stallTimeoutMs`, `noProgressStepLimit`, and `pagesPerStep` as options, which lets a host with a slow disk or a busy source widen them.