I don't really see it as such a problem. When interacting with the database you either use an ORM with your strongly-typed business objects or you write the SQL and use parameters (again strongly typed)

> When interacting with the database you either use an ORM with your strongly-typed business objects

If you have strongly-type business objects then why not have a strongly typed storage? If you code can control the constraints that a correct data type ensures, then why have "strongly type business objects" to begin with? Why not store everything in your code as strings as well?

I see this misconception all the time. The database (and its data) lives way longer than most applications. And it's also a wrong to assume that there is always only one application accessing the database. Bulk loads are a typical case of secondary applications.

Not choosing the proper data type in a relational database is a really bad decision and we see question on stackoverflow and similar sites on a weekly (if not daily) basis asking how to fix invalid data in those "un-typed" columns.

I'm not saying it's useless to have type constraints in a database. Just that with a bit of care it's not such a big deal.

In the end there will always be some business rules that are not constrained by the database. So, you always have to be a bit careful about what you store in it. Indeed not being careful and hoping that your types, constraints and triggers are going to save you is more risky

> Not choosing the proper data type in a relational database is a really bad decision

Well, then rejoice, you can't make this bad decision in SQLite because everything is +/- a number or a string

SQL parameters aren't strongly typed though and in any case SQLLite doesn't have stored procedures.

It's a feature:

https://stackoverflow.com/questions/29476818/how-to-avoid-in...

That is... interesting...

I can't believe that runs. What a misfeature.

You just put me off Sqlite for life. I can't believe someone thought this to be a good idea.

SQLite just does what JavaScript and other weakly typed languages do. The developer is responsible for what they input. If type checks are a must, SQLite is just not for you, easy as that.

But event JavaScript will reject data if it's considered not valid for a type.

It won't throw but it will set an "invalid" / "NaN" / etc. value.

I don't see how having "NaN" or "undefined" in a number field makes any difference to just putting the value there. If you are using a strongly typed language, this will not be a problem.

SQLite made a design choice in favor of simplicity. It's also missing basic date functions all together. The only way to compare dates is by using Unix Epoch.

I use ISO 8601 full timestamps in UTC.

They were carefully designed so that collation order is identical to temporal order. Which is convenient!

If you need interval logic, though, SQLite won't help you, and epoch is the better choice. It's possible to solve some queries with a regex, but you won't love it.

Chill out. This is a design choice to make it more viable to be used in dynamically typed languages. It's maybe not the design choice everyone would make, but it's not, like, insane: this is how all variables in Python/JavaScript/Ruby/whatever works.

If you really care about this, adding strongly typed columns is trivial: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9baffa184672a7...