SQLite happily stores the text 'garbage' in a column typed INTEGER

The one keyword that makes SQLite stop guessing

Evan Hahn's post, *Prefer STRICT tables in SQLite*, opens with that exact insert: text going into a column declared INTEGER, no complaint from the database. It's not a fluke. SQLite also accepts a column declared as a type it's never heard of, like DATETIME, JSON, or a typo such as BLOBB. The database doesn't check declared types against any real list unless told to.

Telling it to is a one-line change. Append STRICT to a CREATE TABLE statement, like CREATE TABLE people (name TEXT) STRICT;, and SQLite starts enforcing what it already declares. Strict tables narrow column types to a short, real list: INT, INTEGER, REAL, TEXT, BLOB, plus one escape hatch called ANY that keeps a single column fully flexible even inside an otherwise strict table. Lossless conversions still happen quietly, so the string '123' still becomes the integer 123, but nonsense no longer slips through disguised as data.

Hahn flags two costs himself. There's no ALTER TABLE path from a non-strict table to a strict one; the only route is creating a new table and copying every row across. STRICT also needs SQLite 3.37.0 or newer, released in November 2021, so any tool built against an older SQLite can't even open a database file that contains a strict table. He tested for a performance penalty and found none worth reporting.

None of this is what SQLite's own maintainers recommend. Their published rationale argues flexible typing suits key-value-style storage and messy data imports, and that rigid enforcement can hide certain bugs as easily as it surfaces others. Hahn's post is a quiet disagreement with the people who built the database he's telling everyone to lock down.

The thread takes SQLite's own defense apart

Dan Fabulich, co-founder of the interactive-fiction publisher Choice of Games, went straight at that same rationale document, quoting its claim that type mistakes are "easy to spot" once they happen:

"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.)" — Dan Fabulich, co-founder of Choice of Games

Someone else supplied the failure Fabulich was describing. A UUID is supposed to be an opaque string, but under SQLite's default typing a number-shaped one can get coerced into an actual number:

"I had a UUID (partly?) mis-converted to a number if the UUID started w (from memory) something like 08123… which was parsed as octal. Confusing, annoying, fixed w 'strict' and a complete table rebuild." — an engineer who fixed it only by switching to STRICT and rebuilding the table

Hahn's post names one open problem: no ALTER TABLE path to strict, only copy-and-recreate. Simon Willison, creator of Datasette, read that as a missing feature rather than a permanent limitation:

"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)." — Simon Willison, creator of Datasette

The command is uvx sqlite-utils transform data.db mytable --strict: the migration Hahn described as manual copying, shipped as a single CLI flag within the same news cycle as the post that pointed out the gap.

Not everyone in the thread wants STRICT everywhere. The clearest dissent came from a Rust consultant available for hire in web and performance work, who works with the sqlx crate that many Rust programs use to talk to SQLite directly:

"I don't like strict mode because it quite unnecessarily thwarts better strict types in the application layer: by restricting the spellings of column types, it stops you from using more meaningful names… 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." — a Rust consultant available for hire in web and performance work

His objection isn't about data corruption. It's that STRICT's short list of legal type names collides with a separate convention some tools rely on: using column type names to carry meaning for the application layer, which sqlx and similar crates read back out. Fix one class of bug and you can break a different piece of tooling that was quietly depending on the old flexibility.

Nobody in the thread disputes that STRICT catches real bugs. The open question it leaves is narrower: whether your other tools read column type names for their own purposes, because that's the one place turning it on can cost you something you didn't expect.