SQLite Backups Without the Footgun

The backup mechanism was already there

A WAL-mode database can have committed changes in its write-ahead log that have not reached the main file. An unsynchronized copy of a live database can miss those changes or combine inconsistent file states. That is a reason to choose a supported backup procedure, not evidence that every file copy or filesystem snapshot is corrupt.

SQLite's online backup API provides that procedure. The SQLite shell's .backup command and Python's sqlite3.Connection.backup() use the same underlying mechanism. Choosing the Python interface does not give the wrapper a different consistency guarantee from the shell command.

Successful completion produces a consistent destination snapshot. Incremental copying can release read locks between steps, but writes from another connection may restart the copy; sustained writes can delay or prevent completion. The wrapper defaults to copying all pages in a step. Its --pages-per-step option exposes a more incremental approach for the backup command. Neither mode turns an active database into a contention-free system.

Four commands, with different jobs

The v0.1.0 CLI source implements four operations. These examples describe the v0.1.0 CLI, written with its current command name, and use illustrative filenames; the source database and destination directory must exist, and backup destinations must be new.

• Checkpoint: moldbox checkpoint myapp.db -m truncate. Run a WAL checkpoint. Available modes are passive, full, restart, and truncate; completion depends on SQLite's locking and checkpoint rules. cp is an alias.
• Backup: moldbox backup myapp.db /backups/backup.db. Copy through SQLite's backup API, then report destination size, SHA-256 digest, and elapsed copy time.
• Snapshot: moldbox snapshot myapp.db /backups/snapshot.db. Run checkpoint and backup in sequence and return both reports. They are separate operations, not one transaction that freezes application writes between them.
• Info: moldbox info myapp.db. Inspect journal mode, WAL-file observations, page counts, and freelist information.

For JSON output, put the global option before the subcommand: moldbox --json info myapp.db. The reports contain fields relevant to each operation; info does not produce a backup checksum. The Python checkpoint, backup, and snapshot functions return frozen dataclasses, while db_info() returns a dictionary.

Checkpointing before a backup may serve an operational policy, but the online backup API does not require the WAL to be emptied first. A snapshot result also deserves inspection: the wrapper can report a partial checkpoint and continue to backup. In this release, checkpoint reporting compares page counts and discards SQLite's first checkpoint status field. It is not a complete substitute for interpreting SQLite's checkpoint status semantics.

What the digest tells you

The recorded SHA-256 value fingerprints the completed file. Recomputing and comparing a digest later can detect changed bytes or transfer errors. That later comparison requires reading the file; carrying the old digest forward does not verify a new copy by itself.

A digest does not show that SQLite's structures are sound or that the application can recover from the backup. PRAGMA integrity_check examines database structures within its documented scope; foreign-key violations require a separate check. A restore exercise answers the application-level question. These checks establish different properties, and an operational backup policy should say which ones it relies on.

A consistent database is not an atomic filename handoff

The v0.1.0 core implementation opens the destination path directly. It does not write a temporary file and atomically rename the finished result into place. A consumer watching that path therefore cannot treat its existence as a completion signal. The function's successful return is the relevant event in this interface.

The tool checks for an existing destination before copying and refuses one it finds. That is useful protection against an ordinary accidental overwrite; it is not an exclusive reservation against another process using the same name. Scheduling, destination ownership, retention, and recovery policy remain the operator's responsibilities.

That is the honest value of a small wrapper: a convenient interface to established SQLite mechanisms, with results that surrounding automation can inspect. The package metadata declares Python 3.11 or newer and no third-party runtime dependencies. It does not ship a scheduler, retention service, or storage-provider integration. The original article's PyPI, watch-mode, and retention ideas were prospective work, not evidence of shipped capabilities.

The release can be useful without claiming to invent safe SQLite backup. A precise account makes it easier to decide where the tool belongs, and where another part of the recovery system still has work to do.

Sources and related work

The pinned release and the SQLite references above let you inspect the mechanism and its limits. For another small tool with a deliberately narrow job, see the devcap Chronicle. The OpenForge catalog collects the related public utilities.