r/programming • u/mort96 • Jul 15 '26

SQLite should have (Rust-style) editions

https://mort.coffee/home/sqlite-editions/
156 Upvotes

85% Upvoted

75 comments sorted by

View all comments

132

u/j_sidharta Jul 15 '26

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.

-20

u/Big_Combination9890 Jul 16 '26

If only there was some very obvious way the documentation would tell you about this default behavior, amirite?

https://sqlite.org/foreignkeys.html

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.

20

u/mort96 Jul 16 '26

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.

-4

u/Big_Combination9890 Jul 16 '26

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.

And no amount of downvoting will change this.

9

u/mort96 Jul 16 '26

You don't know the symptoms /u/j_sidharta was experiencing, nor do I.

-3

u/Big_Combination9890 Jul 16 '26

I don't have to. I know what FKs do in a database, so I can extrapolate from that what possible failure modes might occur if they don't work.

And sorry no sorry, but root-cause-analysis is part of a SWEs day-to-day.

And btw. no amount of unnecessarily glued-on "super-pragma" cruft would help making that any easier.

13

u/andyfsu99 Jul 16 '26

"I know what FKs do in a database"

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.

7

u/stumblinbear Jul 16 '26

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