Even foreign keys aren't enabled by default, you have to use `PRAGMA foreign_keys = ON;` [1]. The bigger issue with strict tables is that there is no equivalent pragma, and you're forced to use the non-standard STRICT on each CREATE TABLE. A global STRICT pragma was considered but not implemented, see this forum thread [2].
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.
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.
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.
> 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.
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)?
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?
> 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.
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?
... than by surprise, when you find your integer column contains "[object Object]"? Jokes aside, breaking backwards compatibility is obviously a non-starter.
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.
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.
SQLite has a LOT of footguns that one only discovers over time. Dynamically typed by default, Off-by-Default foreign keys, ID re-use in AUTOINCREMENT, WAL Mode needing explicit enabling to ensure readers are not blocked, double-quote/single-quote issues, positional placeholders & named parameters issues, no TIMESTAMP type in 2026 despite being a CORE feature in SQL-92 standard, etc.
"Enforce authoritarian type-checking when inserting new content into tables" lol you can really get into the mindset by reading these threads. I think having the strict defaultable at the database level seems like a good option, maybe they think everyone will pile into the new default and leave the old databases in a state of decaying disrepair?
My only experience with versionitus type things is with microsoft sql and other "enterprisey" things that used it and its hard enough without huge upgrade blockers like text in an integer field lol still they're willing to add the default to the table create statement. I'm thinking that sqlite folks feel that things like type safety, database consistency are disliked authoritarian attributes, and I'm sure thats a consideration in letting old code touch databases that old code doesn't understand.
Its wierd that they didn't like having a pragma for any new table creation even at the database level but they allow this problem factory:
"Because of a quirk in the SQL language parser, versions of SQLite prior to 3.37.0 can still read and write STRICT tables if they set "PRAGMA writable_schema=ON" immediately after opening the database file, prior to doing anything else that requires knowing the schema."
There are more similar issues, like disabling foreign key constraints by default "for compatibility reasons". Makes me wonder if there was a time when SQLite supported foreign key syntax, but didn't actually implement the functionality.
That quote leaves open whether SQLite "pretended" to support foreign keys by allowing to create tables with them, but didn't implement them. Otherwise, I don't see the compatibility problem.
2002-06-17 (2.5.0), "Parse (but do not implement) foreign keys."
At one point there was also a tool which would generate trigger rules to enforce foreign key constraints. (2008 Oct 15 (3.6.4), Added the source code and documentation for the genfkey program for automatically generating triggers to enforce foreign key constraints)
Forcing all dates to be cast to a single time zone (UTC) complicates workflows when you’re on the other side of the globe. Many DBs have added a timestamp offset type for this to keep the data in the offset it occurred in.
In a 2021 podcast interview[0], Dr. Hipp noted they've sold zero copies of the TH3 (extensive, proprietary) test suite in SQLite's history.
By the time TH3 was added in 2008, SQLite had gained a fair bit of traction across multiple industries. Though I totally agree that the comprehensive coverage is a leading reason why they've been so stable over the last ~20 years.
So indirectly, TH3 is why they (continue to) exist and (are able to) make money, but it isn't a direct line as one might assume.
Yeah it's a really weird design decision. Why would I want the database to let me accidentally insert the wrong type? SQLite is mostly great but its philosophy towards type safety leaves something to be desired. I once had to clean up in a project where someone had accidentally stored the strings '1' and '0' in a Boolean column in code deployed to thousands of devices; not fun.
Another thing I dislike is the lack of timestamp types. Instead, you're expected to just use a text column and store a textual timestamp. Even worse, instead of using ISO, the standard date time functions produce strings on the form "yyyy-mm-dd HH:MM:SS" which you're just supposed to assume are in UTC. Why not at least give us "yyyy-mm-ddTHH:MM:SSZ"? Or, you know, a proper space efficient timestamp data type.
A truly great project, with some truly baffling design decisions.
> Instead, you're expected to just use a text column and store a textual timestamp.
You can actually use an integer column and store Unix timestamps (or floats for subsecond accuracy).
But yes, sqlite has very little types support and its default behaviour is very much unityped / dynamically typed which I also dislike. Same with having to enable foreign keys every time you open a connection.
You do not. SQLite date/time functions work with time-values which can be ISO timestamp strings, Julian fractional day numbers, or Unix timestamps. See https://sqlite.org/lang_datefunc.html for the details.
If every time the SQLite team added a better default for something they changed it, we'd be at SQLite 11 by now and every application would contain at least six incompatible versions of SQLite.
'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type.
Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc
But if you do the function based index, you actually increase the stored size, right?
Seems like a microoptimization to me that I would only do if I really have to.
"SQLite began as a TCL extension that later escaped into the wild." case closed, everything else is a rationalization but who doesn't like a good rationalization every now and then?
If you are using SQLite as an embedded database, which seems to be SQLite's primary use-case, why wouldn't you prove statically that you are not accidentally inserting the wrong type? Runtime checks are unnecessary overhead.
Runtime validation is there to enable when using SQLite in other ways.
Yes. I always considered that a downside of SQLite. You have to validate numeric fields on the read side or risk the application blowing up on bad data.
1. https://sqlite.org/foreignkeys.html
2. https://sqlite.org/forum/forumpost/1b9d073a37ca5998