Four SQLite files, audited as bytes and then opened by sqlite 3.45.1

Reference page for dbcheck. All four files are generated by samples/make_samples.py; the outputs below are from the shipped CLI, not edited. Published 2026-11-05.

Claim. PRAGMA integrity_check returns ok on a file whose tables live entirely in a -wal that was never copied. Integrity is a property of the file; completeness is a property of the file plus everything it depends on, and only the second one explains “my restore has no tables”.

Why the file is read as bytes

Opening a WAL-mode database with sqlite3 has write side effects: the connection checkpoints the WAL on close and removes the -wal. Any “read-only inspection” that opens the file therefore destroys exactly the evidence it was asked to preserve, and can turn Case 2 below (data present, in the WAL) into Case 1 (data gone). dbcheck parses the file directly and never links against sqlite.

Case 1 — the backup nobody checks critical

App running, journal_mode=WAL, cp app.db backup.db, restore later:

samples/backup_naive.db  (4.0 KB)
  page_size=4096  journal_mode=WAL  header_pages=1  file_pages=1  tail=0 pages
  freelist=0  change_counter=1  valid_for=1
!! WAL_MODE_BUT_NO_WAL: header says journal_mode=WAL, there is no -wal file, and the
   main file holds only 1 page(s): if you expect tables in here, they are not in this file
  => CRITICAL

Opened by sqlite 3.45.1:

sqlite_master : []                                   <- not one table
select count(*) from users : OperationalError: no such table: users
PRAGMA integrity_check : ok                          <- and it says the file is fine

The live database had 440 rows. The backup is 4 KB, opens without error, passes integrity_check, and is empty. This is the normal output of cp, tar czf, and most “snapshot the volume” scripts while any WAL-mode writer is connected.

Case 2 — the case people get wrong in the other direction critical

crash.db and crash.db-wal copied together. The .db looks even worse (1 page), but the data is all there — in the other file:

samples/crash.db  (4.0 KB)
  page_size=4096  journal_mode=WAL  header_pages=1  file_pages=1  tail=0 pages
  -wal=61832 bytes  frames=15  valid=15  commit_pages=13
!! MAIN_DB_STALE: the newest commit lives in the -wal (the commit sets db size to 13),
   but this .db file still says 1 page — copying only the .db loses those commits
  => CRITICAL

sqlite on the same two files: select count(*) from users = 500. The verdict here is not “restore from an older backup”; it is keep the pair, do not clean up the -wal. Case 1 and Case 2 are indistinguishable to file, to ls -l, and to every tool whose only question is “does it open”.

Case 3 — what a healthy backup looks like ok

samples/backup_clean.db  (52.0 KB)
  page_size=4096  journal_mode=WAL  header_pages=13  file_pages=13  tail=0 pages
  freelist=1  change_counter=2  valid_for=2
 ·  WAL_MODE_CLEAN: journal_mode=WAL with no -wal: normal for a cleanly closed database
 ·  FREELIST_ZEROED: 1 free page(s) hold no readable text: the writer ran with secure_delete=ON
  => OK

sqlite: 440 rows, integrity_check ok. Three page counts agree (header 13, file length 13, no WAL commit overriding either), and there is no readable residue past the declared end.

Case 4 — deleted rows are still in the file warning

samples/deleted_off.db  (20.0 KB)
  freelist=3  change_counter=3  valid_for=3
 * DELETED_ROWS_READABLE: 3 free page(s) still contain 200 readable string(s) (longest 24 B):
   rows that were deleted or dropped are physically recoverable from this file
  => WARN

grep -c customer299@example.com deleted_off.db finds the deleted customer on disk. The identical operation on this machine's default build (secure_delete=ON) leaves nothing. Whether deleted data is recoverable is a property of the specific file and build, not of SQLite in general — which is why it is answered per file here.

What is checked, and what is not

Checked	Not checked
magic, page size, reserved bytes	b-tree walk / row counts (“you lost N rows” is never claimed)
header page count vs file length vs WAL commit `dbsize`	page checksums (SQLite has none by default)
WAL header checksum; frames after the last commit	the content of a missing `-wal`
freelist chain (cycle- and bounds-guarded), readable strings in free pages	whether `secure_delete` was on: inferred from free-page residue, not from compile flags
readable strings past the declared end of file	recovery of any kind

21 assertions in test_dbcheck.py, each paired with the same file opened by real sqlite, so every verdict above is a claim about observed behaviour rather than the parser's opinion.

Cite this

dbcheck, “Four SQLite files, audited as bytes and then opened by sqlite 3.45.1”, 2026-11-05.
URL: https://huxin.online/dbcheck/report.html
Reproduce:

python3 samples/make_samples.py && python3 dbcheck.py samples/backup_naive.db samples/crash.db samples/backup_clean.db samples/deleted_off.db

Use it

Local, free, nothing uploaded: python3 dbcheck.py yourfile.db --json. Online, one file ≤ 20 MB plus its -wal (≤ 4 MB) if you still have it — both deleted before the response: /dbcheck/. Questions or a second opinion on a specific broken file: xia0bing@agent.qq.com (the link pre-fills five questions; no attachment needed on the first email).