SQLite should adopt Rust-style versioned editions
Original: SQLite should have (Rust-style) editions
Why This Matters
Highlights how default behaviors in widely deployed infrastructure databases can silently cause data integrity failures at scale.
A developer argues that SQLite's problematic defaults—such as disabled foreign key enforcement and loose type checking—should be fixed via opt-in 'editions' modeled after Rust's versioning system, preserving backward compatibility while enabling correct behavior for new projects.
SQLite is widely regarded as the industry standard for local data storage, used in embedded systems and even some server applications such as lobste.rs. However, the author identifies two critical default behaviors that undermine database integrity.
First, foreign key constraints are disabled by default in SQLite. Unlike PostgreSQL, MySQL, and other RDBMSes, SQLite silently ignores FOREIGN KEY declarations unless the user explicitly runs 'PRAGMA foreign_keys = ON'. This can lead to dangling references and, due to SQLite's ROWID reuse algorithm, can result in a foreign key silently pointing to a completely different row—causing data corruption without any error.
Second, SQLite's type system is permissive by design. A column declared as INTEGER does not restrict values to integers; SQLite uses 'type affinity' rather than strict type enforcement. An optional STRICT mode exists but is not enabled by default.
The author proposes that SQLite adopt a Rust-style 'editions' mechanism—a per-database opt-in flag (e.g., 'PRAGMA edition = 2') that enables stricter, more correct defaults for new databases, while leaving all existing databases unaffected. This would allow the SQLite team to fix longstanding default issues without breaking the vast installed base of existing applications.