Hacker News Reader

As long as an app is architectured around having a data layer with "typed" objects, the need for SQLite to support strict column types is not too important.

However, it does make handling more "advanced" types less standard. For example, there isn't a standard way to store a date, especially if we want to preserve high-precision (nanoseconds).

View on HN

It’s actually really important in some edge cases. For instance, I have a column that is declared as text but I read and write to it via the blob api (because the source is utf8 bytes I don’t want to parse client-side for allocation reasons). The flexible typing ended up - silently - storing the fields as blobs, but comparisons with strings continued to work (“foo” == table.col) until I tried changing that to a pattern (table.col like “%foo”) at which point there were runtime exceptions (iirc). I had to update all existing data to cast the columns to the correct type, whereas the original insertion query should have been doing that from the start, but SQLite was too flexible for my own good.

> especially if we want to preserve high-precision (nanoseconds).

Why not just by convention as ints, nanoseconds UTC post epoch?

because the lack of a type annotation via introspection makes tools automatically generating code for your less than useful.

Reply on news.ycombinator.com