Oh, that's a bit scary. Both for use as a file format (in case of attacker supplied db file) and more general.

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

https://www.sqlite.org/datatype3.html

Firebird (https://www.firebirdsql.org) can essentially work that way. It comes with two versions of the client library. One of them runs the database engine in-process (embedded mode), similar to SQLite. Later you can switch over to a separate server process, like with PostgreSQL. Though both those two set the bar pretty high, Firebird is a solid RDBMS that’s been around for a long time, with a well-balanced feature set (including strict data typing).

I think it's a design choice. Ignoring types lets you focus on other features, in case of SQLite, things like crash resistance and reliability.

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.

Interestingly, it's amazing to me that many SQLite libraries in the Go ecosystem don't support these mixed/flexible data types in tables.

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.

SQLite originated as a TCL extension, so it would make sense for it to share TCL's (notoriously lax) philosophy regarding types.

It's really not a problem. I use statically typed languages (mostly Go and Rust), and everything just works as long as my database later retrieves and stores the right types. The only slight complexity is currency, which would be stored as an interger because there's no decimal type.

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.

I have recently been thinking if it were possible to marry pg query engine with sqlite storage layer and c api. Idea being that you could get all the rich types etc from pg with the embeddability of sqlite.