Prefer Strict Tables in SQLite

Evan Hahn argues for adding STRICT to SQLite table definitions. A strict table enforces fixed column types the way most SQL engines do, instead of SQLite's default behavior where a column type is a suggestion rather than a rule. The change is one keyword at the end of the CREATE TABLE statement:

CREATE TABLE people (name TEXT) STRICT;

What the default does: type affinity

In an ordinary SQLite table, a column's declared type is not a constraint on what you can store. SQLite assigns each column a type affinity — a preference for how it will try to convert incoming values — and then stores whatever it ends up with. If a value can be losslessly converted to the affinity, it is; otherwise the original value goes in unchanged. So an INTEGER column happily accepts 'garbage', and the string stays a string. The affinity system is why '123' and 123 both land as the integer 123, but '1O' (with a letter O) inserted into an integer column stays the text '1O' — the conversion isn't lossless, so SQLite keeps the value rather than reject it.

The permissiveness extends to schema definition. SQLite lets you declare columns with types it doesn't have: DATETIME, JSON, UUID, or an outright typo like BLOBB all parse fine and get bucketed into an affinity by pattern-matching the type name. The declared type is documentation at best and a silent lie at worst, because nothing enforces it.

This traces back to origins. As one of SQLite's contributors explained on HN (rogerbinns), SQLite started as a local dev database built on dbm, where every stored value was effectively a string and the code auto-converted on demand — the + operator would parse strings to numbers, add them, and stringify the result. TCL, the wrapper language, worked the same way. Only SQLite 3 (2004) introduced a real storage backend with distinct types. Flexible typing is a survival of that "everything is a string" lineage, later documented as a deliberate feature in "The Advantages Of Flexible Typing".

What STRICT changes

A strict table enforces the declared types:

• Inserting or updating with the wrong type is rejected. Text into an INTEGER column errors instead of silently storing the string. Lossless conversions still pass — '123' into an integer column is fine — so the check is about representability, not literal type.
• Column types must be real. Only INT, INTEGER, REAL, TEXT, BLOB, and ANY are allowed. Bogus types like DATETIME or UUID error at CREATE TABLE time, catching typos and misunderstandings about which types SQLite actually has.
• Every column needs an explicit type; CREATE TABLE tbl (name) is rejected.

If you genuinely want a mixed-type column, ANY is the escape hatch: it accepts integers, text, floats, and blobs even inside a strict table. The difference from the default is that ANY states the intent explicitly, rather than every column silently behaving that way.

This maps onto the type-system-axes distinction. SQLite's default is the database equivalent of weak, dynamic typing — types are checked late (or never) and values get implicitly coerced. STRICT moves it toward strong typing: coercion only where lossless, hard rejection otherwise. It's the same argument the type-systems-vocabulary note makes about calling this "strict" being imprecise — the real axis here is how much implicit coercion the system tolerates, not when checks happen. A parallel appears in unsigned-sizes-c3-mistake: a permissive default (unsigned sizes, flexible columns) that seems convenient but quietly admits a class of bugs the stricter option would have rejected loudly.

Costs and caveats

You can't ALTER an existing table into strictness. The migration is manual: create a new strict table, INSERT ... SELECT the data across, drop the old table, rename the new one. If the old table already holds type-violating data, the copy step fails and you have to clean or CAST it first — which is the check doing its job, just later than you'd want.

Strict tables need SQLite 3.37.0 (November 2021) or newer. A database containing a strict table also can't be opened by older SQLite versions at all, even for reading unrelated tables.

Performance is a theoretical concern — the engine does a type check on each insert/update — but Hahn's informal test (millions of rows, 100 columns) showed no measurable difference and identical on-disk size. He speculates strict tables might even be marginally faster by avoiding affinity mismatches, but didn't measure it.

The SQLite developers themselves don't share the preference; their flexible-typing page lists legitimate uses like pure key-value stores, grab-bag attribute columns, and importing messy CSVs where you'd rather keep every value than lose it to a rejected insert. STRICT will not become the default: as Simon Willison noted in the discussion, SQLite treats backwards compatibility as near-sacred and won't silently turn every CREATE TABLE strict. The same reasoning keeps foreign key enforcement off by default (though SQLITE_DEFAULT_FOREIGN_KEYS=1 flips it if you compile your own build) and WITHOUT ROWID opt-in.

Hahn's practical stance: prefer strict from the start; a rule that all new tables are strict is a reasonable middle ground, at the cost of inconsistent enforcement across a schema. Related SQLite type gaps came up in the thread too — there is still no dedicated timestamp type, though integer Unix timestamps work with the built-in date/time functions. For index-level footguns in the same family of "the database quietly did something you didn't intend," see database-index-gotchas.