LLM Best Practices

SQLite Best Practices

4 min read

View markdown source

Overview

SQLite 3.46 is the right database for single-writer workloads: edge and local-first apps, mobile, CI fixtures, embedded readers, and any service whose data fits on one disk. Pick postgres when you need concurrent writers. This page covers when to choose SQLite, the pragmas to set, backup options, and driver picks.

Choose SQLite for single-writer workloads

SQLite handles one writer at a time across the whole database, with unlimited concurrent readers. Pick it when writes are serializable to a single process.

  • Local-first and edge apps. Cloudflare Durable Objects, Turso, Fly.io regional replicas.
  • Mobile. iOS via Core Data (which sits on SQLite) or GRDB. Android via Room.
  • CI fixtures. A fresh :memory: database per test is faster than spinning up Postgres.
  • Embedded readers. Ship a prebuilt file with your app and read it without a server.
  • Desktop apps and CLI tools that need durable local state.

Pick postgres when more than one process writes concurrently, or when you want managed replication and failover.

Enable WAL mode and set synchronous=NORMAL

The default journal mode is conservative. WAL gives you concurrent reads alongside the single writer.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;   -- 64 MB cache
PRAGMA temp_store = MEMORY;
  • WAL persists across connections; set it once and the file remembers.
  • synchronous=NORMAL is safe with WAL and noticeably faster than FULL. Use FULL only when a power loss between commit and fsync is intolerable.
  • foreign_keys=ON must be set per connection. It is not persistent.
  • busy_timeout lets writers wait briefly when the database is locked instead of failing immediately.

Treat the writer as a serial queue

SQLite serializes writes. Concurrency is reader-side.

  • Many readers, one writer is the sweet spot. Reads do not block reads. Reads do not block the writer in WAL mode.
  • If you have multiple writer threads, funnel them through a single connection or queue.
  • Wrap related writes in BEGIN IMMEDIATE to take the write lock up front and avoid retries.
  • For batched inserts, prepare the statement once and run it in a single transaction. Expect 10x to 100x over auto-commit.

Back up with .backup or VACUUM INTO

Copying the file with cp while a process has it open will corrupt the backup. Use the online tools.

sqlite3 app.db ".backup '/backups/app-$(date +%F).db'"
sqlite3 app.db "VACUUM INTO '/backups/app-$(date +%F).db';"
  • Both take an internal lock and produce a consistent snapshot.
  • VACUUM INTO also rebuilds the file, dropping fragmentation. Run it weekly on write-heavy databases.
  • For continuous backup, use Litestream to ship WAL frames to S3-compatible storage.

Never put a SQLite file on a network filesystem

NFS, SMB, and FUSE-mounted object stores break SQLite’s locking guarantees. The database will corrupt under concurrent access; corruption is often silent until a later read.

  • Keep the file on a local disk attached to the process that opens it.
  • If you need shared access across machines, you do not need SQLite. Use postgres or a SQLite-as-a-service provider (Turso, Cloudflare D1) that handles replication for you.
  • For read-only fanout, copy the file to each reader’s local disk.

Pick the driver that matches the runtime

Native bindings beat WASM and FFI on hot paths.

  • Node: better-sqlite3 for synchronous, in-process access. Fast, simple API, blocks the event loop on long queries (usually fine).
  • Bun: bun:sqlite is built in. Same shape as better-sqlite3. No install step.
  • Distributed and edge: libsql (the Turso fork) adds replication and HTTP access while staying SQLite-compatible.
  • Python: the stdlib sqlite3 module for scripts; aiosqlite in async code.
  • Swift: GRDB.swift, or Core Data for Apple’s higher-level wrapper. See core-data.

prisma supports SQLite, with the caveat that migrations are more limited than on Postgres.

Graph View

Backlinks