Prefer strict tables in SQLite

Original Article ↗ Hacker News Discussion ↗

Strict Tables vs Default Behavior

  • Many argue STRICT tables should be the default, or even the only mode in future versions.
  • Others counter that STRICT is just one preference; SQLite’s current behavior is intentional and fits its niche.
  • Some suggest a global pragma or “default sets”/versioned defaults (e.g., “use 2026.1 defaults”) to enable strictness and other safer options without breaking old code.

SQLite’s Typing Philosophy & History

  • SQLite began in a “everything is a string/number” world (Tcl, dbm-style storage), and its dynamic typing largely persisted into SQLite 3.
  • Project docs explicitly defend flexible typing; critics in the thread often find these justifications unconvincing or post‑hoc.
  • Supporters emphasize SQLite as “competition for fopen,” not Oracle/Postgres, and see strict runtime checks as optional overhead when you can prove correctness in application code.

Practical Concerns: Types, Dates, Booleans

  • Common complaints:
    • Column types not enforced by default.
    • Lack of native DATE/TIMESTAMP/BOOL types, especially awkward in STRICT mode.
    • Surprises like integers columns accepting arbitrary text, and NUL bytes in strings affecting functions like length().
  • Workarounds: store dates as text or Unix timestamps; booleans as integers or bitfields; enforce via CHECK constraints.

Backwards Compatibility & Defaults

  • SQLite very rarely changes defaults (e.g., foreign keys off by default, WAL off by default) to avoid breaking existing software.
  • Some see this as responsible; others see it as forcing new users to learn and disable “footguns” one by one.
  • There is debate over whether surprising type acceptance is a worse failure mode than breaking older code during upgrades.

Migrations, Tooling, and Modes

  • Schema migrations in SQLite are seen as cumbersome; third‑party tools and patterns try to simplify this.
  • STRICT tables can’t be toggled via simple ALTER; typical approach is recreate-and-copy, though some tools automate this.
  • Opinions diverge on whether strict mode improves higher‑level language integrations (e.g., Rust/Go ORMs) or complicates type mapping.