Database File

SQLite version 1.0 shipped on August 17, 2000. It used gdbm as the storage backend. Version 2.0 followed in September 2001, replacing gdbm with a custom B-tree implementation and adding transaction support. Version 3.0 arrived in June 2004 with funding from America Online, introducing manifest typing, internationalization, and the 100-byte file header structure that defines every SQLite .db file written since.

The trillion-database engine

SQLite is the most deployed database engine in the world, and it is not close. The project estimates over one trillion active SQLite databases across all devices. Every Android phone, every iPhone, every Mac, every Windows 10 and later machine, every Firefox, Chrome, and Safari browser, every Skype client, every iTunes installation, and every Dropbox client embeds SQLite. The SQLite developers describe it as "probably the second most widely deployed software library, after libz."

A single app on a single phone may contain dozens of .db files. Chrome stores browsing history, cookies, favicons, and autofill data in separate SQLite databases. WhatsApp stores message history in msgstore.db. Signal stores its encrypted message database as signal.db. When a forensic examiner acquires a mobile device, the first step is usually cataloguing and opening the .db files — they contain the user's digital life.

SQLite file structure

Every SQLite database begins with a 100-byte header. The first 16 bytes are the ASCII string "SQLite format 3" followed by a null byte (hex: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00). This magic string makes identification unambiguous — no other database engine uses it.

Bytes 16-17 store the page size as a big-endian 16-bit integer. Valid values are powers of two from 512 to 32768, with the special value 1 representing 65536. The default since SQLite 3.12.0 (2016) is 4096 bytes. The entire database is divided into fixed-size pages, and every read or write operates on whole pages.

Byte 18 is the file format write version: 1 for the legacy rollback journal, 2 for WAL (Write-Ahead Logging). Byte 19 is the read version with the same encoding. WAL mode, introduced in SQLite 3.7.0 (2010), allows concurrent readers while a single writer appends changes to a separate -wal file. A checkpoint operation merges the WAL back into the main database.

Bytes 56-59 store the text encoding: 1 for UTF-8, 2 for UTF-16LE, 3 for UTF-16BE. Once set during database creation, the encoding cannot change. Bytes 96-99 record the SQLite library version number that last modified the database, encoded as a single integer (for example, 3041002 for version 3.41.2).

B-tree storage

SQLite organizes data using B-tree structures. Table data lives in table B-trees keyed by the 64-bit integer rowid. Index data lives in index B-trees keyed by the indexed column values. Each B-tree node occupies one database page. Interior pages contain pointers to child pages and separator keys. Leaf pages contain the actual cell payloads — the serialized column values for each row.

Page 1 is special: it contains both the 100-byte database header and the root of the sqlite_master table, which is the schema. Every CREATE TABLE, CREATE INDEX, CREATE VIEW, and CREATE TRIGGER statement is stored as a row in sqlite_master with the original SQL text. This design means the schema is always inside the database file itself — there is no external catalog.

When a cell's payload exceeds the page's usable space, SQLite writes the overflow to one or more overflow pages linked in a chain. This mechanism allows individual values up to approximately 2 gigabytes, though in practice values exceeding a few megabytes are uncommon.

The extension ambiguity problem

The .db extension has no single owner. While SQLite dominates, the same three letters appear on files from entirely unrelated systems.

Berkeley DB was created at UC Berkeley by Margo Seltzer and Keith Bostic, first released in 1991 as part of 4.4BSD. It is an embedded key-value store — not a relational database — designed for fast lookups in system utilities and mail servers. Sleepycat Software commercialized it in 1996 after Netscape requested improvements for their LDAP server. Oracle acquired Sleepycat in February 2006 and changed the license to AGPL v3 in 2013, which sharply reduced adoption in open-source projects. Berkeley DB .db files have their own binary format that is completely unrelated to SQLite.

Windows Thumbs.db is an OLE Compound Document that caches folder thumbnail images as small JPEG files resized to 96x96 pixels. It appeared with Internet Explorer 4's Active Desktop Update for Windows 95-98 and persisted through Windows XP. Starting with Windows Vista, Microsoft moved thumbnail caching to a centralized location (thumbcache_xxx.db in the user's AppData folder), but Thumbs.db files still appear on network shares with write permissions. They are forensically significant: the FBI used Thumbs.db evidence in a 2008 case because the cache reveals which images were previously viewed in a folder, even after the originals are deleted.

Application databases from mobile apps, games, and desktop software also use .db. In most of these cases, the underlying format is actually SQLite — the application simply chose .db as the extension rather than .sqlite or .sqlite3.

How to identify an unknown .db file

The first bytes reveal the origin. Open the file in a hex editor or use the file command on macOS/Linux:

• Bytes starting with 53 51 4C 69 74 65 ("SQLite") at offset 0 indicate a SQLite database
• Bytes starting with D0 CF 11 E0 at offset 0 indicate an OLE Compound Document (Thumbs.db or similar)
• Other signatures indicate Berkeley DB or application-specific formats

Context helps too. A .db file on an Android or iOS device is almost certainly SQLite. A Thumbs.db in a Windows folder is a thumbnail cache. A .db file in a Unix /var directory may be Berkeley DB from a legacy system service.

Corruption and recovery

SQLite databases can become corrupted through several mechanisms: interrupted writes during transactions, deletion of the WAL or journal file while the database is open, copying the file while a write is in progress (hot copy), disk-full conditions during write operations, and hosting on NFS or network drives where POSIX advisory locking fails silently.

The PRAGMA integrity_check command walks every B-tree page and verifies structural consistency, checksums, and index alignment. It returns "ok" if no corruption is found. For damaged databases, the .recover command (added in SQLite 3.29.0) exports all recoverable data as SQL statements that can rebuild the database from scratch. Third-party tools like undark can extract data from heavily corrupted files by scanning for valid cell patterns outside the normal B-tree structure.

Prevention is more effective than recovery. Using WAL mode reduces corruption risk because the main database file is never modified during a transaction — only the WAL receives writes. The SQLite backup API provides a safe way to copy a live database without the risks of a file-system-level copy.

ACID compliance and concurrency

SQLite provides full ACID compliance — Atomicity, Consistency, Isolation, and Durability — through its journal mechanism. In rollback journal mode, the original page contents are written to a separate -journal file before modification. If a crash occurs mid-transaction, the journal replays on next open to restore the original state. In WAL mode, new pages are appended to a -wal file, and readers continue seeing the pre-transaction state until the writer commits.

The single-writer limitation is SQLite's most significant architectural constraint. Only one connection can write at a time; other writers must wait. This makes SQLite unsuitable for high-concurrency server workloads but perfectly adequate for local application storage, where write contention is rare. Read concurrency in WAL mode is excellent — any number of readers can proceed simultaneously without blocking the writer or each other.

Public domain and long-term commitment

SQLite's source code is in the public domain — not merely open source, but explicitly dedicated with no copyright restrictions whatsoever. Contributors must sign an affidavit placing their work in the public domain. This means any person or organization can use, modify, and distribute SQLite for any purpose without attribution or license compliance.

The Library of Congress recognizes SQLite as one of four recommended formats for long-term storage of datasets. The SQLite developers have stated their intention to maintain the software and the file format through the year 2050. Given that the format has remained backward-compatible since version 3.0 in 2004 — a database created twenty years ago opens in today's SQLite without modification — this commitment has a strong track record behind it.