> That's what enums/unions are for.

I don't think providing unions or algebraic data types are well supported in most RDBMS systems, they're such a pain to build.

> I find it hard to believe that static type checking

It's not static type checking. Errors happen in runtime, not compile time. In compile time there's no data yet.

If you need static type checking you're most likely to do it in your programming languages. If you want static type checking against the database schema you need languages with type providers like F#.

If you want to ensure business invariants, it's not enough to just use rigid tables. Plus those invariants are better to be enforced in the model because you can write better tests against them. There's no point to write it twice as there would be inconsistencies.

As a personal experience, being unable to express algebraic data type often leads to bad design/modeling, as developers don't have to tool to model things are they are, they have to use subpar workarounds.

Yup, the only practical difference between the behavior of SQLite and other databases is whether you get an error if you try to insert values that can't be converted to the declared column type. You'll need application logic to handle such errors in any case, so in principle I tend to agree with the SQLite devs that more rigid type checkin doesn't really buy you much.

Though in practice I think the more lax behavior makes it easier to skip or defer error handling. Also, devs need more awareness of if these (differences in) behaviors. It seems an alarming number of them don't.

> I find it hard to believe that static typechecking is not advantageous compared to checking for every returned value whether it's undefined or null or a string instead of the expected array.

+1. A simple way to think of this is that realize that strict type checking significantly reduced the number of possible codepaths, so the cognitive load on you is much smaller making it less likely for you to make mistakes.

With static typing, what you should also practice is using the strictest type possible, e.g., don't use a pointer if null has no meaning for you, don't use a vector if the size of the continer is fixed, etc.

I feel like you are talking about types in programming languages but Object–relational impedance mismatch is still a thing and I'm not sure if you want a solution (for the mismatch) at DB level.