Blog
4 min read

Is SQLite Good Enough for Production? When It Works and When It Doesn't

SQLite now runs real production apps. When a single-file database is a great choice, the settings you must change (WAL mode, busy timeout, foreign keys), backups with Litestream, the single-writer limit, and the hosting setups where SQLite will lose your data.

SQLite used to be dismissed as a "toy" database for prototypes and mobile apps. That's changed: Rails 8 made SQLite a first-class production option, and many apps happily serve real traffic from a single SQLite file. It's also a trap in the wrong setup. Here's how to tell which situation you're in.

What makes SQLite different

PostgreSQL and MySQL are servers: a separate process your app connects to over a network. SQLite is a library: the database is a single file, and your app reads and writes it directly.

That means:

  • No database server to install, run, secure or connect to.
  • Very fast reads — no network round trip, no connection overhead. Queries run in microseconds. (What is latency?)
  • Trivial setup — the database is a file you can copy.

When SQLite works well in production

  • One server. Your app runs on a single machine with a persistent disk.
  • Read-heavy workloads — content sites, internal tools, dashboards, most small SaaS apps.
  • Moderate write volume — SQLite handles many writes per second, as long as each transaction is short.
  • Simplicity matters — small team, small app, minimal operations.

The limits

One writer at a time

SQLite allows many simultaneous readers but only one write transaction at a time. Writes queue up. With short transactions that's fine for a surprising amount of traffic; with long-running write transactions, or very write-heavy workloads, it becomes the bottleneck.

One machine

The file lives on one disk. You can't have three app servers writing to the same SQLite file over the network — never put SQLite on a network filesystem (NFS, SMB, many shared volumes); file locking there is unreliable and can corrupt the database. Scaling out to several app servers means moving to a client-server database, or using a SQLite-based distributed service.

Fewer features

No row-level security, fewer data types, fewer extensions, more limited ALTER TABLE. Most apps don't miss these; some do. (Postgres vs MySQL covers what Postgres adds.)

Where SQLite will lose your data

This is the important part. SQLite needs a persistent disk. Many hosting setups don't have one:

  • Serverless functions — each invocation may run on a fresh, temporary filesystem. (What is serverless?)
  • Containers without a volume — redeploying replaces the container and its filesystem. (Docker volumes vs bind mounts.)
  • Platforms with ephemeral disks — some PaaS hosts wipe the filesystem on every deploy or restart.

A common story: an AI-built app uses SQLite, works perfectly, gets deployed to a platform with an ephemeral filesystem — and every deploy silently resets the database to empty. If your app "forgets" data after deploys, check this first. (Why does my app work locally but not in production?)

Production settings you must change

SQLite's defaults favour compatibility, not server workloads. Set these when opening the connection:

PRAGMA journal_mode = WAL;      -- readers don't block the writer
PRAGMA synchronous = NORMAL;    -- safe with WAL, much faster
PRAGMA busy_timeout = 5000;     -- wait up to 5s for the write lock instead of failing
PRAGMA foreign_keys = ON;       -- OFF by default!
  • WAL mode is the big one: it lets reads continue while a write is happening.
  • busy_timeout prevents "database is locked" errors under concurrent writes.
  • foreign_keys = ON — SQLite doesn't enforce foreign keys unless you turn this on, per connection. (Primary key vs foreign key.)

Also keep write transactions short, and use BEGIN IMMEDIATE for transactions that will write, to avoid lock upgrade deadlocks.

Backups

Copying the file while the app is writing can produce a corrupt copy. Use:

  • sqlite3 app.db ".backup backup.db" or VACUUM INTO 'backup.db' for safe point-in-time copies,
  • Litestream, which continuously streams changes to object storage (S3-compatible) and can restore to a point in time — the popular choice for production SQLite.

Test restores, as with any backup. (Backups for beginners.)

SQLite or Postgres for a new app?

Choose SQLite when you have one server with a persistent disk, a read-heavy workload, and you value simplicity above all.

Choose Postgres when:

  • you'll run more than one app server, or might soon,
  • you're on serverless or ephemeral hosting,
  • you need many concurrent writers, row-level security, or extensions like pgvector,
  • several services or tools need to query the database.

If you're unsure, Postgres is the safer default — it never hits the walls above, and moving from SQLite to Postgres later is a real migration. But SQLite in the right setup is fast, cheap and robust.

The summary

  • SQLite is a file-based database library — fast, simple, no server to run.
  • It's production-worthy on one server with a persistent disk and short write transactions.
  • Never use it on serverless, ephemeral containers, or network filesystems.
  • Enable WAL, set a busy timeout, turn on foreign keys, and back up with .backup or Litestream.

EasySpawn servers have persistent disks, so SQLite files survive restarts and redeploys — and when you outgrow it, PostgreSQL is already provisioned on the same server. See how it works or join the waitlist.

Related: Which Database Should an AI-Built App Use? · What Is a Database? · SQL vs NoSQL · Database Transactions Explained

Keep reading