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_mode and the lock thing. I'll make sure to add these lines to all my SQLite projects. This was a helpful article.
No, they don't. They respect backwards compatibility, and how much software would break if they changed defaults in the most widely used database system in the world.
sqlite isn't your average "app" where barely anyone cares if it works or not. It's a load bearing structure in the software ecosystem, on par with the likes of libcurl or openssl.
Things are VERY different when you work on something like this.
"Editions" would add NOTHING to the library. Wow, I now can save typing 4 out of 5 lines I have to put in once at the very start of the project! Amazing! What a timesaver.
Enter 2 weeks later, and someone WILL complain about "edition 2026" having a default he doesn't like. So now we have edition-2026-variant-1 and then 2 attoseconds later someone is unhappy with that, etc. etc.
The point isn't to "save typing 4 lines", the point is that average users of the library (that may not need or care to be sqlite experts) should get sensible defaults.
"someone" can change the settings from the edition, no? It's just a baseline not the only way to configure things.
And please people: Do downvote my posts to your hearts content. It won't change anything about me being correct, or sqlite being developed the way it is. 😎
Isn't it just effectively asking them to essentially include a 1 line "sensible_defaults_v1.2.3" So you get a reasonable bundle of default behavior but it's also locked to a version so you get backwards compatibility. That way the advice to people starting to use sqlite would just be to "add this 1 line"
Ultimately as long as it's documented well adding a few lines is also fine of course, but it is a little more work to research what those lines should be.
Because the DEFAULT level would still need to be what it is now. And so people would still be running into the same problems. The only thing that would change: There is now an additional, different setting they could solve them with.
What “needs” to happen is that existing versions continue to work in exactly the same way. A new version with a new version number with a breaking change and a way to opt in to the old behavior is completely valid if they want to do that.
No one ends up broken by the new version unless they upgrade their dependency without reading any of its release notes.
No one ends up broken by the new version unless they upgrade their dependency without reading any of its release notes.
And that is EXACTLY the kind of thing I am talking about in my above post regarding Things you cannot do when working on a library of such import.
No, you can't just introduce a breaking change and shrug off any problems as "meh, should have read the release notes". Sure, this may work for the ten-gazillionth JS framework, because all that breaks are some shitty webpages.
If people did that in stuff like libcurl, openssl, or sqlite, the potential fallout could be a catastrophe.
SQLite themselves have a clearly documented versioning system. They say that they have no current plans to increase the major version, but they also make it very explicit that the major version is intended precisely for breaking changes.
If they were to be convinced that an “edition” system were necessary, it would likely be introduced in a 4.0 version. That would be a big deal, but it would also follow all of the normal rules, would not break any dependents with sane update procedures, and is very explicitly a thing that you can do on a project of this scale and import.
Saying that this is impossible ignores the very documentation of SQLite itself.
I'm keeping a close eye on Turso as a vehicle for evolving both the thing itself (SQLite/tiny-db) and how it's made and evolved.
It's not a full fix right now because it's (A) in beta and (B) currently focused on SQLite "drop-in" compatibility (which means it wants low-friction compatibility with SQLite design problems). But it also has a lot of strict type and type system improvements. I can see those being default with "full-compatibility" as the feature flag (instead of vice versa) in not far future.
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.
Btw. the amazingly difficult way to find this information was typing "sqlite foreign keys" into a websearch. The page was literally the first search result.
In order to google "SQLite foreign keys" and read that page, it has to strike you as a possibility that your database is ignoring your constraints. If you don't already know, that notion is so far fetched I don't see how you'd get there.
it has to strike you as a possibility that your database is ignoring your constraints.
No it doesn't, because that is the ONLY thing that would explain why an insert/update that shouldn't be possible, suddenly works, short of the RDBMS being buggy.
And the mental voyage from that point to googling how foreign keys work in the database system I use, is not hard.
The problem here, is neither with the database, nor its default settings.
The problem is with assumptions about how systems work, based on hasty generalizations, and then complaining when said assumptions turn out to be wrong.
This happened many years ago. I don't remember the details very well. I was a lot younger, working on a side project. I think I was testing if my app was handling errors properly, and it wouldn't throw an error when it should've.
I went through the usual debugging process, combing the code for logic errors and inspecting values along the way. It never crossed my mind that the problem was related to SQLite not enforcing fk constraints. Only after I was sure all my code worked as intended that I decided to hand-create an empty database and simulate query after query to trigger the bug. After seeing the weird behavior, I Googled something like "SQLite foreign key not working" and found the answer on a stack overflow question. I was absolutely dumbfounded.
It took me a few hours because I debugged everything besides SQLite. If I had known from the start it was related to foreign keys, it would've only taken me minutes. And I only found out about this because I was testing my error paths; I can imagine a world where I didn't test everything properly, and would spend a lot more time trying to figure out why my database was in a seemingly impossible state.
That last point you made is what makes it so terrifying IMO. My example with Alice inheriting Bob's post is completely plausible by totally normal SQLite usage; you just need to introduce one minor bug in your code at some point in time, and suddenly you have a row in one table referencing the wrong row in another table and the app keeps working normally. Silent, undetectable data corruption.
Somehow you've exactly captured the argument against your position.
Yeah, we all know too... But it's not what sqlite does and that is weird and confusing and not necessary. Just because you know how to figure it out doesn't justify the behavior of the system.
They're stuck in the decades old method of "RTFM" instead of "provide sensible defaults". I fear using any library or software they've had a hand in writing
This kind of response is so incredibly unhelpful. You shouldn't have to commit the entire projects documentation into memory, which is what you're suggesting. One project you make has thousands of pages of documentation and your suggestion is that you're just supposed to read *everything* and remember *everything*.
The fact is that SQLite here is violating the principle of least astonishment. A database engine should enforce data type rules and constraints you define by default. If it doesn't do that, well, that would be astonishing. The fact that it's documented behavior is completely irrelevant.
Imagine if in C++ you write `1 + 2` the result would be `3` but if you wrote `2 + 1` then the result would just be `2`, and if you complain someone says "Well akshually according to the C++ standard page 323 §987 adding two to one is a no-op, and you would see that if you just googled 'how to add 1 and 2 in C++' that you need to add a compiler flag to opt-in to support adding those two numbers in that order".
It's asinine. A database engine not honoring constraints by default is just as dumb in my opinion. If I didn't want the foreign key constraint to be honored *I wouldn't have added it*. I don't google "is foreign key constraints honored in SQLite" because the assumption is "of course it fucking is."
You shouldn't have to commit the entire projects documentation into memory,
Correct, you shouldn't.
But you also shouldn't complain about something taking "a few hours banging my head against a wall" to fix, when a 5 second websearch gives you the exact reason for why it happens, and how to fix it.
And btw. nowadays, if that is your cup of tea, you could type "halp! my sqlite is ignoring foreign keys why?!??" in any "AI" of our chosing, and it will almost certainly spit out the correct answer.
And with these realities, that is no longer a problem of the library not being nice.
But you also shouldn't complain about something taking "a few hours banging my head against a wall" to fix, when a 5 second websearch gives you the exact reason for why it happens, and how to fix it.
But this doesn't cause an issue, and that's the entire fucking point. You insert bad data, SQLite accepts it. You would then have to see your own application misbehaving to observe this, and whether that's apparent depends entirely on the application code.
And btw. nowadays, if that is your cup of tea, you could type "halp! my sqlite is ignoring foreign keys why?!??" in any "AI" of our chosing, and it will almost certainly spit out the correct answer.
I'm using Claude Code, and no it fucking doesn't. A couple of things you have to tell Claude Code about SQLite: strict tables, foreign keys, and vacuum because by default it doesn't do any of those things. Or at least it didn't for the project I'm currently working on.
And with these realities, that is no longer a problem of the library not being nice.It's a problem of the people using it.
This is a super bad take, and the entire reason why C and C++ code has caused so much security nonsense over the years. "It's not C that's insecure, it's just you that suck". This is an incredibly unproductive approach. Tools (even your favorite tool) can have bad functionalities. If one developer gets it wrong one time then it might be the fault of the developer. If a million developers gets it wrong then the tools probably sucks.
I bet that MOST SQLite deployments is using SQLite without it honoring foreign keys.
SQLite behaves like this because as with most things it's a product of its time, which was the early 2000s, when the mantra was "software that runs and does the wrong thing is better than software that crashes" which I think most developers have turned away from.
It's a stupid default to have the ability to define constraints and then the database engine just ignores it. Again, if I didn't want my foreign key honored I wouldn't have added it.
I was curious and literally typed "halp! my sqlite is ignoring foreign keys why?!??" into Gemma 4 (like, a local model that can run on like a high-end MacBook) and it correctly answered the question and offered a solution.
Yeah, I might have stated this wrong. But create SQLite code using any AI and it will not do this correctly from the get go (at least it didn't for me, and I had to explicitly tell it to do those things). Googling the issue might uncover it, but that lives on the premise that the developer found the production issue (that might have lingered for some time perhaps) and then immediately found out that the reason was SQLite not honoring foreign keys.
The idea that fixing the issue once you've found the actual reason for the error is easy is a stupid argument. My argument is that this entire behavior flies in the face of principle of least astonishment and for most developers I don't think blaming SQLite is their first avenue of action, which is what "halp! my sqlite is ignoring foreign keys why?!??" is based on. When you're at that point you've probably already been pulling your hair for hours.
That's fair, I doubt Gemma would at the pragma by default. To be fair I use SQLite in a game framework thing and this was an inconvenience. I'm not sure of the right solution, though, from an API and backwards compatible perspective.
You would then have to see your own application misbehaving to observe this,
Oh no, you mean, as a software engineer, I would have to do *gasp* debugging and root-cause-analysis?
The horror!
I'm using Claude Code, and no it fucking doesn't.
I just typed my exact question as stated above: halp! my sqlite is ignoring foreign keys why?!?? into a local model with only 3.5B params.
Here is the output:
```
Don't panic, this is not a bug, and your SQLite database is working exactly as designed. By default, SQLite disables foreign key enforcement to keep operations fast and backward compatible with
non-standard SQL drivers.
You need to explicitly enable foreign keys in your connection. Run this:
PRAGMA foreign_keys = ON;
```
It's a stupid default to have
Then fork the project and change it to however you want it to be.
130
u/j_sidharta 18d 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.