SQLite in Production: How Litestream Makes the ‘Unthinkable’ Safe

The 2 AM Disaster

It was 2 AM when my phone started vibrating off the nightstand. The monitoring dashboard for a small SaaS tool I managed was a sea of red. The SSD on my $5/month VPS had suffered a fatal hardware failure. Because I was running a standard SQLite setup, my data was trapped on a dead disk in a data center hundreds of miles away. My first thought was pure regret: ‘I should have just shelled out the $15 a month for managed Postgres.’

I’ve used PostgreSQL for complex relational schemas and MongoDB for rapid prototyping. They are industry standards for a reason. But for many side projects and mid-sized apps, they are massive overkill. They introduce network latency, require connection pooling, and eat up hundreds of megabytes of RAM. I wanted the raw speed of SQLite—where queries often run in under 0.1ms—without the ‘single point of failure’ anxiety. That is where Litestream changed everything.

The Problem: Why Engineers Fear SQLite

SQLite is incredibly fast because it lives inside your application process. There is no TCP overhead or hunting for a database server. However, it traditionally fails two production litmus tests:

• Disaster Recovery: If your server’s NVMe drive dies or the provider goes bankrupt, your database vanishes.
• Point-in-time Recovery: Running a cron job to copy a 2GB file every hour is clunky. You risk losing 59 minutes of user data if the crash happens just before the next backup.

Litestream solves this by acting as a ‘sidecar’ process. It monitors SQLite’s Write-Ahead Log (WAL) and streams every single transaction to cloud storage like AWS S3 or Cloudflare R2 in near real-time.

How It Works: The Magic of WAL Mode

To use Litestream, you must understand Write-Ahead Logging (WAL). In standard mode, SQLite writes directly to the main database file. In WAL mode, SQLite appends changes to a secondary -wal file first. This allows multiple readers and one writer to operate simultaneously without blocking each other.

Litestream watches this WAL file like a hawk. Every time a transaction is committed, Litestream captures the delta and ships it to your S3 bucket. If your server explodes, you simply spin up a new instance. You then tell Litestream to restore the data. It pulls the latest snapshot and replays the incremental changes. You lose virtually zero data.

Hands-on Practice: Setting up SQLite + Litestream

Let’s build a resilient setup. We will use Cloudflare R2 as our target because it has zero egress fees and a generous free tier of 10GB—perfect for small to medium apps.

1. Install Litestream

Litestream is a single Go binary. It is lightweight and has no dependencies. On a Linux server, installation takes seconds:

curl -LO https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.tar.gz
tar -xzf litestream-v0.3.13-linux-amd64.tar.gz
sudo install litestream /usr/local/bin/litestream

2. Enable WAL Mode

Your application needs to tell SQLite to use the WAL journal. You can do this in your code (e.g., using a PRAGMA statement) or manually via the CLI:

sqlite3 /var/lib/my-app/data.db "PRAGMA journal_mode=WAL;"

3. Configure the Replication

Create a configuration file at /etc/litestream.yml. This file acts as the bridge between your local disk and the cloud.

access-key-id: <your-r2-key>
secret-access-key: <your-r2-secret>

dbs:
  - path: /var/lib/my-app/data.db
    replicas:
      - url: s3://my-bucket-name.r2.cloudflarestorage.com/backup

4. Automate with Systemd

You don’t want to run this manually. Use a systemd service to ensure replication starts when the server boots. Create /etc/systemd/system/litestream.service:

[Unit]
Description=Litestream Replication
After=network.target

[Service]
ExecStart=/usr/local/bin/litestream replicate
Restart=always

[Install]
WantedBy=multi-user.target

Fire it up with: sudo systemctl enable --now litestream.

The Moment of Truth: Testing Recovery

A backup is just a wish until you test the restore. Let’s simulate a total server wipe. Delete your database file (carefully!):

rm /var/lib/my-app/data.db

Now, bring it back from the dead:

litestream restore -o /var/lib/my-app/data.db s3://my-bucket-name.r2.cloudflarestorage.com/backup

Within seconds, your database is back. For a production workflow, you should add this restore command to your Docker entrypoint or CI/CD pipeline. This ensures your app always starts with the latest state.

The Trade-offs: Is This Right for You?

This setup is the “Goldilocks zone” for many projects, but it isn’t a silver bullet for every use case.

The Wins:

• Cost: Managed Postgres on AWS RDS starts around $15–$30/month. This setup costs $0 on the R2 free tier.
• Zero Latency: Your app talks to a file on the local NVMe drive. There is no network hop to a database server.
• Simplicity: No more managing connection pools or complex VPC peering.

The Catch:

• Single Writer: SQLite handles concurrent reads beautifully, but only one process can write at a time. If you have a high-traffic app doing 500+ writes per second, stick to Postgres.
• Vertical Scaling Only: This is for single-node apps. If you need to scale horizontally to 10 application servers, you’ll need a distributed DB like CockroachDB or LiteFS.

Final Thoughts

Don’t default to a heavy, expensive database just because you’re afraid of a hardware failure. SQLite with Litestream gives you the resilience of a distributed system with the simplicity of a local file. It turned my 2 AM nightmares into peaceful sleep. I know that even if my VPS provider goes offline tomorrow, my data is safe in an encrypted bucket, ready to be restored in seconds. Keep your stack lean, keep your backups real-time, and only add complexity when your traffic actually demands it.