Skip to content

0001. PostgreSQL and SQLite are both first-class stores

  • Date: 2026-07-13
  • Status: accepted

Context

Most households want a single container with a file-backed database and nothing else to run. Some already operate PostgreSQL and want Loomarr on it, or may later want more than one instance.

Decision

Support both behind one Store interface, with one shared database/sql code path: modernc.org/sqlite (pure Go, so the image stays cgo-free) and pgx through its stdlib shim. Dialect differences are confined to per-dialect goose migrations and the ClaimDue* queries. One conformance suite runs against both.

Consequences

  • Jobs could not use a Redis- or PostgreSQL-only queue; the job engine had to support SQLite.
  • SQLite means exactly one instance. PostgreSQL permits replicas only with row claims (FOR UPDATE SKIP LOCKED) and the channel revision checks in storage.
  • Timestamps are stored as epoch integers to keep the schema dialect-neutral.
  • An install can move from SQLite to PostgreSQL in the app; the reverse is a restore.