Hacker News Reader

Comment by simonw | Hacker News Reader

SQLite has five types, documented here: https://www.sqlite.org/datatype3.html

    null, integer, real, text, blob

It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html

View on HN

> It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL)

Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package.

[0] https://www.sqlite.org/different.html

[1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...

Ah! I mean, only two years old, but OK ;P. Regardless: my point stands that you can trivially store and work with timestamps in SQLite as they are simply a real or integer or text; like, the idea that you need a special timestamp data type or you can't work with such values in an engine that only two years ago got support for checking the types at all and encourages you to just write code is highly strange.

JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another.

See https://sqlite.org/json1.html#value_arguments

The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.

Reply on news.ycombinator.com