Prefer Strict Tables in SQLite

I'd like to see STRICT as the default.

That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!

The downside of strict tables is that some data types are not available, such as Date.

Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else.

On the other hand, the main use case for SQLite is embedded databases. And that means only one application is using the database. In that scenario being able to evolve the schema (as opposed to creating a new database and copying the data over) can be seen as an advantage. The application's code knows what to expect in each column--including mixed data types.

I would’ve thought this was the default.

I think I can see how dynamic data types make sense (eg flat key/value store), but my question would be:

What is least surprising? That INTEGER implicity accepts 'hello world' without error, or that you can't insert such a value unless you use a keyword like NONSTRICT or a type like ANY?

I would wager the vast majority of SQLite users if asked would probably not expect it to work.

It really should be default, but it isn't due to backward compatibility (i assume).

Using Entity Framework, this doesn't come up as a particular issue, but I still wish it were strict by default because I expect there could be some performance optimizations made for de/serialization.

really interesting, thanks

about the use of ANY, that's perfect for tracking changes on an audit table per field