EDIT: It looks like something that mostly addresses my problem, “strict tables”, was added in 2021. Thank you everyone.
It seems this rewrite would not have addressed my biggest gripe with SQLite: Surprisingly, it is not actually type safe.
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.
https://www.sqlite.org/datatype3.htmlThat’s right, the type you give to a column when creating a table is merely a suggestion. This means at some point, TEXT may make it into a column where a NUMERIC is expected.
This actually caused a large scale problem in production once.
I still love SQLite, it’s the very best at what it does, but I’m a bit more careful now. The same documentation says SQLite uses dynamic typing so that you can “do more things” than with other databases, but for me this is akin to saying you can “do more things with assembly than with a high level language” (in both cases that’s mostly not even true).
I expect my SQL database to keep data from taking the wrong form. It looks like the SQLite 4 rewrite would at least have enabled constraints by default…
I think many would appreciate a “no surprises” strict, type safe mode in SQLite 3, for me this would complete it. Maybe that exists already?
It seems this rewrite would not have addressed my biggest gripe with SQLite: Surprisingly, it is not actually type safe.
https://www.sqlite.org/datatype3.htmlThat’s right, the type you give to a column when creating a table is merely a suggestion. This means at some point, TEXT may make it into a column where a NUMERIC is expected.
This actually caused a large scale problem in production once.
I still love SQLite, it’s the very best at what it does, but I’m a bit more careful now. The same documentation says SQLite uses dynamic typing so that you can “do more things” than with other databases, but for me this is akin to saying you can “do more things with assembly than with a high level language” (in both cases that’s mostly not even true).
I expect my SQL database to keep data from taking the wrong form. It looks like the SQLite 4 rewrite would at least have enabled constraints by default…
I think many would appreciate a “no surprises” strict, type safe mode in SQLite 3, for me this would complete it. Maybe that exists already?