There is a somewhat recent STRICT mode that strengthens them: https://www.sqlite.org/stricttables.html
Saying that SQLite doesn't have "real types" is simply false. If one doesn't want to learn how to use a tool, blaming it for that failure is poor form.
INT
INTEGER
REAL
TEXT
BLOB
ANY
Unfortunately, that is not the same as typing. Sqlite lacks typing because, as the sqlite author explains in the docs, flexibility is the goal. He continues with "But other developers are aghast", and so strict tables where born, but you can clearly see this cannot overcome real concerns. Try to look for the DATETIME datetype in that list.
Deep bow to sqlite, its design goal was to be the ini file replacement and it has outperformed itself on that one.
Thanks for the extension link. Although constraints are not reuable type definitions, they would still be helpful in this context. Pity that json doesn't have a type for dates, one has to rely on string formats: https://json-schema.org/understanding-json-schema/reference/...
I don't consider this a valid distinction where databases are concerned.
If you define a datatype in an ordinary SQL database, and try to pass it invalid data, it will fail at runtime. How else could it work? There's no compile-time interaction between the value and the database.
If you define a field as BOOLEAN in Postgres, then the value must be 0 or 1, or the database will refuse to write it and return an error. In SQLite this is spelled INTEGER NOT NULL CHECK (col_name = 0 or col_name = 1). More verbose? Yes. Identical semantics? Also yes.
It would certainly be nice if SQLite had a datetime validator that could be used as a check constraint! Hold, up, I got you fam: CHECK(date IS strftime('%Y-%m-%d', date)). If you need a different format, those are,, available.
I guess it has a date type after all! Learn something new every day.
NULL
INT(EGER)
REAL
TEXT
BLOB
(INTEGER PRIMARY KEY)