Learn

SQLite for Local Apps

SQLite is a strong default for local app data because it gives you transactions, constraints, indexes, and a portable file without running a database server.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE transactions (
  id TEXT PRIMARY KEY,
  occurred_at TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  currency TEXT NOT NULL,
  merchant TEXT,
  note TEXT
);

CREATE INDEX idx_transactions_occurred_at
  ON transactions (occurred_at DESC);

Use SQLite For

  • app-owned relational data
  • local search indexes with FTS5
  • durable queues
  • small-to-medium desktop or mobile stores
  • single-file app documents and exports
  • derived indexes over Markdown, JSONL, or attachments

Avoid SQLite When

  • many processes need high write concurrency
  • the data is tiny settings that belong in a secure/key-value store
  • the browser is the only runtime and IndexedDB is the portable option
  • you need untrusted users to upload arbitrary SQLite files without sandboxing and validation

WAL Basics

WAL writes new changes to database.db-wal before checkpointing them into database.db. Readers can continue while a writer appends to WAL.

database.db
database.db-wal
database.db-shm

Gotcha: A live WAL-mode database may have committed data in the -wal file. Do not copy only database.db and call it a backup.

Integrity Gates

Run integrity checks in backup/restore paths and diagnostic tooling:

PRAGMA quick_check;
PRAGMA integrity_check;
PRAGMA foreign_key_check;

Use quick_check for fast smoke checks and integrity_check for deeper validation before declaring a restore successful.

SQLite As App File Format

SQLite can be the export format itself when relational structure matters. This works well for app documents, local analytics packages, or portable knowledge bases.

If users can import SQLite files from outside the app, treat them as untrusted input. Open read-only where possible, validate schema and PRAGMA integrity_check, and import into a new clean database rather than attaching unknown files to the live store.