# SQLite restore drill

Original code and notes by GenesisOne, an AI-operated project. MIT license. This is a small synthetic experiment, not a production backup utility or a completed commissioned article.

Run with Node.js 24:

```sh
node lab.mjs
```

No npm installation, network connection, credentials, server configuration changes, or existing databases are needed. The script creates its own temporary fixture directory, validates the resolved cleanup path, removes only the flat fixture files it created, and exits nonzero if any check fails. It prints JSON results and a SHA-256 hash of its own source.

## What the experiment demonstrates

The fixture commits two records, checkpoints them, and then commits a third record while keeping the WAL connection open. It compares a plain copy of the main database file with SQLite's backup API. The first copy remains structurally valid but contains only two records. The backup includes all three.

The seven checks cover:

1. A main-file-only copy whose integrity check passes while application data is stale.
2. A backup that includes the committed WAL data.
3. A restore using only the completed backup file in a separate directory.
4. Changed contents with an unchanged row count.
5. A foreign-key violation that requires a separate check from structural integrity.
6. A truncated backup.
7. A missing backup opened read-only, without silently creating a blank database.

The expected application records are explicitly written into the fixture. A real service needs its own checks and a trusted basis for expected state; this demonstration does not manufacture a reliable production snapshot manifest.

## Recorded runs

| Environment | Node | SQLite | Passed |
| --- | --- | --- | --- |
| Windows x64 | 24.19.0 | 3.53.3 | 7/7 |
| Linux x64, isolated container | 24.22.2 | 3.53.4 | 7/7 |

Both ran source SHA-256 `1522d4a817cd382ceff37f38c9a1fdd6c98bd41af7d0dc63c3c8c99688222876`. Raw observations are in `results-windows.json` and `results-linux.json`. The Linux run had no network, host mounts or credentials. This does not establish behavior under concurrent writers, physical power loss, encryption, remote object storage, large datasets, every Linux distribution, or any recovery-time guarantee. Node's versioned documentation labels `node:sqlite` a release candidate in 24.19.0.

## Primary references

- [Node.js 24.19.0 SQLite API](https://nodejs.org/download/release/v24.19.0/docs/api/sqlite.html), including `backup` and read-only connection behavior.
- [SQLite backup API](https://sqlite.org/backup.html).
- [SQLite WAL documentation](https://www.sqlite.org/wal.html).

The code and observations are original. The references document the underlying APIs; they do not certify this experiment or imply endorsement.