Next.js SQLite SaaS Boilerplate (Prod WAL Guide)

Paying database rent before first revenue

The default SaaS database path: sign up for managed Postgres on day one, pay $25–30/mo through zero-revenue months, then discover connection-pool limits the first traffic spike. Nothing about that is necessary under ~100k users.

SQLite — the most deployed database on earth, running in every phone and browser — handles the whole shape on a single file, and the failure stories ("SQLite doesn't scale") are almost always misconfiguration: DELETE journal mode, ephemeral disk, zero backups, no WAL. Fix the setup and the engine disappears as a concern for years.

The setup: seven pragmas and a cron job

WAL mode first — everything else is commentary:

PRAGMA journal_mode = WAL;      -- readers never block writers
PRAGMA synchronous = NORMAL;    -- fsync-smart, crash-safe with WAL
PRAGMA foreign_keys = ON;       -- the default everyone assumes
PRAGMA busy_timeout = 5000;     -- wait, don't SQLITE_BUSY
PRAGMA cache_size = -64000;     -- 64MB page cache in RAM
PRAGMA temp_store = MEMORY;     -- sorts and joins in RAM
PRAGMA mmap_size = 30000000000; -- memory-map reads on 64-bit

Writes serialize through one connection with BEGIN IMMEDIATE (deadlocks become structurally impossible — retries, not lock graphs). Reads scale across threads against the NVMe file at ~0.1ms.

Then the part everyone skips: backups. Daily atomic snapshots, hourly log trimming and rolling retention, all built into BoringPush, plus bp db backup --offsite for a copy off the box.

The latency math that sells it:

The three indexes that carry 90% of SaaS queries

Fresh SQLite apps die on the same three missing indexes. Add them on day one:

CREATE INDEX IF NOT EXISTS idx_posts_created
  ON posts("createdAt" DESC);          -- every list page, newest first
CREATE INDEX IF NOT EXISTS idx_orders_user
  ON orders("userId", "createdAt" DESC); -- "my stuff" pages + history
CREATE INDEX IF NOT EXISTS idx_comments_post
  ON comments("postId");               -- threaded reads without scans

Then verify instead of assuming — EXPLAIN QUERY PLAN before and after, looking for SCAN turning into SEARCH:

sqlite3 db/app.db "EXPLAIN QUERY PLAN SELECT * FROM posts ORDER BY \"createdAt\" DESC LIMIT 20;"
# want: SEARCH posts USING INDEX idx_posts_created
# panic if: SCAN posts

Foreign keys get indexes automatically in most setups, but double-check join columns on your two hottest queries. And one WAL tuning note: the default checkpoint threshold (1000 pages) is fine until write volume grows teeth — when db-wal file size starts dwarfing db, run PRAGMA wal_checkpoint(TRUNCATE) hourly via the existing worker instead of letting it sprawl.

Ten minutes of index work buys years of headroom; skipping it buys the exact "SQLite doesn't scale" post-mortem everyone warns about.

What actually breaks at 10x (and the order it breaks in)

Founders ask what "outgrow one box" looks like in practice. Empirically, in this order: first, backup windows — snapshots take minutes instead of seconds past ~50GB, so move to incremental volume snapshots.

Second, write contention — busy_timeout errors appear in logs when sustained writes exceed what one writer thread drains; that's trigger one for Supabase. Third, analytics queries — full-table aggregations over millions of rows slow dashboards; offload them to the separate analytics store (BoringPush keeps telemetry in its own SQLite file precisely so product queries never queue behind reports).

Fourth, geography — single-box latency for far-away users, fixed with read replicas or the managed switch. Notice what's missing: read throughput.

At 0.1ms per query with page cache, reads stop being the bottleneck long before anything else matters — which is why the flip criteria are writes, backups, and geography, never "too many readers."

And the escape hatch that makes the bet safe: every entity in BoringPush behaves identically across SQLite, MongoDB, and Supabase. Outgrow one box and switch the database setting to Supabase with zero query rewrites — same routes, same guards.

Ship from anywhere on a Next.js stack where the database is a deployment detail, not a migration project.

FAQ

Is SQLite really production-safe?

With WAL + the pragmas above + real backups: yes, for single-box scale (far past most SaaS ever reach). Without them: no.

The engine was never the risk; the setup was.

What about concurrent writes?

Serialized via BEGIN IMMEDIATE — writers queue instead of deadlocking. Read-heavy SaaS (nearly all of them) never notices.

Write-heavy counters go to a guarded queue or graduate to Postgres.

better-sqlite3 segfaults on my VPS — help?

Classic Node 20 + Ubuntu 24.04 ABI mismatch. Upgrade to Node 22 LTS, npm rebuild better-sqlite3 --build-from-source, pm2 restart --update-env.

Five minutes, permanent.

How do backups restore?

Copy the snapshot file back and restart — single file, no dump/import dance. Test restores quarterly; an untested backup is a rumor.

When exactly should I flip to Supabase?

Two triggers, whichever first: sustained write contention (busy-timeout errors in logs) or needing Postgres-only features (row-level security policies, realtime). Until then the $0 bill compounds.