This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this:

  uvx sqlite-utils transform data.db mytable --strict

  import sqlite_utils

  db = sqlite_utils.Database("data.db")
  db.table("mytable").transform(
    strict=True
  )

Here are the relevant docs:

- Using table.transform(strict=True): https://sqlite-utils.datasette.io/en/stable/python-api.html#...

- The sqlite-utils transform command: https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

> rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows.

That doesn't line up with my experience. (In particular, it may not be easy to fix those corrupted rows; the data may be entirely lost.)

> By suppressing easy-to-detect errors and passing through only the hard-to-detect errors, rigid type enforcement can actually make it more difficult to find and fix bugs.

This doesn't line up with my experience at all.

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

https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type...

If you’re going to work with a database through something like the Rust sqlx crate, I think you’re better to eschew strict mode.

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.

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.

  CREATE TABLE users (
    user_id CHAR(36) NOT NULL PRIMARY KEY CONSTRAINT user_id_length CHECK (LENGTH(user_id) = 36),
    email_address VARCHAR(255) UNIQUE CONSTRAINT email_address_length CHECK (email_address IS NULL OR LENGTH(email_address) < 256),
    role UNSIGNED TINYINT(1) NOT NULL CONSTRAINT role_valid CHECK (role >= 0 AND role <= 9)
  )

It's a little frustrating that all this extra cruft is necessary to get the world's most popular RDBMS to take data correctness seriously. I hope that some SQLite fork that behaves more like other RDBMSes when it comes to this stuff catches on some day, but the fact that that hasn't happened yet makes me think that the demand isn't there, somehow, unfortunately.

https://sqlite.org/lang_createtable.html#ckconst

“NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.”

0: https://sqlite.org/quirks.html