SQLite Basics: Enforce Storage Types with STRICT Tables

An ordinary SQLite column’s declared type does not reject every value of a different type. STRICT tables make that boundary firmer when an application expects consistent integers, text, or blobs. They are useful for catching a malformed import before a surprising value reaches later calculations.

STRICT tables require SQLite 3.37.0 or later. All programs opening the database need a compatible runtime, not just the tool creating the table. Only the installed runtime below was executed.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Inspect lossless conversion at the table boundary

Save as strict-stock.sql:

CREATE TABLE stock (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity >= 0)
) STRICT;
INSERT INTO stock VALUES
    (1, 'pen', '12'), (2, 'pad', 3.0);
SELECT id, sku, quantity, typeof(quantity)
FROM stock ORDER BY id;

Run:

python3 run-sql.py strict-stock.sql

Output:

1|pen|12|integer
2|pad|3|integer

Both quantities are stored as integers, even though one input was text and the other was a real-number literal. STRICT allows lossless conversion; it does not demand that the incoming SQL literal already have the destination storage class. A value such as the text dozen or the real number 3.5 cannot be stored in this INTEGER column and raises a datatype constraint error.

The nonnegative CHECK remains necessary. An integer of -2 has the correct type but would be an invalid stock quantity under this example’s rule. NOT NULL handles missing values separately. Decide these domain constraints explicitly instead of expecting a type declaration to express the whole business contract.

2. Preserve incoming values with ANY

Save as preserve-any-values.sql:

CREATE TABLE raw_values (
    id INTEGER PRIMARY KEY,
    value ANY NOT NULL
) STRICT;
INSERT INTO raw_values VALUES
    (1, '00420'), (2, 420), (3, X'4142');
SELECT id, typeof(value), quote(value)
FROM raw_values ORDER BY id;
CREATE TABLE ordinary_values (value ANY);
INSERT INTO ordinary_values VALUES ('00420');
SELECT 'ordinary', typeof(value), quote(value) FROM ordinary_values;

Run:

python3 run-sql.py preserve-any-values.sql

Output:

1|text|'00420'
2|integer|420
3|blob|X'4142'
ordinary|integer|420

The strict ANY column keeps the leading-zero text, integer, and binary value distinct. quote() makes their representations visible, including the blob written as a hexadecimal SQL literal. This can be useful for a small staging area where you need to inspect an input’s original storage class before deciding what it means.

The ordinary table provides a deliberate contrast: its ANY declaration converts numeric-looking text into an integer. The strict-table behavior is therefore important, not just the spelling ANY. If a field is always an identifier, TEXT is usually a clearer schema than accepting every storage class. Reserve ANY for a real mixed-value requirement.

3. Copy checked data into a new strict table

Save as copy-into-strict.sql:

CREATE TABLE incoming (id INTEGER PRIMARY KEY, title TEXT, attempts);
INSERT INTO incoming VALUES
    (1, 'Build index', '2'), (2, 'Write summary', 0);
BEGIN;
CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    attempts INTEGER NOT NULL CHECK (attempts >= 0)
) STRICT;
INSERT INTO jobs (id, title, attempts)
SELECT id, title, attempts FROM incoming;
COMMIT;
SELECT id, title, attempts, typeof(attempts)
FROM jobs ORDER BY id;
PRAGMA quick_check;

Run:

python3 run-sql.py copy-into-strict.sql

Output:

1|Build index|2|integer
2|Write summary|0|integer
ok

The copied attempts are checked by the destination schema. This is a useful rehearsal before a migration: inconsistent text in incoming would cause the INSERT to fail instead of silently becoming a job’s attempt count. quick_check returns ok for this toy database, including its strict-table type checks.

This script deliberately keeps the source table. A production migration also needs a backup, dependency review, application updates, and an explicit error path that rolls back the transaction rather than continuing to COMMIT. Do not treat this small copy as a complete table-replacement procedure. The teaching runner closes its in-memory database on an exception, so there is no persistent partial migration here.

The STRICT table reference documents conversion, ANY, compatibility, and the allowed type names: INT, INTEGER, REAL, TEXT, BLOB, and ANY. Names such as BOOLEAN or VARCHAR are not accepted as strict column types. Use a supported storage type plus the appropriate domain checks.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py strict-stock.sql preserve-any-values.sql copy-into-strict.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.