Prefer strict tables in SQLite

Original Article ↗ Hacker News Discussion ↗

SQLite’s new STRICT tables option has reignited debate over the database’s long‑standing choice to allow flexible, largely unenforced column types. Supporters of STRICT argue that enforcing types by default would prevent subtle data corruption, make schemas a more reliable contract, and align SQLite with other relational databases, while critics counter that dynamic typing and permissive defaults are essential for embedded, single‑app use cases and backward compatibility. The conversation also touches on related footguns—like foreign keys and WAL mode being opt‑in, and the lack of native date/boolean types—and on tooling and workarounds for developers who want stronger guarantees.

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.