SQLite Format: Complete Guide to the World's Most Deployed Database

What Is SQLite?

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine embedded in a single C library. Created by D. Richard Hipp in 2000 and released to the public domain, SQLite is arguably the most widely deployed database engine in the world — embedded in billions of devices including every iPhone, every Android phone, every macOS installation, most web browsers, and countless embedded systems.

SQLite stores its entire database — schema definitions, data tables, indexes, and transaction logs — in a single ordinary file on the host file system. There is no separate server process, no setup, no administration, and no network connection required. The library reads and writes directly to the disk file.

SQLite File Format

The SQLite database file format is precisely specified and stable — files created by SQLite version 3 in 2004 are fully readable by current SQLite releases (backward and forward compatibility). The format uses .db, .sqlite, or .sqlite3 extensions (all identical, just naming conventions).

Page Structure

The SQLite file is organized as a sequence of fixed-size pages. The default page size is 4096 bytes (4 KB), though values from 512 to 65536 bytes (powers of two) are supported and stored in the header.

SQLite Database File
├── Page 1 (Database Header + first B-tree root page)
│   ├── Header (first 100 bytes)
│   │   ├── Magic string: "SQLite format 3\000" (16 bytes)
│   │   ├── Page size in bytes (2 bytes)
│   │   ├── File format write/read versions
│   │   ├── Reserved space per page
│   │   ├── Maximum/minimum embedded payload fractions
│   │   ├── File change counter (4 bytes)
│   │   ├── Size of database in pages (4 bytes)
│   │   ├── First freelist trunk page (4 bytes)
│   │   ├── Total freelist pages (4 bytes)
│   │   ├── Schema cookie (4 bytes) — incremented on schema change
│   │   ├── Schema format number (1-4)
│   │   ├── Default page cache size
│   │   ├── Largest B-tree root page number
│   │   ├── Text encoding (1=UTF-8, 2=UTF-16le, 3=UTF-16be)
│   │   ├── User version (application-defined integer)
│   │   ├── Incremental vacuum mode
│   │   └── Application ID (for identifying SQLite files by purpose)
│   └── Root page of sqlite_schema (sqlite_master) table
│
├── Pages 2 to N (B-tree pages, overflow pages, freelist pages)
│   ├── B-tree interior page (index or table)
│   ├── B-tree leaf page (index or table leaf)
│   ├── Overflow page (for large column values)
│   └── Freelist page (unused space)
│
└── (WAL file if in WAL mode: database-wal)
    └── WAL header + WAL frames

Page Types

B-tree Organization

SQLite uses a B-tree (balanced tree) data structure for all data storage:

• Table B-trees store rows indexed by the 64-bit row ID. The row ID is the key; the row data is the value.
• Index B-trees store index keys (columns specified in CREATE INDEX) along with the row IDs they point to.

Each B-tree page stores a sorted array of keys (for interior nodes, pointers to child pages; for leaf nodes, the actual data). A 4KB page holds tens to hundreds of rows depending on row size.

Record Format

Row data within leaf pages uses a compact variable-length encoding:

• Header size varint — encodes the total header length
• Type/size varints — one per column, describing the column value's type and size
• Data section — concatenated column values in the order described by the header

Varint encoding: SQLite uses a variable-length integer encoding (similar to protobuf/msgpack) where values 0-127 use 1 byte, 128-16383 use 2 bytes, etc. This makes the record format very compact for small integers.

Type codes in the header:

SQLite Data Types

SQLite uses dynamic typing — column types are suggestions rather than strict constraints. SQLite recognizes five "storage classes":

This is quite different from other databases where column types strictly enforce stored data. In SQLite, you can store a text string in a column declared as INTEGER (though type affinity rules prefer conversion when possible).

Type Affinity

Column type names are mapped to affinities: INTEGER, REAL, NUMERIC, TEXT, BLOB (NONE). Affinity determines how values are compared and stored:

• INT, INTEGER, TINYINT, BIGINT → INTEGER affinity
• REAL, FLOAT, DOUBLE → REAL affinity
• CHAR, VARCHAR, TEXT, CLOB → TEXT affinity
• No type declaration → BLOB (NONE) affinity

WAL (Write-Ahead Logging) Mode

SQLite supports two journaling modes:

Delete Mode (Default)

1. Before modifying a page, copy the original page to a rollback journal file (-journal)
2. Modify the page in the database file
3. After commit, delete the journal file

Readers block writers; writer blocks readers. One writer at a time. Good for mostly-read workloads.

WAL Mode

1. Writers append new versions of modified pages to the WAL file (-wal) without touching the database file
2. Readers read pages from the WAL file if they have newer versions, otherwise from the database
3. A checkpoint periodically writes WAL pages back to the main database file

WAL mode allows concurrent readers and one writer simultaneously — a major performance advantage for applications with multiple reading threads. Enabled with:

PRAGMA journal_mode=WAL;

WAL mode also improves write speed (append-only WAL writes are sequential, even on spinning disks) and crash recovery (WAL is more atomic than the delete journal).

SQLite in Practice

When to Use SQLite

• Mobile apps — both iOS (Core Data often uses SQLite) and Android have SQLite built in
• Desktop apps — local configuration, user data, application state
• Embedded systems — IoT devices, smart appliances, automotive systems
• Testing — swap PostgreSQL/MySQL for SQLite in tests without Docker
• Caching layer — persistent local cache for API responses
• Small to medium web sites — up to a few hundred GBs of data, moderate concurrent writes
• Data analysis — DuckDB uses SQLite's format ideas; SQLite itself handles datasets of millions of rows well

When NOT to Use SQLite

• High-concurrency writes — SQLite allows only one writer at a time (WAL helps but has limits)
• Very large databases — maximum is 281 TB, but performance degrades for writes past tens of GBs
• Network access — SQLite has no network protocol; it's a library, not a server
• Multiple machines — can't run SQLite across a cluster; use PostgreSQL, MySQL, or CockroachDB

Common SQLite Tools

Reading SQLite with Common Languages

# Python (built-in module)
import sqlite3
conn = sqlite3.connect('database.sqlite')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE active = 1")
rows = cursor.fetchall()
conn.close()

// Node.js (better-sqlite3 package)
const Database = require('better-sqlite3');
const db = new Database('database.sqlite');
const rows = db.prepare('SELECT * FROM users WHERE active = ?').all(1);

// PHP (PDO)
$pdo = new PDO('sqlite:database.sqlite');
$stmt = $pdo->prepare('SELECT * FROM users WHERE active = ?');
$stmt->execute([1]);
$rows = $stmt->fetchAll();

SQLite vs. Other Databases

Inspecting SQLite Files

# Open a SQLite file in the CLI
sqlite3 database.sqlite

# List all tables
.tables

# Show schema for a table
.schema tablename

# Run a query
SELECT count(*) FROM tablename;

# Export table to CSV
.mode csv
.output table.csv
SELECT * FROM tablename;
.output stdout

# Check file integrity
PRAGMA integrity_check;

# Show page count and size
PRAGMA page_count;
PRAGMA page_size;

SQLite File Identification

SQLite files begin with the ASCII bytes SQLite format 3\000 (the string "SQLite format 3" followed by a null byte). This magic number allows tools like file command and hex editors to identify SQLite files regardless of extension:

file database.db
# database.db: SQLite 3.x database, last written using SQLite version 3039002...

Related conversions

Frequent conversions across the catalogue:

• JPG to PNG
• MP4 to MP3
• PDF to DOCX
• PNG to JPG
• MP3 to WAV
• DOCX to PDF