A lot of people in the HN community like types. Me included. I really don't want my database to ignore type errors. I basically want Postgres but embedded like a library. I cannot think of any reason why that couldn't work. Perhaps just no one has built it yet.
I see that even with "INTEGER" affinity, I can insert and read back text to columns :/
sqlite :memory:
CREATE TABLE test(
x INTEGER, y TEXT);
INSERT INTO test
VALUES("a", "b");
SELECT * FROM test;
a|b
In other words, typechecking is also a cost for language designer.
Even SQL itself has not particularly strong type system. Perhaps you're looking for something else entirely.
If you read back a set of values and one of the fields in a row doesn't match the data type given by the column, the libraries will error out.
One library that works correctly is github.com/gwenn/gosqlite, which checks the data type of each field of the result set (per row), so handles things gracefully.
Unless you have really strict requirements (generally not the case), SQLites storage classes (NULL, INTEGER, REAL, TEXT, BLOB) are sufficient. If it really bothers you, you can use extensions to provide whatever abstractions you like. If you're mostly doing CRUD, you really shouldn't have any issues.