51–60 of 188 posts
I had a UUID (partly?) mis-converted to a number if the UUID started w (from memory) something like 08123… which was parsed as octal. Confusing, annoying, fixed w “strict” and a complete table rebuild.
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!
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…
(please note that I personally strongly prefer static types, but I still found this an interesting read).
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!
Well, I would also like a proper datetime/timestamp datatype that isn't just a string.
Strict type all the things.
The downside of strict tables is that some data types are not available, such as Date. Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else. On the other hand, the main use case for SQLite is embedded databases. And that means only one applicati…
> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html
the only thing that sucks about SQLite is migrations.
They DO include a nice section at the bottom about why these limitations exist, but I wish they would make the process easier.
Earlier quoted context omitted.
You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.
Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.
You would only inherit a project where everything was ANY if anything could go anywhere.
With SQLite's default behavior, anything can always go anywhere, so the type definitions are at best semi-accidentally observed by the code, and at worst completely misleading. You have no idea which of the two the developer intended.
I get the impression that this SQLite behavior is a historical oddity caused by the original use case for the tool, rather than something that was intentionally planned and thought through, and was later retconned to be intentional and benign. To me, it makes no sense, even after reading the explanation on sqlite's website.
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!
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.
The downside of strict tables is that some data types are not available, such as Date. Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else. On the other hand, the main use case for SQLite is embedded databases. And that means only one applicati…
> some data types are not available, such as Date. That’s not a type, you just get a numeric-affinity column.
Earlier quoted context omitted.
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.
They already had this concern with WITHOUT ROWID. They recommend using WITHOUT ROWID whenever possible, but can't make it the default.
Yeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.
Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting integers where your static analysis showed that it inserts strings.
SQLite is not like Postgres, which is designed for many different applications all sharing the same data, where you have to place trust in third-parties to also do the right thing. Runtime validation is critical in that environment. SQLite is designed for one application, one database. While it technically can support multiple applications sharing the same file, support is poor and it is not really designed for that. In the typical case, the only trust you need is your code, which you can evaluate at compile time. For the atypical cases you can enable strict tables.