STRICT Tables (3.37+)

Opt-in real typing

SQLite 3.37 (Nov 2021) introduced STRICT tables. Append STRICT to a CREATE TABLE statement and SQLite will reject inserts that don't match the declared types. This is the modern default for new schemas.

Strict mode allows only five types: INTEGER, REAL, TEXT, BLOB, and ANY. The relaxed affinity names like VARCHAR(255) are rejected at table creation — STRICT keeps you honest.

Tip: If you're starting a brand-new schema in 2026, default every table to STRICT. The dynamic-typing 'feature' was a deliberate compromise from the early-2000s embedded-C era; modern application code wants the database to enforce the contract.

One quirk: STRICT tables require all column types to be one of the five strict types. Migrating an existing schema means rewriting any VARCHAR, NUMERIC, etc. into the strict set. ANY exists for the 'I really do want a polymorphic column' case — like a value column in a key-value table.

Code

STRICT in action·sql

CREATE TABLE notes (
  id   INTEGER PRIMARY KEY,
  body TEXT NOT NULL,
  tags TEXT,                -- JSON array, opaque to SQL
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO notes(body) VALUES (42);
-- Error: cannot store INTEGER value in TEXT column body

INSERT INTO notes(body) VALUES ('hello');
-- OK

STRICT + ANY for polymorphic columns·sql

CREATE TABLE settings (
  k TEXT PRIMARY KEY,
  v ANY
) STRICT;

INSERT INTO settings(k, v) VALUES ('theme', 'dark');
INSERT INTO settings(k, v) VALUES ('font_size', 14);
INSERT INTO settings(k, v) VALUES ('debug', 1);

SELECT k, v, typeof(v) FROM settings;
-- theme    | dark | text
-- font_size| 14   | integer
-- debug    | 1    | integer

Exercise

Take the messages table you designed in lesson s01 and rewrite it as a STRICT table. Confirm the SQLite version on your machine is >= 3.37. Then attempt three inserts: a valid one, one with a wrong type, and one with a NULL where NOT NULL is set. Confirm SQLite rejects the bad ones with descriptive errors.

Opt-in real typing

Code

External links

Exercise

Progress

Comments 0