SQLite

SQLite is a different tool from PostgreSQL that happens to speak SQL. It is a library, not a server, and it stores a whole database in one file on disk.

Why we still choose it

For a great many jobs a database server is pure overhead. Build-time indexes, local caches, desktop and handset applications, analytics over a dataset that fits comfortably on one machine, test fixtures, and anything that has to work on a field device with no network can all use SQLite, with no process to run, no port to filter and no credential to rotate.

It is also the most thoroughly tested software most people will ever use. The project maintains an extraordinary test suite with full branch coverage, and it ships inside effectively every phone, browser and aeroplane in service.

SQLite is in the public domain, which goes further than a permissive licence. There is no notice to reproduce and no licence that can change. Since we argue that software should be yours to keep, that is about as strong a guarantee as we could ask for.

Where it fits in our work

We use it for local indexes for retrieval over your documents, on-device storage for the Linux handsets, and build-time data for Jekyll sites. During a survey we load things like somebody’s invoice history into one file and query it there, which beats standing up a server for two days’ work.

Where it falls short

It allows one writer at a time. SQLite handles concurrent readers well, and with WAL mode it handles mixed read-write loads on a single machine, but it is not built for many concurrent writers across several application servers. For that kind of problem we use PostgreSQL and do not try to be clever.

It is also easy to reach for out of laziness. Running Nextcloud on SQLite, for instance, is the most common cause of “Nextcloud is slow”.