2 months ago
- SQLite is a library-based relational database management system (RDBMS) widely used in embedded and server projects, but has problematic defaults.
- Foreign key constraints are ignored by default in SQLite, unlike other RDBMSes, leading to potential data consistency issues and dangling references.
- SQLite's loose typing system allows columns to store incorrect data types by default, though strict tables can enforce type safety with the 'strict' keyword.
- Concurrent writers in SQLite result in immediate SQLITE_BUSY errors by default, but can be configured with 'busy_timeout' to wait for locks.
- Performance defaults are suboptimal, with Write-Ahead Log (WAL) disabled; enabling it along with 'synchronous = NORMAL' improves write speed and reduces disk syncs.
- The author proposes introducing a 'super pragma' based on Rust-style editions, such as 'PRAGMA edition = 2026', to bundle improved defaults without breaking backward compatibility.