SQLite in Production: Effortless Real-time Backup with Litestream

SQLite is no longer a “demo toy” if you know how to protect it

SQLite is shedding its reputation as a “database for small apps” and making serious inroads into the production environments of many modern systems. Rather than burning hundreds of megabytes of RAM on a bloated PostgreSQL cluster, many developers choose SQLite for its simplicity: your entire dataset lives in a single file. But one question still gives people pause: “If the server crashes or the database file gets corrupted, what do I have to fall back on?”

Two years ago, I was running a small inventory management app on SQLite. One fine morning, the server’s hard drive threw a fit and corrupted the .db file mid-write. The most recent backup from the cron job was 24 hours old. The result? Every customer order from that entire day vanished into thin air. It was an expensive lesson in complacency. After that incident, I found Litestream — a tool that completely changed the game for SQLite reliability.

Comparing popular SQLite backup approaches

Before diving into configuration, let’s look at the traditional approaches to understand why Litestream stands out.

1. Manual file copy or cron job

You set up a script to periodically copy data.db to Google Drive or S3 — hourly or daily.

• Pros: Extremely easy to set up, no complex tooling required.
• Cons: Very high data loss risk (RPO). If the server goes down at 11 PM but your last backup ran at 1 AM, you lose 22 hours of data. On top of that, copying a file while it’s being written can result in a structurally corrupt backup.

2. Using SQLite’s .backup command

Running sqlite3 data.db ".backup 'backup.db'" is safer because it guarantees data consistency at the moment of the copy.

• Pros: The backup file is always in a clean, uncorrupted state.
• Cons: It’s still just a snapshot. You’re still exposed to a data gap between each backup run.

3. Litestream: Streaming WAL (Write-Ahead Log)

Litestream takes a fundamentally different approach. Instead of waiting for a scheduled time to copy the entire file, it monitors SQLite’s WAL file and pushes each individual change to S3 in real time.

• Pros: RPO (Recovery Point Objective) drops to under one second. If the server goes up in flames, you lose only a few milliseconds of the most recent data. Resource consumption is minimal — just a few megabytes of RAM.
• Cons: Requires a background process (sidecar) running alongside your application.

Why is Litestream so effective?

The key lies in WAL (Write-Ahead Log) mode. When WAL is enabled, SQLite writes changes to a secondary file (with a -wal suffix) before merging them into the main database file. Litestream acts like a surveillance camera — it reads this WAL file and streams each chunk of data to Cloudflare R2 or AWS S3 in real time. This streaming WAL approach is conceptually similar to how Barman handles continuous WAL archiving and point-in-time recovery for PostgreSQL, but purpose-built for the simplicity of SQLite.

What I appreciate most about Litestream is that it’s completely non-invasive. You don’t need to modify a single line of Python, Go, or Node.js code. It runs independently, protecting your database from the outside without adding any latency to your application.

A practical deployment guide with Cloudflare R2

I’m using Cloudflare R2 for this guide because they offer the first 10 GB of storage for free and — crucially — charge no egress fees for outbound bandwidth, which is a significant saving compared to AWS S3.

Step 1: Create a bucket on R2

Go to the Cloudflare Dashboard -> R2 -> Create bucket (name it my-sqlite-backup). Then create an API Token with Edit permissions. Save the Access Key ID and Secret Access Key for the next step.

Step 2: Install Litestream on your server

On an Ubuntu or Debian server, you can install the latest release with just a couple of commands:

wget https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.deb
sudo apt install ./litestream-v0.3.13-linux-amd64.deb

Step 3: Configure litestream.yml

Create the configuration file at /etc/litestream.yml. This is where you define the “source” and “destination” for your data.

access-key-id: YOUR_R2_ACCESS_KEY_ID
secret-access-key: YOUR_R2_SECRET_ACCESS_KEY

databases:
  - path: /var/www/app/data.db
    replicas:
      - url: s3://my-sqlite-backup.YOUR_ACCOUNT_ID.r2.cloudflarestorage.com/db

Pro tip: If you use Git to manage your configuration, never paste your keys directly into this file. Use environment variables instead to avoid leaking sensitive credentials — and consider encrypting your database file at rest as an additional layer of defense against unauthorized access.

Step 4: Enable WAL mode

Litestream only works when the database is in WAL mode. Run this command to enable it:

sqlite3 /var/www/app/data.db "PRAGMA journal_mode=WAL;"

Step 5: Start the service

Start Litestream to begin the replication process:

sudo systemctl enable litestream
sudo systemctl start litestream

To confirm everything is running smoothly, check the logs:

journalctl -u litestream -f

The restore process

Backups are only useful if you can actually restore from them. When an old server dies, spin up a new one and run a single command to recover all your data:

litestream restore -o /var/www/app/data.db s3://my-sqlite-backup.YOUR_ACCOUNT_ID.r2.cloudflarestorage.com/db

Litestream will automatically download the latest snapshot and apply the WAL changes to bring the database back to the state it was in right before the failure.

Hard-won tips and things to watch out for

• Monitor closely: Litestream supports exporting metrics to Prometheus. Set up an alert if the replication stream is interrupted for more than 5 minutes.
• Manage costs: While S3 storage is cheap, a write-heavy application will generate a large number of WAL segments. Configure a retention period of around 7–30 days to keep costs in check.
• Run regular drills: Don’t wait for the house to burn down before looking for a fire extinguisher. Once a month, practice restoring your data to a clean virtual server to make sure your recovery process actually works.
• Docker deployment: You can run Litestream as a sidecar container alongside your app. However, the simplest approach is to bundle it into your app’s image and manage it with supervisord. If you’re building a more complete self-hosted stack, self-hosted Supabase via Docker is worth exploring as a complementary setup.

The combination of SQLite and Litestream delivers genuine peace of mind at virtually zero cost. If you’re running small to medium-sized projects, give this combo a try — you get all the convenience of SQLite without the constant fear of losing your data. And if your workload eventually shifts toward heavy analytics, DuckDB offers a similarly lightweight, single-file experience purpose-built for analytical queries.