SQLite Overview

SQLite is a relational database implemented as a library that runs inside your process. There is no server, no network protocol and no configuration file. The entire database is one file.

It is almost certainly the most widely deployed database in the world — in browsers, phones, operating systems and embedded devices — and it is more capable in server contexts than its reputation suggests.

Appropriate Use Cases

• Application-local storage. Desktop and mobile applications, browser storage, on-device caches.
• Edge and embedded systems, where running a database server is impractical.
• Read-heavy web services with a single writer. With WAL mode, SQLite comfortably serves substantial read traffic from one file. Many small services are over-engineered with a database server they do not need.
• A file format. SQLite is an excellent application file format: transactional, queryable and self-describing. Far better than a bespoke binary format or a directory of JSON.
• Test fixtures, where an in-memory database gives fast, isolated tests.
• Analytics on a single machine, over datasets that fit locally.

Limitations

Other limits that decide suitability:

• No network access. Clients must share the filesystem. Which leads to the next point.
• Network filesystems are unsafe. SQLite's locking depends on filesystem locking semantics that NFS and SMB implement inconsistently. Database corruption is a documented outcome. Never place a SQLite database on a network share used by more than one host.
• No built-in replication. Third-party projects add it; core SQLite has none.
• Dynamic typing. Columns have type affinity rather than strict types by default. Use STRICT tables (SQLite 3.37+) where you want enforcement.
• Limited ALTER TABLE. Adding a column is supported; many other changes require recreating the table.
• No user management or permissions. Access control is filesystem access control.

Architecture

• Single file containing the schema, data and indexes, organised as B-trees in fixed-size pages.
• Journal — either a rollback journal or, preferably, a write-ahead log. See Concurrency and WAL.
• In-process — a function call, not a network round trip, which is why simple queries are measured in microseconds.

Configuration that matters

PRAGMA journal_mode = WAL;        -- persistent; readers and the writer stop blocking each other
PRAGMA synchronous = NORMAL;      -- with WAL, a good durability/speed balance
PRAGMA busy_timeout = 5000;       -- wait instead of failing immediately on a lock
PRAGMA foreign_keys = ON;         -- off by default, per connection
PRAGMA cache_size = -64000;       -- 64 MB of page cache (negative means kibibytes)