TLDR: If you're reading a live-written SQLite database you don't own, open it with mode=ro — NOT immutable=1. The difference is subtle, the failure is brutal, and it will look exactly like a bug in your query.

The Setup

I built a tiny Swift Mac app that reads Apple's iMessage chat.db (a SQLite 3 database in ~/Library/Messages/) and gives me a keyboard-driven inbox.

The app worked great on day one.

Then it started telling me I had zero messages.

Not fewer messages. Zero. Total inbox wipe, like everything vanished.

What I Was So Sure About

When I opened chat.db, I reached for immutable=1 in the connection URI.

Seemed like the right call — I was a read-only consumer of Apple's database. I didn't want to accidentally write anything, didn't want to create WAL sidecar files inside Apple's Messages directory. immutable=1 felt respectful to the live app.

So I had something like:

file:///Users/me/Library/Messages/chat.db?immutable=1

Totally reasonable. Completely wrong.

What Actually Happened

Here's the thing about immutable=1 that I didn't know: it tells SQLite the file will never change.

SQLite believes you.

So it snapshots the database at open time and ignores the WAL (Write-Ahead Log, where iMessage stages all its writes). When a WAL checkpoint fires — and Messages.app checkpoints constantly — the outstanding writes flush into the main DB file, the chat.db-wal sidecar gets deleted and recreated…

And my connection is staring at a stale snapshot that no longer reflects reality.

To my app, the inbox looked EMPTY. Not stale-but-close. Genuinely empty. Zero rows.

An empty result that was a lie.

The Fix

Two changes, both small.

1. Switch to mode=ro.

file:///Users/me/Library/Messages/chat.db?mode=ro

mode=ro opens the database read-only — you still can't write — but SQLite knows the file can change. It reads the WAL. It sees current state. That's all I needed.

2. Add an empty-read guard in refreshChats.

Even with mode=ro, I didn't want a zero-row result to silently replace a full inbox. So now: if a refresh returns zero chats and the previous state had chats, I treat it as a suspect read and skip the update.

An empty result is a question now, not an answer.

3. Re-arm FSEvents after checkpoint.

I was watching chat.db-wal for changes via FSEvents. But on checkpoint, the WAL gets deleted and recreated — the FS fires .delete and .rename events, not a content-change event. I had to re-arm the watcher after a short delay when those fired.

Why This Matters to Me

I wasted time staring at SQL queries that were completely fine.

The lesson isn't SQLite-specific, really. It's this: when you consume a database you don't own, respect that it's alive. Don't tell the runtime it's frozen just because you're not writing. The thing you're reading from has its own life.

And never treat zero as ground truth. Zero might mean nothing's there — or it might mean your view just expired.

Took me an afternoon to learn the difference.

P.S. A different flavor of the same lie hit me later on a Supabase migration: the result grid showed "Success. No rows returned." after a DDL run, and I nearly concluded it had failed. A DDL statement should return no rows. Empty is not always wrong — but it's always worth asking why.