[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!

[tptacek]

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.

[Animats]

> 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.

[tialaramex]

> Now we need this for C/C++

P1881 Epochs proposed to WG21 (the C++ standards committee) in 2019 by Vittorio Romeo

The committee found plenty of problems with this, and made it clear that if Vittorio did all the hard work to resolve those problems they would find more, P1881 was abandoned.

There was a Reddit thread https://www.reddit.com/r/cpp/comments/1tja9zr/c_profiles_a_c... which suggested that the "Profiles" idea Bjarne is pushing for C++ 29 could be used to deliver this.

So, you're not the first person to notice that this is a good idea, P1881 was written after Rust's 2018 Edition, but before 2021 Edition with its even more significant improvements. I firmly believe Rust's Editions unlock not only technical possibilities (though it does certainly do that) it unlocks an appetite from users which is good for your ecosystem.

[cenamus]

I hope we're getting it for C++ with CppFront, but I'm not hopeful it'll ever become mainstream

https://github.com/hsutter/cppfront

[lelanthran]

> Now we need this for C/C++, which have much legacy stuff which ought to go away for new code.

In C++, certainly. In C, though, what do you not do in C23 that you was doing in C99?

[bashauma]

IIRC gets() ?

[lelanthran]

I don't think that counts - fgets (the safer replacement) was already in C89.

"Modern C" by 1999 already included "Don't use gets()"

[Animats]

The key concept of an "edition" is that you can deprecate stuff. "gets()" should have been pulled from the standard library around 1990 or so, along with "strcat" and its frenemies.

[doc_ick]

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.

[tptacek]

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.

[mikepurvis]

While that's true, editions are more about leaving legacy decisions behind while keeping the backward compatibility promise.

Even if you're in one of those unconventional settings (say, a bare-metal microcontroller or something), you'd probably still start from edition 2026 and mutate your settings accordingly, rather than using the defaults that are 26 years old.

[tialaramex]

Yeah, that's important. Rust's 2015 edition is worse, not just different from what you'd write today with 2024 edition. There's a clear direction of travel.

[doc_ick]

So you’re saying there’s no data to group these settings by editions.

[pstuart]

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.

[justinsaccount]

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.

[usefulcat]

If they’re opt-in, how could the new defaults be a problem for backwards compatibility?

[dspillett]

GPP's wording suggests that the defaults simply be changed to what is being discussed as more generally sensible in current times, rather than being opt-in.

Given how many projects are potentially out there effectively relying on the current settings, and SQLite's general attitude to backwards compatibility, that would likely not be considered a good idea. Opting in with an edition flag for new (or updating) projects does seem like a good solution to this to serve all of old, active, and new projects, but it would increase potential bug surface area and therefor testing requirements, and the existing setting do allow all that to be opted in/out to/from already (and it is only four settings we are talking about here).

That FKs being enforced is set per-connection rather than at the database level is something that surprised me a lot when I found out. A way of setting that at DB creation (or via ALTER DATABASE after) seems like quite an omission because if you have multiple potential routes that can update the same DB any one of them could cause serious the others will encounter.

[sethev]

Yes, agree. These are very sane defaults and match what I use..

[groundzeros2015]

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.

[afiori]

yes but no... most database allow for at least as many parallel and concurrent writes as there are tables at a minimum.

The "lock on write" problem is that in MySQL i could run a OLAP pipeline for a few hours and have a fully functioning database with degraded perfomance, on SQLite the same pipeline would lock the database for the full hour. (there are surely ways to solve this (eg using the main db as read-only and a secondary db for writes or splitting the writes in incremental transaction), but it is not a "myth".

[groundzeros2015]

The “myth” is you can’t use it for a website because if you have multiple requests that need to write they can’t do it concurrently. (They just take turns of course for the milliseconds of write time.)

If you run a transaction with writes for an hour on any database, the data you update will literally be locked. So your example only works if results are independent of the data other programs want to use.

Of course more granularity of locking is better and enables more designs that would not otherwise work. But somewhere you run into the same problem of writers taking turns.

[DANmode]

They don’t know by now, honestly they don’t want to know.

[platz]

busy_timeout is often sidestepped (ignored) when a transaction attempts to upgrade from a read to a write producing SQLITE_BUSY.

By default, SQLite transactions start in DEFERRED mode, acting as read transactions until an actual write operation occurs.

If another connection begins writing to the database while your transaction is in this read state, an immediate SQLITE_BUSY error is triggered regardless of what you set busy_timeout to

[tzot]

Exactly. In cases where I expect long-running parallel connections from separate processes to the same sqlite file, I make sure that all read transactions do `BEGIN DEFERRED` so `COMMIT` releases the read locks, and all write transactions do `BEGIN IMMEDIATE` so that `SQLITE_BUSY` timeout is not side-stepped.

There was one case where all transactions were implemented using nested `SAVEPOINT bla` so `BEGIN IMMEDIATE` could not be used without more hassle, so this ended all “I know I'm going to write” transactions to instantly update a single-row table so that their lock would not begin as DEFERRED and eventually switch to `IMMEDIATE`; this way almost all `SQLITE_BUSY` side-steppings disappeared. (timeout was set to 30 seconds but all read/write transactions were instrumented to have less than 5 seconds duration).

[platz]

lol, I briefly thought of such a technique, but in the end, found it simpler to just use a synchronization primitive at the application level to serialize db access on transactions where I knew I was going to write. it amounts to about the same honor code.

[mamcx]

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).

[aidenn0]

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.