The SQLite Database File: Header, Page Size, Schema Cookie, and File Format

Learning outcomes

A SQLite database looks deceptively simple in a file browser: usually one .db file. Chapter 8 explained how transactions protect that file, Chapter 9 explained how concurrent connections coordinate around it, and Chapter 10 showed how tables and indexes create different access paths. This chapter now asks a lower-level question: what is actually inside the file, and which parts of that knowledge help an application developer make safer decisions?

A stable file format is one of SQLite’s deployment strengths

SQLite is not merely a library that happens to write opaque implementation files. Its main database file format is publicly documented and has been used across SQLite 3 releases for decades. That makes SQLite valuable as an application file format: a file produced on one supported platform can normally be moved to another and opened by SQLite without a server-side export/import ceremony.

That portability does not turn direct byte editing into normal administration. The file contains B-trees, free-page structures, overflow chains, schema metadata, and transactional invariants. A hex editor can observe the documented header safely on a closed or copied file, but ordinary changes belong through SQL or documented APIs so SQLite can maintain every dependent structure consistently.

Pages: the allocation unit of the main database file

SQLite divides the main database into equal-sized pages. A page is the unit that the pager loads, writes, journals, and caches. Current SQLite permits page sizes that are powers of two from 512 through 65,536 bytes. The long-standing default for new databases is commonly 4,096 bytes, but a build/platform can choose differently, so observe rather than assume.

SELECT sqlite_version();PRAGMA page_size;PRAGMA page_count;PRAGMA freelist_count;PRAGMA encoding;PRAGMA application_id;PRAGMA user_version;

For the main database, page_count × page_size normally matches the database file’s logical length in bytes once the file has been flushed to disk. That arithmetic includes pages currently on the freelist; free pages are still pages in the file.

The first 100 bytes: the database header

Page 1 is special because its first 100 bytes are a database-wide header. Every valid SQLite 3 database begins with the 16-byte magic string SQLite format 3. The documented header then records fields SQLite needs to interpret the rest of the file.

Schema cookie versus application-managed version fields

The schema cookie is internal SQLite metadata. It changes when the database schema changes; prepared statements compare it with the version they were compiled against and may be reprepared or fail with SQLITE_SCHEMA. Application code should not manually write the internal schema-version field as a migration technique.

By contrast, PRAGMA user_version and PRAGMA application_id are explicitly available for applications to manage. Chapter 12 will use them as part of database-as-file-format validation and migration handoff.

-- FieldNotes demonstration values in a disposable database.PRAGMA application_id = 1179537236;PRAGMA user_version = 11;PRAGMA application_id;PRAGMA user_version;

Encoding belongs to the database, not the terminal

Chapter 4 separated SQLite’s database text encoding from console/application display encoding. The database header stores one of UTF-8, UTF-16le, or UTF-16be for text records. PRAGMA encoding observes that choice. Once the database has been created, attempts to change its encoding are ignored; this is a creation-time format decision rather than a per-query display preference.

Read-only header observation with Python

This lab reads a copy or closed disposable database. It does not write bytes. Python’s struct module lets us interpret documented big-endian integer fields and compare them with SQLite-level PRAGMAs.

from pathlib import Pathimport structpath = Path("fieldnotes-storage.db")with path.open("rb") as f:    header = f.read(100)assert header[:16] == b"SQLite format 3\x00"raw_page_size = struct.unpack(">H", header[16:18])[0]page_size = 65536 if raw_page_size == 1 else raw_page_sizepage_count_header = struct.unpack(">I", header[28:32])[0]freelist_pages = struct.unpack(">I", header[36:40])[0]schema_cookie = struct.unpack(">I", header[40:44])[0]encoding_code = struct.unpack(">I", header[56:60])[0]user_version = struct.unpack(">I", header[60:64])[0]application_id = struct.unpack(">I", header[68:72])[0]last_writer_version = struct.unpack(">I", header[96:100])[0]print("magic:", repr(header[:16]))print("page_size:", page_size)print("header page_count:", page_count_header)print("freelist pages:", freelist_pages)print("schema cookie:", schema_cookie)print("encoding code:", encoding_code)print("user_version:", user_version)print("application_id:", application_id)print("last writer version number:", last_writer_version)

Do not infer that every header field is safe to modify because you can parse it. Observation is the pedagogical purpose; SQLite remains responsible for writes.

Growth lab: page_count before and after inserts

Create the lab in its own directory. The exact page counts depend on the page size, SQLite build, row packing, and content, so record your own values rather than copying a screenshot.

DROP TABLE IF EXISTS storage_probe;CREATE TABLE storage_probe(  probe_id INTEGER PRIMARY KEY,  label TEXT NOT NULL,  payload TEXT NOT NULL);PRAGMA page_size;PRAGMA page_count;WITH RECURSIVE seq(x) AS (  VALUES(1)  UNION ALL  SELECT x+1 FROM seq WHERE x<5000)INSERT INTO storage_probe(label,payload)SELECT printf('row-%05d',x),       printf('%0400d',x)FROM seq;PRAGMA page_count;SELECT COUNT(*) FROM storage_probe;

After the 5,000-row insert, page_count should be larger. Outside SQLite, compare the file length with page_size × page_count. The important result is the relationship, not a universal number of pages.

Common wrong turns

Verification checkpoint

Production judgment and bridge

The header and page geometry explain the outer container. Lesson 2 goes inside the useful pages themselves: table and index B-trees, root/interior/leaf navigation, cells, rowids, composite indexes, and the organizational change introduced by WITHOUT ROWID.

Authoritative references

• SQLite — Database File Format.
• SQLite — PRAGMA page_size and page_count.
• SQLite — PRAGMA application_id.
• SQLite — Stable cross-platform file format overview.