Comment by sethev
5 hours ago
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!
This isn't so much a list of pet peeves as it is the almost universal way people that work seriously with SQLite configure the database. It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.
> It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.
That's the key concept here. When tightening up the defaults, an "edition" mechanism is a good solution.
Now we need this for C/C++, which have much legacy stuff which ought to go away for new code. This is more feasible than it used to be, because "Convert this Edition 4 code to Edition 5" is something LLMs can do now.
I'd never seen all the rules for SQLite soft typing written out before. Those are more complicated than strong typing.
I’d say these are reasonable settings for most uses. Though do you know of surveys that back this up? I don’t mean to nit pick too much, I’d just like to see common uses and the data.
SQLite is used in a lot of unconventional settings (for SQL databases) where these settings don't make as much sense. But that's what makes the "edition" useful; it captures the use case we all mean when we're thinking of the "database" lego in an application stack.
1 reply →
[dead]
Yes, agree. These are very sane defaults and match what I use..
You are probably correct, but I imagine the SQLite team's dedication to backwards compatibility has things the way they are so that existing systems can user later versions a swap without worrying about changing the SQL using it.
If they’re opt-in, how could the new defaults be a problem for backwards compatibility?
The entire point of "SQLite should have editions" is so that projects can opt into a set of modern defaults for 2026 and not get all of those backwards compatible decisions from 20 years ago.
After read, it hit me that because sqlite is a DB, "editions" as-is not work.
Because it not tied to the data but to the code.
Instead, what I think should be is that the PRAGMAs become "data" that is always checked in full with "if manually set" and then on next "open" THEY GET APPLIED.
That is.
(and in the command line when open interactively they show up).
Yep. The whole locking database thing is this persistent myth about SQLite. All databases lock on write, it’s a question of the granularity of the lock. Multiple writers simply take turns.
They don’t know by now, honestly they don’t want to know.
RE: SQLITE_BUSY: I would replace "often" with "nearly always." On top of that, it's often not fixed even when pointed out. "This software only has one writer, so we don't need to handle SQLITE_BUSY" translates to me sending SIGSTOP to a process any time I want to run some queries against its database.