SQLite's unsafe defaults, and the case for Rust-style editions

SQLite is arguably the default choice for local data storage — an RDBMS shipped as a library rather than a separate process — but its out-of-the-box behavior undermines the data integrity most developers assume they’re getting. Two defaults draw the sharpest criticism. First, foreign key constraints are ignored unless you explicitly turn them on with PRAGMA foreign_keys = ON. Combined with SQLite’s habit of recycling ROWID values, this means a deleted user’s ID can be handed to a new account, silently transferring the old user’s records to the new one with no error at insert or query time. Second, column types are advisory: an INTEGER column will happily accept and store arbitrary text through SQLite’s ‘affinity’ rules, so a bug that writes the strings ‘1’ and ‘0’ into a boolean column produces no complaint and a painful debugging session later.

SQLite does offer fixes, but they’re opt-in and awkward. STRICT tables enforce declared types (and support an explicit ANY type for genuinely dynamic columns), yet there’s no global pragma to make every table strict — you must tag each one by hand. Strict mode also changes how type names are parsed, which breaks a useful trick where custom type labels like DATETIME or COLOR let a wrapper auto-serialize columns, giving longtime users a real reason to resist flipping the switch.

That tension is the author’s central point. The safer choices can’t simply become the new defaults without breaking existing databases and tooling, so the proposal is to borrow Rust’s ‘editions’ model: let a database or connection declare which edition of SQLite’s semantics it wants, so new projects get sane defaults (enforced foreign keys, strict typing) while old ones keep working unchanged. It’s a design-culture argument about how a mature, backward-compatibility-obsessed project can still evolve away from decisions it now regrets.