The Practical Developer

SQLite As Your Application Database In 2024: When It's The Right Call

June 21, 2024 by The Practical Developer

Most teams reach for Postgres because "SQLite is for embedded use." That assumption is years out of date. SQLite with WAL mode and Litestream replication runs real production workloads at 50,000 writes per second. Here is when it's the right tool, the patterns that work, and the limits to know.

database

sqlite

reliability

A circuit board close-up — the right metaphor for the dense, focused machinery of SQLite

The team is provisioning a Postgres for a side project. Setup, monitoring, backups, connection pool tuning — about a day of work. Somebody asks “do we need Postgres?” The honest answer for a low-traffic SaaS is no. SQLite would handle it, with simpler ops, lower cost, faster reads, and zero network round-trips.

SQLite has a reputation as “the embedded DB on your phone,” and people miss that the same engine is genuinely production-grade for many web workloads. With WAL mode, Litestream for backups, and a single-process server, SQLite hits 50,000+ write/sec on a single laptop and is dramatically simpler to operate than Postgres.

This post is when SQLite is the right call, the patterns that work, and the realistic limits.

When SQLite is the right answer

Three conditions:

1. The application has one writer process. SQLite supports concurrent reads but serializes writes. If your app is one Node.js / Go / Python process (or one process with worker threads), this is fine. If you want to scale horizontally to many writer instances, SQLite is the wrong tool.

2. Database fits on local disk. SQLite is a file. Most modern hardware has terabytes of fast local SSD. 100 GB of data is no problem.

3. Read-heavy or moderate write rate. SQLite reads are the fastest of any database — no network round trip, no serialization. Writes are bound by fsync. With WAL and synchronous = NORMAL, you can do tens of thousands per second.

The kinds of apps that match: side projects, internal tools, single-region SaaS up to ~100k users, edge / IoT applications, CLI tools that need to persist state.

What SQLite is NOT good for

  • Multi-writer apps. Your app-1, app-2, app-3 instances all writing to the same DB — possible (with file locking) but slow. Not the right tool.
  • High write contention. Hundreds of concurrent writers on the same table. SQLite serializes at the database level.
  • Large vertical-scale needs. SQLite has tuning knobs but is limited compared to Postgres’ richer query optimizer.
  • Specific Postgres features. Range types, full-text search with ranking, row-level security, MVCC isolation. SQLite has its own equivalents for some, but not all.

For these, use Postgres or another full-featured RDBMS.

The setup that makes SQLite production-grade

Three things to enable:

1. WAL mode. Default journal mode is “rollback” which serializes everything. WAL allows concurrent readers and one writer.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

synchronous = NORMAL is safe with WAL; it relaxes fsync after every write. Combined, this is often 10× faster than the default config.

2. Reasonable cache and timeout.

PRAGMA cache_size = -64000;     -- 64 MB cache
PRAGMA busy_timeout = 5000;     -- 5 seconds before SQLITE_BUSY
PRAGMA foreign_keys = ON;
  1. Backups via Litestream.

Litestream replicates every WAL frame to S3 (or compatible storage) in near real time. Set up once:

# /etc/litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: app-backups
        path: prod
        region: us-east-1

Run as a sidecar / systemd service:

litestream replicate -config /etc/litestream.yml

Restoring a database in disaster recovery:

litestream restore -o /data/app.db s3://app-backups/prod

Litestream gives you point-in-time recovery and continuous backup with seconds of RPO. Same operational guarantees as a properly-configured Postgres, much simpler to set up.

Better-sqlite3 in Node.js

The Node ecosystem has two SQLite drivers. better-sqlite3 is the right one — synchronous, fast, simple API.

import Database from 'better-sqlite3';
const db = new Database('app.db');
db.pragma('journal_mode = WAL');

const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
const select = db.prepare('SELECT * FROM users WHERE id = ?');

insert.run('Alice', '[email protected]');
const user = select.get(42);

Yes, it’s synchronous — but SQLite is fast enough that this is fine, and it removes the async-callback noise. For Node-based apps, the sync model is actually a feature.

For prepared statements with multiple inserts, use a transaction:

const insertMany = db.transaction((users) => {
  for (const u of users) insert.run(u.name, u.email);
});
insertMany(largeArrayOfUsers);

Inside a transaction, SQLite batches the writes and runs orders of magnitude faster.

Concurrency model

SQLite’s reader-writer concurrency:

  • Many readers can read simultaneously.
  • One writer at a time.
  • Readers do not block writers; writers do not block readers (in WAL mode).

For a typical Node.js app on one process, this maps cleanly. For Bun or other multi-threaded runtimes, the writer is a single connection — multiple worker threads enqueue writes through it.

For Python with multi-process workers (gunicorn), be careful: each worker has its own connection, and concurrent writes from different processes will retry on SQLITE_BUSY and serialize. Tune busy_timeout and consider running through a single writer goroutine / worker.

Performance numbers

Real-world numbers from a 2023 m1 MacBook Pro:

  • Reads: 200,000+ point reads/sec with cache hot. Single-digit microseconds per read.
  • Writes: 30-60k writes/sec in batched transactions. ~5-10k for single-row writes.
  • Disk size: Postgres typically uses 1.5-2× the data size due to MVCC overhead. SQLite is closer to 1.1×.

For most web workloads (a few thousand RPS, mostly reads, mostly cached), SQLite is genuinely faster than the network-attached Postgres alternative.

The Fly.io / single-region pattern

Fly.io and other “deploy code close to users” platforms have made SQLite practical for multi-region deployments via LiteFS. One primary writer, many read replicas distributed globally. Writes go to the primary; reads are local.

For a SaaS that mostly serves reads from a global user base, this gives Postgres-like multi-region semantics with a much simpler operational model.

Migrations

SQLite has limited DDL support compared to Postgres. Some operations require recreating the table:

  • DROP COLUMN — supported since SQLite 3.35 (2021).
  • RENAME COLUMN — supported since 3.25.
  • Changing a column’s type or default — requires a table recreation.

For complex schema changes, the pattern is:

BEGIN;
CREATE TABLE users_new (...);  -- new schema
INSERT INTO users_new SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;

Most ORMs (Drizzle, Prisma) handle this automatically. For raw SQL, write a migration helper.

Common gotchas

Type system is loose. SQLite stores any value in any column (“type affinity” instead of strict types). A TEXT column can hold integers. Use a strict ORM or runtime validation if this matters.

No native UUID type. Store as BLOB (16 bytes) or TEXT. Integer primary keys are still the fast default.

No JSONB. SQLite has JSON1 extension with json_extract etc. Slower than Postgres JSONB but functional.

Single-file is single-point-of-disk-failure. Mitigate with Litestream replication.

Locking on writes during long reads. A long-running read transaction can stall write commits in some configurations. Keep transactions short.

When to migrate to Postgres

A few real signals:

  1. You need multi-writer. Sharding workers, multiple servers writing same DB.
  2. You hit SQLite-specific limits. Concurrent contention, missing features.
  3. The team is large enough that Postgres-specific tooling is widely known and expected.

The migration from SQLite to Postgres is straightforward — both are SQL, both have similar feature sets at the application layer. Plan for a few days of work; switch ORMs over if needed; verify queries.

Don’t migrate prematurely. Many teams migrate to Postgres at the first sign of growth and end up running a 50 GB Postgres database with 10k DAU — SQLite would have been fine for years.

The takeaway

SQLite in 2024 is genuinely production-ready for a wide class of web applications. WAL mode, sensible pragmas, Litestream backups — set up in 30 minutes. The reads are dramatically faster than network DB calls. The operational surface is “a file on disk.”

The next time someone says “we need a database for this side project,” ask whether SQLite would fit. For most projects under ~100k users, the answer is yes — and the simpler ops are a real ongoing benefit.

A note from Yojji

The kind of architectural judgment that picks the right-sized database for a workload — SQLite when it fits, Postgres when it doesn’t — is the kind of senior backend skill Yojji’s teams bring to client work.

Yojji is an international custom software development company founded in 2016, with teams across Europe, the US, and the UK. They specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, GCP), and full-cycle product engineering — including the database choices that decide whether a system stays simple or accumulates infrastructure for its own sake.