SQLite added strict mode recently, check it out. Agreed time handling is sub par - could use builtin date formatting from epoch to ISO which would fix all the problems IMO.
I know about STRICT tables [0], but they still follow the quirky coercion rules. The reasoning seems to be that other DBMs have a similar behaviour. However, I want _errors_ if I insert '123' into an INT column, so it's easier to find problems in my code.
The quirky coercion rules that PG, MySQL, SQL server and oracle also all follow? Let’s be clear, if this is a problem it’s a problem with all SQL DBs, not just SQLite.
I’m curious why ‘123’ in an INT column is so bad? I suspect the conversion rules are in place because they shouldnt ever cause logical errors.
I personally appreciate using created_at < ‘2021-05-23’ in Postgres queries. The query would only be more verbose if I had to explicitly construct a date object for it.
The reason for that being not that good basically has the same reason as with coercion rules in weakly typed languages like JavaScript.
If I'm passing a string to an int column, there is most likely an issue in my application code. If there currently is none, there might will be.
For example, I might have forgotten to parse the string properly in my application code. If I'm doing '10' * 2 in JS, it returns 20. If I later change it to '10' + 10, it will be '1010'.
Say I then save the result of that compilation in the DB. Raising on '1010' would have prevented me from persisting the error and gave me an opportunity to investigate the situation. Without an error, there will be a much harder debugging session.
These coercions were popular back in the 90s/00s, which is why I think most DBMs have them. At least that's why JS has it.
- A type for Instants / time handling
- being strict with types. No inserts of ints into a string column