[simonw]

SQLite very rarely changes defaults because of their commitment to backwards compatibility. They don't want software written against SQLite 3.53 to start throwing errors when upgraded to 3.54 because suddenly `CREATE TABLE` is creating strict tables and the rest of the software breaks as a result.

[boredatoms]

The need ‘default sets’ so that as the very first command I can say ‘use 2026.1 defaults’

[krick]

Or even better just SET STRICT_TABLES, or whatever other opt-in feature you want. A couple of such statements in the beginning of your migration script (refactored in a way to be reused across all migration scripts) and you are done. I wouldn't want to learn WTF "2026.1 defaults" are.

[firesteelrain]

I just do it in my code like

def create_table(name, cols): return f"CREATE TABLE {name} ({cols}) STRICT;"

[boredatoms]

That relies on you knowing about what good settings are though

[ezst]

Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.

[Asooka]

Well no, the software should be configured in its best state by default. Otherwise you run into the case where the user has to read the documentation like a legal contract to find all the footguns that need disabling. If STRICT is strictly better, than that should be the default. The correct approach here would be for the user to pass along a "compatibility version" tag when first connecting, which would set the defaults to whatever was default in that version. That should be something you force each user to set in their source and it should never ever have a "latest" value. It may be too late for sqlite, but if I were designing an API that had to remain stable for decades now, I would put an enum with possible versions in a header and require the user to pick one.

[thaumasiotes]

> It may be too late for sqlite, but if I were designing an API that had to remain stable for decades now, I would put an enum with possible versions in a header and require the user to pick one.

I read an essay about XML once where the author noted that he got a warm feeling inside whenever he saw the opening tag:

    <?xml version="1.0">

He noted that there were no higher versions. (Though they're now up to 1.1.) But he considered that the inclusion of a version number from the beginning of the standard was a shining example of why they hadn't needed a new version.

[alwa]

I didn’t get the sense from TFA that STRICT is strictly better, only that it’s how this person prefers to operate in their time and context and experience.

At some level, shouldn’t choices where one option is strictly better not surface as configurable choices at all?

If I have to memorize sets of behaviors by “compatibility version,” don’t I now have to remember lots of sets of particular footguns, across time and across systems that I work on (or parachute into)?

[sinfulprogeny]

Unless you don't know it exists of course

[alwa]

Is it better to learn that it exists through surprise, by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?

[thaumasiotes]

> Is it better to learn that it exists through surprise, by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?

The premise of your question doesn't work. If one person is going to learn about the option and a second one isn't, the better system for that to occur in is the one in which more people end up with correct behavior.

In this case, that means surprising the guy with existing (buggy) software; he'll have to fix the bugs. It means not bothering the guy who's starting a greenfield project. Neither of them will be subject to this bug.

The alternative, which you seem to be saying you prefer, is that the guy with buggy software should continue to have bugs, and the greenfield guy should also have bugs.

[inigyou]

How do you feel about Apple breaking all the apps on every major update?

[dwattttt]

Which is the worse surprise, when you update your dependency during development and get a fault, or when a user somehow ends up with a fault because of the previous default?

[esterna]

... than by surprise, when you find your integer column contains "[object Object]"? Jokes aside, breaking backwards compatibility is obviously a non-starter.

[justin66]

Developing with sqlite isn't quite like developing software that generically works against an ODBC or JDBC interface with minimal tweaking. You probably will want to peruse the documentation.

[walthamstow]

Even a pure AI naysayer can use it to find obscure SQLite options

[inigyou]

They already had this concern with WITHOUT ROWID. They recommend using WITHOUT ROWID whenever possible, but can't make it the default.

[formerly_proven]

The SQLite documentation doesn't say that. WITH and WITHOUT ROWID tables are also different data structures (ordinary tables are B+ trees, without rowid B*). In particular, without rowid tends to be detrimental for tables consisting of wide rows, while being an advantage for narrow rows with a non-integer key.