SQLite should have (Rust-style) editions

Original Article ↗ Hacker News Discussion ↗

SQLite’s long-standing commitment to backward compatibility is colliding with modern expectations for safer defaults, prompting calls for a Rust-style “edition” mechanism that opts new databases into stricter behavior without breaking old ones. Commenters debate whether a single `PRAGMA edition=2026`-style switch—for things like enforcing foreign keys, strict typing, busy timeouts, and WAL mode—would meaningfully improve reliability or just obscure important configuration trade-offs, especially given SQLite’s role as a portable on-disk format. Others argue that wrapper libraries, compile-time flags, or even alternative embedded databases (like DuckDB or Firebird) may be better ways to get stronger guarantees while preserving SQLite’s minimalism and tiny footprint.

Editions concept and goals

  • Many commenters like the idea of an “edition” or “compatibility level” pragma that bundles a set of modern defaults (e.g., stricter typing, FK enforcement, busy timeout, WAL) without breaking old code.
  • Editions are seen as an opt‑in way to move beyond 20‑year‑old defaults while preserving SQLite’s backward‑compatibility promise.
  • Comparisons are drawn to Rust editions, C++ “epochs/profiles”, CMake policies, Postfix compatibility_level, and JS use strict.

Backwards compatibility & portability

  • SQLite is a data container frequently moved between machines and tools; older /usr/bin/sqlite3 binaries often read files created by newer embedded versions.
  • Critics worry editions could:
    • Require understanding both underlying pragmas and edition mappings.
    • Lock out intermediate SQLite versions if edition N implies features added later.
    • Complicate handling existing data when stricter semantics are enabled.
  • Supporters respond that these are largely the same issues as with individual pragmas today and propose:
    • Making editions purely connection‑level macros (not stored in the file).
    • Keeping strict one‑way rules and self‑describing metadata.

Defaults: FKs, typing, busy timeout, WAL

  • Many agree current defaults (no FK enforcement, loose typing, no busy timeout, rollback journal) are poor for typical “app database” use and routinely get overridden.
  • Others defend the status quo:
    • Embedded/low‑resource environments may prefer minimalism and loose typing.
    • busy_timeout is inherently application‑specific; choosing 5s vs 1s vs 60s is arbitrary.
    • WAL is a different format, not universally supported, and seen as riskier for corruption.

Typing, STRICT tables, and data quality

  • Several commenters find SQLite’s type system “dangerous” and would prefer strict typing as a default.
  • Others argue loose typing is invaluable for ingesting messy CSV/legacy data where invalid dates, numbers, etc. are common.
  • STRICT tables are viewed as a useful step but not a complete validation solution; serious validation often still belongs at a higher layer.

Scope of SQLite & alternatives

  • Strong pushback against turning SQLite into a full client‑server, highly concurrent system; some say “use Postgres” or other embedded engines (DuckDB, Firebird) if you need that.
  • Counterpoint: even a local app database should have safer defaults; editions or feature‑sets could provide this without changing legacy behavior.

Implementation & ecosystem concerns

  • Worries include code bloat for each added edition, testing surface growth, and potential confusion (“edition 2026” hides what’s actually enabled).
  • Mitigations suggested: implement editions as tiny macros over existing pragmas; possibly allow only a limited set, or feature‑sets instead of year‑based editions.
  • Some ORM and wrapper ecosystems lag on newer SQLite features (e.g., STRICT), reducing practical benefit until tooling catches up.