TopolearnSTRICT tables give you the type safety of other databases while keeping SQLite's simplicity.
Since SQLite 3.37, adding the keyword STRICT after a CREATE TABLE statement turns on rigid typing for that table:
- Each column must be declared with one of
INT,INTEGER,REAL,TEXT,BLOBorANY. - Inserting a value that can't be losslessly converted to the column's type raises an error:
cannot store TEXT value in INTEGER column. Text like'42'is still converted to the integer 42, since that is lossless. ANYcolumns accept anything and keep it exactly as given.INTEGER PRIMARY KEYcolumns still alias the rowid, andPRIMARY KEYcolumns are implicitlyNOT NULL.
STRICT doesn't add date or boolean types, nor length limits on text. For those, use CHECK constraints, which SQLite has always enforced:
CHECK (length(code) = 3)CHECK (active IN (0, 1))CHECK (created_at IS datetime(created_at))to ensure valid ISO timestamps.
Combine STRICT with WITHOUT ROWID as ) STRICT, WITHOUT ROWID;. Older SQLite versions can't open databases containing STRICT tables, so check the minimum version your users have (Python bundles the system or its own SQLite; check sqlite3.sqlite_version).
For new schemas, STRICT tables are a cheap way to catch bugs early.
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.execute('''
CREATE TABLE account (
id INTEGER PRIMARY KEY,
code TEXT NOT NULL CHECK (length(code) = 3),
balance INTEGER NOT NULL,
active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)),
meta ANY
) STRICT
''')
con.execute("INSERT INTO account (code, balance, meta) VALUES ('AMD', '100', 1.5)")
print(con.execute("SELECT balance, typeof(balance) FROM account").fetchone()) # (100, 'integer')
for bad in [("USD", "lots"), ("EURO", 5)]:
try:
con.execute("INSERT INTO account (code, balance) VALUES (?, ?)", bad)
except sqlite3.Error as e:
print(type(e).__name__, e)
# IntegrityError cannot store TEXT value in INTEGER column account.balance
# IntegrityError CHECK constraint failed: length(code) = 3Remember
- STRICT enforces INT, INTEGER, REAL, TEXT, BLOB or ANY per column
- Lossless conversions like '42' -> 42 still happen
- Use CHECK constraints for lengths, booleans and date formats
Watch out for
- Shipping a database with STRICT tables to clients running SQLite older than 3.37.