Tool brief

SQLite in Production: When It Fits

Learn where SQLite works in production, how application files and SQLite browsers differ, and when write concurrency should trigger a server database.

Editorial update

Decision in brief

Choose SQLite when the application can own a database file on the same host and can keep writes short enough for the tested concurrency. It is a strong fit for local-first apps, small same-host services and portable application data. Use a server database when multiple hosts need to write shared data directly or contention already violates the application’s latency budget.

Test SQLite fit: One host. Short writes. Test contention.
Check who opens the file and what happens to tail latency under representative writes.

A database library embedded in your process

SQLite stores tables and indexes in a file and runs queries in the application process. A SQLite browser or studio is a GUI for inspecting the schema and data. Give it a disposable copy or open read-only where supported. When write-ahead logging is active, use the SQLite backup API or compatible tooling to take a consistent snapshot rather than copying a live database file alone.

WAL lets readers and one writer operate concurrently in many cases. Writes still serialize, and WAL requires the participating processes to be on the same host. Design transactions around application invariants and measure hold times.

WorkloadUse SQLite whenRevisit if
Desktop, mobile or offline-first applicationEach device owns its own file and any sync is implemented explicitlyFile copying is being used to reconcile independent writers
Small internal web appMost transactions are short and the service owns local storagePeak writes cause unacceptable lock waits or the service needs multiple writable hosts
Portable report or analysis artifactThe database is a convenient read-mostly formatBulk analytics require a different scan or storage profile
Admin inspection of live dataUse a backup or authorized read-only snapshotThe GUI can write or a live file is being copied without its WAL state

A portable file and a stressed writer

bashtian introduced Capsule on HN to package web app code and its SQLite-backed data into one file. That is a concrete local-ownership pattern. An offline trial should still establish how the data is recovered if the vendor application cannot open the file.

In the discussion of an unusually high SQLite throughput result, rich_sasha described diagnosing lock contention despite using WAL. That experience is a reason to test the slowest write transaction together with ordinary reads instead of sizing from a headline throughput number.

Run a backup and concurrency drill on a copy

Pin the SQLite library version the app will ship, and choose a fixture with representative data and a realistic burst of writers. Specify a p95 target and an acceptable busy or retry rate.

  1. Build a fixture on the intended filesystem. Open a snapshot using your GUI and test exports without granting it write access to the staging database.
  2. Replay reads and writes during a burst and hold one typical transaction open for longer than usual. Record busy errors, retry latency and WAL growth.
  3. Take a backup through a database-aware interface, restore it to a clean location, and check the schema, counts and important application queries.
  4. Keep SQLite if contention and recoverability meet the agreed limits. Stop if normal peak traffic repeatedly times out or operations require copying a live database across hosts.

Related articles