Jump to content
Setting up an AI agent? ReverseEngineering.wiki skill (works from the command line, no configuration): https://reverseengineering.wiki/skill/rewiki/SKILL.md · setup for every client, including MCP: https://reverseengineering.wiki/llms.txt · REWiki:Connect

Format:SQLite database file

From ReverseEngineering.wiki
Format
Developer D. Richard Hipp and the SQLite developers
Versions file format 1-4 (schema format 4 default since 3.3.0); WAL since 3.7.0
Magic 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ("SQLite format 3\0") at offset 0
Extensions .db, .sqlite, .sqlite3, .db3, .gpkg, .mbtiles, many application-specific
Endianness big
License public domain (format and reference implementation)
Website https://sqlite.org/fileformat2.html
On this wiki Facts · Code · Notes · Topics

The single-file database format of SQLite 3 (.db, .sqlite, .sqlite3 and countless application-specific extensions): the 16-byte magic "SQLite format 3\0", a 100-byte big-endian header on page 1, and fixed-size pages (512-65536 bytes) holding B-trees of variable-length records, overflow chains, a freelist and, with auto-vacuum, pointer maps.

Overview

An SQLite 3 database is one file of equal-sized pages (512 to 65536 bytes, a power of two), numbered from 1. Page 1 starts with a 100-byte header; every table and index is a B-tree of pages whose root page number is stored in the sqlite_schema table (called sqlite_master before 3.33.0), itself the B-tree rooted at page 1. Other pages are overflow pages (payload that did not fit in a cell), freelist pages and, in auto-vacuum databases, pointer-map pages. All integers in the file are big-endian. Apart from the data, a database can have a rollback journal (<db>-journal) or a write-ahead log (<db>-wal, with its shared-memory index <db>-shm): see Format:SQLite database file/Rollback journal and WAL. The file format has been stable and backwards compatible since 3.0.0 (2004); the library that writes it is Library:SQLite.

Common extensions are .db, .sqlite, .sqlite3, .db3, but many applications use their own (browser profiles, mobile apps, game saves and caches, GeoPackage .gpkg, MBTiles .mbtiles, Fossil repositories). Recognise it by the 16-byte magic at offset 0, never by the extension.

Header

The first 100 bytes of page 1 (Fact:SQLite database file/struct/Database header):

Offset Size Field
0x00 16 "SQLite format 3\0"
0x10 2 page size (512-32768; the value 1 means 65536)
0x12 1 write version: 1 = rollback journal, 2 = WAL
0x13 1 read version: 1 = rollback journal, 2 = WAL
0x14 1 reserved bytes at the end of every page (usable size U = page size - reserved; used by encryption and checksum extensions)
0x15 3 payload fractions 64, 32, 32 (fixed)
0x18 4 file change counter
0x1C 4 database size in pages (valid only if the version-valid-for number at 0x5C equals the change counter)
0x20 4 first freelist trunk page
0x24 4 number of freelist pages
0x28 4 schema cookie (incremented on every schema change)
0x2C 4 schema format number 1-4 (4 by default since 3.3.0; 4 adds serial types 8 and 9 and descending indexes)
0x30 4 suggested page cache size
0x34 4 largest root page in auto-vacuum / incremental-vacuum databases, else 0
0x38 4 text encoding: 1 UTF-8, 2 UTF-16le, 3 UTF-16be
0x3C 4 user version (PRAGMA user_version)
0x40 4 incremental-vacuum flag
0x44 4 application id (PRAGMA application_id)
0x48 20 reserved, zero
0x5C 4 version-valid-for number (the change counter when 0x60 was written)
0x60 4 SQLITE_VERSION_NUMBER of the library that last wrote the file

Offset 0x60 names the SQLite version an application ships without looking at its code: a database written by 3.53.1 has 00 2E 95 C9 (3053001) there. Offset 0x44 names the application for registered formats (magic.txt in the source tree): 0x0F055111-0x0F055113 Fossil, 0x47504B47 / 0x47503130 GeoPackage, 0x4D504258 MBTiles, 0x45737269 Esri, 0x42654462 Bentley BeSQLite (Fact:SQLite database file/identification/Application id at offset 0x44). Many applications instead put a schema version in user_version (0x3C).

GeoPackage files are SQLite databases with application_id 0x47504B47 (or the older 0x47503130 / 0x47503131) and a user_version such as 10200; see Format:GeoPackage.

B-tree pages

A B-tree page starts with an 8-byte (leaf) or 12-byte (interior) header, at offset 100 on page 1 and offset 0 elsewhere: type byte, first freeblock (u16), cell count (u16), start of the cell content area (u16, 0 means 65536), fragmented free bytes (u8) and, on interior pages, the right-most child page (u32). The cell pointer array (u16 offsets from the page start, in key order) follows; cells are packed from the end of the page downwards. Type bytes (Fact:SQLite database file/enum/B-tree page type):

Type Page Cell
0x02 index interior u32 left child, varint payload size, payload, [u32 first overflow page]
0x05 table interior u32 left child, varint rowid (the largest key in that child)
0x0A index leaf varint payload size, payload, [u32 first overflow page]
0x0D table leaf varint payload size, varint rowid, payload, [u32 first overflow page]

The type byte is the PTF flag set: 0x01 intkey, 0x02 zerodata, 0x04 leafdata, 0x08 leaf. Tables (rowid tables) keep the data in the leaves only; indexes and WITHOUT ROWID tables are index B-trees whose payload is the whole record (for WITHOUT ROWID: the primary-key columns first).

Varints are 1-9 bytes, big-endian groups of 7 bits with the high bit as continuation, and all 8 bits of a ninth byte (Fact:SQLite database file/behavior/Varint encoding). This is not LEB128: the most significant group comes first.

Overflow. With U the usable page size, a cell keeps the whole payload P on the page if P <= X, where X = U - 35 on table leaves and X = (U-12)*64/255 - 23 on index pages. Otherwise it keeps K = M + (P - M) % (U - 4) bytes if K <= X, else M bytes, with M = (U-12)*32/255 - 23, and the rest goes to a chain of overflow pages (u32 next page, then U - 4 bytes of data each) (Fact:SQLite database file/behavior/Local payload size). For U = 4096: X = 4061 (table) or 1002 (index), M = 489.

Records

The payload of a table row or index entry is a record: a varint header size (counting itself), one varint serial type per column, then the column values in order (Fact:SQLite database file/enum/Record serial type):

Serial type Bytes Value
0 0 NULL
1, 2, 3, 4, 5, 6 1, 2, 3, 4, 6, 8 big-endian two's-complement integer
7 8 IEEE 754 double, big-endian
8, 9 0 the integers 0 and 1 (schema format 4)
10, 11 reserved for internal use; never in a well-formed file
N >= 12, even (N-12)/2 BLOB
N >= 13, odd (N-13)/2 text in the database encoding (0x38), no terminator

A row may have fewer values than the table has columns (columns added by ALTER TABLE ADD COLUMN later): the missing ones take their default value.

Schema table

Page 1 is the root of sqlite_schema (sqlite_master), a rowid table with five columns: type (table, index, view, trigger), name, tbl_name, rootpage and the original CREATE statement text. Parse the SQL to name the columns. Internal objects start with sqlite_: sqlite_sequence (AUTOINCREMENT counters), sqlite_stat1/sqlite_stat4 (ANALYZE), sqlite_autoindex_<table>_<n> (UNIQUE and PRIMARY KEY indexes). FTS3/4/5 and R-Tree virtual tables appear as a CREATE VIRTUAL TABLE row plus ordinary shadow tables (<name>_content, <name>_data, <name>_idx, <name>_node...) that hold their data.

Freelist, pointer map and lock-byte page

  • Freelist. Header 0x20 points to the first trunk page: u32 next trunk, u32 leaf count, then u32 leaf page numbers. The content of leaf pages is undefined: unless the library was built with SQLITE_SECURE_DELETE or PRAGMA secure_delete is on, they and the freeblocks inside B-tree pages keep deleted data (Fact:SQLite database file/behavior/Deleted rows stay in the file).
  • Pointer map (auto-vacuum, header 0x34 non-zero): page 2 and every (U/5 + 1)-th page after it hold 5-byte entries (type, u32 parent page) for the following pages; types 1 root page, 2 free page, 3 first overflow page, 4 later overflow page, 5 non-root B-tree page (Fact:SQLite database file/enum/Pointer-map entry type).
  • Lock-byte page. The page that contains file offsets 0x40000000-0x400001FF (PENDING_BYTE and the shared lock range) is never used for data, so databases over 1 GiB have one dead page at 0x40000000 / page size + 1.

Topics

  • Rollback journal and WAL — The side files of an SQLite database that hold transactions in flight: the rollback journal (<db>-journal, magic D9 D5 05 F9 20 A1 63 D7, sector-aligned headers

Pitfalls

  • INTEGER PRIMARY KEY is not in the record. A column declared INTEGER PRIMARY KEY is an alias of the rowid: the record stores NULL (serial type 0) for it, and the value is the cell's rowid.
  • REAL columns may hold integers on disk. A value in a column with REAL affinity that has no fractional part is stored with an integer serial type (1.0 as type 9, -0.0 as type 8, losing the sign) and turned back into a double when read; a raw reader must apply the column affinity from the CREATE TABLE text (Fact:SQLite database file/behavior/REAL values stored as integers).
  • The database file alone may be stale. In WAL mode committed transactions live in <db>-wal until a checkpoint; copy the -wal file too, or read it with Format:SQLite database file/Rollback journal and WAL.
  • Header 0x1C can be stale: versions before 3.7.0 ignored it, so it is valid only when the version-valid-for number (0x5C) equals the change counter (0x18) and it is non-zero; otherwise the size is file length / page size.
  • Reserved bytes (0x14 non-zero) usually mean an encryption or checksum VFS (SQLCipher, SEE, the cksumvfs extension). An encrypted database (SQLCipher, SEE) does not start with the magic at all: page 1 is encrypted too, except for SQLCipher's optional plaintext-header mode.

Code & repositories

  • Code:SQLite database file/sqlite3info (Python): header, schema, B-tree walker with record decoder and overflow chains, page map (freelist, pointer map, lock-byte page), WAL and rollback-journal checkers and a library scanner, standard-library Python that never opens files through SQLite.
  • https://github.com/sqlite/sqlite @ 43a48f77585a88940c727f33682151418f544059 · license: blessing (declared by the contributor; GitHub's detector did not recognize LICENSE.md)
    Official Git mirror of the SQLite Fossil repository (public domain: src/, ext/, test/, tool/). File format layouts are documented in src/btreeInt.h, src/wal.c and src/pager.c; serial types in src/vdbeaux.c; source-id, keyword and compile-option generators in tool/ (mksourceid.c, mkkeywordhash.c, mkctimec.tcl); JSONB in doc/jsonb.md; registered application ids in magic.txt; fuzz databases under test/. Release tags (version-3.X.Y) to diff a binary against; the amalgamation itself is a build product downloaded from sqlite.org.
    Tested on: Read at this commit (3.55.0 development); layouts checked against databases, WALs and journals written by SQLite 3.53.1 and against 11 Windows builds (3.8.8 to 3.53.4) with Code:SQLite database file/sqlite3info (Python). Not built. · checked 2026-10-17; last activity 2026-10-17

Evidence & references

  • Source of https://github.com/sqlite/sqlite (official mirror of the Fossil repository) at commit 43a48f77585a88940c727f33682151418f544059 (3.55.0 development): src/btreeInt.h lines 40-218 (file, page, cell and freelist layout), src/btree.c lines 1219-1233 and 3471-3474 (local payload size), src/vdbeaux.c lines 3885-3904 (serial types), src/os.h lines 140-163 (lock-byte page), src/vdbe.c lines 374-384 and 3644-3649 (REAL affinity), magic.txt (application ids).
  • Tested on databases made with the SQLite 3.53.1 of CPython 3.11 (default settings; 64 KiB pages with auto-vacuum and UTF-16le; WITHOUT ROWID; rows with 29 KB payloads): the Python reader on this wiki reproduced every row and index key exactly as SQLite returned them.
  • The official description is https://sqlite.org/fileformat2.html.