Guide

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

PC By Pablo Cirre • Updated May 7, 2026

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

Page type	Description
B-tree table leaf	Stores actual row data
B-tree table interior	Internal nodes pointing to subtrees
B-tree index leaf	Index key + row ID
B-tree index interior	Internal nodes of index B-tree
Overflow	Continuation of large values that don't fit in a leaf page
Freelist trunk	Head of the free page linked list
Freelist leaf	Free pages in the freelist chain

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:

Code	Type
0	NULL
1-4	INTEGER (1, 2, 3, 4 bytes)
5	INTEGER (6 bytes)
6	INTEGER (8 bytes)
7	REAL (IEEE 754 double, 8 bytes)
8	INTEGER 0 (no bytes stored)
9	INTEGER 1 (no bytes stored)
≥10, even	BLOB (n = (code-12)/2 bytes)
≥13, odd	TEXT (n = (code-13)/2 bytes)

SQLite Data Types

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

Storage class	Description
NULL	Null value
INTEGER	Signed integer, 1-8 bytes
REAL	8-byte IEEE floating point
TEXT	UTF-8, UTF-16 string
BLOB	Binary data, stored exactly as provided

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)

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

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

WAL Mode

Writers append new versions of modified pages to the WAL file (-wal) without touching the database file
Readers read pages from the WAL file if they have newer versions, otherwise from the database
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

Tool	Type	Description
sqlite3	CLI	Built-in command-line tool
DB Browser for SQLite	GUI	Free desktop viewer/editor
DBeaver	GUI	Multi-database; supports SQLite
Datasette	Web	Browse/publish SQLite as a REST API
SQLiteOnline.com	Web	Browser-based SQLite
Beekeeper Studio	GUI	Modern, free SQLite GUI

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

Feature	SQLite	PostgreSQL	MySQL
Deployment	File (embedded)	Server process	Server process
Setup	Zero config	Complex	Complex
Concurrent writes	Limited	Excellent	Good
Max database size	281 TB	Unlimited	64 TB
Full-text search	FTS5 extension	tsvector	FULLTEXT
JSON support	JSON1 extension	Native	JSON column
Spatial (GIS)	SpatiaLite extension	PostGIS	MySQL Spatial
Transactions	ACID	ACID	ACID
Network access	❌	✅	✅

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:

Frequently Asked Questions

A SQLite file (usually .db, .sqlite, or .sqlite3 extension) is a single-file relational database. It stores all tables, indexes, and data in one binary file. To open it: (1) Command line: install sqlite3 and run sqlite3 yourfile.db — type .tables to list tables, then run SQL queries. (2) GUI on desktop: use "DB Browser for SQLite" (free, cross-platform) or DBeaver for a visual table browser. (3) Online: upload to SQLiteOnline.com for browser-based access. (4) In your code: Python has sqlite3 built in; PHP has PDO SQLite; Node.js can use the better-sqlite3 package. SQLite files are binary files — do not try to open them in a text editor.

A SQLite arquivo (Geralmente .db, .sqlite, ou .sqlite3 extension) is a single-file relational database. It stores all tables, indexes, e data in one binary file. To abrir it: (1) Command line: install sqlite3 e run sqlite3 yourfile.db — type .tables to list tables, then run SQL queries. (2) GUI on desktop: usar "DB Browser para SQLite" (free, cross-platform) ou DBeaver para a visual table browser. (3) Online: enviar to SQLiteOnline.com para browser-based access. (4) In your code: Python has sqlite3 built in; PHP has PDO SQLite; Node.js can usar the better-sqlite3 package. SQLite arquivos são binary arquivos — do not try to abrir them em um text editor.

A SQLite Datei (Normalerweise .db, .sqlite, oder .sqlite3 extension) is a single-file relational database. It stores all tables, indexes, und data in one binary file. To öffnen it: (1) Command line: install sqlite3 und run sqlite3 yourfile.db — type .tables to list tables, then run SQL queries. (2) GUI on desktop: verwenden "DB Browser für SQLite" (free, cross-platform) oder DBeaver für a visual table browser. (3) Online: hochladen to SQLiteOnline.com für browser-based access. (4) In your code: Python has sqlite3 built in; PHP has PDO SQLite; Node.js can verwenden the better-sqlite3 package. SQLite Dateien are binary Dateien — do not try to öffnen them in einem text editor.

A SQLite archivo (Normalmente .db, .sqlite, o .sqlite3 extension) is a single-file relational database. It stores all tables, indexes, y data in one binary file. To abrir it: (1) Command line: install sqlite3 y run sqlite3 yourfile.db — type .tables to list tables, then run SQL queries. (2) GUI on desktop: usar "DB Browser para SQLite" (free, cross-platform) o DBeaver para a visual table browser. (3) Online: subir to SQLiteOnline.com para browser-based access. (4) In your code: Python has sqlite3 built in; PHP has PDO SQLite; Node.js can usar the better-sqlite3 package. SQLite archivos are binary archivos — do not try to abrir them en un text editor.

On KaijuConverter every file is processed inside an isolated container, encrypted in transit (TLS 1.3) and at rest, and automatically deleted after 60 minutes with multi-pass overwrite. We never train on, share, or analyze user content. For maximum privacy on extremely sensitive material, prefer offline tools (ImageMagick, FFmpeg, LibreOffice) that you control end-to-end.

Yes, for many production use cases. SQLite works excellently for websites with moderate traffic — the Datasette framework runs many public datasets on SQLite, and Laravel supports SQLite in production. SQLite handles hundreds of thousands of reads per second and thousands of writes per second in WAL mode. The main limitation is concurrent writes: only one process can write at a time (WAL mode allows one writer + multiple readers simultaneously). For very high-traffic sites with hundreds of concurrent users all writing simultaneously, PostgreSQL or MySQL scale better. But for most web applications with typical read-heavy traffic, SQLite is a completely valid production database.

Sim, para many production usar cases. SQLite funciona excellently para sites com moderate traffic — the Datasette framework runs many public datasets on SQLite, e Laravel suporta SQLite in production. SQLite handles hundreds of thousands of reads per second e thousands of writes per second in WAL mode. The main limitation is concurrent writes: only one process can write at a time (WAL mode permite one writer + multiple readers simultaneously). para muito alta-traffic sites com hundreds of concurrent users all writing simultaneously, PostgreSQL ou MySQL scale better. mas para most web aplicativos com typical read-heavy traffic, SQLite is a completely valid production database.

Ja, für many production verwenden cases. SQLite works excellently für Websites mit moderate traffic — the Datasette framework runs many public datasets on SQLite, und Laravel unterstützt SQLite in production. SQLite handles hundreds von thousands von reads per second und thousands von writes per second in WAL mode. The main limitation is concurrent writes: only one process can write at a time (WAL mode erlaubt one writer + multiple readers simultaneously). für sehr hoch-traffic sites mit hundreds von concurrent users all writing simultaneously, PostgreSQL oder MySQL scale better. aber für most web Anwendungen mit typical read-heavy traffic, SQLite is a completely valid production database.

For 95% of use cases, yes — server-side ImageMagick, FFmpeg and LibreOffice produce identical output to the same tools on your laptop. Desktop software wins for: extremely large files (multi-GB), batch jobs of thousands of files, scripted pipelines, or content too sensitive to upload. KaijuConverter caps at 25 MB per file on the free tier (up to 2 GB on paid plans).

WAL (Write-Ahead Logging) is an alternative journaling mode for SQLite that significantly improves performance for read-heavy applications with occasional writes. In default mode, a writer blocks all readers; in WAL mode, readers and one writer can operate simultaneously. WAL also improves write speed (sequential WAL writes instead of random database file writes) and provides better crash safety. Enable it with PRAGMA journal_mode=WAL — you only need to set it once per database file, and it persists. WAL mode is recommended for almost all production use cases. The main caveat: WAL creates additional -wal and -shm files alongside the database; all three files must be kept together when copying or backing up the database.

WAL (Write-Ahead Logging) is an alternative journaling mode para SQLite that significantly improves performance para read-heavy aplicativos com occasional writes. In default mode, a writer blocks all readers; in WAL mode, readers e one writer can operate simultaneously. WAL also improves write speed (sequential WAL writes em vez de random database arquivo writes) e fornece better crash safety. Enable it com PRAGMA journal_mode=WAL — you only precisar set it once per database file, e it persists. WAL mode is recommended para almost all production usar cases. The main caveat: WAL creates additional -wal e -shm arquivos alongside the database; all three arquivos must be kept together when copying ou backing up the database.

WAL (Write-Ahead Logging) is an alternative journaling mode für SQLite that significantly improves performance für read-heavy Anwendungen mit occasional writes. In default mode, a writer blocks all readers; in WAL mode, readers und one writer can operate simultaneously. WAL also improves write speed (sequential WAL writes anstatt random database Datei writes) und bietet better crash safety. Enable it mit PRAGMA journal_mode=WAL — you only need to set it once per database file, und it persists. WAL mode is recommended für almost all production verwenden cases. The main caveat: WAL creates additional -wal und -shm Dateien alongside the database; all three Dateien must be kept together when copying oder backing up the database.

WAL (Write-Ahead Logging) is an alternative journaling mode para SQLite that significantly improves performance para read-heavy aplicaciones con occasional writes. In default mode, a writer blocks all readers; in WAL mode, readers y one writer can operate simultaneously. WAL also improves write speed (sequential WAL writes en vez de random database archivo writes) y proporciona better crash safety. Enable it con PRAGMA journal_mode=WAL — you only need to set it once per database file, y it persists. WAL mode is recommended para almost all production usar cases. The main caveat: WAL creates additional -wal y -shm archivos alongside the database; all three archivos must be kept together when copying o backing up the database.

Most format conversions are lossy by design — JPG, MP3, MP4, WebP all discard perceptual data to save bytes. Going through a lossy intermediate compounds the loss. To minimize visible/audible drift: convert from the original master, choose a higher quality setting, and avoid converting back and forth between lossy formats.

The theoretical maximum SQLite database size is 281 terabytes (limited by the 32-bit page number × maximum 65536-byte page size). In practice, SQLite works well for databases up to several gigabytes with excellent performance, and remains functional into the tens of gigabytes range, though write performance degrades as the file grows. For databases regularly exceeding 1 GB with frequent writes, consider PostgreSQL. For read-heavy large datasets, SQLite remains surprisingly capable — DuckDB (an OLAP database often compared to SQLite) handles multi-gigabyte analytical datasets efficiently using similar embedded concepts. The Datasette project has published SQLite databases exceeding 50 GB that serve thousands of queries per second.

Yes — KaijuConverter accepts multiple files in a single drop and returns a ZIP. For very large batches (thousands of files) consider command-line tools or our API: <code>find . -name "*.heic" -exec magick {} {.}.jpg \;</code> or similar one-liners scale to millions of files when run locally.