How I stream row changes out of SQLite without touching the app (reading the WAL)

#sql #rust #database #opensource

SQ
Lite has no way to tell another process "row 42 in users just changed." I wanted that,
so I built tailite: it follows a live SQLite
database from outside and emits every committed insert, update and delete with before and
after values. The writing app doesn't change.

The options that didn't work

sqlite3_update_hook and the preupdate hook only run inside the process doing the write.
The session extension has to be turned on by the writer.
Trigger-based CDC adds triggers and a log table, which is fine for your own app and not an option for a database some other app owns.
Litestream and LiteFS replicate pages. That's great for backups, but they can't tell you which row changed.

So: read the write-ahead log myself.

Step 1: which transactions are committed?

A WAL is a 32-byte header plus frames (24-byte header + one page image). Each frame's
checksum chains from the previous one, and a frame with a non-zero "db size" field ends
a transaction. I accept frames by the same rule SQLite's recovery uses: salts match the
header, the checksum chain is unbroken, and the run ends in a commit frame. Anything
still being written or rolled back just fails the chain.

Step 2: pages → rows

A transaction is a bag of page images. To get rows you need to know which table each page
belongs to, and SQLite pages don't point back to their parent. tailite builds a
page → table and page → parent map once, then updates it per transaction by walking only
the paths from changed pages up to their roots.

The surprise was deletes. When SQLite frees a page it skips writing it
(sqlite3PagerDontWrite), so after a big DELETE the freed leaves aren't in the WAL at
all. Their old images still hold the deleted rows, though. So the tracker looks for
orphans: pages that were on a changed path and didn't show up again when re-walking the
new tree. It reads their old images.

My first version assumed "parent page unchanged → page still in the tree." DELETE FROM t
broke that: the table's root got rewritten, and pages below unchanged-but-freed parents
went stale. The randomized tests caught it. The index self-check (rebuild from scratch
after every commit, compare) pointed straight at the drifted pages.

Step 3: two gotchas in the record format

Same-size blob updates. SQLite overwrites overflow pages in place, so the leaf page stays byte-for-byte identical and only an overflow page changes. I keep an overflow page → row index for exactly this case.
REAL affinity. Store 140.0 in a REAL column and SQLite writes the integer 140 to disk, then converts it back when you read. The decoder has to do the same.

Step 4: checkpoints will ruin your day

This is the part that matters. A checkpoint copies WAL frames into the main database
file, and once everything is copied the next writer resets the WAL. If that happens
before you've read a transaction, you lose its frames, or you lose the old version of a
page you needed for the "before" image.

SQLite already protects readers from this: it never checkpoints past the snapshot of an
open read transaction. So tailite holds one (I call it the pin) at a point it has
already processed. Each poll it opens a new pin, reads everything in the log, then drops
the old pin. At every moment some pin sits at or behind what's been processed. Litestream
uses the same trick.

To make sure this wasn't just theory, I wrote a test where a writer thread commits and
runs PASSIVE/RESTART/TRUNCATE checkpoints while tailite polls. I disabled the pin and ran
it again: it failed every run (frames vanishing mid-read, wrong before-images, duplicate
inserts). With the pin it passes on Linux, macOS and Windows.

Testing against SQLite itself

The main test is differential. Random workloads (inserts, range deletes, DELETE FROM,
INSERT OR REPLACE, VACUUM, auto_vacuum, 512-byte pages, WITHOUT ROWID tables, DDL)
run through real SQLite. Every event gets replayed into a mirror, and after every poll the
mirror must equal SELECT * of every table. Updates and deletes also have to carry the
exact previous row.

Using it

cargo install tailite
tailite watch app.db --json      # JSON Lines, one object per row
tailite watch app.db --snapshot  # existing rows first, then the stream
tailite diff old.db new.db       # row diff of two files

Or as a library: Tail::open, poll, snapshot.

Limits (being honest)

WAL mode only for watch. You get the net effect per transaction, not statement order.
There's no resume across restarts yet. Attaching reads the whole database once.

Repo and architecture notes: https://github.com/zaydmulani09/tailite

DEV Community