Why SQLite Should Adopt Rust-Style Editions to Fix Dangerous Defaults
SQLite should have (Rust-style) editions
I argue that SQLite, despite being the industry standard for local storage, suffers from dangerous defaults like ignored foreign keys and loose type checking. These settings lead to silent data corruption and referential integrity failures. I propose adopting Rust-style editions to enable strict typing and constraint enforcement by default, ensuring data safety without sacrificing the flexibility developers love.
A dangling reference easily results in a reference to the wrong column, which is even worse than a dangling reference because everything will seem fine.
- sethev
Interesting idea - I like seeing a list of pet-peeves followed by a proposal for a straightforward way to have a set of 'alternative defaults' that remains backwards compatible. If you don't want to opt in, don't run the new PRAGMA edition = 2026.
Too often it's just a list of issues and a wish that everyone else will change.
In (mild) defense of SQLITE_BUSY - busy_timeout just tells sqlite to sleep and retry up to the timeout when it receives SQLITE_BUSY. It seems like a sensible default for a library to leave that up the calling code - which may have something else it could do while it waits. However, that logic often gets missed!
- kccqzy
SQLite is slightly different from Rust in that it is a data container. It’s somewhat more common for people to move SQLite database files from one machine to another and then inspect using the command line tool. And it is often the case that the embedded SQLite version in your app is a newer version than whatever version /usr/bin/sqlite3 happens to be. Adding editions to your SQLite file will probably break this use case of using an older version to read a database written by a newer version because it does not know what has changed in a new edition.
Not a big deal though. Probably just need better ops to bundle the command-line utility that’s the same version as what’s used in your app.
- andai
The "use strict" thing is interesting. I often hear people say, well we can't fix absurd behavior in JS because backwards compatibility! Well, we already did, and we can do it again!
- dolmen
Counterpoint: each new edition will add bloat to SQLite as future edition will need to keep each past edition pragma set for backward compatibility.
As SQLite is often used embedded, bloat matters.
So I suggest that "PRAGMA edition" to be only be a shortcut to a list of PRAGMA commands, that would be expanded at the library level: PRAGMA edition would never appear in the DB file. As such, the build of the library would just support a limited set of editions, with a removal policy in default builds. Maybe editions could even be defined at runtime (a system table?) as a way to load them dynamically if old editions are needed beyond builtin support (think about the state of SQLite in 2046).
- jmull
I actually think this wouldn’t be that helpful.
The developer still needs to ensure they apply their set of pragmas, whether that’s a single edition pragma or a set of pragmas. And they still need to understand and carefully choose the pragmas/options they use (an edition really makes this a little harder by abstracting/hiding something that needs to be directly understood and visible).
And these proposed new default pragmas are more incremental improvements rather than complete solutions, more convenience than critical. E.g., while the default affinity is goofy, strict tables don’t come close to a comprehensive validation mechanism — so if you need strong validation you’re likely going to need to implement that at a higher level anyway. (Also, there’s a decent separation-of-concerns argument that you should handle it separately.)
Likewise the busy timeout. The pragma is convenient but you still need to handle busy timeouts. (The author’s problem, “I didn’t realize busy timeouts could happen so the app didn’t handle them correctly”, is not solved by the pragma.)
- mort96
Oh hi, author here. Fun to see this make it to HN.
- andai
In the first example, there's a a second thing that surprised me: you delete an entity and it's unique ID gets reused? Is that a good idea?
I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.
- azeirah
Oh I really like this! The one counterargument that comes to mind is when I think about the likes of C++, where there are many editions and they can be confusing to keep track of.
I'm not sure if you'd want to set one edition in stone every year. Perhaps every 3 years? Or 5 years? Especially for a long-term project like SQLite, that sounds perfectly acceptable!