SQLite File Format
A SQLite database is a single file. The first 100 bytes are a fixed header describing everything else — page size, format version, schema cookie, freelist root, journal mode indicator, text encoding. The rest of the file is a sequence of identically-sized pages (the page size is in the header, between 512 and 65 KB). Every persistent structure — every table, every index, the schema itself — is a b-tree of these pages. The format is small, stable, well-documented, and the reason SQLite databases are routinely portable across operating systems and CPU architectures with byte-for-byte fidelity. This page walks the header field by field, explains page numbering and the lock-byte page, and shows how the header's flags encode whether the file is in rollback-journal or WAL mode.
Database File Layout
Key Numbers
Why a Single File
The 100-Byte Header
Every byte has meaning.
offset size field example 0 16 Magic: "SQLite format 3\0" 53 51 4c 69 74 65 ... 16 2 Page size in bytes 0x10 0x00 = 4096 18 1 File-format write version 1 = legacy, 2 = WAL 19 1 File-format read version 1 or 2 20 1 Reserved space per page usually 0 21 1 Max embedded payload fraction 64 (always) 22 1 Min embedded payload fraction 32 (always) 23 1 Leaf payload fraction 32 (always) 24 4 File change counter incremented per change 28 4 DB size in pages cached for sqlite3_total_changes 32 4 First freelist trunk page page number, 0 if none 36 4 Total freelist pages 40 4 Schema cookie incremented on schema change 44 4 Schema format number 1, 2, 3, or 4 48 4 Default page cache size 52 4 Largest btree root page for incremental vacuum 56 4 Database text encoding 1=UTF-8, 2=UTF-16le, 3=UTF-16be 60 4 User version PRAGMA user_version 64 4 Incremental vacuum mode 0 or 1 68 4 Application ID PRAGMA application_id 72 20 Reserved (zero) 92 4 Version-valid-for change counter 96 4 SQLite version number that last wrote
The header is read on every database open. The page size at offset 16 must be parsed first so subsequent operations know how to chunk the file. The schema cookie at offset 40 is checked on every connection to invalidate cached prepared statements when the schema has been modified by another process.
Page Format
Same shape for every btree page.
page header (8 bytes for leaves, 12 for interiors):
byte 0: page type
0x02 = interior index
0x05 = interior table
0x0a = leaf index
0x0d = leaf table
bytes 1-2: first freeblock offset
bytes 3-4: number of cells on this page
bytes 5-6: cell content area start (offset)
byte 7: fragmented free bytes count
bytes 8-11: rightmost child page (interior pages only)
then cell pointer array: 2 bytes per cell, listing offsets into this page
then unused space (between pointers and cells)
then cells: variable-sized, packed from page end backward The 8-byte header for leaves and 12-byte header for interiors gives the b-tree code a uniform shape. The cell types differ — interior cells are (key, child page); leaves carry payload.
Page 1: Special Treatment
The 100-byte header sits where page 1 would start.
Page 1 is special: its first 100 bytes are the database header, and the remaining bytes
hold the root of the schema btree (sqlite_master). The schema btree contains
one row per table, index, view, and trigger:
CREATE TABLE sqlite_master ( type TEXT, -- 'table', 'index', 'view', 'trigger' name TEXT, -- the object's name tbl_name TEXT, -- the table the object belongs to (or self) rootpage INTEGER, -- root page of this object's btree (0 for views) sql TEXT -- the original CREATE statement );
When SQLite opens a database, it reads page 1, finds the schema btree root at byte 100, and
walks it to discover all tables and indexes. From each row's rootpage, it knows
where to find that table or index's b-tree.
The Lock-Byte Page
A reserved phantom page for OS-level locking.
Bytes 1073741824 (1 GB) through 1073742335 (1 GB + 511 bytes) of the database file are
reserved for OS-level file locks. SQLite uses POSIX byte-range locks (or
LockFileEx on Windows) to coordinate concurrent access; locks within the
lock-byte page have known semantics: SHARED, RESERVED, PENDING, EXCLUSIVE all map to
specific bytes/ranges.
The page is "phantom" — its bytes are never read or written by SQLite as data. If the database is bigger than 1 GB, the lock-byte page is in the file but its content is undefined; SQLite skips over it during page numbering. If the database is smaller than 1 GB, the lock-byte page never exists; locks land beyond the end of the file, which most OSes still support.
Encoding the Journal Mode
Bytes 18 and 19 of the header.
The file-format write/read versions distinguish rollback-journal-only databases from WAL-capable ones:
byte 18 (write version) byte 19 (read version) meaning 1 1 legacy/rollback only 2 2 WAL mode active
A connection that doesn't support WAL (very old SQLite, <3.7.0) refuses to open files
with version 2. Switching to WAL via PRAGMA journal_mode=WAL writes 2 to both
bytes; switching back via PRAGMA journal_mode=DELETE writes 1. The actual
journal/WAL files live alongside (foo.db-journal or foo.db-wal);
the header byte just tells future opens which protocol the database expects.
Backward Compatibility
SQLite has not broken its file format since version 3.0 in 2004.
A database written by SQLite 3.0 (2004) is readable by SQLite 3.50 (2024). New features
have been added by appropriating reserved bytes in the header (e.g.,
application_id was once-reserved space) and by extending the schema format
version. A modern SQLite reading an old database adapts to the older schema format
(defaulting unset fields to legacy semantics). Conversely, an old SQLite reading a
modern database may refuse if the file uses a feature it doesn't understand (the
file-format-write-version is checked first).
Long-Term Support: SQLite officially commits to read compatibility through 2050. New versions remain able to read databases written by all 3.x predecessors.
FAQ
What's actually in the 100-byte database header?
Magic ('SQLite format 3\0' = 16 bytes), page size (2 bytes), file format write/read versions, reserved space per page, max/min embedded payload fractions, change counter (incremented on every change), in-header database size (in pages), freelist trunk page number, freelist page count, schema cookie, schema format version, default cache size, root page of incremental vacuum, text encoding (1=UTF-8, 2=UTF-16le, 3=UTF-16be), user version (PRAGMA user_version), incremental vacuum mode flag, application_id, version-valid-for change counter, and the SQLite version that last wrote it. Lots of metadata in 100 bytes.
Why are page sizes restricted to powers of 2?
Two reasons. (1) Modulo and division by power-of-2 are bit shifts — fast page number → byte offset arithmetic. (2) Disk sectors and OS page sizes are powers of 2 (512 B, 4 KB, 16 KB, 64 KB), and aligning page size to one of these matches the underlying I/O units, eliminating sub-page write amplification at the kernel and disk levels. SQLite supports 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536.
What is the lock-byte page?
Bytes 1073741824 through 1073742335 (i.e., 0x40000000 through 0x400001FF — a 512-byte range starting at exactly 1 GB) are reserved for OS-level file locks. Operating systems support range locking, so SQLite locks specific byte ranges within this region to coordinate readers/writers. The lock-byte page exists in any database larger than 1 GB; it's a phantom page that contains no data, just space for the kernel to keep its lock state. Smaller databases skip this complication entirely.
How does the schema cookie work?
It's a counter at offset 40 of the header that increments every time the schema changes (CREATE/DROP table, CREATE INDEX, etc.). Each connection caches the parsed schema; before reusing a prepared statement, it checks the schema cookie. If it changed, the cached schema is invalid and SQLite re-reads sqlite_master and re-prepares affected statements. This is how SQLite handles concurrent schema changes without coordination — the cookie is cheaper than a notification system.
Are journal-mode and synchronous in the file?
journal_mode is technically a per-connection PRAGMA, not stored in the file — different connections to the same file can use different journal modes (with caveats). However, WAL mode requires marking the file by changing the file-format-write-version field to 2 in the header. Once a file is in WAL mode, no rollback-journal-mode connection can write to it without first checkpointing and converting back. synchronous is purely per-connection and doesn't touch the file. PRAGMA settings stored in the file are user_version and application_id.
Why is the page size locked at creation?
Because changing page size means rewriting every page in the file. SQLite supports it via VACUUM with a different PRAGMA page_size (set the new size, run VACUUM, the rewrite happens). Setting page_size before any tables exist takes effect immediately; setting it after requires VACUUM. Common motivations: bigger pages (16 KB) reduce overflow chains for big BLOBs but waste space on small databases; smaller pages (1 KB) are best for tiny databases with high random-write rates on flash.