SQLite

SQLite is a small, embedded, public-domain relational database engine. The reference implementation is written in ANSI C and ships as a single source file — the amalgamation — of approximately one hundred fifty thousand lines, which compiles to a library of a few hundred kilobytes. The library has no separate server process: an application links the SQLite library into its own address space and reads and writes a database file directly through ordinary file-system operations. Each database is a single ordinary file, portable across operating systems, byte orders, and pointer widths. The query language is the SQL of ISO/IEC 9075, with a deliberately permissive grammar, a distinctive type affinity discipline in place of strict static column typing, and a substantial library of extensions including window functions, recursive common table expressions, full-text search, JSON, and an open-ended virtual table mechanism through which arbitrary data sources can be queried as if they were tables.

SQLite is, by an enormous margin, the most-deployed database engine in the world. It is bundled with every Apple operating system, every Android distribution, every major web browser, the Python standard library, the PHP standard library, the Tcl standard library, and the Windows 10 platform; it is used as the application file format by Adobe Lightroom, the iOS Photos library, the macOS Mail app, and the OpenOffice document model; it is qualified for use on the avionics of the Airbus A350. The published estimate of total installations exceeds one trillion.

History

SQLite was created by D. Richard Hipp at Hipp, Wyrick & Company in August 2000. The motivation was a contract obligation to the United States Navy: a damage-control program for the destroyer USS Oscar Austin required a small, dependency-free database engine that could continue to operate without administrator intervention if its host server was destroyed. The first public release, SQLite 1.0, used the GDBM library for storage and a simple flat-file rollback journal. SQLite 2.0 (2001) replaced GDBM with a custom B-tree implementation. SQLite 3.0 (2004) was a substantial rewrite that introduced the present file format, the present type-affinity discipline, BLOB support, and UTF-8 / UTF-16 storage; the file format has remained backward-compatible since.

Subsequent revisions have been incremental and have not broken the file format. SQLite 3.7 (2010) introduced write-ahead logging (WAL) as an alternative to the rollback journal, substantially improving read concurrency. SQLite 3.8 (2013) introduced partial indexes and the next-generation query planner. SQLite 3.9 (2015) introduced the JSON1 extension and the FTS5 full-text-search module. SQLite 3.24 (2018) introduced upsert through the ON CONFLICT clause. SQLite 3.25 (2018) introduced window functions. SQLite 3.35 (2021) introduced the RETURNING clause, mathematical functions, and ALTER TABLE … DROP COLUMN. SQLite 3.37 (2021) introduced STRICT tables for those who prefer classical column typing. SQLite 3.39 (2022) introduced RIGHT and FULL OUTER JOIN. SQLite 3.45 (2024) introduced JSONB as a binary representation of JSON.

The source code is dedicated to the public domain by signed affidavit; SQLite is not open-source software in the conventional licensing sense but rather un-licensed: anyone may use, modify, and redistribute it without restriction. A commercial licence is available for jurisdictions that do not recognise the public-domain dedication. Development is led from Charlotte, North Carolina by a small team at the SQLite Consortium, whose membership funds the project.

Hello world

A complete SQLite session at the command-line shell:

sqlite3 example.db

CREATE TABLE customer (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    email   TEXT UNIQUE
);

INSERT INTO customer (name, email) VALUES
    ('Ada Lovelace',    'ada@example.com'),
    ('Donald Knuth',    'don@example.com'),
    ('Grace Hopper',    NULL);

SELECT id, name FROM customer ORDER BY name;

1|Ada Lovelace
3|Grace Hopper
2|Donald Knuth

Wait — the second result is misordered, because lexicographic order places G between A and D in the string sense but the surrounding text presented the rows by id. The actual ordering by name is A, D, G; the example above is corrected:

1|Ada Lovelace
2|Donald Knuth
3|Grace Hopper

The shell exits with .quit. The single file example.db contains the schema, the data, and the indexes; it can be copied, attached to an email, or version-controlled like any other file.

SQL dialect

SQLite implements the SQL of ISO/IEC 9075 with a small, identifiable set of departures, all of which are documented in the SQLite reference and are noted on the relevant pages of this volume. The principal departures are: type affinity in place of strict column types (a column declared INTEGER will accept text and silently store it unless the table is declared STRICT); NULL is permitted in PRIMARY KEY columns of WITHOUT ROWID tables but not in conventional rowid tables; the barewords rule for non-aggregated columns in a GROUP BY query is permissive (any value from the group is returned); identifiers may be double-quoted or single-quoted, and a string in single-quotes that does not match a column name is silently treated as a string literal; the keywords RIGHT JOIN and FULL OUTER JOIN were added in 3.39 and earlier versions support only INNER and LEFT joins; there is no GRANT / REVOKE and no concept of database user — access control is the responsibility of the host application.

The dialect adds a number of features that simplify embedded use. The single-file format permits a database to be passed between machines as a file. The ATTACH DATABASE statement allows a single connection to query across multiple database files. The PRAGMA statement exposes engine-level configuration. The C API permits applications to register their own scalar, aggregate, and window functions and their own virtual-table modules. The shell tool sqlite3 provides convenient interactive access and a substantial library of .dot commands for inspection, import, export, and benchmarking.

Use as an application file format

A frequent and idiomatic use of SQLite is as an application file format: instead of inventing a custom serialisation, an application stores its document as an SQLite database. Adobe Lightroom uses SQLite for its catalogue; the Apple Mail application uses SQLite for the message index; iOS uses SQLite for the contacts, calendars, and notes; Firefox uses SQLite for bookmarks, history, and cookies; the macOS keychain on recent versions is backed by SQLite. The advantages are durability under power loss (the WAL mechanism preserves invariants across crashes), efficient partial reads (an application need not load the whole document into memory), atomic edits (a transaction either commits in full or rolls back), and the entire SQL surface for querying and modifying the document. The single-file property preserves the user’s expectation that a document is a thing to be copied, mailed, or backed up.

Bindings and ecosystem

The C API is the canonical interface. Wrappers exist for every widely used programming language and are commonly distributed in the standard library. Python ships sqlite3 in the standard library; Node has better-sqlite3 and the Node-API binding node-sqlite3; Go has mattn/go-sqlite3 (cgo) and modernc.org/sqlite (pure-Go); Rust has rusqlite and sqlx; Java has the sqlite-jdbc driver; Ruby has the sqlite3 gem. The bindings expose the same surface — a connection, a prepared statement, parameter binding, and result iteration — and a programmer who has learned SQLite through one binding will recognise the API of any other.

This volume is organised so that a working programmer can read it end-to-end as a small monograph or use it as a reference. The SQL fundamentals — SELECT, WHERE, GROUP BY, joins, subqueries, aggregation — are covered alongside the SQLite-specific features (type affinity, WITHOUT ROWID, virtual tables, FTS5, JSON, WAL) and the engine’s distinctive corners (NULL handling, the permissive grammar, the PRAGMA surface).