SQLite in production

Why SQLite

Your app probably does not have the traffic that justifies a Postgres server. SQLite is a single file on disk, needs no service, no port, no credentials, and handles concurrent reads perfectly. With WAL mode it handles a single writer without blocking readers. For a solo product, that is almost always enough.

1. WAL mode + basic hygiene

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

WAL means readers don’t block the writer and the writer doesn’t block readers. The database is a .db file plus a -wal and a -shm sidecar.

2. The D1 adapter pattern

This site runs the exact same worker code on the VPS and on Cloudflare. The trick is a tiny adapter that presents a D1-shaped interface over better-sqlite3:

// server/d1-adapter.ts
import Database from 'better-sqlite3';

export function makeD1Compatible(dbPath: string) {
  const db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  return {
    prepare: (sql: string) => ({
      bind: (...values: unknown[]) => ({
        all: () => db.prepare(sql).all(...values),
        run: () => db.prepare(sql).run(...values),
        get: () => db.prepare(sql).get(...values),
      }),
    }),
    exec: (sql: string) => db.exec(sql),
  };
}

Your app talks to “D1”. In dev it is wrangler’s local D1; in prod on the VPS it is this adapter over a file. Same code, two runtimes.

3. Backups

Before any risky change (migration, schema edit, agent rewrite):

# safe online backup to a new file
sqlite3 /home/ubuntu/hackup-data/hackup.db ".backup '/home/ubuntu/hackup-data/backup-$(date +%F).db'"

Schedule it in cron weekly and keep a couple of generations.

4. When to outgrow SQLite

Move off SQLite only when you have: multiple writers hitting the same rows hard, more than a few hundred writes/sec sustained, or you need the DB on a separate machine. For a solo product in 2026, you almost never do.