Single File Web App SQLite [2026]: Migrations + Backups That Work

Originally published at kunalganglani.com — read it there for inline code, hero image, and live links.

You can absolutely ship a single file web app sqlite setup that feels like magic: one server binary, one app.db file, and you’re done.

The prerequisite that trips people up is boring and unforgiving: SQLite is not “just a file” once you’re in WAL mode. You’re running a real database engine with journaling, locks, checkpointing, and failure modes. If you treat it like “just copy the file” and “just run migrations on startup,” you will eventually corrupt a backup or deadlock a deploy.

This post is the safety-first guide I wish more “SQLite everywhere” tutorials shipped with. No romance. Just patterns that keep your data intact.

What is a single-file web app with embedded SQLite?

A single-file web app with embedded SQLite is a web application where the server ships as a single deployable artifact (often one binary or one container image) and persists state in a single on-disk SQLite database file (e.g., app.db) that lives alongside the app.

Done right, it’s the cleanest way to ship internal tools and small self-hosted products: fewer moving parts than Postgres, easier backups than “managed database plus networking,” and much simpler disaster recovery.

But there’s a catch. The moment you enable Write-Ahead Logging (WAL), you’ve opted into a storage model that’s safe and fast, but only if you operationalize it.

Here’s the mental model I use:

• SQLite is a database engine first, a file format second.
• The “one file” story is a deployment story, not an operations story.
• Your app is either single-node (easy) or distributed (hard). There is no free lunch in between.

If you want the broader framing on when SQLite beats Postgres, see my earlier writeup: SQLite Production API Concurrency: WAL Mode Limits [2026].

Write-Ahead Logging (WAL) overview (and why everyone enables it)

WAL is SQLite’s journaling mode introduced in SQLite 3.7.0 (2010-07-21). It changes the write path so changes go to a -wal file first, then later get checkpointed back into the main database file.

In practice, WAL is popular for web apps for one reason: concurrency.

The official SQLite docs say WAL provides more concurrency because readers do not block writers and writers do not block readers. That’s not marketing. If you’ve ever had a read-heavy app stall because one request is doing a write transaction, WAL is how you stop that pain.

WAL also tends to be faster in many scenarios and uses fewer fsync() calls. That’s one of those “boring answer is actually right” optimizations. Less sync overhead helps a lot on typical cloud disks.

But WAL has a non-negotiable limitation that should be in giant red text on every SQLite tutorial:

• All processes using the database must be on the same host. WAL does not work over a network filesystem.

That’s straight from the SQLite WAL documentation: it requires shared memory for the WAL index, and processes across hosts cannot share that memory.

So if your plan involves multiple app replicas mounting the same SQLite file from NFS/EFS/SMB, stop. That’s not “clever lightweight architecture.” That’s a corruption incident waiting for a Tuesday.

Also: WAL adds siblings.

• app.db (main database)
• app.db-wal (write-ahead log)
• app.db-shm (shared memory / WAL index)

If you back up only app.db, you might not be backing up the database state you think you are.

Internal links that pair well with this mindset:

• microservices (because most people reach for distributed systems too early)
• Kubernetes (because the orchestration layer will happily restart your app at the worst possible time)

Can SQLite handle concurrent users? Real limits and patterns that help

SQLite has “one writer” semantics, but that slogan is too shallow to be useful.

What matters for a web app is the shape of your workload:

• Many concurrent readers: WAL handles this well.
• Many concurrent writers: you will see SQLITE_BUSY unless you plan for it.
• Long transactions: they amplify lock contention and make everything look worse.

Here are the patterns I rely on for embedded SQLite in web apps:

1. Set a busy timeout.

   • If you don’t, contention turns into immediate failures.
   • Pick something honest like 2,000–10,000ms depending on your P99 budget.

2. Prefer shorter transactions and avoid doing “business logic” while holding locks.

   • Reads inside a write transaction are fine, but don’t do external calls or slow work in there.

3. Use BEGIN IMMEDIATE for “I need to write and I’d rather fail fast than deadlock.”

   • It acquires the write lock early.

4. Shape writes.

   • If you’re doing bursty writes (webhooks, ingestion, background jobs), put them behind a queue and let a single worker serialize them.
   • This is the SQLite-friendly version of what you’d otherwise do with advisory locks in Postgres.

5. Keep WAL from growing forever.

   • Which brings us to checkpointing.

If you want to go deeper on concurrency limits and what “one writer” really means in API services, I covered that in SQLite Production API Concurrency: WAL Mode Limits [2026].

WAL checkpointing: the part you ignore until disk pages explode

WAL works by appending changes to app.db-wal. Checkpointing is the process of moving committed pages from the WAL file back into the main database file.

If checkpointing doesn’t happen often enough, you’ll eventually get:

• a huge -wal file
• slower startup and recovery behavior
• more disk usage than you planned for
• backups that are larger and slower

SQLite supports automatic checkpointing and app-initiated checkpointing. The WAL docs call this out explicitly under “Activating and configuring WAL mode” and the checkpointing section.

Here’s the pragmatic guidance I give teams:

• Use automatic checkpointing as a baseline.
• Add an app-initiated checkpoint in predictable places if you have a batchy workload.

Examples of predictable places:

• after a large background import
• after a migration
• before creating a snapshot-style backup artifact

A concrete operational number that’s easy to reason about: if your write workload is heavy enough that the WAL hits hundreds of MB regularly, you need to look at checkpoint strategy. For most small internal tools, WAL stays much smaller, but don’t assume. Measure the -wal size over a week.

If you’re deploying on platforms that restart instances often (containers, orchestrators), checkpoint behavior matters even more. A restart at the wrong time is a great way to discover you don’t actually understand your write path.

Related: If you’re shipping on a platform where automation can restart things without context, learn from the reliability folks. My mental model for this comes from building SOC 2 scaffolding tooling at Rise People. “Compliance baked into scaffolding beats compliance review at PR time” applies here too. Bake in database operational safety as defaults, not as tribal knowledge.

SQLite Online Backup API and when to use it (especially under WAL)

Most “backup SQLite” advice on the internet is some version of: stop the app, copy the file, start the app.

That can work. But it’s not what most web apps want.

The SQLite team built the Online Backup API specifically to safely back up a live database. The docs call out why naive file copying is dangerous:

• a naive external file copy can corrupt the backup if a power/OS failure occurs during copying
• it can block writers while a shared lock is held

The Online Backup API addresses that by allowing an incremental copy. The key property, straight from sqlite.org:

• the source database only needs to be locked briefly during reads
• when the backup sequence completes, the destination is a bit-wise identical snapshot of the database as it was when copying commenced

That “snapshot as-of start” semantics is exactly what you want when your app is receiving traffic.

So when should you use it?

• You want consistent backups without downtime.
• Your workload can’t tolerate blocking writers for the duration of a file copy.
• You want a clean story that doesn’t depend on filesystem-level tricks.

If your language runtime exposes this API (many do), it’s my default recommendation for “classic” backups.

If you don’t have easy access to the Online Backup API, SQLite also documents other techniques.

Other backup techniques (VACUUM INTO) and when a plain file copy is safe

SQLite’s own Backup API page lists “Other Backup Techniques,” including VACUUM INTO.

VACUUM INTO creates a copy of the database into another file, and because it’s vacuuming, it can produce a compacted artifact. That’s handy if you want:

• a backup artifact that’s smaller
• a way to defragment / compact at the same time
• a “golden” snapshot you can ship or export

Now: the controversial question.

Is it safe to copy a SQLite database file for backup?

Sometimes.

A plain file copy is safest when:

• the database is not being written to
• or you can guarantee a consistent snapshot at the filesystem layer

But the SQLite docs are very explicit: the historical approach (shared lock + external copy) has shortcomings, including writer blocking and possible corruption if the system fails during the copy.

If you want a simple rule you can put in a runbook:

• If the app is live and you care about correctness, don’t use cp app.db as your backup strategy.

If you still insist on filesystem-level copies, at least build your process around checkpointing and include all relevant files.

What files matter in WAL mode?

At minimum, you must treat app.db, app.db-wal, and app.db-shm as part of the database’s state.

If your backup process captures only app.db while there are committed transactions still in the WAL file, your restore may come back missing recent writes.

This is why “one file” is a packaging fantasy unless you’re disciplined about snapshotting.

File/database locking considerations during backup

Locking is the real reason online backups exist.

The Backup API doc calls out that the incremental approach avoids locking the source database for the entire duration, only briefly when it reads. That matters because:

• you can keep serving traffic
• writers don’t sit blocked for seconds/minutes
• your backup process is less likely to trigger timeouts and retries upstream

For teams that want a clean operational story, that is worth more than the tiny complexity of using a real backup mechanism.

If you liked this style of runbook thinking, my How to Back Up PostgreSQL With pgBackRest [S3 + Restore Test] post is the “big database” cousin of this guide. Different tools, same discipline.

Migrations for embedded SQLite: versioning, idempotency, and startup races

Most SQLite migration discussions are either:

• “just use your ORM migrations” (fine until you ship a binary)
• “just run a SQL file” (fine until you have multiple instances)

For embedded SQLite web apps, I recommend a boring, explicit playbook.

How should migrations be versioned?

Use PRAGMA user_version as your schema version number.

It’s built-in, it’s an integer, and it lives in the database header. No extra tables required.

A pattern that works well:

• Each migration increments user_version by 1.
• Migrations run in order.
• Each migration is idempotent.

Idempotent means: if you run it twice, the second run is a no-op. This matters more than people admit, because “it should only run once” is the first lie your deploy system tells you.

Concrete example checks (not code, just intent):

• before creating a table, check if it exists
• before adding a column, check if it exists
• when creating an index, use IF NOT EXISTS

How do you prevent two app instances from running migrations at the same time?

You have three realistic options:

1. Run migrations in a one-off job, not on app startup.

   • This is the cleanest pattern on Docker, VMs, and most PaaS.
   • Your app refuses to boot if schema version is behind.

2. Leader election / single migrator.

   • One instance becomes the “migrator” via an external lock (Redis, etcd, a cloud lock service).
   • This breaks the “one binary + one file” purity, but it’s operationally simple.

3. Use SQLite itself for migration locking.

   • Create a lock table and take a transaction that prevents concurrent migration runners.
   • Works on single-host deployments. Gets tricky if you’re already in distributed territory.

In small internal tools, option 1 is my default because it’s easy to reason about and easy to audit.

Transactional migrations and rollback strategy

Wrap each migration in a transaction where possible.

Also: migrations should be written so old code can tolerate the new schema during rolling deploys, or you’ll end up requiring downtime. This is the same expand/contract thinking we use in bigger databases. SQLite doesn’t exempt you from reality.

If your system is already complex enough to need careful deploy choreography, you may also be at the point where you should ask if a single-node SQLite is still the right choice. Sometimes it is. Sometimes it’s time for Postgres.

For more on deploy discipline, I often point people to How to Design Webhook Retries, Ordering, Idempotency [2026]. Different domain, same failure mode: “things run twice” is normal.

Deployment decision tree: single node, replicated reads, or distributed SQLite (LiteFS)

This is the section most competitors don’t write because it forces them to be opinionated.

I’m going to be opinionated.

The three deployment patterns that actually exist

The “don’t” option is common because it sounds like it preserves the “one database file” promise. It doesn’t. It trades a database server for a distributed filesystem problem.

LiteFS: distributed/replicated SQLite approaches and project status/cautions

LiteFS is a distributed filesystem that transparently replicates SQLite databases. Fly describes it as: you run your app like it’s using a local on-disk SQLite database, but it replicates to nodes in your cluster.

That’s compelling.

But Fly also puts a very 2026-relevant warning in bold:

• Do not combine LiteFS with Fly Machines autostop/autostart.
• A stale machine can win the lease and LiteFS may discard newer changes, risking rollback and data loss.

That warning is the whole story: distributed SQLite can be great, but you’ve now introduced cluster coordination and lease ownership as part of correctness.

LiteFS also states it’s stable in production environments but still pre-1.0, and APIs may change.

I’m not anti-LiteFS. I’m anti-accidental-distributed-systems.

Running database migrations in a replicated/primary setup

Fly’s LiteFS migration docs say it plainly: LiteFS is a single-writer system, so only the primary can write.

That changes migration strategy:

• migrations must run on the primary
• you need a plan for which node is primary during deploy
• migrations should be idempotent because they may run on candidate nodes
• replica nodes may receive schema updates before app deploy completes, so your app must tolerate that

In other words, you’re back to real production rollout discipline.

Backup approaches for replicated setups (periodic export + continuous replication)

If you’re running something like LiteFS, you need two layers:

• periodic exports (human-friendly restore points)
• continuous replication (tight RPO)

Fly’s docs discuss backing up LiteFS clusters via export and also mention continuous backup tools.

If you want “boring and reliable” continuous replication for single-node SQLite, Litestream is the common choice.

Litestream’s own description is exactly what you want to see:

• it continuously streams SQLite changes to object storage
• it can recover to your most recent replicated transaction
• it runs as a separate process with no code changes

This is the point where you should think in RPO/RTO, not vibes:

• If you replicate every N seconds, your worst-case data loss is roughly N seconds (plus storage consistency realities).
• Your restore time is: download + replay + boot + verify.

Side note: running this blog’s LLM pricing tracker taught me the same operational lesson in a different domain. Based on the live pricing data I maintain at /llm-prices, “simple comparisons” break the moment you don’t model workload shape and failure modes. Backups are the same. A backup that exists but isn’t verifiable is just a comforting story.

Backup/restore drill: what I’d actually put in your runbook

Backups that haven’t been restored are not backups. They’re hopes.

Here’s a drill you can run monthly in under 30 minutes.

1) Restore into a temp location

• Create a temp directory on a clean machine or ephemeral VM.
• Restore app.db (and WAL siblings if applicable).

2) Verify the database file is sane

Run:

• PRAGMA integrity_check;

You want the result ok.

If you’re using WAL mode and your process expects checkpointing, also verify you can open the DB with the same pragmas your app uses.

3) Verify schema version and migrations

• Read PRAGMA user_version;
• Confirm it matches what your current app expects.

If it doesn’t, you have a decision:

• either run migrations as part of restore
• or version your restore artifacts so you can restore + boot without running migrations under pressure

4) Smoke test the app

This is the part everyone skips.

• Boot the app pointed at the restored DB.
• Hit at least 3 endpoints: a read, a write, and whatever your “critical path” is.

If your app is an internal tool, your “critical path” might be “create record and export CSV.” Whatever it is, automate that.

5) Time it and write down RTO

If your restore drill takes 12 minutes, that’s your RTO for now. Don’t guess. Measure.

And if it takes 45 minutes because you’re copying giant artifacts around, you’ve learned something valuable before an incident forces you to learn it.

For people who like hardening checklists, you’ll probably also like How to Secure Docker Rootless Mode in Production [2026]. Different topic. Same principle: defaults and drills beat heroics.

Security note: a single portable DB file is also a single exfiltration target

A “one file” database is extremely easy to copy. That’s a feature until it isn’t.

At minimum:

• lock down file permissions (chmod 600 style discipline)
• store the DB on encrypted disk if the host is shared
• treat “downloaded database file” as sensitive data

If you’re worried about data leakage as an org-level practice, my LLM Data Leakage Playbook [2026]: Logging, Retention, Redaction has a useful mindset even outside AI.

Is SQLite good for web apps?

Yes. For a big chunk of web apps, SQLite is not a compromise. It’s a simplification.

But only if you accept the constraints:

• SQLite is fantastic when your system can tolerate a single-writer primary.
• WAL improves concurrency meaningfully, but it does not make SQLite “multi-node.”
• Backups and migrations are operational features. Treat them like product features.

If your plan depends on “we’ll just add replicas later” without deciding whether that’s LiteFS, a move to Postgres, or a queue-based architecture, you’re not planning. You’re deferring.

My prediction for 2026 and beyond: we’re going to see more “SQLite everywhere” apps in production, and the teams that win won’t be the ones with the cutest one-binary demo. They’ll be the ones who can pass a restore drill on a random Wednesday with no Slack drama.

Originally published on kunalganglani.com