# Next.js SQLite SaaS Boilerplate (Prod WAL Guide)

> Run your whole SaaS on SQLite in WAL mode: pragmas, backups, latency math, and the one-var escape hatch. Starter kit inside.

Source: https://boringpush.com/blog/nextjs-sqlite-saas-boilerplate

## 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:

```sql
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:

| Shape | Cloud Postgres hop | Local SQLite |
|---|---|---|
| 1 query | 35–100ms network | 0.1ms disk/RAM |
| 3 queries per page | 105–300ms pure wait | 0.3ms total |
| Monthly bill | $25–30+ | $0 (rides the $5 VPS) |
| Connection limits | Pool exhaustion at spikes | File descriptor, no pool |

## The three indexes that carry 90% of SaaS queries

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

```sql
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`:

```bash
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.