SQLite3

SQLite3 is the third major generation of the SQLite database engine and the only version anyone uses today. When developers type sqlite3 at a shell, import Python’s sqlite3 module, or link libsqlite3, they are working with this generation — the line that began with SQLite 3.0.0 on June 18, 2004 and continues, fully backward-compatible, in the 3.53.x releases of 2026. Like every version of SQLite, it is a small, fast, self-contained SQL database engine that runs inside the application rather than as a separate server: there is no daemon to install, no network port to open, and no user accounts to configure. A program links the library, opens an ordinary file, and reads and writes it with standard SQL. That file is the database.

The distinction between “SQLite” in general and “SQLite3” specifically is largely one of file format and era. The 3.x series introduced a redesigned on-disk format that broke compatibility with the older 2.x files, and — crucially — has kept that format stable ever since. A database created in 2004 can still be opened, unchanged, by the newest release, and vice versa. That combination of a permissive public-domain license, an obsessive commitment to reliability, and a format frozen for over two decades is why SQLite3 is the most widely deployed database engine in the world.

History & Origins

SQLite was created by D. Richard Hipp, who designed the original engine in 2000 while working on a U.S. Navy contract that needed a database with no server to administer. The first two generations (1.x in 2000 and 2.x in 2001) proved the serverless concept, but by 2003 their file format was showing its limits: it stored numbers as ASCII, lacked native BLOB support, and offered only rudimentary internationalization.

The answer was a clean-sheet redesign. SQLite 3.0.0, released on June 18, 2004, introduced a completely new file format that — according to SQLite’s own documentation — produced databases typically 25–35% smaller than the equivalent SQLite 2.x (2.8) files, stored integers and floating-point numbers in compact binary form, and added first-class support for BLOBs, 64-bit row identifiers, and both UTF-8 and UTF-16 text. The internationalization and several other improvements were partially funded by America Online. This was the release that turned SQLite from a clever embedded library into infrastructure — and its format has been the foundation of every release since.

Design Philosophy

SQLite3 inherits and refines the principles that made the project famous:

• Serverless and zero-configuration. There is no separate server process and no setup step. Applications embed the library and open a file; that is the entire installation and administration story.
• Self-contained. The library has minimal external dependencies and a small footprint (well under a megabyte with all features enabled), which is why it fits comfortably on phones and embedded devices.
• A single-file, stable format. An entire database is one file that can be copied, emailed, or moved between machines of different word sizes and byte orders without conversion — and the version 3 format has stayed backward-compatible since 2004.
• Transactional and durable. SQLite3 provides full ACID transactions, so changes either complete entirely or not at all, and committed data survives crashes and power loss.
• Reliability above features. The project is famous for its test suite, which achieves 100% branch test coverage and includes extensive fault-injection testing that simulates out-of-memory conditions, disk I/O errors, and power failures.

Type System: Manifest Typing

One way SQLite3 deliberately departs from most SQL databases is its approach to types. Traditional relational databases use rigid typing: a column declared INTEGER may only ever hold integers. SQLite3 instead uses manifest typing with type affinity — a value’s type travels with the value, and a column has only a preferred type rather than a strict one. By default, this means the engine will let you store a string in a column declared INTEGER, converting when it reasonably can and otherwise storing the value as given.

This flexibility is convenient for dynamic and scripting use, though it surprises developers coming from stricter systems. Since version 3.37.0 (2021), SQLite3 also offers STRICT tables, an opt-in mode that enforces conventional, rigid column types — giving developers a choice between flexibility and rigor within the same engine.

Key Features

Despite its size, SQLite3 supports a large and modern subset of SQL:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- A whole database is just this file: app.db
CREATE TABLE tasks (
  id        INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  done      INTEGER DEFAULT 0,
  created   TEXT DEFAULT (datetime('now'))
);

INSERT INTO tasks (title) VALUES ('Write SQLite3 page'), ('Verify the facts');

-- Window functions, available since 3.25.0
SELECT title,
       ROW_NUMBER() OVER (ORDER BY created) AS seq
FROM   tasks
WHERE  done = 0;

Notable capabilities of the 3.x line include:

• Write-Ahead Logging (WAL) mode — added in 3.7.0 (2010), it lets multiple readers run concurrently with a single writer, improving throughput for many workloads.
• Common table expressions and recursive queries — including recursive CTEs for hierarchical and graph-shaped data.
• Window functions — analytical queries such as ranking and running totals, available since 3.25.0 (2018).
• Full-text search — the FTS5 extension provides fast text indexing and search.
• JSON support — built-in JSON functions for querying and generating JSON, useful for semi-structured data.
• Generated columns, partial indexes, triggers, and STRICT tables — features that let a fair amount of logic and discipline live in the database itself.

Evolution

Within the 3.x generation, the engine has grown steadily more capable while never breaking its file format. The Next-Generation Query Planner arrived in 3.8.0 (2013), rewriting how the engine chooses execution strategies. WAL mode came in 3.7.0 (2010); the JSON1 extension and FTS5 full-text search in 3.9.0 (2015); SQL window functions in 3.25.0 (2018); and STRICT tables in 3.37.0 (2021). Through all of this, a database file remains readable by any 3.x release back to the original 3.0.0, an unusually long run of stability for any file format. Development continues at a brisk pace, with the 3.53.x series current as of 2026.

Current Relevance

It is difficult to overstate how ubiquitous SQLite3 has become. It is embedded in every major web browser, in Android and iOS, in Python’s standard library, in countless desktop and mobile applications, and in embedded systems ranging from cars to consumer electronics. Because the version 3 format has been frozen since 2004, code and data written two decades ago still interoperate seamlessly with today’s releases — a rare property that makes SQLite3 a safe default wherever a program needs to store structured data locally.

That trust has institutional recognition too: the United States Library of Congress lists the SQLite version 3 file format among its Recommended Storage Formats for datasets, valuing it for long-term digital preservation. The project’s authors have publicly committed to supporting the format through at least the year 2050, a promise aimed squarely at the archival and embedded users who need multi-decade stability.

Why It Matters

SQLite3 is the release that made “a database in a file” a permanent, dependable reality. By pairing a compact, internationalized, binary file format with an ironclad promise never to break it, the version 3 generation turned SQLite from a useful library into foundational infrastructure that billions of devices quietly rely on. Few software formats designed in 2004 are as widely used today — and fewer still can claim that a file written on the day of their release will open, unchanged, in the newest version decades later. That is the quiet achievement of SQLite3: not a flashy feature, but two decades of never letting its users down.