SQLite Needs Rust-Style Editions

SQLite is the local database you reach for when you want something self-contained, battle-tested, and free of a separate server process. It ships in phones, browsers, desktop apps, and even some production services. That ubiquity comes with a price: defaults chosen decades ago still shape every new connection, and some of them are actively hostile to correct programs.

Foreign-key constraints are off unless you turn them on. Column type declarations are affinities, not contracts. The result is silent data corruption that other RDBMSes refuse to allow. A proposal floating around the community is to give SQLite something like Rust's editions: opt-in epochs that let the library ship better defaults without breaking the mountain of existing code that depends on the old behavior. That idea is more interesting than a simple wishlist. It forces a hard look at how foundational libraries manage change when "just fix the default" would be catastrophic.

The two defaults that still bite

Start with foreign keys. In any other serious SQL engine a declaration like FOREIGN KEY(user_id) REFERENCES users(id) means what it says. In SQLite the constraint is parsed and then ignored until you run PRAGMA foreign_keys = ON. Forget the pragma and you can delete a parent row while child rows dangle. Because SQLite reuses ROWIDs under certain conditions, the dangling reference can later resolve to an entirely different row. The classic horror sequence is easy to reproduce: Bob posts, Bob's account is deleted without cascading, Alice is created and receives the same integer ID, Alice now "owns" Bob's post. No error. Just wrong data.

The second footgun is type affinity. Declare a column INTEGER and SQLite will still cheerfully store the string "Way too long, I mean come on" in it. Numeric strings get coerced; everything else is stored as-is. The engine is not a document store, yet it behaves like one unless you create the table with the STRICT keyword. STRICT tables have existed for years as an opt-in, but the default remains the loose affinity rules that date back to the project's earliest days.

Neither behavior is a bug in the narrow sense. Both are frozen compatibility choices. Changing the global defaults tomorrow would break uncountable applications that never expected enforcement or type checking.

Why a flag day is off the table

SQLite's entire value proposition is stability and extreme portability. It is written in carefully constrained C precisely so that the implementation language itself does not thrash underneath the library. The public interface has remained source-compatible for a very long time; the project even documents that it could theoretically be recoded in Rust one day, but only if that did not sacrifice the guarantees developers already rely on.

That conservatism is why SQLite is the storage backend inside Android apps, Firefox, Thunderbird, Apple products, Windows components, and countless embedded devices. Any change that alters the meaning of an existing schema or connection must be opt-in, or the library ceases to be a drop-in component. The same pressure that keeps the language C also keeps the defaults frozen.

What editions actually buy

Rust solved an analogous problem with editions. The language can introduce breaking changes (new keywords, different module resolution, adjusted borrow rules) while old crates continue to compile under the edition they were written for. Crates of different editions interoperate cleanly. The edition is a property of a crate, not of the whole program.

Translating that model to SQLite is not trivial, but the shape is clear. An edition could be selected at connection open time, via a URI parameter, a compile-time flag, or a one-time PRAGMA. A "2026" or "modern" edition would turn foreign keys on by default, make STRICT the ordinary table behavior, and perhaps lock in other contemporary settings (WAL, better synchronous defaults, etc.). Existing databases and connections that never declare an edition keep today's semantics forever.

The engine would still need to support both modes for the foreseeable future. Schema files and dump formats would have to record which edition created them. Test matrices grow. Documentation becomes longer. Those costs are real. The benefit is that new application code stops paying a tax that exists solely for the sake of 2004-era programs.

What developers would actually do

Today the workaround is manual and repetitive. With rusqlite or sqlx you open a connection and immediately run:

conn.execute_batch("PRAGMA foreign_keys = ON;")?;
// then CREATE TABLE ... STRICT for every new table

Many codebases wrap the open call so the pragmas are impossible to forget. Bundled builds already give you a modern SQLite version; the remaining pain is the defaults themselves. Higher-level ORMs and migration tools sometimes set the flags for you, but the moment you drop to raw SQL the footguns reappear.

If editions shipped, the same open call could become:

let conn = Connection::open_with_flags(path, OpenFlags::default(), Edition::Modern)?;

or a connection string parameter. New projects would get the safe defaults for free. Libraries that embed SQLite could advertise "we open every database in the modern edition" and stop arguing about whether callers remembered the pragma. Old databases would continue to open under the classic edition unless an explicit migration step upgraded them.

The trade-offs are familiar to anyone who has lived through a language edition change. You still have to understand both sets of rules when you touch legacy code. Tooling (schema linters, dump tools, replication) must become edition-aware. And if the SQLite team ever wanted to retire an old edition, the deprecation timeline would be measured in decades, not years.

Even without official support, the practical advice is already clear. Treat every new connection as requiring an explicit modern configuration. Use STRICT tables. Turn foreign keys on before the first write. Prefer the bundled SQLite that ships with rusqlite or sqlx so you are not at the mercy of an ancient system library. Those habits eliminate the two worst classes of silent failure today.

The larger pattern for foundational libraries

SQLite is not unique. Many long-lived libraries accumulate defaults that later look like mistakes. Changing them is politically and technically expensive. Editions (or an equivalent versioned contract) are one of the few mechanisms that let the library evolve its public face without a simultaneous rewrite of the world.

Whether the SQLite project ever adopts the idea is secondary to the question it raises. When a library becomes infrastructure, its compatibility surface is part of its product. The only sustainable way to improve the defaults is to make the improvement opt-in, discoverable, and permanent for new code. Rust showed one workable design. SQLite already has the technical pieces (pragmas, STRICT tables, connection flags). Formalizing them into named editions would simply make the contract explicit.

Until that happens, the responsibility stays with the application. Open the connection, set the pragmas, declare the tables strict, and move on. Alice should never inherit Bob's posts again.