SQLite Strict Tables Prevent Silent Type Bugs

SQLite's loose typing has been hiding bugs in your data for years. STRICT tables fix that with one keyword.

Dafallah Ibrahim

— 2 min read
Share

SQLite has a dirty secret. You can insert the string "hello" into an INTEGER column and it won't complain. It stores the string. Query it back later and you get "hello", silently, forever.

This "type affinity" system has caused data corruption in production for years — mismatched types in joined columns, broken aggregations, queries returning wrong results without throwing a single error.

Why this matters

SQLite runs in more production systems than ever: mobile apps, edge databases, embedded devices, distributed platforms like Cloudflare D1 and Turso. Most developers assume the schema enforces types. It doesn't — unless you explicitly ask.

How it works

Since SQLite 3.37.0, you can add the STRICT keyword to a table definition. This flips SQLite from advisory type affinity into hard type enforcement.

Without STRICT, a column declared INTEGER has INTEGER affinity — a preference, not a rule. Insert a string and SQLite shrugs. With STRICT, the declared type is the law. A type mismatch produces a runtime error at insert time, exactly like Postgres or MySQL would.

STRICT mode allows exactly five type names: INTEGER, REAL, TEXT, BLOB, and ANY. Any other type name causes a parse error at table creation — which means typos like ITNEGER fail immediately instead of silently creating a column with no affinity.

Where this helps

  • Application databases — catch type mismatches at write time instead of discovering them during a debugging session three months later.
  • Local test parity — if you run SQLite for fast tests but Postgres in production, STRICT narrows the behavioral gap between them significantly.
  • Edge and embedded systems — SQLite is often the only database available. STRICT gives you type guarantees you'd otherwise assume were missing.
  • Schema validation — unknown column types are rejected at creation, so ORM-generated DDL with custom type names surfaces errors before a single row is written.

Watch out

STRICT requires SQLite 3.37.0 or later. Older distributions — common in long-term Linux releases and some embedded environments — will reject the keyword entirely. Check your version with SELECT sqlite_version();.

You can't alter an existing table to STRICT in place. You need the standard SQLite table rebuild: create a new STRICT table, copy data over, drop the old table, rename. Most ORMs don't generate STRICT by default, so expect to write raw DDL or a migration hook.

The ANY type in STRICT mode accepts all values, effectively making that column non-strict. Useful for JSON or opaque blobs, but don't reach for it reflexively — it defeats the purpose.

Try it yourself

sqlite3 test.db

-- Without STRICT: silent coercion
CREATE TABLE loose (id INTEGER, name TEXT);
INSERT INTO loose VALUES ('banana', 42);
SELECT typeof(id), typeof(name) FROM loose;
-- text | integer   ← silently wrong

-- With STRICT: caught immediately
CREATE TABLE strict_tbl (id INTEGER, name TEXT) STRICT;
INSERT INTO strict_tbl VALUES ('banana', 42);
-- Runtime error: cannot store TEXT value in INTEGER column

-- Typos fail at creation too:
CREATE TABLE typo (id ITNEGER) STRICT;
-- Parse error: unknown column type

TL;DR

  • What changed: SQLite 3.37.0 added STRICT tables that enforce real column types instead of using loose type affinity.
  • Why it matters: Silent type coercion corrupts data without a single error — STRICT prevents that at the storage layer.
  • What to try today: Add STRICT to your next CREATE TABLE and watch old assumptions break immediately.

Read more