SQLite in production: when it's the right choice and when it isn't

You're building an internal tool: a job tracker, a quoting app, a small portal for one team. Someone suggests SQLite and someone else says it's a toy and you need PostgreSQL. Both are guessing. The SQLite project publishes a fairly blunt guide to when its own database is the wrong choice, and most of the "toy" arguments fall away once you read it. A few real limits don't.

This post works through that guidance, then covers the handful of settings that separate a SQLite app that runs quietly for years from one that throws "database is locked" at lunchtime.

What SQLite is actually competing with#

The most useful line in the official Appropriate Uses For SQLite page is this one: "SQLite does not compete with client/server databases. SQLite competes with fopen()."

That reframes the decision. SQLite is a library linked into your application, and the database is a single file on the same disk. There's no server process, no port, no user accounts and nothing to patch separately. That's the attraction for small internal software, where the biggest long-term cost is usually the moving parts someone has to keep alive.

On load, the same page says that "any site that gets fewer than 100K hits/day should work fine with SQLite", and calls that a conservative estimate, noting SQLite has been shown to handle ten times as much. Most internal tools never get near either figure. Size isn't usually the constraint either: the documented limit is 281 terabytes, though the page suggests looking at a client/server database once your content might creep into the terabyte range.

The official checklist, in plain terms#

The SQLite page ends with a four-question checklist. It's short enough to apply in a planning meeting.

1. Is the data separated from the application by a network? Choose client/server.
2. Do many concurrent writers need to write at the same instant, without queuing? Choose client/server.
3. Will the data outgrow what you're comfortable keeping in one file? Choose client/server.
4. Otherwise, choose SQLite.

The first question trips people up, so it's worth being precise. "Application" here means the code that issues SQL. A web app running on one server, with the database file on that server's disk, passes the test even though your users are across the internet. The page says SQLite "might still be appropriate" in exactly that case. What fails the test is several machines all opening the same database file, for example three app servers behind a load balancer sharing a file over a network drive.

The concurrency question is the one that matters most for business apps. SQLite allows an unlimited number of readers but only one writer at any instant. Writers queue, and the docs say that in most cases a write transaction takes milliseconds, so they can simply take turns. A team of 30 updating job records is fine. A system ingesting sensor readings from hundreds of devices, each holding a write open while it does other work, is not.

Where SQLite really is the wrong choice#

Three situations come up again and again in the official docs.

The first is a network filesystem. The network caveats page says SQLite "is designed for situations where the data and application coexist on the same machine", and that if your data is separated from the application by a network, you generally want a client/server database. It isn't tested across networks, and the page's advice on relying on it anyway is "at your (and your customers') peril". If your plan involves a database file on a shared drive that several desktops open directly, stop there and use PostgreSQL or similar.

The second is horizontal scaling. If you already know the app will run on several servers that all need to write, a client/server database saves you a migration later. The SQLite guide says as much for websites that are write-intensive or busy enough to need multiple servers.

The third is heavy write concurrency, covered above. If writes can't queue for a few milliseconds, SQLite is the wrong tool.

Notice what's missing from that list: "it's for production", "it's not a real database" and "we might get big one day". None of those appear in the official guidance as reasons to avoid it.

Turn on WAL mode#

If you go with SQLite, the first setting to change is the journal mode. By default SQLite uses a rollback journal. Write-ahead logging is usually the better fit for an app with several users, because in WAL mode "readers do not block writers and a writer does not block readers". The same page says WAL is significantly faster in most scenarios.

PRAGMA journal_mode=WAL;

Unlike other journal modes, WAL is persistent: set it once and the database comes back in WAL mode after it's closed and reopened.

There are trade-offs to know about. All processes using the database must be on the same host, because WAL relies on shared memory, and it doesn't work over a network filesystem. You'll also see two extra files next to your database, ending in -wal and -shm. The -wal file is part of the database's state. The WAL docs warn that if you copy or move the database without it, committed transactions can be lost or the file can be corrupted.

One less obvious failure is checkpoint starvation. SQLite folds the WAL back into the main file in checkpoints, by default whenever the WAL reaches 1,000 pages. A checkpoint can only reset the WAL if no reader is using it, so an app with a constant overlap of long-running reads can see the WAL file grow without bound. The fix is to keep read transactions short so there are gaps, or run manual checkpoints yourself, which the docs suggest for this case.

Handle "database is locked" properly#

Most complaints about SQLite in production are really complaints about SQLITE_BUSY. Two settings deal with nearly all of it.

Set a busy timeout on every connection. The busy timeout makes SQLite sleep and retry when the database is locked, until the time you give runs out, and only then return SQLITE_BUSY. Your driver may expose it as a connection option, or you can use PRAGMA busy_timeout. For an internal app we'd start with a few seconds.

Start transactions that will write with BEGIN IMMEDIATE. SQLite's default is a deferred transaction, which starts as a read and upgrades to a write when it reaches the first write statement. The transaction docs explain the catch: if another connection has already modified the database or is in the process of doing so, the upgrade isn't possible and the write fails with SQLITE_BUSY, partway through your logic. IMMEDIATE starts the write straight away, so the transaction either gets the write lock at the start or fails before it has done anything. That's far easier to retry cleanly.

Settings people forget#

Foreign keys are off by default. The foreign key docs say they're disabled for backwards compatibility and "must be enabled separately for each database connection" with PRAGMA foreign_keys=ON. If your connection pool opens new connections, make sure each one runs it, or your REFERENCES clauses are decoration.

Types are flexible by default. An INTEGER column will try to convert what you insert, but if the conversion would lose information it stores the original string instead. If you'd rather get an error, declare STRICT tables, available since version 3.37.0, by adding STRICT after the closing bracket of CREATE TABLE. For a business app where a bad import should fail loudly, that's worth doing on day one.

Back it up like a database, not a file#

Copying the .db file with cp while the app is running is the classic mistake. The backup API documentation explains that the old copy-the-file approach blocks writers for the duration and can leave a corrupt backup if the machine fails mid-copy. In WAL mode you also risk separating the database from its -wal file.

Use one of the supported methods instead. The online backup API copies a live database incrementally without locking it for the whole copy, and VACUUM INTO 'backup.db' writes a compacted copy of a live database to a new file. Both are easy to put in a nightly job. For continuous protection, Litestream is a tool that continuously monitors a SQLite database and backs it up to S3 (its getting-started tutorial uses MinIO), with a litestream restore command to pull a copy back down. Whatever you pick, test a restore before you need one.

A quick way to decide#

For a new internal tool, ask two things. Will the code that talks to the database run on one machine? Can your writes take turns for a few milliseconds? If both answers are yes, SQLite is a sound production choice, and it's the one we reach for first at TheAILAB for small single-server tools because there's less to run. Turn on WAL, set a busy timeout, use BEGIN IMMEDIATE for writes, enable foreign keys on every connection, and schedule a proper backup. If either answer is no, or you can already see it becoming no within a year, start on PostgreSQL and skip the migration.

Sources

1. Appropriate Uses For SQLite — SQLite (sqlite.org) (checked 2026-10-12)
2. Write-Ahead Logging — SQLite (sqlite.org) (checked 2026-10-12)
3. SQLite Over a Network, Caveats and Considerations — SQLite (sqlite.org) (checked 2026-10-12)
4. Transaction — SQLite (sqlite.org) (checked 2026-10-12)
5. Set A Busy Timeout — SQLite C interface documentation (sqlite.org) (checked 2026-10-12)
6. SQLite Foreign Key Support — SQLite (sqlite.org) (checked 2026-10-12)
7. STRICT Tables — SQLite (sqlite.org) (checked 2026-10-12)
8. Using the SQLite Online Backup API — SQLite (sqlite.org) (checked 2026-10-12)
9. Getting Started — Litestream documentation (checked 2026-10-12)