SQLite in production on a VPS

When SQLite is the right production database on a VPS

Running SQLite in production on a VPS is the right choice for most small applications, and the reason is simple: one process on one machine writing to one file does not need a database server. There is no daemon to supervise, no port to firewall, no password to rotate, no second machine to keep alive. A query is a function call rather than a network round trip, so a page that runs forty queries costs you forty function calls.

The cost is narrow and real. SQLite allows one writer at a time across the whole database file, and the file cannot be shared between two machines. Both limits are fine for a single VPS running a single application. Both are fatal the moment you outgrow that shape. This guide covers the settings that make SQLite safe on a server, continuous backup with Litestream, and the point where you should stop.

Install the command line tool first. Everything below was run on Ubuntu 24.04.

sudo apt update
sudo apt install -y sqlite3
sqlite3 --version

That prints a version starting with 3. followed by a build date and a source hash. Ubuntu 24.04 ships SQLite 3.45.1 as of July 2026. Your application probably does not use this binary: most language runtimes bundle their own copy of the SQLite library, often a newer one, so check the version your database driver reports before you rely on a recent feature.

Why WAL mode is the first thing you change

By default SQLite uses a rollback journal. Before changing a page it copies the original page into a -journal file, then edits the database in place. To do that safely it takes an exclusive lock on the whole file, so every reader waits while any write is in progress. On a laptop nobody notices. On a web server, one slow write stalls every request that touches the database.

WAL (write-ahead log) mode reverses the order. A writer appends the new pages to a separate -wal file and leaves the main database alone. Readers keep reading the main file at the snapshot they started with, so readers do not block the writer and the writer does not block readers. Later, a checkpoint copies the accumulated WAL pages back into the main database. This one change is most of what makes SQLite usable behind a web application.

Turn on WAL mode and confirm it stuck

mkdir -p ~/app
sqlite3 ~/app/app.db "PRAGMA journal_mode=WAL;"

The command prints wal. That output is not decoration. PRAGMA journal_mode returns the mode the database is actually in, so a reply of delete means the change failed and you are still on the rollback journal.

WAL mode is persistent. It is a flag in the database header rather than a connection setting, so you run it once per database file and every connection afterwards inherits it, including after a reboot. Prove that with a fresh connection.

sqlite3 ~/app/app.db "PRAGMA journal_mode;"

Now create a table and look at what appears on disk.

sqlite3 ~/app/app.db <<'SQL'
CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes (body) VALUES ('first row');
SQL
ls -l ~/app/

There are three files now: app.db, app.db-wal and app.db-shm. The -wal file holds committed pages that have not been checkpointed yet. The -shm file is a shared memory index that every connection maps so they all agree on what the WAL contains. Both belong to the database and are not scratch files. Copy app.db on its own while the application is running and you get a file that is missing every recent commit. Delete app.db and leave the other two in place and SQLite will apply those stale WAL pages to whatever new file appears under that name, which is how people corrupt a fresh database while trying to reset one.

The connection settings every production app needs

Only journal_mode is stored in the database. Every other setting below is per connection, which means your application has to run it on each connection it opens, including every connection a pool creates in the background.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

busy_timeout = 5000 tells SQLite to keep retrying a locked database for up to 5000 milliseconds before it returns database is locked. The default is 0, so by default SQLite fails instantly the first time two writers overlap. Setting this single value removes most of the lock errors that get blamed on SQLite itself.

synchronous = NORMAL is the right setting in WAL mode, and the trade is worth understanding. At FULL, SQLite calls fsync on the WAL at every commit. At NORMAL, it syncs at checkpoints instead. The SQLite documentation is blunt about what you give up: transactions are no longer durable after a power failure or a hard reset. The database cannot be corrupted by that power loss, you simply lose the last commits that had not reached the disk. On a VPS that is usually the right trade, because it takes an fsync out of the path of every single write.

foreign_keys = ON is off by default for backwards compatibility, and it is per connection. A schema full of REFERENCES clauses enforces nothing at all until each connection turns this on.

One more setting matters only later. SQLite checkpoints automatically once the WAL grows past 1000 pages, and the work is done by whichever connection happens to finish a transaction at that moment. That is fine on its own. It becomes a question when Litestream is running, because Litestream wants control over when checkpoints happen.

Why database is locked still happens after you set busy_timeout

This is the failure that sends people back to Postgres, and it has one specific cause.

A busy timeout installs a busy handler, and SQLite does not promise to call it.

If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY to the application instead of invoking the busy handler.

The deadlock it is avoiding happens when a transaction upgrades. A bare BEGIN in SQLite means BEGIN DEFERRED. If the first statement after it is a SELECT, you are in a read transaction. When a later UPDATE in that same transaction needs to become a write transaction, and another connection has written since your read began, SQLite cannot make you wait, because your snapshot is already out of date and waiting would only deadlock the two connections against each other. The documentation states the outcome directly:

Subsequent write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY.

Your 5000 millisecond timeout is never consulted. The error arrives immediately, which is why it looks like the setting did nothing.

The fix is one word.

BEGIN IMMEDIATE;
UPDATE notes SET body = 'edited' WHERE id = 1;
COMMIT;

BEGIN IMMEDIATE takes the write lock at the start, before reading anything. There is no upgrade, so there is no deadlock to avoid, so the busy handler does apply and the connection waits its turn instead of failing. Keep read-only transactions deferred. Any transaction that contains a write should be immediate.

The second cause of lock errors is harder to spot: holding a write transaction open across slow work. SQLite serialises writers, so a transaction that opens, calls an external API over the network, and then commits will block every other writer for the length of that call. Read what you need, close the transaction, do the slow work, then open a short write transaction to store the result.

Continuous backup with Litestream

A nightly copy loses up to a day of writes, and running cp against a live SQLite database can produce a copy that will not open. Two things are safe. sqlite3 app.db ".backup /path/to/backup.db" uses SQLite's online backup interface and works against a database in use. Litestream goes further: it watches the WAL and ships changes to object storage continuously, which moves your worst-case data loss from a day to about a second.

Litestream is one Go binary that runs beside your application. It does not sit between the app and the database. Your application writes to SQLite exactly as before, and Litestream reads the WAL and uploads what changed.

cd /tmp
curl -fsSL -O https://github.com/benbjohnson/litestream/releases/download/v0.5.14/litestream-0.5.14-linux-x86_64.deb
sudo dpkg -i litestream-0.5.14-linux-x86_64.deb
litestream version

v0.5.14 is the release the official Linux install page documents as of July 2026, and v0.5.15 followed on 21 July 2026. Change the version in both lines to match the current tag on the releases page, and take the matching arm64 package instead if your VPS is arm64.

The configuration file lives at /etc/litestream.yml. Start with a local file replica, because it proves the whole loop without needing cloud credentials.

dbs:
  - path: /home/appuser/app/app.db
    replica:
      type: file
      path: /var/backups/litestream/app

Note that the field is replica, singular. Litestream 0.5 replaced the replicas array from the 0.3 series with a single replica block, and a config carrying two entries now fails at startup. Many third-party guides still show the old array, so copy the shape above rather than the first example a search turns up. The 0.5 series also renamed the litestream wal subcommand to litestream ltx, because the on-disk backup format changed.

Check that the config parses before you enable anything.

sudo litestream databases -config /etc/litestream.yml

Then prove the round trip by hand. This form skips the config file and replicates one database to one path.

mkdir -p /tmp/replica
litestream replicate ~/app/app.db file:///tmp/replica/app

That runs in the foreground and keeps running. In a second shell, write a row and restore the replica into a new file.

sqlite3 ~/app/app.db "INSERT INTO notes (body) VALUES ('written after replication started');"
litestream restore -o /tmp/restored.db file:///tmp/replica/app
sqlite3 /tmp/restored.db "SELECT count(*) FROM notes;"

The count includes the new row. If it does not, the change has not synced yet: Litestream pushes on a sync-interval that defaults to 1 second, so wait and restore again. That one second is also your recovery point. A crash loses at most the writes from the last sync interval, and no configuration makes that zero.

For real storage, swap the replica block for an S3 URL. This works against Amazon S3 and against S3-compatible object storage from other providers.

dbs:
  - path: /home/appuser/app/app.db
    replica:
      url: s3://your-bucket-name/app
      region: us-east-1

snapshot:
  interval: 24h
  retention: 24h

Keep credentials out of that file. Litestream reads LITESTREAM_ACCESS_KEY_ID and LITESTREAM_SECRET_ACCESS_KEY from the environment, so put them in a systemd drop-in owned by root with mode 600.

The snapshot values above are the defaults, and the retention default surprises people. Retention is how long Litestream keeps snapshots and the files that belong to them, so it is also how far back in time you can restore. Twenty-four hours means a bad migration you notice on Wednesday morning is already unrecoverable from Monday's state. Set retention: 168h for a week and pay for the extra storage.

Prove the restore before you need it

litestream restore -o /tmp/check.db /home/appuser/app/app.db
sqlite3 /tmp/check.db "PRAGMA integrity_check;"
sqlite3 /tmp/check.db "SELECT count(*) FROM notes;"

Given a database path, litestream restore looks up the matching replica in /etc/litestream.yml and pulls it down. PRAGMA integrity_check prints ok on a healthy file, and any other output means the restored copy is not usable. Run this on a schedule with a systemd service and timer and read the output. Until you have restored a backup once, you do not know it works.

Run Litestream under systemd

The Debian package installs a litestream unit that reads /etc/litestream.yml.

sudo systemctl enable litestream
sudo systemctl start litestream
sudo journalctl -u litestream -f

Healthy output names each database from the config and then stays quiet apart from periodic sync lines. An error of no such file or directory against your database path means the path in the config is wrong, or the process cannot read it. The unit runs as root by default, which is more privilege than this job needs. Litestream must be able to read and write both the database and the directory holding it, because it works with the -wal and -shm files next to your database, so give it the account your application already uses.

# /etc/systemd/system/litestream.service.d/override.conf
[Service]
User=appuser
Group=appuser

Apply it with sudo systemctl daemon-reload and sudo systemctl restart litestream. Setting up a dedicated service account with least privilege takes a few minutes, and it is the difference between a backup agent and a second root process on the box.

One ordering detail matters if you ever rebuild the machine from nothing. You want the database restored before the application starts. litestream restore accepts -if-db-not-exists, which exits 0 when the file is already there, so it is safe to run on every boot. Put it in an ExecStartPre line on your application's unit and a fresh VPS pulls the database down while an existing one does nothing. litestream replicate has a matching -restore-if-db-not-exists flag if you would rather keep it in one place.

Where SQLite breaks on a VPS

Network filesystems. This is the limit you cannot configure around. WAL mode requires every process using the database to share a small region of memory, which is what the -shm file provides. The SQLite documentation states the rule without qualification:

All processes using a database must be on the same host computer; WAL does not work over a network filesystem.

So a database on a mounted NFS (network file system) or SMB share can corrupt, and no pragma prevents it. There is a distinction here that people miss. A network block device, which is what most VPS providers attach as extra storage, appears to Linux as an ordinary disk with an ordinary filesystem on it, and that is fine. A mounted file share is not.

A second application server. No setting makes this work. Once you need two machines serving the same data, you need a database that speaks over the network. Decide on that move while you still have time to plan it.

Write-heavy workloads. One writer at a time is a property of the file format, not a tunable. Short writes are cheap because each commit is an append to the WAL, so throughput follows your disk's small-write latency more closely than your CPU. See NVMe against SATA SSD storage on a VPS for what that difference looks like. Long transactions are the real problem, because they queue every other writer behind them.

Analytical queries. SQLite is a row store built for transactions. A dashboard scanning a hundred million rows is a different job for a different tool, and DuckDB compared with SQLite for server work covers where that line falls.

VACUUM under replication. A full VACUUM rewrites the entire database file, which means Litestream has to upload all of it again, and the Litestream documentation advises against running it in place while replication is active. Stop the replicator, vacuum, start it again, and expect a fresh full snapshot.

Two replicators on one database. Never run two Litestream processes against the same database or the same replica destination. The documentation is explicit that preventing this is your responsibility, and the result is a replica you cannot restore.

What Litestream does not cover

Litestream protects the database file and nothing else. Uploaded files, the application config, TLS (transport layer security) certificates and the unit files are all still yours to handle. Pair it with encrypted off-box backups using restic on a schedule and both halves are covered. If the machine is new, the first ten minutes on a new VPS covers the user account and firewall work this guide assumes is already done.

FAQ

Is SQLite good enough for a production application?

For one application on one server, yes, provided you turn on WAL mode, set a busy timeout, and back it up continuously. The limits that matter are structural: one writer at a time, and one host machine. An application that fits inside those limits gets a database with no network hop and no separate process to monitor. An application that does not fit needs a client-server database, and no amount of tuning changes that.

Why do I still get database is locked after setting busy_timeout?

Because SQLite skips the busy handler when waiting could cause a deadlock. A transaction that starts with a bare BEGIN is deferred: an opening SELECT puts it in a read transaction, and a later write has to upgrade. If another connection wrote in between, SQLite returns SQLITE_BUSY immediately instead of calling your busy handler, since your read snapshot is already stale. Start any transaction that will write with BEGIN IMMEDIATE so the write lock is taken up front and the timeout applies.

Can I keep my SQLite database on network storage?

Not on a network filesystem such as NFS or SMB. WAL mode needs all processes to share memory through the -shm file, and the SQLite documentation states that every process using the database must be on the same host computer. A network block device attached by your provider is a different thing: Linux sees a normal disk with a normal filesystem on it, and SQLite works there.

Do I need Litestream if I already run nightly backups?

It depends on how much data you can afford to lose. A nightly job means losing up to twenty-four hours of writes. Litestream syncs about once a second, so a crash costs you roughly the last second. It is also safer than copying the database file with cp, which can capture a database mid-write. Litestream covers only the database, so keep a general file backup running next to it.