The first 100 bytes of the file, at the start of page 1, are the database header. It names the format and holds the settings a reader needs before it can find anything else: the page size, the reserved space at the end of each page, the text encoding and the start of the freelist. Every number in it is big-endian. The rest of page 1 is the root of the schema table, a b-tree page whose page header starts at offset 100.
Fields
| Offset | Size | Type | Name | Description |
|---|---|---|---|---|
| 0 | 16 | bytes | Header string | SQLite format 3 and a zero byte. |
| 16 | 2 | u16 | Page size | A power of two from 512 to 32,768; 1 means 65,536. |
| 18 | 1 | u8 | Write version | 1 rollback journal, 2 write-ahead log. |
| 19 | 1 | u8 | Read version | 1 rollback journal, 2 write-ahead log. |
| 20 | 1 | u8 | Reserved space | Bytes left unused at the end of every page. Usually 0. |
| 21 | 1 | u8 | Maximum embedded payload fraction | Must be 64. |
| 22 | 1 | u8 | Minimum embedded payload fraction | Must be 32. |
| 23 | 1 | u8 | Leaf payload fraction | Must be 32. |
| 24 | 4 | u32 | File change counter | Incremented when the file is unlocked after a change. |
| 28 | 4 | u32 | Database size in pages | The in-header database size; valid only in some cases. |
| 32 | 4 | u32 | First freelist trunk page | 0 when the freelist is empty. |
| 36 | 4 | u32 | Freelist pages | The number of freelist pages, trunks and leaves together. |
| 40 | 4 | u32 | Schema cookie | Incremented whenever the schema changes. |
| 44 | 4 | u32 | Schema format number | 1 to 4; 0 in a database with no schema. |
| 48 | 4 | i32 | Default page cache size | A suggested cache size in pages. |
| 52 | 4 | u32 | Largest root b-tree page | Not zero in auto-vacuum files; 0 otherwise. |
| 56 | 4 | u32 | Text encoding | 1 UTF-8, 2 UTF-16LE, 3 UTF-16BE. |
| 60 | 4 | i32 | User version | Free for the program to use. |
| 64 | 4 | u32 | Incremental vacuum | Not zero for incremental vacuum. |
| 68 | 4 | i32 | Application ID | Says which program the file belongs to. |
| 72 | 20 | bytes | Reserved for expansion | Must be zero. |
| 92 | 4 | u32 | Version-valid-for number | The change counter when offset 96 was written. |
| 96 | 4 | u32 | SQLite version number | The version of SQLite that last wrote the file. |
A real header
The header of a database from the files examined for this site: 4,096-byte pages, UTF-8, schema format 4, last written by SQLite 3.8.2. The header string is marked.
00000000 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00
00000010 10 00 01 01 00 40 20 20 00 00 1C 43 00 00 00 FD
00000020 00 00 00 00 00 00 00 00 00 00 00 07 00 00 00 04
00000030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1C 43
00000060 00 2D E6 02- Offset 16:
10 00, a page size of 4,096. Offsets 18 and 19:01 01, rollback journal. Offset 20:00, no reserved space. Offsets 21 to 23:40 20 20, the required 64, 32 and 32. - Offset 24:
00 00 1C 43, change counter 7,235. Offset 28:00 00 00 FD, 253 pages. Offset 92 is00 00 1C 43too, equal to the change counter, so the size of 253 pages is valid. - Offsets 32 and 36: zero, no freelist. Offset 40:
00 00 00 07, schema cookie 7. Offset 44:00 00 00 04, schema format 4. - Offset 56:
00 00 00 01, UTF-8. Offset 96:00 2D E6 02= 3,008,002, SQLite 3.8.2.
The fields in detail
Header string
The 16 bytes 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00: the text
SQLite format 3 followed by a zero byte. Every SQLite 3 database starts
with them, whatever the version that wrote it.
Page size
The size of every page in bytes: a power of two from 512 to 32,768. The value 1 means 65,536, which does not fit in 16 bits; SQLite 3.7.1 (2010) and later can use it. Read as a little-endian number, the same two bytes give the page size divided by 256, which also covers 65,536.
The page size is the same for every page and fixes where each page starts: page N begins at byte (N − 1) × page size. See Pages.
Write and read versions
Two bytes that say how the database is written. Each is 1 for the rollback journal, the older way, or 2 for write-ahead logging (WAL), from SQLite 3.7.0 (2010). See Journal modes.
| Value | At offset 18 (write) | At offset 19 (read) |
|---|---|---|
1 | rollback journal | rollback journal |
2 | write-ahead log | write-ahead log |
| above 2 | if the read version is 1 or 2, SQLite may read the file but opens it read-only | SQLite refuses to read the file |
This is how the format leaves room for change: a later library that writes something an older one could not read sets a higher read version, and the older library stops rather than read it wrongly. Libraries before 3.7.0 refuse a WAL database for this reason.
Reserved space
The number of bytes left unused at the end of every page, for extensions to use. The SQLite Encryption Extension keeps a nonce and a checksum there, for example. It is usually 0, and it may be odd. The page size minus the reserved space is the usable size of a page, which the b-tree and overflow rules work in. It must be at least 480.
Among the files examined for this site, 3 of 189 databases reserved 12 bytes; the rest reserved none.
Payload fractions
Three bytes that were meant to let a database tune how much of a cell stays on its page. They were never made adjustable: the maximum embedded payload fraction must be 64, the minimum embedded payload fraction 32, and the leaf payload fraction 32. The values a reader actually uses are fixed formulas, given under overflow thresholds.
File change counter
Incremented each time the database is unlocked after it was changed. Other connections use it to see whether their cached pages are still current. In WAL mode changes are detected through the WAL index instead, and the counter may not be incremented on every transaction.
Database size in pages
The in-header database size: the number of pages in the database. A reader trusts it only when it is not zero and the change counter at offset 24 equals the version-valid-for number at offset 92. Otherwise the size comes from the length of the file divided by the page size.
The rule exists because versions before 3.7.0 did not keep this field up to date. They do update the change counter but not offset 92, so after an older library has changed the file the two no longer match, and the stale size is ignored. Pages past a valid in-header size are not part of the database.
Freelist
Offset 32 gives the page number of the first freelist trunk page, or 0 when no page is free. Offset 36 gives the total number of freelist pages, trunk pages and leaf pages together. See Freelist.
Schema format number
Which features of the record and index format the database may use. A library that does not know a number refuses the file.
| Value | Meaning | Read and written since |
|---|---|---|
0 | No schema yet: an empty database. | – |
1 | Understood by every SQLite 3. | 3.0.0 (June 2004) |
2 | Rows may hold fewer values than the table has columns, after ALTER TABLE ADD COLUMN. | 3.1.3 (February 2005) |
3 | Added columns may have non-NULL defaults. | 3.1.4 (March 2005) |
4 | DESC in an index is respected, and records may use serial types 8 and 9. | 3.3.0 (January 2006) |
New files use format 4, the default since SQLite 3.7.10 (2012); between 3.3.7 and
3.7.10 the default was 1. PRAGMA legacy_file_format, the
SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option and building SQLite with
SQLITE_DEFAULT_FILE_FORMAT=1 make new files format 1. Of the databases
examined for this site, 173 were format 4, 11 format 1 and 5 format 0.
Default page cache size
A suggested size for the page cache, in pages, set by
PRAGMA default_cache_size. Its absolute value is used. It is only a
suggestion: a program can set its own cache size, and 0 leaves SQLite’s
default.
Auto-vacuum and incremental vacuum
Offset 52 is not zero in a database that uses auto-vacuum or incremental vacuum, and then holds the largest root page number of any table or index. Not zero also means the file has pointer-map pages. In other databases it is 0.
Offset 64 tells the two modes apart: not zero for incremental vacuum, where free pages are given back only when the program asks; 0 for full auto-vacuum, where they are given back at every commit. It must be 0 when offset 52 is 0.
| Offset 52 | Offset 64 | Mode |
|---|---|---|
| 0 | 0 | no auto-vacuum (the default) |
| not zero | 0 | full auto-vacuum |
| not zero | not zero | incremental vacuum |
Auto-vacuum came with SQLite 3.1.0 (2005) and incremental vacuum with 3.4.0 (2007).
Text encoding
The encoding of every text value and every name in the database. There is one encoding for the whole file; it is chosen when the database is created and cannot be changed afterwards.
| Value | Encoding |
|---|---|
1 | UTF-8 |
2 | UTF-16, little-endian |
3 | UTF-16, big-endian |
No other values are allowed. A database that has never had a table can hold 0 here; the explorer reads it as UTF-8.
User version
A 32-bit signed number set and read with PRAGMA user_version. SQLite
itself never uses it; programs often store the version of their own schema
here.
Application ID
A 32-bit signed number set with PRAGMA application_id, so that a
program can mark a database as its own file format. The registered values are
listed in the file magic.txt in the SQLite source. Some programs choose
four letters read as a big-endian number, and the explorer shows a value of four
printable characters that way. A database that no program has marked holds 0.
Reserved for expansion
Twenty bytes, offsets 72 to 91, kept for later use. They must be zero.
Version-valid-for number
The value of the change counter when the SQLite version number at offset 96 was stored. When it equals the change counter, both offset 96 and the in-header database size are current.
SQLite version number
SQLITE_VERSION_NUMBER of the library that most recently changed the
file: X × 1,000,000 + Y × 1,000 + Z for version X.Y.Z. For example 3008002 is 3.8.2
and 3054000 is 3.54.0.