SQLite STRICT Tables: Enforce Column Types Properly

Why STRICT Tables Exist

SQLite's default behavior with types is famously relaxed. Declare a column INTEGER, insert the string "hello", and SQLite shrugs and stores the string. That flexibility was a deliberate design choice in the 90s, but it surprises people coming from Postgres or MySQL — and it hides bugs.

STRICT tables, added in SQLite 3.37, fix that. You opt in per table, and from that point on the column types mean what they say.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

The STRICT keyword goes after the closing parenthesis. Everything else looks like a normal CREATE TABLE. The difference shows up the moment you try to put the wrong kind of value in a column.

What STRICT Actually Enforces

In a regular table, type affinity tries to convert a value to the declared type and falls back to storing it as-is if it can't. In a STRICT table, a mismatch is an error.

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- ok
INSERT INTO accounts (balance) VALUES ('100');   -- ok, '100' converts cleanly
INSERT INTO accounts (balance) VALUES ('oops');  -- error

Try the same against a non-STRICT table and the third insert succeeds — SQLite cheerfully stores the string 'oops' in a column you said was INTEGER. Months later, an aggregate query returns nonsense and you spend an afternoon hunting it down. STRICT makes the failure happen at insert time, where you can fix it.

The error you'll see:

Runtime error: cannot store TEXT value in INTEGER column accounts.balance

Clear, immediate, hard to ignore.

The Five Allowed Types

STRICT tables only accept five type names:

• INTEGER — whole numbers.
• REAL — floating-point numbers.
• TEXT — strings.
• BLOB — raw bytes.
• ANY — any type, no conversion.

That's it. The casual aliases SQLite normally accepts — VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT — all raise an error inside a STRICT table:

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

The error:

Parse error: unknown datatype for bad.name: "VARCHAR(255)"

The fix is to use one of the five canonical names. VARCHAR(255) becomes TEXT, DATETIME becomes TEXT (SQLite stores dates as ISO strings anyway), BOOLEAN becomes INTEGER (with 0 and 1).

The ANY Escape Hatch

ANY is the one type that lets a STRICT column hold heterogeneous values — useful for things like a generic value column in a key/value table:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY is special inside STRICT tables: it stores values without the type-coercion that the same word would imply elsewhere. A '100' string stays a string; a 100 integer stays an integer. The typeof() calls in the query above prove it.

In a non-STRICT table, an ANY-affinity column would coerce numeric-looking strings to numbers. STRICT preserves the original type exactly.

STRICT and PRIMARY KEY

One subtle difference: in a regular table, INTEGER PRIMARY KEY is special — it becomes an alias for rowid and accepts only integers. Other primary key declarations are looser.

In a STRICT table, the column type is enforced regardless of whether it's the primary key:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Widget', 9.99);
INSERT INTO products VALUES (42, 'Gizmo', 14.99);  -- 42 converts to '42'? No — error.

SELECT * FROM products;

The second insert fails. In a non-STRICT table, 42 would be silently stored in the TEXT primary key column. Here, you get told.

Mixing STRICT and Non-STRICT Tables

STRICT is per-table, not per-database. You can have a strict users table and a relaxed events table in the same file. Foreign keys work across them just like they would otherwise.

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- no type declared; affinity rules apply
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'login');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

The events table has no STRICT and no declared type on payload, so it accepts whatever you throw at it. Useful occasionally — risky as a default. Reserve untyped storage for cases where you genuinely need a grab-bag column.

When to Use STRICT

For new schemas, the answer is "almost always." The cost is small — one keyword per table, and remembering the five canonical type names. The benefit is that bugs which would normally lurk in your data show up at the insert that caused them.

Skip STRICT when:

• You're maintaining an old SQLite database where the existing schema relies on relaxed typing.
• You're targeting SQLite older than 3.37 (October 2021) — the keyword doesn't exist there.
• You actually want a column to hold mixed types; even then, prefer STRICT plus an ANY column over a non-STRICT table, because everything else stays enforced.

A short checklist when converting a regular table to STRICT:

• Replace VARCHAR, CHAR, NVARCHAR with TEXT.
• Replace DOUBLE, FLOAT, NUMERIC with REAL.
• Replace BOOLEAN, BIT, TINYINT with INTEGER.
• Replace DATETIME, TIMESTAMP, DATE with TEXT (or INTEGER if you store unix timestamps).
• Add STRICT after the closing paren.

Next: Primary Keys

STRICT tables tighten how columns store their data. The next thing worth tightening is which column identifies each row — and SQLite's primary keys have a couple of quirks (especially around INTEGER PRIMARY KEY and rowid) that are worth knowing before you design a real schema.