alt Hacker NewsIt gets praise because of stuff other than the type system.
I agree it is disappointing, especially before strict tables.
You should check out DuckDB which is basically SQLite but with proper types. Although it is also OLAP (struct of arrays) rather than OLTP (array of structs) which may have worse performance for typical SQLite loads. In practice I doubt it matters if you have an application where you're considering either.
A lot of oltp databases have modeling conventions for making read only reporting tables. Do tabular dbs have an inverse for transaction heavy data, that later gets batched to a read optimized structure? I kind of think most databases (even oltp workloads) really are read dominated. I feel like DuckDB is really close to working as the ‘main db’ for such systems, but my lack of knowledge for how to handle quick mutations bothers me. It feels like some form of temporal data modeling would solve it but I don’t know.