Hacker News Reader

Comment by jll29 | Hacker News Reader

I'd like to see STRICT as the default.

That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!

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

1. https://sqlite.org/foreignkeys.html

2. https://sqlite.org/forum/forumpost/1b9d073a37ca5998

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.

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.

Unless you don't know it exists of course

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

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?

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

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?

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

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

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.

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.

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

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

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

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.

I just do it in my code like

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

That relies on you knowing about what good settings are though

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

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.

I knew most of he issues that you mentioned ... but 'ID re-use in AUTOINCREMENT'?

What the ...?

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

https://github.com/simonw/sqlite-utils/issues/344#issuecomme...

I guess for ID reuse a rationalization could be that theres only so many integer values that can fit into a certain number of bytes haha

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.

> This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19 (2009-10-14).

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.

Yes.

From the release notes:

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)

Thank you. Strange decisions, but not completely baffling then.

Well, I would also like a proper datetime/timestamp datatype that isn't just a string.

Someone should fork it in Rust using Claude and rename it SQRite.

Strict type all the things.

Why not ~Zoidberg~ Zig?

A large portion of the tests are closed source unfortunately which would make it tough to create a port.

What the hell? This is the first I'm learning of this.

Why did they do that? Is it owned by a private company?

https://www.hwaci.com/

See https://news.ycombinator.com/item?id=23511151

That's one way they make money. That's the reason sqlite exists.

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.

[0] https://corecursive.com/066-sqlite-with-richard-hipp/

[1] https://youtu.be/5zQdYx-fqJg?t=300

They sell several niche things, for hopefully hundreds of thousands of dollars, to just a few people each. TH3 is just one instance of the general pattern.

https://github.com/tursodatabase/turso

Why not store them as integers? Representing dates is a UI responsibility

I also want to be able to debug the database in an SQL console with nice looking date-times.

You can already do that: https://sqlite.org/lang_datefunc.html

Just to state the obvious, you can create a view which uses these functions to make your integer date/times readable, and then debugging is easy.

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.

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.

Only if you're not compiling yourself, otherwise, there's SQLITE_DEFAULT_FOREIGN_KEYS=1

Of course you can store timestamps as integers, but then you lose sqlite's built-in date and time functions.

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

When you need many booleans on a table, use an integer with bitwise and/or to record them as powers of 2.

I have done this many times. Function-based indexes are necessary if they must be searched.

Why would you do that? To save some bytes in storage?

The largest table where I've done this had over 50 of these booleans.

That's one 64-bit number, or 50 different columns.

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.

For the searched column only?

If you are greatly concerned, SQL Server and the Sybase database from which it emerged have a native boolean data type.

Another benefit of this scheme is that adding another boolean means using the next power of 2 in the existing integer, assuming room remains. No new column necessary.

When the column is an integer column, you don't want strings in it.

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.

the SQLite team explains their preference for dynamic types here: https://sqlite.org/flextypegood.html

(please note that I personally strongly prefer static types, but I still found this an interesting read).

"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?

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.

lmao default okay buddy

I agree with you. I'd go one step further and let it be the only mode available starting with new versions of the library.

Reply on news.ycombinator.com