SQLite should have (Rust-style) editions

mort96

Oh hi, author here. Fun to see this make it to HN.

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.

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!

Rendello

It might be worth bringing this up on the forum [1]. The developers are quite active there, and it's possible they've never considered this option, or they have considered it and have reasons to not go for it. The original design followed Postel's Law (see my comment from the other say [2]), it would (theoretically) be nice if that mess could be avoided by specifying an edition. Today I noticed I could do `pragma foreign_key = ON`, and despite the pragma being wrong (it should be foreign_keys , plural), it reported nothing. In fact, it reports nothing with the correct pragma either. So check your pragmas! 1. https://sqlite.org/forum/forum 2. https://news.ycombinator.com/item?id=48900625

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.

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!

Thaxll

SQLite gets so much praise here but when you start using it, you realize quickly how bad it is, the type system is by default very limited and dangerous. It's like comparing old php with a strongly typed language. There is not even a date type...

souvlakee

Sadly, the ORM layer lags here: Drizzle has no way to declare STRICT tables. The request has been open since March 2023 (issue #202, now discussion #2435) and didn't make the v1.0 beta either. The only workaround is hand-appending STRICT to generated migration SQL, which doesn't work at all if you use `drizzle-kit push`. https://github.com/drizzle-team/drizzle-orm/discussions/2435

chillfox

you can also set those defaults with compile time flags, that's what I have been doing.

Retr0id

An alternative is to use wrapper libraries that set sane defaults, e.g. https://rogerbinns.github.io/apsw/bestpractice.html But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.

andrewchambers

It is sqlite3. Emphasis on the 3 - it already has 'editions'.

GianFabien

Nah, all those defaults are features . Of course, there are contexts where those defaults are unsuitable which means: Use a Different RDBMS!

eduction

HN… for the love of god… please please stop trying to make SQLite be something it isn’t. Leave this poor project alone. It’s a great tool if you want to give a local app its own database. If you need concurrent writes and full ACID guarantees of an industrial strength database, use an industrial strength database. Yes, other databases will require you to read more manual pages and configure a service. Higher up front cost. Not “lightweight.” But given enough operating time there is a certain unarguable lightness to using the right tool for the job.

tmpfile

Why not a .conf file like everything in /etc or postgresql.conf?