Every format in this series so far has been a way of writing a document down. SQLite is the one that asked a different question: what if the application file format were a database?

It is the best-designed thing I have written about in this whole series. It has a real specification, a versioned header, a page allocator, transactions, and a compatibility promise going back to 2004. The Library of Congress recommends it for long-term preservation. Its own documentation makes the case that a .sqlite file is a better application format than a custom binary blob or an XML tree, and the case is correct.

And it will still hand a deleted password to anyone who runs strings on the file, or not, depending on which copy of the library you happened to link.

The Header Is Legible

The first hundred bytes of any SQLite database are a fixed-layout, big-endian header. You can read it with xxd:

$ xxd -g 1 -l 32 notes.db
00000000: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00  SQLite format 3.
00000010: 10 00 01 01 00 40 20 20 00 00 00 06 00 00 00 02  .....@  ........

Sixteen bytes of magic, and it is the literal string SQLite format 3 with a nul terminator. Then decoding what follows:

page size        4096
write version    1  (1 = rollback journal, 2 = WAL)
read version     1
reserved/page    0
change counter   6
size in pages    2
freelist head    0
freelist count   0
text encoding    1  (1 = UTF-8)
sqlite version   3047001

The page size is a two-byte field, which creates a small problem: the format allows 65,536-byte pages, and 65,536 does not fit in two bytes. The spec’s answer is a special case, and it is worth reading in the original:

to specify a 65536-byte page size, the value at offset 16 is 0x00 0x01. This value can be interpreted as a big-endian 1 and thought of as a magic number to represent the 65536 page size.

That is the whole hack, documented in the spec, with the word “magic number” used by the authors about their own file. Compare that to every other format in this series, where the ambiguities were things nobody wrote down.

Every page after the header starts with a one-byte type flag:

page 1: flag 0x0d  leaf table
page 2: flag 0x0d  leaf table

0x0d is a table leaf, 0x05 a table interior node, 0x0a an index leaf, 0x02 an index interior node. Four values, and from those four you can walk a B-tree without a library. Each page is slotted: a pointer array grows down from the header, cell content grows up from the bottom, and free space is whatever is left in the middle.

This is a database engine’s internal layout, written down, versioned, and promised to stay readable. There is a reason people keep reaching for it as a document format. The project’s own argument for it is that you get transactions, partial reads, and incremental writes for free, and that for blobs under about 100KB it reads and writes faster than the filesystem does.

Two Libraries, Two Answers

Here is a three-row table. I delete the row with the secret in it, and then look at the file.

import sqlite3
c = sqlite3.connect("notes.db")
c.executescript("""
CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes(body) VALUES ('the wifi password is hunter2-CANARY');
INSERT INTO notes(body) VALUES ('groceries: milk, eggs');
INSERT INTO notes(body) VALUES ('call the dentist back');
""")
c.commit()
c.execute("DELETE FROM notes WHERE body LIKE '%CANARY%'")
c.commit()

$ strings notes.db | grep -i canary
(Sthe wifi password is hunter2-CANARY

The row is gone from every query. SELECT count(*) returns 2. The bytes are still on the page, because deleting a row marks its cell as a freeblock and links it into a chain of free space. Nothing overwrites it. The next insert that happens to fit will land there and clobber it, and until then it sits in the file.

That is the behavior everyone knows about. If you ran the same three statements through the sqlite3 command-line tool on the same machine, on the same directory, and the string will be gone.

$ sqlite3 --version
3.51.0 ...

$ sqlite3 notes.db 'PRAGMA secure_delete;'
2

$ python3 -c "import sqlite3; print(sqlite3.sqlite_version); \
    print(sqlite3.connect(':memory:').execute('PRAGMA secure_delete').fetchone()[0])"
3.47.1
0

secure_delete off means freed space keeps its contents. secure_delete set to 2 is FAST mode, which zeroes freed space inside a page without chasing it into the freelist. macOS ships one build with it on. CPython ships another with it off. Neither reports the difference in PRAGMA compile_options. Nothing in the file records which one wrote it.

So “does my database keep deleted data” has no answer at the format level. It has an answer per binary, and both binaries are called SQLite, and both are on this laptop.

VACUUM does resolve it, because it rebuilds the entire file from live rows:

$ python3 -c "import sqlite3; sqlite3.connect('notes.db').execute('VACUUM')"
$ strings notes.db | grep -ci canary
0

Which is the same shape as yesterday’s PDF post. The safe operation and the obvious operation are different operations. DELETE is the obvious one.

The Single-File Database Is Three Files

The pitch for SQLite as an application format leans hard on it being one file you can copy, email, and back up. In the default rollback-journal mode that is true between transactions. Turn on WAL, which is what you want for anything with concurrent readers, and while a connection is open:

-rw-r--r--  4096 w2.db
-rw-r--r-- 32768 w2.db-shm
-rw-r--r-- 12392 w2.db-wal

The write-ahead log and the shared-memory index are each larger than the database. The -wal file starts with its own magic number:

37 7f 06 82 00 2d e2 18 00 00 10 00

0x377F0682 says little-endian checksums, and the last four bytes are the page size again, 4096, matching the main file.

Copy just w2.db while that connection is open and you get the database as of the last checkpoint, silently missing every committed transaction still sitting in the WAL. The main file’s header even tells you this is possible: the read and write version bytes both flip from 1 to 2 when WAL is enabled, which is the format’s way of saying “a reader that does not understand WAL must not open me.”

Both files disappear on a clean close, which is why this bites people at exactly the wrong moment. Your backup script tested fine.

What To Do About It

• Set PRAGMA secure_delete=ON explicitly if the database holds anything sensitive, in your application code, not in a config file you hope got read. Do not assume the default. Check it at startup and log what you got.
• VACUUM after deleting anything that mattered. It is the only operation that actually reclaims and rewrites. On a large database it is expensive and takes a full-file lock, so schedule it.
• Back up with .backup or VACUUM INTO, never cp. Both take a consistent snapshot including the WAL. cp on a live WAL database gives you a file that opens fine and is missing data.
• Check PRAGMA integrity_check in your test suite, not just in production incident response.
• Read the header yourself when debugging. Page size, page count, encoding, and journal mode are all in the first 100 bytes, and a hex dump will tell you in two seconds whether the file is truncated, whether it is even SQLite, and what wrote it.
• Use it as an application file format. Seriously. The argument is right. Just know that “it is a database” means it has a database’s habits, including keeping things you told it to delete.

Two weeks into this series the pattern is uncomfortably consistent. The problem is almost never the specification. CSV had none, JSON had a small one, YAML had a good one that arrived late, XML had one for everything, and SQLite has one that is legible, complete, and honest about its own hacks. It still ships a data-remanence behavior that flips based on a compile-time flag neither binary bothers to report.

The format is not what you get. The build is what you get.

Sources

• SQLite Database File Format — the 100-byte header, the page types, the freeblock chain
• SQLite as an Application File Format — the project’s own argument, including the claim that it beats the filesystem for blobs under 100KB
• Write-Ahead Logging and the WAL file format — the -wal and -shm companions, and why cp is not a backup
• PRAGMA secure_delete — including the FAST mode that macOS ships and CPython does not
• Library of Congress: SQLite as a recommended preservation format — the format description

I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].