Why SQLite Dominates Everywhere: Origins, Architecture, and Secrets

Origins and Motivation

In 2000 Richard Hipp needed a reliable data store for a U.S. Navy destroyer control software. Existing Informix server could fail, so he decided to embed the database directly in the application, reading data from local disk. This led to the concept of an embedded database – the database runs in the same process as the application.

First Implementation (SQLite v1)

SQLite v1 was a thin wrapper around GDBM, a Unix DBM‑derived key‑value library. The README declared “SQLite: An SQL Database Built Upon GDBM”. Because GDBM offered only a C API, Hipp built a virtual machine that translated SQL statements into bytecode which then invoked GDBM functions.

Layered Architecture

SQLite processes a SQL statement through six layers:

User Interface (UI)

Tokenizer

Parser

Code Generator

Virtual Database Engine (VDBE)

Database Back‑End (DBBE)

Example: INSERT processing

An INSERT INTO examp VALUES (99,'Hello, World!') statement follows these steps:

The UI passes the text to the Tokenizer, which splits it into tokens via sqliteGetToken() (e.g., [INSERT, INTO, examp, VALUES, (, 99, ',', 'Hello, World!', )]).

The Parser matches the token sequence against grammar rules and calls the handler sqliteInsert().

The Code Generator emits bytecode such as Open examp, Integer 99, String "Hello, World!", Put using sqliteVdbeAddOp().

The VDBE executes each opcode; for Put it invokes sqliteDbbePut().

The DBBE layer finally calls the storage engine. In v1 the call is gdbm_store(), which writes the record to the GDBM hash file.

Transition to B‑Tree Storage

In 2001 Hipp replaced GDBM with a custom B‑tree implementation based on Donald Knuth’s algorithms. This change gave SQLite true relational capabilities, supporting indexes, transactions, and multi‑column queries. The commit history shows the diff in the README and source tree reflecting the removal of GDBM and addition of the B‑tree module.

Self‑Contained Toolchain

To keep external dependencies minimal, Hipp also wrote his own lexer/parser generator (Lemon), version‑control system (Fossil), and editor. Fossil itself stores repository metadata in SQLite, creating a circular dependency that illustrates the self‑contained nature of the ecosystem.

Key Takeaways

SQLite began as an embedded solution to avoid unreliable external databases.

Version 1 wrapped GDBM and provided SQL via a custom virtual machine.

The six‑layer architecture cleanly separates UI, parsing, code generation, execution, and storage.

Replacing GDBM with a B‑tree in 2001 gave SQLite a full relational engine.

Richard Hipp’s emphasis on writing his own components resulted in a highly portable, dependency‑free library.

References

First‑version commit timeline: https://sqlite.org/src/timeline?a=2000-05-01

SQLite v1 source tree: https://www.sqlite.org/src/tree?ci=e8521fc10dcfa02f

Commit showing B‑tree replacement: https://sqlite.org/src/timeline?a=2001-04-14