SQLite usually allows almost any data type in any column. That flexibility is useful, but it can also hide mistakes: for example, inserting garbage text into an INTEGER column.
The author recommends using strict tables. You only need to add STRICT at the end of the table definition:
CREATE TABLE people (age INTEGER) STRICT;🛡️ With this option, SQLite validates types when data is inserted or updated. A value such as '123' can still be accepted because it converts to a number without loss, but 'garbage' produces a clear error.
Strict tables also reject nonexistent or misspelled column types such as GARBAGE, JSON, or UUID. Only INT, INTEGER, REAL, TEXT, BLOB, and ANY are allowed. The latter preserves flexibility when it is genuinely needed.
⚠️ The main drawback is that an existing table cannot be converted directly: you must create another table, copy the data, and fix incompatible values. SQLite 3.37.0 or newer is also required.
💡 Explanation in a nutshell#
A strict table acts as a quality filter: it prevents each column from storing an unexpected type of data. For most applications, this early validation helps detect mistakes before they become difficult-to-trace problems.
More information at the link 👇
