A proposal for SQLite: one pragma to fix four bad defaults

SQLite is the industry standard for local data storage and, unlike a traditional database server, it is an RDBMS as a library rather than a separate process, which keeps applications self-contained. But in a post published on July 15, developer Mort argues that its defaults are wrong, and that the engine needs a way to move forward without breaking existing software.

Four defaults the author would change

The first is foreign key constraints, which SQLite ignores unless you run PRAGMA foreign_keys = ON. The author says it is the only RDBMS he knows that does not enforce them by default, and that SQLite's tendency to reuse ROWID values makes the consequences worse: a dangling reference can silently point at the wrong row. In his example, a post left behind by a deleted user is inherited by a new account that happens to receive the same ID.

The second is typing. A column declared INTEGER uses INTEGER affinity, so numeric-looking text is stored as a number while anything else is stored as-is; a duration column can therefore hold the string “Way too long, I mean come on”. Strict tables fix this — inserting TEXT into an INTEGER column raises an error — but no pragma makes every table strict, so the keyword has to be added by hand.

The third is concurrency: SQLite allows many readers but only one writer, and by default a second process trying to take the write lock gets an immediate SQLITE_BUSY error. The author prefers waiting for the lock up to a timeout (PRAGMA busy_timeout = 5000), a setting he says he added only after real crashes in production.

Performance and the edition proposal

The fourth is performance. Write-ahead logging is disabled by default; enabling PRAGMA journal_mode = WAL speeds up writes dramatically in most cases and, with PRAGMA synchronous = NORMAL, allows far fewer disk syncs without risking corruption. The post points to Sylvain Kerkour's article on optimising SQLite for servers for more.

To fix all of this without breaking backwards compatibility — the usual justification for keeping the defaults — the author proposes a single super pragma: PRAGMA edition = 2026, an alias for foreign_keys = ON, busy_timeout = 5000, journal_mode = WAL and synchronous = NORMAL, with strict tables as the new default.

The idea is borrowed from Rust editions. A year-based edition beats something like JavaScript's “use strict”, the author argues, because sensible defaults change over time: if a future log format such as WAL2 lands in the main branch, PRAGMA edition = 2034 could set it. A reader comment notes that SQL 99 already defines type aliases through CREATE DOMAIN.