DB File Documentation

---

Technical details

Feature	Value
Full name	SQLite Database File (dominant meaning of the generic .db)
File extension	.db
MIME type	application/vnd.sqlite3
Format type	Single-file relational database, fixed-size pages
Developer	D. Richard Hipp / the SQLite Consortium
Introduced	SQLite 3 file format, 2004
Specification	SQLite Database File Format (public, versioned)
Open standard	Yes; format is in the public domain
Byte order	Big-endian for all multi-byte header integers
Header size	100 bytes at offset 0
Magic number	53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 (“SQLite format 3\0”)
Page size	512 to 65536 bytes, a power of two (default 4096)
Internal structure	B-tree pages, one sqlite_schema catalog table
Companion files	-wal, -shm or -journal sit beside the .db
Related extensions	.sqlite, .sqlite3, .s3db, .db3
Also uses this extension	Windows Thumbs.db (OLE, D0 CF 11 E0); legacy dBASE / Paradox tables; private app stores
Contents	Data, not executable code
Specification URL	sqlite.org/fileformat2.html

What is a .db file?

The .db extension is generic: it just says “this is a database” without naming the engine that wrote it. In practice most .db files you meet on a phone or inside an application’s data folder are SQLite databases. SQLite is a self-contained, serverless database engine created by D. Richard Hipp, first released in 2000; the version-3 file format used today was introduced in 2004 and has been kept backward-compatible ever since. It is the most widely deployed database in the world: Android and iOS apps, web browsers (history, cookies, bookmarks), and countless desktop programs all keep their state in a single SQLite file.

The defining fact about SQLite is that an entire relational database (tables, indexes, triggers and views) lives in one ordinary file that starts with the ASCII string SQLite format 3\000. Everything below describes that format. At the end this article covers what to do when a .db is not SQLite, because the extension is shared by several unrelated formats.

The 100-byte database header

The first 100 bytes of every SQLite database are a fixed header that a reader parses before touching any data. All multi-byte values in it are big-endian. The most important fields:

offset size field
  0     16   "SQLite format 3\000"     magic string
 16      2   page size in bytes (power of two, 512..32768; 1 means 65536)
 18      1   file format write version (1 = legacy/rollback, 2 = WAL)
 19      1   file format read version
 20      1   reserved bytes at end of each page
 24      4   file change counter
 28      4   database size in pages
 32      4   page number of first freelist trunk page
 40      4   schema cookie
 44      4   schema format number (1..4)
 48      4   default page cache size
 56      4   text encoding (1 = UTF-8, 2 = UTF-16le, 3 = UTF-16be)
 60      4   user version (set by PRAGMA user_version)
 68      4   "Application ID" (PRAGMA application_id)
 92      4   version-valid-for number
 96      4   SQLITE_VERSION_NUMBER that last wrote the file

The 2-byte page size at offset 16 sets the granularity of the whole file: the database is an array of equal-size pages, and every internal pointer is a 1-based page number, not a byte offset. Because the field is only two bytes, the value 1 is a special encoding for a 65536-byte page. The text encoding byte at offset 56 tells a reader whether stored strings are UTF-8 or UTF-16, and the file format versions at offsets 18–19 flag whether the database uses the older rollback journal or write-ahead logging. Page 1 always begins with this 100-byte header, so page 1’s B-tree content actually starts at byte offset 100.

Pages and the B-tree layout

SQLite stores tables and indexes as B-trees, one tree per table or index, each spread across pages of the size declared in the header. There are two flavours: a table B-tree keyed by a 64-bit signed integer rowid, and an index B-tree keyed by the indexed columns. Interior pages hold keys and child-page pointers; leaf pages hold the actual rows (called cells).

Each B-tree page opens with an 8-byte header (12 bytes on interior pages, which carry an extra right-child pointer):

offset size meaning
  0      1   page type: 0x0D leaf table, 0x05 interior table,
                        0x0A leaf index, 0x02 interior index
  1      2   byte offset of first freeblock (0 if none)
  3      2   number of cells on the page
  5      2   start of the cell content area
  7      1   number of fragmented free bytes
 (8      4   right-most child pointer, interior pages only)

Immediately after the page header comes the cell pointer array: a list of 2-byte offsets, one per cell, sorted in key order. The cells themselves grow from the end of the page toward the middle, so free space sits between the pointer array and the cell content. A single cell packs the rowid (as a variable-length integer), the payload length, and the record itself; if a row is too big for one page it spills onto a chain of overflow pages, with the first four bytes of each overflow page pointing to the next.

The record format and serial types

Inside a cell, a row is stored in SQLite’s record format. A record is a header followed by the column values. The header is a varint giving the header’s own length, then one serial type varint per column that both names the column’s type and, for text and blobs, encodes its length:

Serial type	Meaning	Bytes
0	NULL	0
1–6	Big-endian signed integer	1, 2, 3, 4, 6, 8
7	IEEE 754 float	8
8 / 9	Integer constant 0 / 1	0 (stored in the type itself)
N≥12, even	BLOB of (N−12)/2 bytes	variable
N≥13, odd	Text of (N−13)/2 bytes	variable

This is why SQLite is dynamically typed: the type lives with each value, not with the column. A varint here is SQLite’s big-endian base-128 encoding, one to nine bytes, where the high bit of each byte signals whether more bytes follow. The constants 8 and 9 store the integers 0 and 1 in zero payload bytes, which keeps boolean-like columns compact.

The sqlite_schema catalog

Page 1 always holds the root of a special table named sqlite_schema (older names: sqlite_master). It is an ordinary table B-tree, but its rows describe every other object in the database. Each row has five columns: type (table, index, view or trigger), name, tbl_name, rootpage (the page number where that object’s B-tree starts), and sql (the original CREATE statement). To open a table, SQLite reads sqlite_schema, finds the matching row, and jumps to the page number in rootpage. Because the schema is itself just a table, a database is fully self-describing: the file carries the SQL that defines its own structure.

WAL, rollback journals and the companion files

A .db rarely travels completely alone while it is in use. To make writes atomic and crash-safe, SQLite keeps a sidecar file. In the traditional rollback journal mode a file.db-journal holds the original pages so a failed transaction can be undone. In write-ahead logging (WAL) mode, new pages are appended to file.db-wal and a shared-memory index file.db-shm coordinates readers; the WAL is periodically checkpointed back into the main file. If you copy a database that was open in WAL mode without its -wal file, you may capture an older state, because the newest committed rows can still be sitting in the WAL. This is a common cause of “my extracted .db is missing recent data”.

A real example: WhatsApp msgstore.db

One of the highest-traffic .db files is WhatsApp’s msgstore.db, the chat message store. Pulled straight from an app’s private data directory it is a plain SQLite file: open it in a viewer and you see tables such as message and chat. The confusion arises with phone backups, which are exported as msgstore.db.crypt14 or msgstore.db.crypt15. Those are encrypted with the account key and are not SQLite until decrypted, so no SQLite tool can read a .crypt file directly. The direction is always decrypt-to-.db, never the reverse.

When a .db is not SQLite

Because .db is a generic label, several unrelated formats reuse it, and the only reliable test is the first bytes of the file. Windows writes a hidden Thumbs.db thumbnail cache that is an OLE compound file starting with D0 CF 11 E0 A1 B1 1A E1, safe to delete. Older desktop databases from dBASE and Paradox reused .db in the 1980s and 1990s and open through import in tools like Access or LibreOffice Base. Some games and mail clients write .db in a private, undocumented layout that only the owning program understands. If a .db refuses to open in a SQLite viewer, check the header in a hex editor: SQLite format 3 means SQLite, D0 CF 11 E0 means an OLE file, and anything else is application-defined.

FAQ

How can I tell whether a .db is really SQLite?

Read the first 16 bytes. If they spell SQLite format 3 (hex 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00) it is a SQLite 3 database. Any other leading bytes mean a different format, so the extension alone is not proof.

Why does SQLite store a page size instead of using byte offsets?

Every internal pointer is a page number, so the engine can address the file in fixed-size units and cache whole pages. The 2-byte field at offset 16 fixes that unit for the life of the database, which is why the page size cannot change after creation without a full VACUUM.

Why did my copied database lose recent rows?

The database was open in WAL mode and the newest committed pages were still in the -wal sidecar. Copy the .db, -wal and -shm files together, or checkpoint the database first, so the main file holds the latest state.

References

• SQLite — Database File Format (official specification)
• SQLite — Most Widely Deployed Database Engine
• DB Browser for SQLite (official site)

Feedback

Was this page helpful?

Yes No

Glad to hear it! Please tell us how we can improve.

Sorry to hear that. Please tell us how we can improve.