# 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:

```sql
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"](https://sqlite.org/flextypegood.html).

## 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]].