Rust solved the compatibility trap with editions

Delete a user from a stock SQLite database, forget to delete their posts, and nothing complains. Insert a new user afterward and, because SQLite recycles ROWIDs, that account can take the integer ID the deleted user held. The orphaned posts then join cleanly to the new account. The author’s example runs it end to end: Bob writes “Hello, I am Bob” and deletes his account, Alice signs up and gets Bob’s recycled ID, and a join across the users and posts tables returns “Alice | Hello, I am Bob.” No error fires at any step.

The cause is the first of several SQLite defaults the author argues are simply wrong: foreign key constraints are ignored unless you enable them with PRAGMA foreign_keys = ON. As far as the author knows, SQLite is the only RDBMS that doesn’t enforce them out of the box. With the pragma on, that stray DELETE fails with “FOREIGN KEY constraint failed (19)” instead of quietly corrupting the join.

Type affinity is the second. A column declared INTEGER isn’t restricted to integers. SQLite stores a valid numeric string as a number and leaves anything else as-is, so an INTEGER column accepts the text ‘Way too long, I mean come on’ and keeps it verbatim. The author once cleaned up a project where code had written the strings ‘1’ and ‘0’ into a column meant to hold the integers 1 and 0. STRICT tables turn the mismatch into “cannot store TEXT value in INTEGER column” at insert time, and the ANY type still covers a column that should genuinely hold any value. There is no pragma to make every table strict, so you tag each CREATE TABLE by hand.

Third is concurrency. SQLite allows many readers but one writer, and by default a second process reaching for the write lock gets an immediate SQLITE_BUSY instead of waiting. The author traces real-world bugs to this default, systems that would sometimes just crash, and wrote retry loops by hand to work around it. PRAGMA busy_timeout = 5000 makes the writer wait up to five seconds for the lock first. Concurrent writers are never fast, but interactive cleanup with the sqlite3 tool or an occasional admin script shouldn’t be able to crash the main process by colliding with it.

Fourth is performance, where the big one is that the write-ahead log is off by default. PRAGMA journal_mode = WAL gives a large write speed-up in most cases, and PRAGMA synchronous = NORMAL cuts disk syncs further without risking corruption under WAL.

Each of these has a one-line fix, and the reason usually cited for why they stay wrong by default is backwards compatibility. Flip a default and you break software that quietly relied on the old behaviour, and you teach people to fear upgrading the library at all. The author draws the parallel to being afraid to upgrade Python because every upgrade breaks something. Not breaking your dependents is a real and uncommon virtue, which is also what keeps SQLite stuck. Until that changes, you apply it yourself: enable the settings with their pragmas and add STRICT to every table.

Rust’s editions are the escape the author proposes. A single opt-in pragma would set a whole generation of defaults at once. The sketch is PRAGMA edition = 2026, standing in for foreign_keys = ON, busy_timeout = 5000, journal_mode = WAL, and synchronous = NORMAL, plus strict tables as the default. A database that never sets an edition keeps today’s behaviour, so nothing breaks; new code opts into the sane set in one line.

The year in the name does real work. The author takes it straight from Rust editions and prefers it to a flat switch like JavaScript’s "use strict" because sensible defaults shift over time. If Hctree’s WAL2 reaches the main branch around 2034, PRAGMA edition = 2034 could set journal_mode = WAL2 while edition = 2026 stays on plain WAL. Each edition is a snapshot of what good defaults looked like that year, and code records which snapshot it was written against.

Making strict tables the default would cost one genuinely useful quirk, which the lobste.rs user ‘zie’ raised. Non-strict SQLite decides a column’s affinity from substrings in the declared type: anything containing “INT” gets INTEGER affinity, “CHAR”, “CLOB” or “TEXT” gets TEXT affinity, and so on. That lets you invent type names like DATETIME, KEY_VALUE_SET or COLOR and have a connector read them back to serialize and deserialize the column, or just use them as documentation. Strict tables drop that. The author’s answer is type aliases, something like CREATE TYPE KEY_VALUE_SET = TEXT usable inside a strict table. As the lobste.rs user ‘masklinn’ pointed out, SQL 99 already specifies exactly this as CREATE DOMAIN, constraints included, so SQLite wouldn’t have to invent anything, only implement the standard statement.