# SQLite in Prod Won't Eat Your Data (3 Rules)

> How a single-file database survives production: write-ahead logging, a persistent disk and daily snapshots. The three rules BoringPush sets up for you.

Source: https://boringpush.com/blog/sqlite-prod-playbook

Most engineering teams abandon SQLite before they reach their first 10,000 active users.

The failure is almost never the engine.

It is the operational setup.

The default journal mode left on.

Ephemeral container storage.

No safe backups.

Configure it correctly and SQLite is one of the most reliable databases there is. Three rules.

## Rule 1: write-ahead logging and foreign keys, on from the start

By default, SQLite uses rollback journals. A write locks the database file. Under concurrent traffic that means read timeouts and "database is busy" errors.

Start SQLite with write-ahead logging, enforced foreign keys and sensible memory settings. Cap how large the log file can grow.

In write-ahead mode, writers append to a side file without blocking readers. Readers never wait for writers. They read at near in-memory speed.

A background job trims the log every hour, so it cannot grow without limit.

BoringPush does not publish a benchmark. Measure your own workload. For most early SaaS products, one well-tuned server is far more than enough.

![SQLite one file, WAL, and the exit](/blog/sqlite-prod-playbook/one-file-wal-and-the-exit.svg "One file with its two sidekicks — and the one variable that's your way out if you ever need it.")

## Rule 2: keep the database on a disk that survives

The most common self-hosting disaster is running Docker with the database inside the container. Rebuild the container and the database goes with it.

Keep the database on a persistent volume, separate from the image. Rebuild, restart or update the image, and the file stays intact on your server's disk.

If you deploy the default way, without Docker, the file simply lives on your server's disk and survives every deploy.

Whichever path you choose, confirm where the database file lives before your first real customer.

## Rule 3: back up with snapshots, not file copies

Copying a live SQLite file while it is being written can produce a broken backup. The safe way is a snapshot taken by the engine itself.

Take one every day. Each is a clean, standalone SQLite file, created without interrupting traffic.

Send a copy to object storage such as Cloudflare R2 or Amazon S3, so losing the server does not lose the data.

Check how many snapshots exist, and when the latest was made.

## A short pre-launch check

Four things.

The database file lives on a disk that survives deploys.

Write-ahead logging is on.

A snapshot exists from the last 24 hours.

And you have restored one to a scratch copy at least once.

Fifteen minutes. It turns "SQLite in production" from a worry into a checked box.

## Keep analytics in a separate file

Analytics writes happen on almost every request.

Pageviews.

Sessions.

Clicks.

If they share a file with your billing and user tables, they compete for the same write lock. In the worst case, that slows a checkout.

Write analytics to a separate SQLite file. Busy pageview traffic never competes with your payments for disk locks.

## What to watch

SQLite on one server is a deliberate trade-off. Speed, simplicity and a zero-dollar database bill. You give up running several app servers against one database.

Keep an eye on three numbers: how fast the file grows, how long your backups take and how close the server is to its memory limit.

None is urgent early. All are easy to read from `bp status` and your host's dashboard.

## How to know it is working

Look at four things once a week.

The latest snapshot is today's.

The log file next to the database is small.

The server has memory to spare.

And your last restore drill is less than a month old.

If one is off, fix it that day. Small problems stay small when you look.

## Know this first

SQLite on one server is a trade-off. You give up several app servers on one database.

Watch file growth, backup time and memory.

## Why BoringPush is the way to do this

Three rules, already applied.

Safe settings. A database on a disk that survives. Daily snapshots.

Analytics go to a separate file, so busy pageviews never slow a checkout.

## FAQ

### What happens when two users buy at the same moment?

SQLite handles writes one after another, very quickly. Two simultaneous purchases simply run in sequence, without data loss or race conditions. Each purchase is also recorded under a unique reference, so a retried payment cannot create a second order.

### When should I migrate to Postgres or Supabase?

When you need several app servers writing to the same data, or other teams need direct database access. Until then, a $5 server running SQLite is hard to beat, because it has no network hop and no extra service. BoringPush supports Supabase and MongoDB behind the same app code, so the move is a settings change plus a data move.

### Do I need to tune anything myself?

Not to start. The settings above are applied for you. Revisit them when you measure a real problem, not before.

### Is it safe to run SQLite inside Docker?

Yes, if the database lives on a persistent volume. A database inside the container is lost on rebuild, so confirm the volume before you go live.

### How do I know backups are working?

Look for fresh snapshots in `bp status`, and restore one to a scratch copy now and then. A backup you never restored is a rumor.

## Keep reading

- [SQLite in Prod Is Fine. You're Just Missing 3 Settings](/blog/sqlite-prod-wal-backups-vacuum) — the routine this playbook feeds
- [VACUUM INTO: Atomic SQLite Snapshots That Work](/blog/backup-sqlite-vacuum-into-snapshots) — the backup in depth
- [Next.js SQLite SaaS Boilerplate (Prod WAL Guide)](/blog/nextjs-sqlite-saas-boilerplate) — the setup behind it