Choosing SQLite STRICT Mode for Stronger Column Typing

Decision and constraints

Adopt SQLite STRICT table mode when you need guaranteed column storage‑class enforcement for new tables or migrated schemas. This improves data integrity by rejecting values whose storage class does not match the declared type.

Constraints:

• SQLite library version ≥ 3.37.0 (the STRICT keyword is unknown in earlier releases).
• Existing tables must either be recreated with STRICT or migrated; rows already present are not altered but future writes that violate the rule will fail.
• Applications that rely on type affinity (e.g., inserting a string into an INTEGER column and expecting it to be stored as integer) may need code changes.

Options comparison

Mode	Type enforcement	Compatibility	Typical use
STRICT	Strict – rejects mismatched storage class	May break legacy code that depends on affinity	New applications or migrated schemas needing strong typing
Default (affinity)	Flexible – SQLite converts values per affinity rules	High backward compatibility	Existing apps, mixed‑type data, or prototyping

Trade‑offs

Enabling STRICT eliminates silent type conversions, reducing bugs and allowing the query planner to rely on exact column types. The safety gain comes with virtually no runtime overhead; EXPLAIN QUERY PLAN shows identical plans for STRICT and non‑STRICT tables.

Drawbacks include the need to migrate existing schemas and the possibility of runtime errors when invalid data is inserted or updated. Performance impact is negligible compared to the integrity benefit.

Concrete implementation

Assuming you have SQLite 3.37.0 or later, create a strict table:

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

Attempt to insert a value with the wrong storage class:

INSERT INTO users (name, age) VALUES ('Alice', 'thirty');
-- Expected error: SQLITE_MISMATCH: datatype mismatch

Insert a correctly typed value succeeds:

INSERT INTO users (name, age) VALUES ('Alice', 30);
-- No error; row inserted

Verification steps

1. Confirm library version: SELECT sqlite_version(); – ensure the result is 3.37.0 or higher.
2. Test type mismatch: run the failing INSERT above and verify that SQLite returns an error with message containing \"datatype mismatch\".
3. Test successful insert: run the succeeding INSERT and confirm the row is present via SELECT * FROM users;.
4. Check planner overhead: compare EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 1; for a strict table versus a non‑strict table; the output should be identical, indicating no extra cost.

Rollback (if needed)

Creating a strict table changes the database schema. To revert, drop the table and recreate it without the STRICT keyword:

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

This rollback only affects the table definition; any data already inserted while the table was strict remains unchanged.