SQLite Is My Production Database: One File, 154 Tables, Zero Servers

The database that is also a file

The backbone of my whole operation is a single file: atlas.db. It's 299MB, holds 154 tables and 51 views, and it powers revenue analytics, a fan CRM, a media vault index, and half a dozen internal dashboards. There is no database server. There is no connection string pointing at a cloud endpoint I pay for by the hour. There is a file on a Mac, and every process that needs the data just opens it.

People hear "SQLite" and think toy — the thing you use for a mobile app's local cache. That reputation is a decade out of date. For a single-machine operation reading and writing from a handful of local processes, SQLite isn't the compromise. It's the right answer, and reaching for Postgres by reflex is the mistake.

Why one file wins

The entire value proposition is that the database has no moving parts you have to keep alive.

• No server process means nothing to crash, nothing to restart, nothing to monitor, nothing to launchd-babysit. The failure mode "the database is down" does not exist because there's nothing to be down.
• No network hop means a query against a local view returns in single-digit milliseconds. My revenue rollup answers in ~4ms because the data is already on the same disk as the code asking for it.
• Backups are cp. The entire database — schema, indexes, every row — is one file. Copying it is your backup. Versioning it, shipping it to another machine, restoring it: all cp.
• It's free forever. No managed-Postgres bill that climbs with your row count. The cost of storing 299MB is zero.

For a workload that fits on one machine, every one of those is a real, daily win, not a theoretical one.

Turn on WAL mode or you'll fight yourself

The one thing you must do before running SQLite in anything resembling production: enable Write-Ahead Logging.

PRAGMA journal_mode = WAL;

Default SQLite locks the whole database on a write, so a long-running write blocks every reader. In WAL mode, readers never block writers and writers never block readers — a reader sees a consistent snapshot while a write is in flight. For my setup, where a sweep job might be inserting a few thousand rows while three dashboards are reading, this is the difference between "smooth" and "everything hangs." Set it once; WAL is persistent per-database.

The single-writer rule

Here's the discipline that keeps it boring: one writer at a time. SQLite handles concurrent readers beautifully, but it serializes writes. If you point five daemons at the same file all trying to INSERT at once, they'll spend their lives retrying on SQLITE_BUSY.

The fix is architectural, not a setting. I funnel writes through a single path — one process, or a small queue — and let everything else read freely. Append-only tables help here: each completed unit writes one row and never updates it, so writes are fast and contention is minimal. If you find yourself wanting many concurrent writers, that's the actual signal you've outgrown SQLite — not row count.

Views are your API layer

The move that made this scale organizationally: I don't let dashboards write raw SQL against 154 tables. I write views — 51 of them — that encode the joins and the business logic once, and everything queries those.

A v_revenue_forecast_7d view or a v_revenue rollup means the dashboard code is a one-line SELECT * FROM v_revenue. When the underlying schema shifts, I fix the view; every consumer keeps working. Views cost nothing to store, they're always in sync because they're computed on read, and they turn a sprawling schema into a clean, stable surface. This is the same instinct as a service API — a contract in front of the messy internals — except it lives inside the database and needs no server.

The one real gotcha: native driver ABI

The place this bites you is the driver, not the database. If you use better-sqlite3 in Node, it's a native module compiled against a specific Node ABI version. Upgrade Node and the module stops loading with a NODE_MODULE_VERSION mismatch that looks scary and is trivial: rebuild it. I pin [email protected] to my Node version and rebuild on any Node bump. Know this in advance and it's a thirty-second fix instead of a confusing deploy failure.

When to actually reach for Postgres

I'm not anti-Postgres. If you need many machines writing concurrently, network access from separate hosts, or row-level security across untrusted clients, that's a real server's job. But "we might scale someday" is not that. A single file that does 154 tables, 51 views, millisecond reads, and cp backups is not a stepping stone you'll be embarrassed by later. It's a foundation that stays out of your way — which, for anything you run yourself, is the whole point.

FAQ

Is SQLite really fast enough for real analytics?

For a single-machine workload, yes — often faster than networked Postgres, because there's no connection or network round-trip. My revenue rollup returns in ~4ms against a 299MB database. Speed only becomes a problem when you need many machines hitting the same data concurrently, which is a different architecture entirely.

What's the catch with concurrent writes?

SQLite serializes writes — one at a time. Concurrent reads are unlimited and cheap, but if you point many processes at the file all writing at once, they'll retry on SQLITE_BUSY. The fix is to funnel writes through one path and enable WAL mode so readers never block. If you genuinely need many concurrent writers, that's your signal to move to a server database.

How do I back up a SQLite database safely?

The database is one file, so a backup is a copy of that file — ideally taken with SQLite's online backup API or VACUUM INTO so you get a consistent snapshot even mid-write. In WAL mode, make sure you capture a checkpointed copy. Then apply 3-2-1: the live file, a local copy, and an off-machine copy.

Why not just use Postgres from the start?

Because a server you don't need is pure operational cost: a process to keep alive, monitor, patch, and pay for. For a workload that fits on one machine, that's all downside. Start with the simplest thing that works — a file — and move to Postgres the day you actually have a multi-writer, multi-host requirement, not before.