SQLite database file format
A SQLite database file, a self-contained embedded SQL database.
- MIME type
- application/vnd.sqlite3
- Extensions
- .sqlite, .db, .sqlite3
- Category
- Data & databases
Magic-number signature
| 0x00000000 | 53514C69746520666F726D6174203300 | SQLite format 3· | 'SQLite format 3\0' header |
About the SQLite database format
A SQLite database is a sequence of fixed-size pages, all the same size, with the first hundred bytes of page one given over to a header that describes the whole file. Everything else — tables, indexes, the schema itself — is a B-tree of those pages, and the schema is simply a table called sqlite_schema stored on page one like any other. That uniformity is why the format is so stable: a file written by SQLite 3.0 in 2004 still opens today, and the project has committed to keeping it readable through 2050.
Header layout, byte by byte
| Offset | Length | Field | Meaning |
|---|---|---|---|
| 0 | 16 | Header string | The ASCII text 'SQLite format 3' followed by a NUL byte — sixteen bytes exactly. The 3 is the file-format generation, not the library version, and it has not changed since 2004. |
| 16 | 2 | Page size | Big-endian, and one of the very few big-endian formats you will meet on a desktop. A power of two between 512 and 32768, or the literal value 1 meaning 65536 because that will not fit in two bytes. 4096 is the modern default, so this field usually reads 10 00. |
| 18 | 1 | File format write version | 1 for the legacy rollback journal, 2 for write-ahead logging. A database in WAL mode needs its companion -wal and -shm files to be complete, which is why copying only the .db of a live database can lose recent commits. |
| 19 | 1 | File format read version | 1 or 2 with the same meaning. A value above 2 tells an old library that the file uses something it does not understand, and it must refuse rather than guess. |
| 20 | 1 | Reserved space per page | Bytes left unused at the end of every page, normally 0. Encryption extensions such as SEE set it to make room for a per-page nonce and authentication tag, so a non-zero value here on a file you cannot open is the explanation. |
| 21 | 1 | Maximum embedded payload fraction | Must be 64. It is a tuning knob that was never made adjustable, so any other value means the header is damaged rather than exotic. |
| 22 | 1 | Minimum embedded payload fraction | Must be 32. Together with the byte above and below it, these three constants are the cheapest integrity check the header offers — 64, 32, 32 in a row. |
| 23 | 1 | Leaf payload fraction | Must be 32. Fixed for the same historical reason: the values were meant to be configurable and never were. |
| 24 | 4 | File change counter | Incremented on every transaction that modifies the file. Readers use it to notice that a cached page is stale, so it is also the quickest way to see whether a database has been written to since you last looked. |
| 28 | 4 | Database size in pages | The in-header size. It is only trustworthy when it matches the version-valid-for number at offset 92; otherwise the true size is the file length divided by the page size, because an older library wrote to the file without maintaining this field. |
| 32 | 4 | First freelist trunk page | Page number where the list of unused pages begins, or 0 if there is none. Deleted rows leave their pages on this list rather than shrinking the file, which is why a database never gets smaller until VACUUM runs. |
| 36 | 4 | Freelist page count | How many pages are on that list. A large number against a small database is the signature of a file that has had a lot of data deleted and is holding onto the space — and of deleted rows still recoverable in a hex editor. |
| 40 | 4 | Schema cookie | Bumped whenever the schema changes. Prepared statements record the value they were compiled against and are recompiled automatically when it moves, which is how SQLite handles an ALTER TABLE under a live connection. |
| 44 | 4 | Schema format number | 1 through 4. 4 is current and has been the default since 2006; it is what enables DESC indexes and the boolean literals. An old library reading a format it does not know refuses the file rather than misreading it. |
| 48 | 4 | Default page cache size | A suggestion for the page cache, set by PRAGMA default_cache_size. Advisory only — the library is free to ignore it, and modern versions largely do. |
| 52 | 4 | Largest root b-tree page | Non-zero only in auto-vacuum or incremental-vacuum mode, where it records the largest root page so that pointer maps can be maintained. Zero means the database will never shrink on its own. |
| 56 | 4 | Text encoding | 1 for UTF-8, 2 for UTF-16 little-endian, 3 for UTF-16 big-endian. Set once when the database is created and immutable afterwards. UTF-8 is what essentially everything writes, and reading strings with the wrong assumption here is what produces a database full of mojibake. |
| 60 | 4 | User version | Untouched by SQLite itself; read and written by PRAGMA user_version. Applications use it as a schema migration counter, which makes it one of the most useful four bytes in the file when you are trying to work out which version of an app wrote a database. |
| 64 | 4 | Incremental vacuum mode | Non-zero when incremental-vacuum is in effect, and meaningful only when the largest-root-page field at offset 52 is also non-zero. |
| 68 | 4 | Application ID | Set by PRAGMA application_id so that a program can claim a database file as its own format. It is what lets a detector say 'this is a Firefox places database' rather than merely 'this is SQLite', when the application bothered to set it. |
| 72 | 20 | Reserved for expansion | Twenty bytes that must be zero. They have stayed zero for two decades, so a non-zero run here is a reliable sign that something has written over the header. |
| 92 | 4 | Version valid for number | The value the change counter had when the version number below was last written. When it does not equal the change counter at offset 24, an older library has modified the file and the in-header size at offset 28 must not be trusted. |
| 96 | 4 | SQLITE_VERSION_NUMBER | The library version that last wrote the file, encoded as major times one million plus minor times one thousand plus patch. 0x002E8DF8 is 3051000, meaning SQLite 3.51.0 — four bytes that tell you exactly which build touched the database last. |
Telling it apart from its neighbours
| Easily confused with | How to tell |
|---|---|
| WAL mode | Byte-identical for the first sixteen bytes; the difference is the pair of bytes at offsets 18 and 19, which read 2 rather than 1. It matters because the newest committed data may not be in the .db file at all — it is in the companion -wal file — so copying only the database silently loses transactions. Checkpoint first, or copy all three files together. |
| Encrypted SQLite | SQLCipher and similar tools encrypt the whole file including the header, so offset 0 is random-looking bytes and no detector will call it SQLite at all. The SEE extension takes the opposite approach and leaves the header readable while setting the reserved-space byte at offset 20 non-zero. A file that identifies as SQLite and still will not open is worth checking at offset 20. |
| OLE2 / CFB | The two general-purpose 'filesystem in a file' formats. SQLite opens with sixteen bytes of readable ASCII; a Compound File Binary container opens D0 CF 11 E0 A1 B1 1A E1. Both use fixed-size pages, both keep a free list, and both survive partial writes badly — but SQLite's format is public, versioned and documented field by field, which CFB's was not for its first fifteen years. |
| Parquet | The pairing that comes up whenever someone asks which single-file format to ship data in. Parquet is columnar, immutable and marked at both ends with 'PAR1'; SQLite is row-oriented, transactional and updatable in place. Parquet wins decisively on scanning one column of a billion rows; SQLite wins on anything that needs indexes, joins, or writing a row back. |
How these files break
- A -wal or -journal file that was lost. The database itself is completely intact and readers still fail or silently roll back, because a transaction was in flight when the copy was made. This is not damage to the .db at all — it is an incomplete copy — and it is the single most common way people corrupt a SQLite database.
- A file copied while being written. Because a commit touches several pages, snapshotting the file mid-transaction captures a mixture of old and new pages that no consistent state ever contained. The header parses perfectly, the page count is plausible, and a B-tree walk falls off into a page that belongs to another table. Use the backup API or VACUUM INTO rather than cp.
- Text-mode transfer or an editor that rewrote the file. Every multi-byte field is binary, and the header's own ASCII prefix contains no 0D 0A, so it survives untouched while every page boundary after it shifts. The file is still identified as SQLite and is unreadable from the first page onward.
- A truncated download. The header at offset 28 declares a page count larger than the file can hold, which is precisely the case SQLite checks: compare the declared size against the file length divided by the page size at offset 16. A short file may still open read-only and serve every table whose pages happened to land before the cut.
- Header bytes overwritten by another program. The three constants at offsets 21, 22 and 23 must read 64, 32, 32, and the twenty bytes at offset 72 must be zero. Those are the cheapest tests for a header that has been trampled, and they catch the case where a file has been written to by something that thought it owned the name.
Where the format stands today
SQLite is the most widely deployed database engine in existence and it is not going anywhere: every Android and iOS device, every mainstream browser, macOS, countless desktop applications and a great deal of embedded firmware all keep their state in SQLite files. It is first choice whenever the data has structure, needs querying, and lives on one machine — application settings, caches, message histories, offline stores, local analytics — because it gives real transactions and real SQL with no server, no configuration and no network. It is the wrong choice when many machines must write concurrently, since its locking is file-based and a network filesystem breaks it, and that is the boundary where PostgreSQL or MySQL start. It is also increasingly used as an application file format rather than a database: a single .sqlite carries a whole project, is updatable in place without rewriting, and survives a crash mid-save in a way that a custom binary format almost never does. Given the format's documented stability guarantee and the fact that its own test suite is larger than the library, it is a reasonable thing to bet an archive on.
Open SQLite database files on this site
Every one of these runs in your browser — the file never leaves your machine.
Related formats
Formats that are easy to confuse with SQLite database — compare their leading bytes.
| Format | First bytes | Extension |
|---|---|---|
| Parquet | 50 41 52 31 | .parquet |
| OLE2 / CFB | D0 CF 11 E0 A1 B1 | .doc |