SQLite Is Enough

Part four of the boring stack. The app is written in Go and renders HTML on the server. Now it needs to keep its data somewhere.

The default answer is Postgres. It’s an excellent database, and I’ve used it for years. For the apps I build now, I use SQLite.

A database that’s a file

SQLite isn’t a server. It’s a library that reads and writes a single file. The app opens ./data/app.db and that’s the whole database.

That removes a surprising amount of work:

• No database server to install, patch, secure or monitor. Nothing listens on a port. There’s no password to rotate and no network to configure between the app and the database.
• Backups are a file copy. More precisely, a consistent snapshot of one file, which SQLite can produce while the app runs. Restoring is putting the file back.
• Development and production are the same. The same engine runs in tests, on my laptop and on the server. Resetting my local state means deleting a folder.
• Moving the app means moving two things: the binary and the data folder.

Is it fast enough?

For almost everything a small team builds, yes, by a wide margin. SQLite reads from the same process, with no network round trip, which makes simple queries extremely fast. With write-ahead logging enabled, readers don’t block the writer and the writer doesn’t block readers.

The real limit is that there’s one writer at a time. Writes queue up. For an internal tool, a booking system or a small SaaS, that queue is almost always empty. If your app handles thousands of writes per second from many servers at once, you’ve outgrown this, and you’ll know it.

One file per tenant, if you have to

Most of my apps have one database. When an app serves several customers whose data must stay apart, SQLite offers an option that sounds odd at first and turns out to be the simplest: one database file per tenant.

data/tenants/acme.db
data/tenants/globex.db
data/tenants/initech.db

The app picks the file from the tenant of the request and opens it. On first open, it runs the migrations against that file like against any other.

Compare that with the usual ways to split one Postgres database:

• A tenant_id column on every table, perhaps with row-level security on top. Every query has to filter by tenant. One forgotten WHERE clause, or one table without a policy, and a customer sees someone else’s data.
• A schema per tenant. Cleaner, but every migration has to run across hundreds of schemas, every connection has to pick the right one, and backing up or restoring one customer means dumping and loading a single schema.
• A database per tenant. The cleanest separation, but each one needs its own connections, and a Postgres server doesn’t like thousands of them.

With a file per tenant, most of those problems disappear:

• Isolation by construction. A query can only see the file it runs against. There’s no WHERE to forget.
• Per-tenant operations are file operations. Backing up a customer is copying a file. Restoring one doesn’t touch anyone else. Deleting a customer, for example because they ask under data protection law, is deleting a file, and you can be sure nothing is left.
• No noisy neighbours on writes. SQLite allows one writer at a time per file. One busy tenant doesn’t make the others wait.
• Tenants can move. A large customer can get its own server by moving one file.

It isn’t free. Reports across all tenants mean opening every file, or attaching several at once. Data that belongs to no tenant, like user accounts that span customers, needs a central database next to the tenant files. And a migration that fails on one file leaves that tenant behind until it’s fixed, so the app must handle files that aren’t on the latest version yet.

When tenants share almost everything, a tenant_id column in one file is still the simpler choice. When they must be kept apart, one file each is easier than anything I’d build in Postgres.

What it costs

• One server. SQLite lives next to the app. You can’t run the app on five servers that share one SQLite file. For me that fits: I run one server anyway.
• Fewer features. No stored procedures, a smaller set of data types, limited ALTER TABLE. Migrations sometimes have to rebuild a table instead of changing a column.
• Fewer tools. There’s no managed SQLite with a dashboard from your cloud provider. You open the file with the sqlite3 command line or a desktop viewer.

When I’d choose differently

When several app servers need to write to the same data, when the write load is genuinely high, or when a team already runs Postgres well, I’d use Postgres. I’d also choose it if I needed its specific features, like full-text search in many languages or PostGIS.

But I’d wait for that need to show up. Most apps never get there, and the ones that do can move. The data is in SQL tables either way.

Next: how the app talks to the database.