SQLite Database

The File Extension Is Anything You Want. What Does That Tell You About the Format?

Open a file manager and look for databases. .sqlite is explicit — Thunderbird's places.sqlite announces its contents. .db is generic — every SQLite-based app can use it, and so can any other database engine. .sqlite3 declares the format version. .db3 does the same. Chrome's browser history lives in a file called History — no extension at all.

All of these contain the same 16 bytes at offset 0: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 — the ASCII string SQLite format 3 followed by a null byte. SQLite itself doesn't care what you name the file. The operating system doesn't care either. Only people do.

That matters for forensic triage. An investigator scanning a mobile device's /data/data/ directory can identify .sqlite-extensioned files instantly, while a generic .db requires content sniffing to distinguish SQLite from Berkeley DB, LevelDB, or something else entirely. Apps that want their storage to be inspectable choose .sqlite. Apps that want to stay opaque choose .db or nothing. The extension is a declaration about author intent, not a requirement of the format. Investigators know this. They skip the extension entirely and read the bytes.

Forensic Investigators Read the First 100 Bytes Before Anything Else. Why Those 100?

Every SQLite investigation begins with 100 bytes. The first 16 are the magic string. Offsets 16-20 reveal the rest.

Offset 0x10 holds the page size — a two-byte big-endian integer that must be a power of 2 between 512 and 65536 (the default is 4096). Offset 0x12 holds the write format version: 1 means rollback journal, 2 means Write-Ahead Logging (WAL). Offset 0x13 holds the matching read format version. Together these three bytes tell you the database's fundamental mode — and whether you need to look for a companion -wal file on disk.

The file change counter at offset 0x18 increments on every write transaction, so caching clients can detect whether the database changed since they last read it. The schema cookie at 0x28 increments when any CREATE/DROP/ALTER runs, telling prepared-statement caches to recompile. The text encoding at 0x38 (1 = UTF-8, 2 = UTF-16 little-endian, 3 = UTF-16 big-endian) determines how cell content is interpreted.

Finally, offset 0x60 holds the SQLite library version that most recently wrote the database — four bytes in decimal-packed format. 00 38 12 00 reads as 3.39.0. Forensic analysts fingerprint a database — last writer version, page size, journal mode — without opening it. 100 bytes tell the story, and version mismatches can reveal tampering or version-specific bugs. Headers reveal what is in the file. The sidecar files next to it reveal what is not yet committed.

Copy a SQLite File Without the -wal Sidecar. You Just Lost Committed Transactions. Why?

Open a SQLite database in WAL mode and a second file appears next to it. database.db is the main file. database.db-wal is the Write-Ahead Log. database.db-shm is a shared memory index used when multiple processes access the database concurrently. Delete the -wal file while it contains uncommitted pages and you've permanently lost those changes.

WAL replaced the traditional rollback journal (-journal) in SQLite 3.7.0 (July 2010). Instead of writing old-page copies before overwriting, WAL appends new-page copies to a separate file and merges them back into the main database at checkpoint time. The benefit is concurrency: readers can continue reading the main database while a writer appends to the WAL. The traditional rollback journal serialized all access.

For mobile forensics this is the common pitfall. WhatsApp's msgstore.db accumulates messages in msgstore.db-wal between checkpoints. An investigator who copies only the main file with cp misses the most recent messages entirely — sometimes days of conversation. The safe copy pattern is sqlite3 source.db '.backup destination.db', which reads through SQLite and includes pending WAL data.

Enable WAL mode with PRAGMA journal_mode=WAL;. Control checkpoint frequency with PRAGMA wal_autocheckpoint=1000;. Close all connections to force a checkpoint — or run PRAGMA wal_checkpoint(TRUNCATE); explicitly. WAL behavior is an engineering detail. The scale at which it runs is the astonishing part.

One Trillion Active SQLite Deployments. How Did It Get There?

Your phone contains dozens of SQLite databases right now. Messages, contacts, browser history, every app's local cache, every system configuration store. WhatsApp's msgstore.db.crypt14 holds years of chat. Apple Messages keeps everything in chat.db. Signal, Telegram, Slack, Discord — all use SQLite. Chrome's History file. Firefox's places.sqlite. Safari's History.db. Every major consumer app on the planet uses SQLite for local caching and offline functionality.

Android ships SQLite as a core framework component. iOS uses Core Data built on SQLite. macOS's Mail and Safari both use it internally. Every Windows 10/11 installation contains SQLite databases for system features. The sqlite.org project estimates over one trillion active deployments worldwide — more than every other database engine combined by orders of magnitude.

Mobile forensic workflows start with SQLite. Extract the app's /data/data/<package>/databases/ directory. Open each file with sqlite3 or DB Browser for SQLite. Recover deleted rows from freelist pages using SQLite 3.29.0's .recover command. The scale is remarkable: most people carry 50-100 SQLite databases in their pocket every day, and every mobile investigation depends on reading them.