SQLite

SQLite is a relational database implemented as a library, storing everything in a single file. There is no server, no configuration, and no network. The application links the library and calls it.

That architecture is the whole thing, and it makes SQLite better than the alternatives for a specific and larger-than-expected set of cases.

Where it is the right answer

Analysis of tabular data too large for memory. This is the use I return to most and the one people overlook. Faced with a multi-gigabyte CSV, the reflexes are pandas — which needs it in memory — or standing up PostgreSQL. Loading it into SQLite takes one command, gives indexed SQL over the data, and produces a file that can be copied to another machine. For exploratory work over data that does not fit comfortably in memory, it is markedly less friction than either alternative.

Application-local storage, which is why it is in every phone, browser, and operating system. It is almost certainly the most deployed database in existence.

A file format that happens to be queryable. Shipping data as a SQLite file rather than a pile of CSVs gives the recipient types, relationships, and indexes with no import step.

Where it is not

Concurrency is the real boundary. SQLite permits many concurrent readers and one writer. WAL mode improves this substantially — readers no longer block the writer — and the single-writer limit remains. For a web application with concurrent writes, this is the point at which you need a server database.

The other constraint is that it lives on a filesystem, so there is no network access and no replication built in. Anything requiring multiple application servers touching one database needs Postgres.

Two things worth knowing

Type affinity is not type enforcement. SQLite will store a string in an INTEGER column. Recent versions support STRICT tables, which enforce types properly, and I would use them by default — the flexibility is a liability, not a feature, for anything that matters.

Foreign keys are off by default for backwards compatibility. PRAGMA foreign_keys = ON per connection, or the constraints are decorative.

See also: PostgreSQL for the server case, Python which bundles it in the standard library, and datasets for the distribution angle.