The Case for STRICT Tables in SQLite

SQLite’s default flexible typing lets you shove text into an integer column, invent nonexistent datatypes like UUID or JSON, and even omit column types entirely — all without complaint. Evan Hahn argues that developers should opt out of this permissiveness by appending the STRICT keyword to table definitions, a feature added in SQLite 3.37.0 (November 2021). STRICT tables reject type mismatches on insert and update, restrict columns to the six real SQLite types (INT, INTEGER, REAL, TEXT, BLOB, and the catch-all ANY), and require every column to declare a type. Lossless coercions still work, so the string ‘123’ remains valid in an integer column.

The tradeoffs are modest but real. You can’t convert an existing table to STRICT in place — you have to create a new table and copy the data across, which can surface latent bad values that were silently tolerated before. STRICT databases also can’t be read by pre-3.37.0 SQLite versions, and mixing strict and non-strict tables in one codebase risks inconsistent validation. Performance testing across millions of rows showed no measurable penalty.

The broader point is a familiar static-versus-dynamic typing debate, and notably SQLite’s own maintainers land on the other side, publishing a page defending flexible typing for use cases like key-value stores and messy CSV imports. Hahn concedes those cases exist but comes down firmly on the side of loud failures over silent data-integrity bugs — a reasonable default for teams that want their databases to catch mistakes rather than absorb them.