3 ms·
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 ad
by anyfoo 3y ago
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.html https://www.sqlite.org/datatype3.html
That’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?
- npn 3y agoYes, create your table with a trailing strict statement.
- deleted 3y ago[deleted]
- gavinhoward 3y ago> 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 does, somewhat. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- bruce_one 3y agoYou're looking for [strict tables](https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html) and they do already exist :-) Added in 3.37.0 (2021-11-27), apparently :-)
- deleted 3y ago[deleted]
- acrispino 3y agoStrict tables were added with 3.37.0, does that help? https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY
- skissane 3y ago> One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY I wish they’d implement CREATE DOMAIN, like Postgres has. Using it you can define a named custom type from a base type, optionally with associated CHECK constraints. A minimal implementation would skip the constraints (and DEFAULT and collation) and just let you do CREATE DOMAIN timestamp AS INT (or TEXT if you’d rather do it that way), and thereafter STRICT tables could have timestamp as column type Previous comment of mine which explains this in more detail: https://news.ycombinator.com/item?id=29367517 https://news.ycombinator.com/item?id=29367517
- deleted 3y ago[deleted]