SQLite for production on one VPS: e dey work?

When SQLite dey the right production database for VPS

To run SQLite for production on VPS dey the right choice for plenty small applications, and the reason simple: one process for one machine wey dey write to one file no need database server. No daemon dey to supervise, no port dey to firewall, no password dey to rotate, and no second machine dey to keep alive. Query na function call instead of network round trip, so page wey dey run forty queries go cost forty function calls.

The cost narrow but e real. SQLite allow only one writer at a time across the whole database file, and the file no fit dey shared between two machines. Both limits dey okay for one VPS wey dey run one application. But both become fatal as soon as the application pass that shape. This guide cover the settings wey make SQLite safe for server, continuous backup with Litestream, and the point wey you suppose stop.

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

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

That go print version wey start with 3., followed by build date and source hash. Ubuntu 24.04 dey ship SQLite 3.45.1 as of July 2026. Your application probably no dey use this binary: most language runtimes dey bundle their own copy of SQLite library, often newer one, so check the version wey your database driver report before you depend on recent feature.

Why WAL mode na be the first thing wey you go change

By default, SQLite dey use rollback journal. Before e change any page, e dey copy the original page enter one -journal file, then e dey edit the database directly. To do this safely, e dey take exclusive lock for the whole file. So every reader go wait whenever any write dey happen. For laptop, person no go notice. For web server, one slow write fit hold up every request wey dey touch the database.

WAL (write-ahead log) mode dey reverse this order. Writer dey append the new pages enter one separate -wal file and e leave the main database as e be. Readers continue to read the main file from the snapshot wey dem start with. So readers no dey block writer, and writer no dey block readers. Later, checkpoint dey copy the WAL pages wey collect back enter the main database. Na this one change dey do most of the work wey make SQLite usable behind web application.

Turn on WAL mode and confirm say e stick

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

The command dey print wal. That output no be decoration. PRAGMA journal_mode dey return the mode wey the database really dey use, so if e reply delete, the change fail and you still dey use the rollback journal.

WAL mode dey persist. E be flag for the database header, no be connection setting. So, you run am once for each database file, and every connection wey come after inherit am, even after reboot. Prove am with fresh connection.

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

Now create table and check wetin appear for 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/

Three files dey now: app.db, app.db-wal and app.db-shm. The -wal file dey hold committed pages wey dem never checkpoint yet. The -shm file na shared memory index wey every connection map, so dem all agree on wetin WAL contain. Both files belong to the database; dem no be scratch files. If you copy app.db alone while the application dey run, you go get file wey miss every recent commit. If you delete app.db and leave the other two files, SQLite go apply those stale WAL pages to any new file wey later appear with that name. Na so people dey corrupt fresh database when dem dey try reset one.

Connection settings wey every production app need

Na only journal_mode dey store for database. Every other setting below na per connection. This mean say your application gatz run am for every connection wey e open, including every connection wey a pool create for background.

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

busy_timeout = 5000 tell SQLite make e continue to retry locked database for up to 5000 milliseconds before e return database is locked. The default na 0. So by default, SQLite fail immediately the first time two writers overlap. If you set this one value, e go remove most lock errors wey people dey blame on SQLite itself.

synchronous = NORMAL na the correct setting for WAL mode, and you need understand the trade-off. For FULL, SQLite dey call fsync for WAL on every commit. For NORMAL, e dey sync during checkpoints instead. SQLite documentation talk am clearly: transactions no longer durable after power failure or hard reset. That power loss no fit corrupt the database. You go only lose the last commits wey never reach disk. For VPS, this trade-off usually make sense because e remove one fsync from every single write path.

foreign_keys = ON dey off by default for backwards compatibility, and e dey apply per connection. A schema wey full of REFERENCES clauses no go enforce anything until every connection turn this on.

One more setting go matter later. SQLite dey run checkpoints automatically once WAL grow pass 1000 pages. The connection wey happen to finish a transaction at that time dey do the work. That one dey okay by itself. But e become a question when Litestream dey run, because Litestream want control when checkpoints happen.

Wetin still dey happen for database is locked after you set busy_timeout

Na this failure dey make people return to Postgres, and e get one specific cause.

A busy timeout dey install a busy handler, but SQLite no promise say e go call am.

If SQLite determine say calling the busy handler fit cause deadlock, e go return SQLITE_BUSY to the application instead of calling the busy handler.

The deadlock wey e dey avoid dey happen when transaction upgrade. A plain BEGIN for SQLite mean BEGIN DEFERRED. If the first statement after am na SELECT, you dey inside read transaction. When later UPDATE for that same transaction need change to write transaction, and another connection don write since your read start, SQLite no fit make you wait. Your snapshot don already become old, and waiting go only cause the two connections to deadlock each other. The documentation talk the result directly:

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

Your 5000 millisecond timeout no dey get checked at all. The error dey come immediately, na why e dey look like the setting no do anything.

The fix na one word.

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

BEGIN IMMEDIATE dey take the write lock from the beginning, before e read anything. No upgrade dey happen, so no deadlock dey to avoid. The busy handler fit apply, and the connection go wait for e turn instead of failing. Leave read-only transactions deferred. Any transaction wey get write inside am suppose be immediate.

The second cause of lock errors harder to notice: keeping write transaction open while slow work dey happen. SQLite dey serialise writers, so transaction wey open, call external API through network, and later commit go block every other writer for as long as that call last. Read wetin you need, close the transaction, do the slow work, then open short write transaction to save the result.

Continuous backup with Litestream

Nightly copy dey lose up to one day of writes, and if you run cp against live SQLite database, e fit create copy wey no go open. Two things dey safe. sqlite3 app.db ".backup /path/to/backup.db" dey use SQLite online backup interface and e dey work against database wey dey in use. Litestream dey go further: e dey watch WAL and continuously ship changes go object storage. This moves your worst-case data loss from one day to about one second.

Litestream na one Go binary wey dey run beside your application. E no dey sit between the app and database. Your application dey write to SQLite exactly as before, and Litestream dey read WAL and upload wetin change.

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 na the release wey official Linux install page document as of July 2026, and v0.5.15 follow on 21 July 2026. Change the version for both lines make e match current tag for releases page, and use the matching arm64 package instead if your VPS na arm64.

The configuration file dey for /etc/litestream.yml. Start with local file replica, because e go prove the complete loop without cloud credentials.

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

Notice say the field na replica, singular. Litestream 0.5 replace the replicas array from 0.3 series with one replica block, and config wey carry two entries now dey fail for startup. Many third-party guides still dey show the old array, so copy the shape above instead of the first example wey search show. The 0.5 series also rename the litestream wal subcommand to litestream ltx, because the on-disk backup format change.

Check say the config parse before you enable anything.

sudo litestream databases -config /etc/litestream.yml

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

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

That one dey run for foreground and e go continue to run. For second shell, write one row and restore the replica into 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 include the new row. If e no include am, the change never sync yet: Litestream dey push on a sync-interval wey default to 1 second, so wait and restore again. That one second na also your recovery point. If crash happen, e go lose at most the writes from the last sync interval, and no configuration fit make that zero.

For real storage, replace the replica block with S3 URL. This one dey work with Amazon S3 and 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

No keep credentials inside that file. Litestream dey read LITESTREAM_ACCESS_KEY_ID and LITESTREAM_SECRET_ACCESS_KEY from environment, so put dem inside systemd drop-in wey root own and wey get mode 600.

The snapshot values above na the defaults, and the retention default dey surprise people. Retention na how long Litestream dey keep snapshots and the files wey belong to dem, so e also show how far back for time you fit restore. Twenty-four hours mean say bad migration wey you notice Wednesday morning don already become unrecoverable from Monday state. Set retention: 168h for one week and pay for the extra storage.

Prove sayin say restore work before you need am

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;"

When you give database path, litestream restore dey find the replica wey match am for /etc/litestream.yml and pull am down. PRAGMA integrity_check dey print ok for file wey healthy, and any other output mean say the restored copy no usable. Run dis on schedule with systemd service and timer and read the output. Until you don restore backup once, you no know whether e dey work.

Run Litestream under systemd

The Debian package dey install one litestream unit wey dey read /etc/litestream.yml.

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

Healthy output dey name each database from the config, then e dey quiet except for periodic sync lines. Error of no such file or directory against your database path mean say the path for the config no correct, or the process no fit read am. By default, the unit dey run as root, and this privilege pass wetin this job need. Litestream must fit read and write both the database and the directory wey hold am, because e dey work with the -wal and -shm files wey dey beside your database. So give am the account wey your application already dey use.

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

Apply am with sudo systemctl daemon-reload and sudo systemctl restart litestream. Setting up dedicated service account with least privilege dey take few minutes. Na this one dey separate backup agent from another root process for the box.

One ordering detail dey important if you ever rebuild the machine from nothing. You want make the database restore before the application starts. litestream restore accepts -if-db-not-exists, wey dey exit 0 when the file don already dey there, so e safe to run am for every boot. Put am for an ExecStartPre line inside your application's unit, and fresh VPS go pull the database down while existing one no go do anything. litestream replicate get matching -restore-if-db-not-exists flag if you prefer keep am for one place.

Where SQLite dey break for VPS

Network filesystems. Na limit wey you no fit configure around. WAL mode need every process wey dey use the database to share small memory region, and na the -shm file dey provide am. SQLite documentation state the rule without qualification:

All processes wey dey use database must dey for the same host computer; WAL no dey work over network filesystem.

So database for mounted NFS (network file system) or SMB share fit corrupt, and no pragma fit prevent am. One difference dey here wey people dey miss. Network block device, wey na wetin most VPS providers attach as extra storage, dey appear to Linux like ordinary disk with ordinary filesystem, and that one dey okay. Mounted file share no be the same thing.

A second application server. No setting fit make this work. Once two machines need to serve the same data, you need database wey fit communicate over network. Decide on that move while you still get time to plan am.

Write-heavy workloads. One writer at a time na property of the file format, no be setting wey you fit tune. Short writes cheap because each commit na append to the WAL, so throughput dey follow your disk small-write latency more than your CPU. See NVMe against SATA SSD storage for VPS to see how that difference dey look. Long transactions na the real problem, because dem queue every other writer behind dem.

Analytical queries. SQLite na row store wey dem build for transactions. Dashboard wey dey scan hundred million rows na different work for different tool, and DuckDB compared with SQLite for server work explain where that boundary dey.

VACUUM under replication. A full VACUUM dey rewrite the entire database file, meaning say Litestream must upload all of am again, and Litestream documentation advise against running am in place while replication dey active. Stop the replicator, run vacuum, start am again, and expect fresh full snapshot.

Two replicators on one database. Never run two Litestream processes against the same database or the same replica destination. Documentation clear say na your responsibility to prevent this, and the result na replica wey you no fit restore.

Wetin Litestream no cover

Litestream dey protect only the database file. E no protect anything else. Uploaded files, application config, TLS (transport layer security) certificates and unit files still dey your responsibility. Use am together with encrypted off-box backups using restic on a schedule, so both parts dey covered. If the machine dey new, the first ten minutes on a new VPS covers the user account and firewall work wey this guide assume say dem don already do.

FAQ

SQLite dey good enough for production application?

For one application for one server, yes, as long as you turn on WAL mode, set a busy timeout, and back am up continuously. The limits wey matter na structural: na one writer at a time, and na one host machine. Application wey fit inside these limits get database wey no need network hop and no need separate process to monitor. Application wey no fit needs client-server database, and no amount of tuning go change that.

Why I still dey get database is locked after I set busy_timeout?

Because SQLite dey skip the busy handler when waiting fit cause deadlock. Transaction wey start with bare BEGIN na deferred: opening SELECT puts am inside read transaction, and later write must upgrade am. If another connection write in between, SQLite returns SQLITE_BUSY immediately instead of calling your busy handler, because your read snapshot don stale already. Start any transaction wey go write with BEGIN IMMEDIATE so e go take the write lock from the beginning and the timeout go apply.

I fit keep my SQLite database for network storage?

No, no be for network filesystem like NFS or SMB. WAL mode needs all processes to share memory through the -shm file, and SQLite documentation talk say every process wey dey use the database must dey for the same host computer. Network block device wey your provider attach na different thing: Linux dey see normal disk with normal filesystem, and SQLite dey work there.

I need Litestream if I already dey run nightly backups?

E depend on how much data you fit afford to lose. Nightly job mean say you fit lose up to twenty-four hours of writes. Litestream dey sync about once every second, so crash go cost you roughly the last second. E safer too pass copying database file with cp, because that one fit capture database while write still dey happen. Litestream cover only the database, so keep general file backup dey run beside am.