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!

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.

As SQLite is often used embedded, bloat matters.

So I suggest that "PRAGMA edition" to be only be a shortcut to a list of PRAGMA commands, that would be expanded at the library level: PRAGMA edition would never appear in the DB file. As such, the build of the library would just support a limited set of editions, with a removal policy in default builds. Maybe editions could even be defined at runtime (a system table?) as a way to load them dynamically if old editions are needed beyond builtin support (think about the state of SQLite in 2046).

The developer still needs to ensure they apply their set of pragmas, whether that’s a single edition pragma or a set of pragmas. And they still need to understand and carefully choose the pragmas/options they use (an edition really makes this a little harder by abstracting/hiding something that needs to be directly understood and visible).

And these proposed new default pragmas are more incremental improvements rather than complete solutions, more convenience than critical. E.g., while the default affinity is goofy, strict tables don’t come close to a comprehensive validation mechanism — so if you need strong validation you’re likely going to need to implement that at a higher level anyway. (Also, there’s a decent separation-of-concerns argument that you should handle it separately.)

Likewise the busy timeout. The pragma is convenient but you still need to handle busy timeouts. (The author’s problem, “I didn’t realize busy timeouts could happen so the app didn’t handle them correctly”, is not solved by the pragma.)

I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.

I'm not sure if you'd want to set one edition in stone every year. Perhaps every 3 years? Or 5 years? Especially for a long-term project like SQLite, that sounds perfectly acceptable!

Well, loose typing can be extremely useful, and having a type of "ANY" would not replace it.

I have built recently an accounting reconciliation system to find discrepancies in data coming from a large variety of sources: some from proper database engines (MySQL MariaDB), but most from proprietary systems that export to CSV. It's amazing how corrupt data can become: dates that are invalid, numbers that aren't numbers, strings strings strings everywhere.

Being able to store the data into tables that have types, but can accept anything, is simply great.

But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.

So this was a write to a column that did not have INTEGER affinity. If it was intended to be used as a boolean, then it should have INTEGER affinity. I know because I've tried hard to enter integer- and float-like strings as strings in INTEGER affinity columns, and I haven't managed to; I could only insert them as BLOBs, or prefix the string with say '\' and check/remove at the application level. (That was for an ontology-like database, where table EAttribute.eatvalue could have any type.)

The first two points are deliberate SQLite design decisions so they're unlikely to change.

    > Bad default #3: SQLITE_BUSY errors with concurrent writers

https://perldoc.perl.org/functions/use

That way somebody can snag a copy of your data and be subject to the same constraints by default.

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

https://github.com/drizzle-team/drizzle-orm/discussions/2435

Then it argues for STRICT tables, recognizing that there are drawbacks without introducing a new feature (custom type aliases, CREATE TYPE alias = base).

If also doesn't even considering what it means for existing data to make tables strict, which is precisely why “there is no pragma to globally make all tables strict”.

Then it argues for setting a busy timeout, and picks 5s. Why? Why 5s and not 1 or 60s? SQLite doesn't decide, which makes perfect sense. Your OS or programming language also doesn't offer you locks with a default timeout: it's either indefinite, or an instant "try lock".

Finally: WAL mode is a different file format, unsupported on many platforms, in more danger of silent corruption. Why should it be the default?

It's like comparing old php with a strongly typed language.

There is not even a date type...

> But I do not recall a single instance where the bugs might have been caught by a rigid type system.

Which is a shame. Of course the author writes more than this, but this is IMO largely the gist of the argument. At this point it's beginning to feel like this is mostly a sort of stubborn sunken cost fallacy, where they've been arguing this for so long they can't take the "hit" of agreeing to change the defaults.

Those choices were made for specific reasons that make sense in embedded environment and when backward compatibility is no.1 concern.

But I wouldn't mind feature-sets. Editions are too wide of a concept and tell you nothing at glance what a given code is doing, "enable 2026 set of features" tells me nothing on what is actually enabled.

The reaction they're likely to have to that can probably be best described in megatons, like any other nuclear explosion ...

D will get caught into that edition trap too

All it does is fragment a ecosystem, bloat a source tree and makes maintenance a painful task only to please people who think maintainers should cater to their poor tech hygiene

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.