SQLite in Production: A Practical Setup with Litestream Backups

SQLite often gets dismissed as a toy database, which surprises the many useful applications quietly running on it. For a small service with one application server, it can remove an entire database process, its network configuration, and a surprising amount of operational work.

The catch is not whether SQLite can store production data. It can. The catch is whether your access pattern fits and whether you can restore it after a bad day. Litestream helps with the second part by continuously replicating SQLite changes to object storage.

When SQLite is a sensible production choice

SQLite runs inside your application process and stores the database on local disk. Reads do not cross a network, and writes use ACID transactions. This is excellent for blogs, small SaaS tools, internal apps, and single-node self-hosted services.

It is less suitable when many application servers must write concurrently, you need horizontal write scaling, or clients require direct network access to the database. PostgreSQL remains the better general answer for those cases. Our MySQL vs PostgreSQL comparison covers the server-database side of that decision.

Choose SQLite because the architecture is genuinely small, not because avoiding PostgreSQL is a goal in itself.

Configure it like you mean it

Enable write-ahead logging and a busy timeout when your application opens the database:

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

WAL allows readers and a writer to work more comfortably together. A busy timeout tells SQLite to wait briefly when the database is locked instead of immediately failing. Foreign-key enforcement should be enabled per connection because SQLite does not assume you want it.

Keep the database on a local disk. A network filesystem with weak locking or unreliable flush behavior can corrupt the assumptions SQLite depends on. Also give the application one well-designed write path; throwing dozens of competing writers at a single file is not a scalability strategy.

Back up uploads and other application data too. Replicating only the database cannot recover files stored beside it.

Add continuous replication with Litestream

Litestream watches SQLite's WAL and streams changes to an S3-compatible bucket or another supported destination. Install it using the official release instructions, create a private bucket, and give Litestream credentials limited to the backup location it needs.

Provide credentials through a protected service environment rather than writing them into your application repository. Then run replication as a system service:

litestream replicate /var/lib/myapp/app.db \
  s3://your-backup-bucket/myapp/app.db

The command stays running while it replicates. Under systemd or another supervisor, configure it to start with the application host and restart on failure. Monitor replication errors and object-storage access; a silently stopped backup process is just an expensive source of false confidence.

Keep retention for older snapshots where your storage setup supports it. Replication faithfully copies the current state, including a mistaken DELETE, so point-in-time recovery options matter.

Practice the restore before you need it

Stop the application or restore onto a separate test machine. Do not experiment on the only live database file.

litestream restore -o /var/lib/myapp-restored/app.db \
  s3://your-backup-bucket/myapp/app.db

Check the restored file and open it with the application:

sqlite3 /var/lib/myapp-restored/app.db 'PRAGMA integrity_check;'

Then verify real records, not just that a file appeared. A restore drill should include starting the app, signing in, and reading recent data.

Remember that continuous replication has a small recovery window: changes after the last successfully replicated update may be lost. Measure it instead of promising zero data loss.

Boring can be a feature

SQLite plus Litestream gives a single-node application a local database and an off-box recovery path without running a separate database cluster. That is a genuinely useful production architecture for the right workload.

Know the single-writer limits, keep backups off the machine, and rehearse recovery. If the application eventually needs multiple write nodes or heavy concurrent writes, migrate deliberately. Until then, one reliable file and a tested restore may be all the database infrastructure you need.