Your Database File Is Not the Whole Database

Reference: SQLite — "Write-Ahead Logging" and SQLite — "How To Corrupt An SQLite Database File" — SQLite documentation (public domain) · measured against SQLite 3.45.1 on Linux 6.8.0

A database file is not the whole database. In WAL mode, SQLite writes a committed transaction into a separate -wal file and updates the main file later, at a checkpoint. Copy only the main file while the app is running and you copy a database that is missing your newest note.

That is not a bug. It is documented behaviour, and SQLite states the consequence in one sentence: "If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted."¹

There is also a real bug in this neighbourhood — a race between a checkpointer and a writer, as old as WAL mode itself, fixed on 2026-03-03² after Tailscale hit 19 separate corruptions in six months of production.³

Keep the two apart. The bug is rare, patched, and only reachable with two or more connections open on the same file.⁴ The lost write measured below needs no bug at all — one connection, one commit, one file copy, and a note that quietly does not arrive.

Why copying a database file can lose a note you already saved

Because the file you copied was never the whole database. A commit in WAL mode appends frames to a companion -wal file; the main file is only rewritten later, during a checkpoint. Between those two moments, every committed change lives outside the file that most backup scripts copy.

The mode is not new: SQLite's manual dates the Write-Ahead Log option to "version 3.7.0 (2010-07-21)".⁵ Its bargain is simple. New pages go to the log, and "Moving the WAL file transactions back into the database is called a 'checkpoint'."⁶ The main file is deliberately left alone in the meantime, "which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL."⁷

Unaltered is the word that matters here. The property that makes WAL fast for readers is the property that leaves the main file incomplete — one design decision, read from two sides.

The companion file is easy to spot once you know its name: SQLite names it after the database file, with an extra -wal suffix.⁸ It is not permanent, either — "Usually, the WAL file is deleted automatically when the last connection to the database closes."⁹

That disappearing act is why the hazard survives casual testing. Quit the app, look in the folder, and you see one tidy file. Run the app, copy that same file, and you have taken a snapshot of the database as it stood at the last checkpoint — not as it stands now.

We have written before about why append-then-checkpoint is a crash-safety win — never overwriting committed bytes means a crash can only damage the record you were writing. This post is the other half of the same mechanism. The appended part is a separate file, and the file everyone thinks of as "the database" is, by design, behind.

The measurement: a committed write the main file never saw

One connection, one committed insert, no bug: the main database file kept the same SHA-256 and the same 8,192 bytes it had before the write. The live connection saw two rows. A copy of that file alone contained one row. Nothing raised an error, and nothing warned.

Here is the whole experiment. It uses Python's standard-library binding against SQLite 3.45.1, and any SQLite from 3.7.0 onward reproduces it.

import sqlite3, hashlib, os, shutil
sha  = lambda p: hashlib.sha256(open(p, 'rb').read()).hexdigest()
size = lambda p: os.path.getsize(p) if os.path.exists(p) else 0

c = sqlite3.connect('notes.db')
c.execute("PRAGMA journal_mode=WAL")
c.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT)")
c.execute("INSERT INTO notes(body) VALUES('note one')")
c.commit(); c.close()                      # clean close: the WAL is checkpointed and removed

before = sha('notes.db')
print("BEFORE  notes.db  sha256 =", before)
print("BEFORE  notes.db  bytes  =", size('notes.db'))

c = sqlite3.connect('notes.db')             # the app reopens; you write a note; it says "Saved"
c.execute("INSERT INTO notes(body) VALUES('note two')")
c.commit()                                  # committed: durable, visible, acknowledged
print("AFTER   notes.db  sha256 =", sha('notes.db'))
print("AFTER   notes.db  bytes  =", size('notes.db'))
print("sha256 UNCHANGED by the committed write:", before == sha('notes.db'))
print("notes.db-wal now holds", size('notes.db-wal'), "bytes of committed data")
print("rows visible to the live connection:", c.execute("SELECT count(*) FROM notes").fetchone()[0])

os.makedirs('backup', exist_ok=True)
shutil.copy('notes.db', 'backup/notes.db')  # "backing up my notes file"
c.close()

b = sqlite3.connect('file:backup/notes.db?mode=ro', uri=True)
print("rows in the copied backup:", b.execute("SELECT count(*) FROM notes").fetchone()[0])
print("PRAGMA integrity_check on the copy:", b.execute("PRAGMA integrity_check").fetchone()[0])

The output, run 2026-08-13:

BEFORE  notes.db  sha256 = c90ac0a55cca97a5c49531956b6a764c445006fa34215eed9ad17bd006e23b9c
BEFORE  notes.db  bytes  = 8192
AFTER   notes.db  sha256 = c90ac0a55cca97a5c49531956b6a764c445006fa34215eed9ad17bd006e23b9c
AFTER   notes.db  bytes  = 8192
sha256 UNCHANGED by the committed write: True
notes.db-wal now holds 4152 bytes of committed data
rows visible to the live connection: 2
rows in the copied backup: 1
PRAGMA integrity_check on the copy: ok

Read the two halves against each other. The write was real: the live connection returns two rows, and 4,152 bytes of it are sitting on disk in notes.db-wal. The copy is also real — structurally valid, and one note short.

State precisely what this does not show. It is not the WAL-reset bug and does not reproduce it. It is not SQLite losing data, either — the commit was durable in exactly the way the project promises: "SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer."¹⁰

The transaction was never lost. The copy was incomplete — and the copy was our doing.

What the backup actually copied

The copy contains everything checkpointed before it ran, and nothing committed after. That boundary is invisible: the file is well-formed, its page count is plausible, and its newest content is older than the application's. SQLite names the move in its list of things likely to corrupt a database — "Copying a database file without also copying its journal."¹¹

flowchart TD
  A["App commits<br/>a note"] --> B["Frames appended<br/>to the -wal file"]
  B --> C{"Has a checkpoint<br/>run since?"}
  C -->|no| D["Main file bytes<br/>unchanged"]
  C -->|yes| E["Pages copied into<br/>the main file"]
  D --> F{"What does the<br/>backup copy?"}
  E --> F
  F -->|main file only| G["Uncheckpointed<br/>commits missing"]
  F -->|main file plus wal| H["Every commit<br/>present"]
  G --> I["integrity_check<br/>still returns ok"]

Figure: where a committed note lives before a checkpoint. A commit appends frames to the -wal file; the main database file is only updated when a checkpoint runs. A backup that takes the main file alone therefore contains every checkpointed change and none of the newer ones, and the resulting copy still passes a structural integrity check. A backup that takes the main file together with its -wal file contains every commit.

The scenario is not exotic. SQLite calls out the exact tool that causes it: "Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt."¹² A folder-sync client, a snapshot script, a nightly cp — each of them is that system.

Why integrity_check called the broken copy fine

PRAGMA integrity_check audits structure, not completeness. It answers whether the pages, indexes and free lists in the file are internally consistent. It has no way to know that a row was committed somewhere else and never arrived, so a copy that is missing your newest note passes as cleanly as a perfect one.

This is where a habit that works for documents stops working for databases. Our own SHA-256 manifest method for catching silent bit rot is sound for files that are supposed to stay unchanged — hash them, re-hash them later, and a mismatch means corruption.

Point the same method at a live database file and it goes quiet in both directions. Here the hash was identical at the exact moment a note went missing. On an ordinary working day it changes at every checkpoint, so a mismatch means nothing either.

That post's own warning turns out to name this case: if you copy files without verifying them first, you faithfully back up corruption and overwrite the last good version.

Tailscale lived that sentence. Their pipeline is deliberately simple — "we take a complete snapshot of the database every few minutes, then upload the entire SQLite file to an S3 bucket. We'd been running this setup without incident since early 2023."¹³ What eventually caught the damage was not a checksum but a database-aware check: PRAGMA integrity_check, run against the backups.¹⁴

The lesson generalises past this one pragma. Verification has to speak the format's language. For a folder of Markdown, a file hash is the right question. For a database, the right question is whether the engine can still read every page it thinks it wrote.

The other failure mode: when the checkpointer itself was wrong

Everything above is correct behaviour meeting a bad copy. The second failure is a real defect, narrowly scoped: "The bug only affects databases in WAL mode when there are two or more database connections open on the same file, in separate threads or processes, and when those two connections attempt to write or checkpoint at the same instant."⁴

SQLite dates it precisely. The bug "is likely present in all version of SQLite from 3.7.0 (2010-07-21) through 3.51.2 (2026-01-09). It is fixed in version 3.51.3 (2026-03-13) and later. Backports of the fix are available for some earlier releases: 3.44.6 and 3.50.7."¹⁵

That first version number is the release that introduced WAL mode.⁵ The defect is not sixteen years old by coincidence — it is exactly as old as the feature. Tailscale's headline reports a developer estimate of "at least 16 years"; SQLite's own version range is the citable form.

The sequence needs six things to happen in order:

1. A connection completes a checkpoint, fully, leaving the WAL in a state where it can be reset.
2. Shortly after, a second checkpoint starts.
3. While that second checkpoint is starting up, another connection commits a transaction that resets the WAL and writes new content into the beginning of the file.
4. A data race means the second checkpoint never notices the reset. "The second checkpoint leaves a field in the header of the WAL-Index set incorrectly. That field indicates that part of the WAL file has already been checkpointed, when in fact it has not been."¹⁶
5. More transactions grow the WAL past the size it had at step 1.
6. A third checkpoint then skips the step-3 transaction. "Thus parts of the transaction from step 3 never reach the database file, and the database file goes corrupt."¹⁷

Note what step 3 physically means: "A WAL reset means to rewind the WAL and start adding new frames at the beginning."¹⁸ The WAL-index header carries a field tracking how much of the log has been copied back — nBackfill, which the format spec says "may be increased while holding this exclusive lock, but it may not be decreased," and which on a reset "is reset to zero while holding this lock."¹⁹

One caution on that link. SQLite's bug description does not name the field it leaves set incorrectly, so reading it as nBackfill is our inference from the file-format spec, not the project's claim.

The fix itself is small. Check-in 7168988acbec2d8d by the developer dan, timestamped 2026-03-03 19:43:19.771, carries the comment "Avoid an obscure race condition between a checkpointer and a writer wrapping around to the start of the wal file" and touches exactly two files — a change to src/wal.c and a new test/walrestart.test.²

Within 72 hours drh backported it to branch-3.44,²⁰ branch-3.50,²¹ and branch-3.51,²² the 3.44 cherry pick noting that it "omits testing infrastructure."²⁰ The changelog entry for 3.51.3, dated 2026-03-13, reads in full: "Fix the WAL-reset database corruption bug."²³

Finding it took production scale and an honest write-up. Alex Chan's account for Tailscale describes the symptom that should be impossible: "data written and committed by one transaction was inexplicably invisible to later transactions. A write had vanished into thin air without raising an error. That should be impossible!"²⁴ The developers themselves never reproduced it organically and had to add test logic that deliberately triggers the circumstances of the bug in order to confirm the fix.²⁵

One detail is worth keeping straight, because it is easy to fold into the same story and it is a different bug. Release 3.52.0 (2026-03-06) was withdrawn, and "All of the features that were originally scheduled for the 3.52.0 release have been moved forward into version 3.53.0."²⁶ After rolling it out, Tailscale's backup monitor "reported corruption in 13 different databases" — false alarms caused by stale expression indexes, not by the WAL-reset race.²⁷

How rare "rare" is, in SQLite's own words

Rare enough that SQLite measures it against hardware failure: "Based on available telemetry, the occurrence rate of this problem in the wild appears to be less than or equal to the expected occurrence rate of SSD malfunctions and/or cosmic-ray hits." The same section adds, plainly, "However, this is not an emergency."²⁸

Read that as calibration, not as a sales line. A project that finds a race as old as its own feature, fixes it in a single source file, backports it to three branches inside 72 hours, then documents the whole thing in its own manual, is behaving well.

The durability claim it makes elsewhere is not marketing either — SQLite says the ACID promise "is extensively checked in the SQLite regression test suite using a special test harness that simulates the effects on a database file of operating system crashes and power failures."²⁹

Tailscale's own conclusion is the fair one, and it is about configuration rather than blame: "running boring technology in a non-standard way is a risk. The common paths and standard configurations are incredibly well-tested and reliable. Most people use SQLite in a standard configuration and never face this sort of issue."³⁰

So the honest summary has two halves. You are very unlikely to meet the bug. You can meet the lost write from earlier in this post today — on a single connection, with a fully patched SQLite, using a backup script you wrote yourself.

How to copy a database that is being written to

Four documented options, all from SQLite rather than folklore. Use the engine to produce the copy, or make sure nothing is writing while you copy. What you must not do is grab the main file mid-flight and assume the result is a backup, because that is precisely the case the vendor lists as likely to corrupt.¹¹

• VACUUM INTO 'backup.db' — "The VACUUM INTO filename command copies out the current state of an SQLite database into a separate file."³¹ It runs inside the engine, so it sees the WAL.
• sqlite3_rsync — "The sqlite3_rsync utility program (available beginning with SQLite 3.47.0 (2024-10-21) and later) will make a copy of a live SQLite over SSH using a bandwidth-efficient protocol."³²
• Copy while quiet — "It is also safe to make a copy of an SQLite database file as long as there are no transactions in progress while the copy is taking place."³³
• Copy the whole set — if you must copy raw, take the -wal file with the database file. The WAL "should be kept with the database if the database is copied or moved."¹ Deleting one by hand is worse still: "The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close()."³⁴

Two of those are measurable with the same script. Replace the naive copy with the lines below, still on the live connection, and both copies come back complete:

# same live connection as above, before c.close()
os.makedirs('copy_both', exist_ok=True)
for f in ('notes.db', 'notes.db-wal', 'notes.db-shm'):
    if os.path.exists(f): shutil.copy(f, 'copy_both/' + f)
c.execute("VACUUM INTO 'backup-vacuum.db'")
c.close()

b = sqlite3.connect('file:copy_both/notes.db?mode=ro', uri=True)
print("rows when the -wal file is copied alongside:", b.execute("SELECT count(*) FROM notes").fetchone()[0])
v = sqlite3.connect('file:backup-vacuum.db?mode=ro', uri=True)
print("rows in the VACUUM INTO copy:", v.execute("SELECT count(*) FROM notes").fetchone()[0])
print("integrity_check on the VACUUM INTO copy:", v.execute("PRAGMA integrity_check").fetchone()[0])

rows when the -wal file is copied alongside: 2
rows in the VACUUM INTO copy: 2
integrity_check on the VACUUM INTO copy: ok

If your notes app is running, the safest backup is the one it exports for you — or the one taken while the app is closed. Everything else is a race with a checkpoint you cannot see.

Trade-offs: one file, or a folder of files

A single database file buys real things: transactions, indexes, concurrent readers, and a query language over thousands of notes that never has to open thousands of file handles. Those are the reasons serious tools reach for one, and the single-file backend is a legitimate architecture, not a shortcut.

What it costs is legibility at the edges. When the storage engine is a database, the unit of loss is a transaction, and only the engine can tell you whether one arrived. When storage is a folder of Markdown files, the unit of loss is a file you can open, read and diff — and a plain hash manifest, which is useless against a live database, becomes the right tool again.

Neither choice removes the need to verify. A folder of files has its own lost-update failure when two writers touch the same note, which we took apart in the case of the agent that read your note before writing it. Copies still need the discipline of the 3-2-1 backup rule. The difference is what you have to trust: with files, your own eyes; with a database, the engine's own tooling.

Pick either. Just know which one you have — the backup procedure is not the same, and the failure is silent in exactly one of them.

Frequently Asked Questions

Can SQLite lose data I already committed? Not by itself. SQLite "implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer."¹⁰ What loses a committed write is a copy that takes the main file without the -wal file.¹

Is it safe to copy my notes database file to a backup folder while the app is running? No. That is the documented hazard: background backup systems "might try to make a backup copy of an SQLite database file while it is in the middle of a transaction," producing a copy with "some old and some new content."¹² Use VACUUM INTO,³¹ sqlite3_rsync,³² or copy only when no transaction is in progress.³³

Why did PRAGMA integrity_check say my backup was fine when a note was missing? Because it checks structural consistency, not completeness. In the measurement above, a copy that was missing one committed row returned ok. A structural check cannot know about a transaction that was committed into a -wal file it never received. It is still the right tool for detecting real corruption — it is simply answering a different question.

Do I need to copy the -wal file too? Yes, if you are copying raw files. The WAL "is part of the persistent state of the database and should be kept with the database if the database is copied or moved."¹ In the measured run, copying notes.db alone gave 1 row; copying notes.db with notes.db-wal gave 2. Never delete a WAL file by hand.³⁴

Am I affected by the SQLite WAL-reset bug? Probably not. It requires "two or more database connections open on the same file, in separate threads or processes,"⁴ it is fixed in 3.51.3 and backported to 3.44.6 and 3.50.7,¹⁵ and SQLite puts its field rate at or below "the expected occurrence rate of SSD malfunctions and/or cosmic-ray hits."²⁸ Update, then stop worrying about it.

How do I tell whether my notes live in a database file? Open the app's data folder. A database-backed app usually shows one large file, sometimes with siblings whose names end in -wal or -shm, since the log is "the name of the database file with an extra "-wal" suffix."⁸ A file-backed app shows one readable document per note. The backup procedure you need differs completely between the two.

Your database file is not the whole database. Treat it as the whole thing and the sentence you lose is the last one you wrote — silently, while every checksum you own still says ok.

MNMNOTE keeps your notes on your own device as plain Markdown you can export and read anywhere: mnmnote.com.