SQLite

SQLite is a small, fast, self-contained SQL database engine that runs inside the application that uses it rather than as a separate server process. There is no daemon to install, no network connection to open, no user accounts to configure: a program links the SQLite library, opens an ordinary file on disk, and reads and writes it with standard SQL. That file is the database — tables, indexes, triggers, and views all live together in a single cross-platform file. This “serverless” design, combined with a public-domain license and an obsessive commitment to reliability, has made SQLite the most widely deployed database in the world, running quietly inside web browsers, phones, aircraft, and countless applications that most people never think of as using a database at all.

It is important to be precise about what SQLite is and is not. It is not a rival to client-server systems like PostgreSQL, MySQL, or Oracle for large, high-concurrency, networked workloads. Its own authors describe it not as a replacement for those systems but as a replacement for fopen() — a better way for a single program to store structured data in a file. Understood that way, SQLite has few peers.

History & Origins

SQLite was created by D. Richard Hipp. He designed it in the spring of 2000 while working for General Dynamics on a contract with the United States Navy, developing a damage-control system destined for a guided-missile destroyer. The existing software relied on an Informix database running on a separate server, and that dependency was a liability: when the database server was unavailable, the program stopped working. Hipp wanted a database the program could talk to directly, with no server to start, stop, or administer, that would keep functioning as long as the disk did.

The result was a compact C library that implemented enough of SQL to be genuinely useful while storing everything in a single file. The first public release, SQLite 1.0, appeared on August 17, 2000. From the very beginning Hipp placed the code in the public domain — not merely open source, but explicitly free of copyright — so that anyone could use, modify, or embed it for any purpose without a license or attribution requirement. That decision removed nearly every barrier to adoption and is a large part of why the engine spread so far.

Design Philosophy

SQLite’s design is a study in doing a great deal with very little. A handful of principles run through the whole project:

• 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 32-bit and 64-bit machines of different byte orders without conversion. The file format is documented and designed for decades of forward and backward compatibility.
• Transactional and durable. SQLite 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 at every step.

Type System: Manifest Typing

One way SQLite deliberately departs from most SQL databases is its approach to data types. Traditional relational databases use rigid typing: a column declared INTEGER may only ever hold integers. SQLite instead uses manifest typing with type affinity. A value’s type travels with the value itself, and a column has only a preferred type (its affinity) rather than a strict one. In practice this means SQLite will, by default, 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. Later versions added STRICT tables (an opt-in mode) for those who want conventional, enforced column types, giving users a choice between flexibility and rigor.

Key Features

Despite its size, SQLite 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 SQLite 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 include:

• Transactions with WAL mode — Write-Ahead Logging (added in 3.7.0) 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 (since 3.25.0).
• 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, and triggers — features that let a fair amount of logic live in the database itself.

Evolution

SQLite has evolved through three major generations. Version 1.0 (2000) used the gdbm library for storage. Version 2.0 (2001) replaced that with a custom B-tree engine written for the project. Version 3.0 (2004) introduced a redesigned, more compact file format — with BLOB support, UTF-16 text, 64-bit row identifiers, and better concurrency — and that 3.x format has remained stable and backward-compatible ever since, an unusually long run for any file format.

Within the 3.x line, the engine has grown steadily more capable while preserving compatibility: WAL mode in 3.7.0 (2010), the JSON1 extension and FTS5 full-text search in 3.9.0 (2015), and SQL window functions in 3.25.0 (2018), among many smaller refinements. 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 SQLite has become. It is embedded in every major web browser, in Android and iOS, in countless desktop and mobile applications, and in embedded systems ranging from cars to consumer electronics. Its combination of a permissive public-domain license, a stable single-file format, and rigorous testing has made it a default choice wherever a program needs to store structured data locally.

That trust has institutional recognition too: the United States Library of Congress lists the SQLite 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

SQLite demonstrated that “a database” does not have to mean a server, a network connection, and an administrator. By collapsing an entire relational engine into a single embeddable library and a single portable file — and by giving that library away with no strings attached — it turned SQL from something you connect to into something you simply include. That reframing quietly changed how enormous numbers of applications store their data. Few pieces of software from the year 2000 are as widely used today, and fewer still are as invisible: SQLite succeeds precisely by being the small, reliable component that billions of devices depend on without anyone noticing.