Why SQLite Deserves a Place in Your Backend Stack

Every backend architecture review I've sat in for the last decade has started with the same assumption: you need a client-server database. Postgres or MySQL, a connection pool, a managed instance, maybe a read replica later. SQLite gets mentioned once, usually to be dismissed as "fine for a mobile app, not for production." That assumption is outdated. For a large share of real applications — read-heavy APIs, internal tools, single-tenant SaaS, edge-deployed services — a single SQLite file with the right configuration outperforms a managed Postgres instance on latency, cost, and operational simplicity.

This isn't a claim that SQLite replaces Postgres everywhere. It's a claim that most teams reach for a networked database by default without asking whether the workload actually needs one.

The concurrency model people get wrong

The standard objection is "SQLite can't handle concurrent writes." That's true in the default rollback-journal mode, where a writer locks the entire database file. It's mostly false once you enable Write-Ahead Logging, described in detail in the SQLite WAL documentation. In WAL mode, readers don't block writers and writers don't block readers — only writer-vs-writer contention remains, which for most applications is rare enough that a short busy timeout handles it cleanly.

-- ❌ default mode: readers and writers block each other
-- (no pragma set, SQLite uses rollback journal)
 
-- ✅ production configuration for a single-writer web service
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -64000; -- 64MB page cache

synchronous = NORMAL is the detail people skip and then regret in the other direction — FULL is safer against power loss but adds an fsync on every transaction commit, which is the single biggest latency cost in SQLite. NORMAL combined with WAL still guarantees durability against application crashes; it only trades off the (rare, and usually irrelevant for a VM-backed deployment) OS-crash-at-exactly-the-wrong-moment scenario.

Connection handling: one writer, not a pool

The mental model shift that trips up engineers coming from Postgres is connection pooling. There's no network round trip to amortize, so pooling a dozen connections to the same file buys you nothing — it mostly adds lock contention.

// ❌ treating SQLite like a networked database
import Database from "better-sqlite3";
 
const pool = Array.from({ length: 10 }, () => new Database("app.db"));
function getConnection() {
  return pool[Math.floor(Math.random() * pool.length)];
}
 
// ✅ one long-lived connection per process, WAL enabled
import Database from "better-sqlite3";
 
const db = new Database("app.db");
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
 
export function getOrder(id: string) {
  const stmt = db.prepare("SELECT * FROM orders WHERE id = ?");
  return stmt.get(id);
}
 
export function createOrder(order: { id: string; total: number }) {
  const stmt = db.prepare(
    "INSERT INTO orders (id, total) VALUES (@id, @total)"
  );
  stmt.run(order);
}

better-sqlite3 is synchronous by design, which sounds alarming until you realize SQLite reads and local-disk writes are faster than the event-loop overhead of an async driver for most queries — there's no I/O wait to yield on. Prepared statements are cached automatically, and because there's a single connection, there's no pool exhaustion failure mode to monitor for.

Durability without a second server

The operational argument against SQLite has always been backups and high availability — a single file is a single point of failure. Litestream addresses this by streaming the WAL file continuously to S3-compatible object storage, giving you point-in-time recovery without running a replica database.

# litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: app-backups
        path: db
        region: us-east-1
        sync-interval: 1s
        retention: 72h

Restoring is a single command on a fresh instance, which makes "the disk died" a non-event instead of an incident:

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

This pattern — one writer, continuous WAL streaming, cheap object storage for retention — gets you most of what a managed Postgres instance with automated backups provides, at a fraction of the cost and with recovery times measured in seconds for databases under a few gigabytes.

Where it genuinely falls over

Being honest about the ceiling matters more than selling the pattern. SQLite is the wrong choice when:

That single-writer constraint is the real ceiling, not file size. SQLite's own documentation on appropriate uses for SQLite is unusually candid about this — it's written by the project maintainers, not a vendor with something to sell you, which makes it worth reading before you commit to the pattern.

Edge deployment changes the calculus further

The pattern that makes SQLite genuinely compelling for 2026-era architectures is edge replication. Services like Turso (built on libsql, a fork of SQLite) and Cloudflare D1 replicate a SQLite database to dozens of regions, giving you single-digit-millisecond reads from wherever the request originates — something a centralized Postgres instance simply cannot do without a dedicated caching layer in front of it.

// edge read replica, local to the region handling the request
import { createClient } from "@libsql/client";
 
const client = createClient({
  url: "libsql://app-prod-myorg.turso.io",
  authToken: process.env.TURSO_AUTH_TOKEN,
});
 
async function getProductCatalog(region: string) {
  const result = await client.execute({
    sql: "SELECT * FROM products WHERE region = ?",
    args: [region],
  });
  return result.rows;
}

Writes still funnel to a primary, but reads — which dominate most product catalogs, content APIs, and configuration services — happen locally. This is the same read-replica pattern teams build manually with Postgres, except the replication and routing are handled by the platform instead of by your infrastructure team.

Key Takeaways

1. Enable WAL mode and set synchronous = NORMAL before judging SQLite's write performance — the defaults are tuned for safety over throughput.
2. Use a single long-lived connection per process instead of a connection pool; pooling a local file database adds contention without benefit.
3. Litestream (or equivalent WAL-streaming backup) gives you durability and point-in-time recovery without running a second database server.
4. The real ceiling is concurrent multi-process writes, not dataset size — route writes through one process if you need horizontal write scale.
5. Edge SQLite platforms (Turso, Cloudflare D1) solve read latency at the network level in a way centralized Postgres can't match without extra caching infrastructure.
6. Default to a networked database when you need row-level locking, heavy analytics, or true multi-writer concurrency — but don't default to one out of habit when a single file would do.