SQLITE File Documentation

---

Technical details

Feature	Value
Full name	SQLite Database (SQLite 3 file format)
File extension	.sqlite, .sqlite3, .db, .db3, .s3db
MIME type	application/vnd.sqlite3
Format type	Self-contained single-file relational database
Developer	D. Richard Hipp / SQLite Development Team
Introduced	SQLite 1.0 in 2000; SQLite 3 file format since 2004
Licence	Public domain
Open standard	Yes — format is documented and stable
Byte order	Big-endian (multi-byte header fields)
Header size	100 bytes, on page 1 only
Magic number (hex)	53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00
Page size	512 to 65536 bytes (power of two), at header offset 16
Storage model	Fixed-size pages holding B-trees per table and index
Schema catalog	sqlite_schema (formerly sqlite_master)
Typing	Dynamic (type affinity), not rigid column types
Companion files	-wal (write-ahead log), -shm (shared memory)
Related extensions	.db, .sqlite3, .s3db, .sql, .accdb
Specification	sqlite.org/fileformat2.html

What is a SQLite file?

A .sqlite file is one entire relational database held in a single file by SQLite, a serverless database engine written by D. Richard Hipp. The first public release was SQLite 1.0 in 2000; the version 3 file format that every current file uses has been stable since 2004. Unlike client-server databases such as MySQL or PostgreSQL, there is no server process and no configuration: an application links the SQLite library and reads and writes the file directly. That design made SQLite the most widely deployed database engine in existence, shipping inside Android, iOS, every major web browser, macOS and countless desktop and mobile apps.

The file holds the schema, tables, indexes and data together. It is not a document you read top to bottom; it is a structured store you query. The whole database is a flat array of fixed-size pages, and inside those pages the engine builds a B-tree for every table and index. Everything below describes how those pages and trees are laid out, starting from the header at byte zero. The format is in the public domain and the SQLite team commits to long-term backward compatibility, so a file written today is meant to be readable for decades.

The 100-byte database header

Page 1 of every SQLite database opens with a fixed 100-byte header. No other page carries it. All multi-byte fields in this header are big-endian (most significant byte first). The first 16 bytes are the format's magic string, and the remaining 84 bytes record the parameters a reader needs before it can interpret a single row.

Offset  Size  Field
  0     16    Header string: "SQLite format 3\000"
 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    Bytes of reserved space at the end of each page
 21      1    Maximum embedded payload fraction (always 64)
 22      1    Minimum embedded payload fraction (always 32)
 23      1    Leaf payload fraction (always 32)
 24      4    File change counter
 28      4    Size of the database file in pages
 32      4    Page number of the first freelist trunk page
 36      4    Total number of freelist pages
 40      4    Schema cookie
 44      4    Schema format number (1..4)
 48      4    Default page cache size
 52      4    Page number of largest root b-tree (auto/incremental vacuum)
 56      4    Database text encoding (1=UTF-8, 2=UTF-16le, 3=UTF-16be)
 60      4    User version
 64      4    Incremental-vacuum mode flag
 68      4    Application ID
 72     20    Reserved for expansion (must be zero)
 92      4    version-valid-for number
 96      4    SQLITE_VERSION_NUMBER that last wrote the file

The page size at offset 16 is the single most important value: it is a big-endian 16-bit integer that must be a power of two from 512 to 32768. Because 65536 will not fit in two bytes, the value 0x00 0x01 is a special case meaning a 65536-byte page. The text-encoding field at offset 56 tells a reader whether strings are UTF-8 or UTF-16, and the reserved-space byte at offset 20 (normally zero) reserves a few bytes at the end of every page, used by extensions such as encryption. The change counter at offset 24 increments on every transaction so caches can detect that the file moved on.

Pages, and how a table becomes a B-tree

After the header, the file is simply page 1, page 2, page 3, and so on, each exactly the page size declared at offset 16. Page numbers start at 1, and byte offset of page N is (N-1) × page_size. There is no separate allocation map beyond the freelist; a page's role is discovered by following the trees that point to it.

Each table and each index is stored as its own B-tree spread across pages. SQLite uses two B-tree flavours. A table b-tree is keyed by a 64-bit integer rowid and stores the row's data in its leaves. An index b-tree is keyed by the indexed columns and stores no row data, only a pointer back to the rowid. Every b-tree page begins with a short page header that identifies its type through a one-byte flag.

Page-type flag (first byte of the b-tree page header)
  0x02  interior index b-tree page
  0x05  interior table b-tree page
  0x0A  leaf index b-tree page
  0x0D  leaf table b-tree page

Interior pages hold only keys and child-page pointers, steering a search toward the right leaf; leaf pages hold the actual cells. A single logical table is therefore a tree of pages: a root page recorded in the schema, interior pages that branch, and leaf pages that carry rows. To read a table sequentially, a cursor walks its b-tree in key order rather than reading pages in file order.

Cells, varints and the record format

The unit of storage inside a leaf page is a cell. A cell holds one row (for a table b-tree) as a length, a rowid and a payload. Lengths and rowids are stored as varints: a variable-length integer of one to nine bytes where the high bit of each byte signals whether another byte follows, so small numbers cost one byte. The payload itself is a single record encoding all the column values for that row.

Record (serial) format
  header:
    varint  total header length in bytes
    varint  serial type of column 1
    varint  serial type of column 2
    ...
  body:
    value of column 1 (as dictated by its serial type)
    value of column 2
    ...

Each column has a serial type code that names both its type and its byte length: 0 is NULL, 1–6 are big-endian signed integers of 1, 2, 3, 4, 6 and 8 bytes, 7 is an 8-byte IEEE 754 float, 8 and 9 are the constants 0 and 1 (stored in zero bytes), and any even value ≥12 is a BLOB while any odd value ≥13 is a text string, with the byte length computed from the code. This is where SQLite's dynamic typing lives: the type is a property of the value in each row, not of the column, which is why a column with INTEGER affinity can still physically hold a string.

When a row is too big for one page, the payload overflows onto a chain of overflow pages: the first bytes stay in the cell and the remainder spills to linked pages, each starting with a 4-byte pointer to the next. The embedded-payload-fraction constants at header offsets 21–23 govern exactly how much stays on the page before overflow begins.

The sqlite_schema catalog on page 1

SQLite is self-describing. The root table of every database is sqlite_schema (called sqlite_master in older releases), whose b-tree root is page 1 itself, immediately after the 100-byte header. It is an ordinary table with five columns, and it lists every table, index, view and trigger in the database.

sqlite_schema columns
  type      'table' | 'index' | 'view' | 'trigger'
  name      the object's name
  tbl_name  the table it belongs to
  rootpage  page number where this object's b-tree begins
  sql       the original CREATE statement text

To open a table by name, the engine scans sqlite_schema, finds the matching row, reads its rootpage, and starts walking that b-tree. The sql column stores the exact CREATE TABLE or CREATE INDEX text, which is how a tool can reconstruct the schema without a separate catalog file. The schema cookie at header offset 40 changes whenever the schema changes, letting prepared statements detect that they must be recompiled.

The write-ahead log and companion files

By default SQLite commits with a rollback journal, but most modern apps enable write-ahead logging (WAL), signalled by write/read format version 2 at header offsets 18–19. In WAL mode new and changed pages are appended to a separate -wal file rather than written back into the main database immediately, and a -shm shared-memory file holds an index into the WAL so multiple connections can find the newest version of each page.

The practical consequence: if you copy only the .sqlite file while an app is running, you may miss committed changes still sitting in the -wal. A clean copy requires checkpointing the WAL back into the main file (or copying the .sqlite, -wal and -shm together). A .db file, a .sqlite3 file and a .sqlite file are byte-identical formats; the extension is only a naming convention chosen by the app. A .sql file, by contrast, is not a database at all but a plain-text script of SQL statements.

Reading a database and exporting tables

Because the file is self-contained, a single tool reads the whole thing. DB Browser for SQLite (free, cross-platform) opens the file and shows its tables; the bundled sqlite3 command-line shell lists tables with .tables and dumps the entire database to a portable script with .dump. To move data into a spreadsheet, export each table to CSV (.mode csv then .output in the shell, or File › Export in DB Browser); Excel and LibreOffice Calc then open the CSV directly. There is no one-click binary conversion to Access .accdb or SQL Server .mdf, because those are different engines — you migrate through CSV or a SQL dump.

Frequently asked questions

Are .sqlite, .sqlite3 and .db the same format?

Yes. All three are the identical SQLite 3 file format, and all begin with the header string SQLite format 3. The extension is only a convention the writing application chose; any SQLite tool opens all of them, and renaming one to another changes nothing.

Why does every SQLite file start with “SQLite format 3”?

Those 16 bytes are the format's magic number. A reader checks them before trusting anything else, and they are what lets a tool recognise a SQLite database whatever its file name. The bytes are followed by the rest of the 100-byte header, including the page size at offset 16 and the text encoding at offset 56.

What are the -wal and -shm files next to my database?

They are the write-ahead log and its shared-memory index. In WAL mode SQLite appends changes to the -wal file and commits them into the main database at a checkpoint. They are part of the live database while the app runs, so copy them alongside the .sqlite or checkpoint first, or you may lose recent committed changes.

References

• SQLite — The SQLite Database File Format
• SQLite — The 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.