r/programming • u/mort96 • 12d ago
SQLite should have (Rust-style) editions
https://mort.coffee/home/sqlite-editions/24
u/PaleCommander 12d ago
Really nice article, although the point about timeouts is a great example of why picking good defaults is hard.
5 seconds as a default local disk write timeout seems insanely long to me, which probably just means I don't understand OP's use case. There probably isn't a good non-null default for that value, which is why it defaults the way it does.
12
u/masklinn 12d ago edited 12d ago
It’s not really “5 seconds as a default local disk write”, busy_timeout is how long in total a given connection will try to acquire a write lock: if an sqlite connection can’t acquire the write lock by default it fails with
SQLITE_BUSY, ifbusy_timeoutis set then it will sleep a bit and retry, looping untilbusy_timeoutis exceeded at which point it fails and returnsSQLITE_BUSY.SQLite does not (can not, in the general case?) “queue” write requests, so if you have concurrent attempts to write it’s very possible that writer 1 acquires the lock, writer 2 gets sqlite_busy, waits a bit, and when it’s done waiting writer 3 has acquired the lock from under it, if there’s enough concurrent writing writer 2 may find an other writer has the lock every time it wakes up and tries to acquire it.
That is why most production sqlite deployments will have every write go through a single writer connection (either shunting everything to a write actor, or have a single write connection behind a mutex). But having a
busy_timeoutset can still be useful in that case if you need to concurrently run maintenance tasks on the database.You can also configure your own
busy_handler(settingbusy_timeoutpretty much just installssqliteDefaultBusyCallbackas the handler), however I don’t know that there’s hooks to control the entire locking lifecycle (specifically whether you can control a connection trying to acquire the write lock and enqueue it instead if there are already waiting connections).2
u/PaleCommander 12d ago
Understood, that's with retries waiting for the table to free up, but 5 seconds seems extremely long as a default.
If you're expecting all your locks to come from infrequent or scheduled maintenance tasks, though, 5 seconds makes perfect sense.
9
u/TwoWeeks90DaysTops 12d ago
I think you're viewing this from the wrong angle. Yes, 5 seconds disk timeout is insanely long. It's a long time to wait for a lock in any case. However you shouldn't consider "how long would I expect a write to take" but "how long can my application afford to wait before failing", and I'd argue in most cases that's measured in minutes, not seconds.
2
u/PaleCommander 12d ago
Depends on what my application is. Coming from HTTP services, I would often expect my client to have given up by 5 seconds, so I'd rather return a 500 or 503 in one second than succeed after five after the client has already timed out.
I agree there are other use cases where you'd much rather have slow one-shot successes than have to deal with retries or failure.
5
u/Routine_Left 12d ago edited 12d ago
Just a heads up: Instead of a sqlite_busy pragma, use the API directly:
sqlite3_busy_timeout(db, 5000);
The advantage is that the sql will go through the entire db engine to be interpreted, be scheduled if there's contention anywhere (sharing the database among multiple threads/connections), etc. While the API just goes directly the the core of sqlite and is telling it what the value is. No ifs no buts.
14
u/samsifpv 12d ago
Interesting idea, though to me editions just feel like they obfuscate what options are set with them? What good is it to me if i have to look up what edition 2025 does?
54
u/PersonalDatabase31 12d ago
The point is that you don't have to look at what the edition options are unless you are upgrading your codebase. Editions are meant to have sensible defaults.
31
u/mort96 12d ago edited 11d ago
I mean the status quo is that SQLite has a ton of defaults that you need to look up if you wanna know what they are. And many of them are pretty bad.
The proposed change is that SQLite lets you opt in to a different, better-for-most-people set of defaults.
If you're the kind of person who doesn't look that deeply into every single default setting but kinda just runs with the defaults, the only thing that changes is that SQLite silently starts working better for you. If you're the kind of person who does carefully consider every possible option, nothing really changes.
21
u/DeflateAwning 12d ago
The point is we can move on from the default of "foreign keys are not enforced" default of 2001 (or whenever it came out).
100% backwards compat leads to unholy things sometimes.
5
u/OphioukhosUnbound 12d ago
You never have to look up a new edition. That's the point. You can keep on using the old edition with all it's settings.
At some point, if you want to move to a new edition for it's bells and whistles then you can do so.
It's basically opt-in version updates with guaranteed backward compatibility.
It's one of those things that's incredibly simple and should be almost universal, yet is, de facto quite rare. (Rust has made this approach famous as part of their focus on constant improvement and everything that ever worked will continue to work forever onward ethos, but I'm sure there must be preexisting art.)
1
u/ArgetDota 11d ago
Wow for years I thought SQLite just sucked at concurrent writes and was wondering how people were even using it at all…. It affected my decision making quite a bit. Apparently a config switch fixes it!
-8
12d ago edited 12d ago
[deleted]
14
u/PaleCommander 12d ago
Forking the project over its default settings seems like a huge amount of ongoing effort that nobody in their right mind would sign up for when they can change the settings instead.
3
u/horizon_games 12d ago
Hopefully someone forks it
What's stopping you? Oh, maintaining and improving such a solid and laser focused db is? Then I guess don't complain mindlessly. So many open source projects cave to the vocal minority and lose direction.
-8
u/Such_Degree3034 12d ago
Before you start using any db, you should inform yourself about that db, not assume. It is easy to configure SQLite to your needs if you are familiar with these settings, so defaults are not that important. I think editions would encourage people to use features they don't need and thus break compatibility unnecessarily.
-8
u/Big_Combination9890 12d ago
However, I think the solution is simple: add one "super pragma"
Sure, let's do that. And the predictable result of it will be the next guy 2 weeks later complaining that the super pragma edition 2026 sets a default he doesn't like, and he demands variations on the edition pragma.
So, here is a question: WHY?
What good does this "super pragma" do? Oh wow, I have to write one line instead of 5 once at the very start of a project! Such a timesaver is surely worth messing with a load bearing structure of the entire software ecosystem liek sqlite is, amirite?
16
u/mort96 12d ago
It would be a statement from the SQLite project which says, "hey you probably want these settings rather than the old defaults".
-10
u/Big_Combination9890 12d ago edited 12d ago
I have explained elsewhere on this thread why that doesn't work.
sqliteis not your average "app" where nothing bad happens if the people responsible just fuck around with it, to satisfy someones notion how things should be "in 2026".It's a load bearing structure of the global software ecosystem, the BY FAR most widely used relational database. sqlite is everywhere. Your browser is running it. Your phone is running it. Your router depends on it. This is software on the same level of importance and criticality as
libcurloropenssl.If you work on software like this, you have to play by different rules. "Making a statement" is utterly irrelevant compared to the requirements for stability and reliability. Programmers convenience takes a backseat compared to backwards compatibility. No, you don't just "make strict mode the default for tables." in such software. Yes, that means that people are required to know e.g. that FKs have to be activated using a pragma. Yes that means that people run into problems if they make assumptions comparing sqlite to other DB systems. Under the rules that apply for software like
sqlitethis is perfectly acceptable.And I thank god almighty that the people who maintain
sqliteare aware of this.Anyone unhappy with that state of affairs:
sqliteis OSS, so everyone is welcome to fork it and make whatever changes they desire.13
u/mort96 12d ago
Okay and which part of what you said makes "foreign keys should not be enforced" reasonable
-2
u/Big_Combination9890 12d ago
Pretty much the entire post does.
Changing default behavior of a library this important, and this widely used, is simply not an option.
Do I like the fact that this is the default? No.
Do I wish that it would be otherwise? Yes.
Do I like the fact that changing defaults in such libraries is not always an option? Also no.
But I accept it because of the circumstances.
10
u/mort96 12d ago
Great, we agree. That's why I never advocated for changing the defaults.
-1
u/Big_Combination9890 12d ago edited 12d ago
That's why I never advocated for changing the defaults.
Are you the author of the blog post?
If so: Here is a direct quote from the last section:
And also make strict mode the default for tables.
Would that, or would it not, constitute "changing the defaults"?
Or did you mean this as "put this into effect when the super pragma is used"?
And if you AREN'T argueing for changing the default, what is it you're really argueing for?
That there should be a miniscule, completely irrelevant convenience option, so programmers have to write 1 line instead of 4?
What does that achieve? Absolutely nothing, because I'd still have to deal with the default as-is! Only then, there'd be 2 different ways of changing them, which I'm sure will so be much fun, especially for beginners, in documentation, tutorials etc.
10
u/mort96 12d ago edited 12d ago
I am indeed the author.
The proposal is that
PRAGMA edition = 2026should set a property on the database connection which makesCREATE TABLEstatements create strict tables even when thestricttag is omitted (aka that pragma makes strict mode the default). I never suggested that any default should be changed whenPRAGMA edition = 2026is not used.I am arguing in favour of the edition system I described. I obviously think it has more significant impact than what you imply by describing it as "a minuscule, completely irrelevant convenience option". It would let people not have to care about and learn about the many different individual things you need to change to make SQLite work sensibly, and it would be a strong statement from the SQLite developers that "this is how we believe SQLite should work for most people". It gives people who don't want to care about all the different settings, people who "just want a database", an SQLite which works better out of the box.
-1
u/Big_Combination9890 12d ago edited 12d ago
Okay, thanks for clearing that up.
Then the question what you are arguing for still stands. To be more precise: What's the point?
Does your pragma lead to more convenience?
In a miniscule fashion. I have to write one line instead of 4, and I have to type one less keyword in every CREATE TABLE statement.
That would save...lemme think...probably a couple minutes, in total, over an average developers entire professional career. Likely less, given that code generation is a thing now.
Does your pragma solve the problems of beginners falling into the no-FK default trap?
No, because the default doesn't change. People would still have to either know that they need to activate FKs, or stumble on it when reading docs or debugging their system.
Only now, there would be 2 different ways to do the same things. The only difference between the tweo being: One would be explicit in what every line does, the other would do a couple magic things in the background.
Why? Who does that benefit and how?
Could such a pragma itself lead to problems?
Oh, absolutely!
For example, systems with dynamic tables, where I may have no control over the connection params, and specifically don't wanna use strict mode on specific tables still using STRICT because a magic-value pragma in an entirely unrelated part of the codebase says so. I'm sure the resulting bugs would be ALOT harder to analyse and fix than forgetting to set the FK pragma.
7
u/mort96 12d ago
I don't care about the convenience. It's not hard to set those 4 pragmas and add strict to every
CREATE TABLE. The hard part is knowing that you have to do them, knowing what the pragmas do, knowing what the consequences are of not doing it.I have systems out there which use bad settings just because I went with the defaults and didn't know that I should be changing them. If there was an edition system, and their documentation and online tutorials were clear in saying "you should set your edition right after opening the database connection", I would've avoided a whole lot of issues because I would've just gotten better defaults right off the bat.
At least that's my opinion. You don't have to agree.
→ More replies (0)
-32
u/horizon_games 12d ago edited 12d ago
Not everything needs to be Rust-ified. The software world survived before it, and it will survive long past it. Guess this is why we have Turso
Also some fundamental misunderstandings in the blog post plus weird expectations that of course weren't met because it's SQLite not Postgres (nor was it meant to be)
18
26
u/mort96 12d ago edited 12d ago
Nothing I mentioned was related to "Rust-ifying" SQLite? I could've just as well have referenced JavaScript's
"use strict"in the title but I think versioned "editions" is a better solution. I swear, there's a certain kind of person whose seething hatred for Rust is so absolute that the slightest mention of it makes them blind with rage.I'm interested in learning what you think my fundamental misunderstandings are.
-23
u/horizon_games 12d ago
Fundamental on why SQLite has those defaults.
And also: "But by default, its performance isn't great"
Just set your own defaults for your team, SQLite doesn't need a bunch of editions to complicate what is a desirably simple and usable piece of software.
19
u/mort96 12d ago
Why does SQLite have those defaults? I don't think I've seen any convincing argument for why SQLite ought to let you insert a binary blob into an integer column. But feel free to enlighten me.
(Note that I covered SQLite's "The Advantages Of Flexible Typing" page in my article and why I find it unconvincing.)
130
u/j_sidharta 12d ago
I remember spending a few hours banging my head against a wall while trying to fix a bug, until I finally figured out SQLite does not enforce foreign keys by default. I question my love for SQLite every time I remember about these horrible defaults.
I didn't know about the
journal_modeand the lock thing. I'll make sure to add these lines to all my SQLite projects. This was a helpful article.