SQLite Strict Tables: Enforcing Data

The Problem: SQLite’s Flexible Typing

SQLite has always been different from other SQL databases. While PostgreSQL, MySQL, and SQL Server reject data that does not match a column’s declared type, SQLite traditionally accepted almost anything. Insert the string 'hello' into an INTEGER column in a non-strict table, and SQLite stores the string. Insert '123' into that same column, and SQLite converts it to integer 123. This behavior, called type affinity, is documented in detail on the SQLite datatypes page.

Some developers love this flexibility. It makes prototyping fast and schema migrations painless. But for applications that need strong data guarantees, especially those handling financial records, user profiles, or sensor data, this leniency is a liability. A bug in application code that writes a string where a number belongs can corrupt a dataset silently, and the error may not surface until a downstream report fails or a query returns unexpected results.

Developer writing SQLite database code on laptop screen — SQLite’s default flexible typing can hide data integrity issues that strict mode catches immediately.

Starting with SQLite version 3.37.0, released on November 27, 2021, the project introduced a solution for developers who prefer traditional rigid type enforcement: STRICT Tables. This feature, documented on the official SQLite STRICT Tables page, allows per-table enforcement of strict typing rules while keeping the same on-disk format and backward compatibility features that make SQLite so widely deployed.

What Strict Tables Change

When you append the STRICT keyword to a CREATE TABLE statement, SQLite changes its behavior in several specific ways. The most important difference is that every column must now declare a valid datatype from a restricted set: INT, INTEGER, REAL, TEXT, BLOB, or ANY. No other type names are accepted, and you cannot omit the type declaration entirely.

Working Examples: Strict vs Non-Strict Tables

The clearest way to understand strict tables is to see them in action. Below are complete, runnable examples that you can copy, paste, and execute with the sqlite3 CLI or any SQLite client.

Example 1: Non-Strict Table Accepts Anything

-- Create non-strict table with single column
CREATE TABLE t1 (value INTEGER);

-- Insert string that looks like number
INSERT INTO t1 VALUES('123');

-- Insert string that does NOT look like number
INSERT INTO t1 VALUES('xyz');

-- Insert float into INTEGER column
INSERT INTO t1 VALUES(45.67);

-- Check what was actually stored
SELECT rowid, typeof(value), quote(value) FROM t1;
-- Output:
-- 1|integer|123
-- 2|text|'xyz'
-- 3|integer|46

Notice three things. First, '123' was converted to integer 123. Second, 'xyz' was stored as text even though the column is declared INTEGER. Third, 45.67 was rounded to 46 when coerced to integer. None of these operations raised an error. The application received no signal that data was being transformed or stored in unexpected ways.

Example 2: Strict Table Rejects Mismatches

-- Create strict table with same column definition
CREATE TABLE t2 (value INTEGER) STRICT;

-- This works: '123' is losslessly converted to integer 123
INSERT INTO t2 VALUES('123');

-- This fails: 'xyz' cannot be losslessly converted to integer
INSERT INTO t2 VALUES('xyz');
-- Error: SQLITE_CONSTRAINT_DATATYPE: datatype mismatch

-- This fails: 45.67 cannot be losslessly converted to integer
INSERT INTO t2 VALUES(45.67);
-- Error: SQLITE_CONSTRAINT_DATATYPE: datatype mismatch

-- Check what was stored
SELECT rowid, typeof(value), quote(value) FROM t2;
-- Output:
-- 1|integer|123

In strict mode, the first insert succeeds because '123' converts cleanly to integer 123. The second and third inserts fail with a datatype mismatch error, exactly as they would in PostgreSQL or MySQL. The application catches these errors at insert time and can log, alert, or reject bad data before it reaches the database.

Example 3: Strict Table With TEXT Column

-- Create strict table with TEXT column
CREATE TABLE users (
 id INTEGER PRIMARY KEY,
 email TEXT
) STRICT;

-- Insert valid data
INSERT INTO users VALUES(1, '[email protected]');

-- This fails: integer 42 is not TEXT
INSERT INTO users VALUES(2, 42);
-- Error: SQLITE_CONSTRAINT_DATATYPE: datatype mismatch

-- This works: '42' as string is valid TEXT
INSERT INTO users VALUES(3, '42');

SELECT * FROM users;
-- Output:
-- 1|[email protected]
-- 3|42

This example shows a common real-world scenario: an application bug that writes a numeric user ID into an email field. In a non-strict table, integer 42 would be stored silently. In a strict table, the error surfaces immediately during development or testing, not months later when a mail merge script fails.

Team brainstorming database schema design on whiteboard — Designing schemas with strict tables forces upfront decisions about data types, catching errors before they reach production.

The ANY Datatype and Data Preservation

One concern developers raise about strict tables is the loss of SQLite’s ability to store heterogeneous data in a single column. The ANY datatype solves this. When a column is declared ANY in a strict table, it accepts any data type exactly as received, with no coercion at all.

The behavior of ANY differs between strict and non-strict tables:

In a strict table, ANY preserves data exactly as inserted. The string '000123' stays as the string '000123'.
In a non-strict table, ANY attempts to convert strings that look like numbers into numeric values. The string '000123' becomes integer 123.

-- ANY in strict table preserves leading zeros
CREATE TABLE strict_any (value ANY) STRICT;
INSERT INTO strict_any VALUES('000123');
SELECT typeof(value), quote(value) FROM strict_any;
-- Output: text '000123'

-- ANY in non-strict table converts numeric-looking strings
CREATE TABLE loose_any (value ANY);
INSERT INTO loose_any VALUES('000123');
SELECT typeof(value), quote(value) FROM loose_any;
-- Output: integer 123

This makes ANY in strict tables the safest option for columns that must store raw, unaltered data. As the SQLite documentation notes, it is the only SQL database engine that supports this capability.

Comparison Table: Strict vs Non-Strict Tables

Behavior	Non-Strict Table	Strict Table
Column type required	No	Yes (INT, INTEGER, REAL, TEXT, BLOB, ANY)
Insert ‘xyz’ into INTEGER	Stored as text ‘xyz’	SQLITE_CONSTRAINT_DATATYPE error
Insert ‘123’ into INTEGER	Converted to integer 123	Converted to integer 123 (lossless)
Insert 45.67 into INTEGER	Rounded to integer 46	SQLITE_CONSTRAINT_DATATYPE error
ANY column: ‘000123’	Converted to integer 123	Preserved as text ‘000123’
PRIMARY KEY NULL handling	NULL converted to unique integer	Same behavior (INTEGER PK only)
PRAGMA integrity_check	No type validation	Validates types in all columns
On-disk format	Standard SQLite format	Identical to non-strict
Minimum SQLite version	All versions	3.37.0 (2021-11-27) or later

Backward Compatibility and Upgrade Path

The STRICT keyword is only recognized by SQLite 3.37.0 and later. If you open a database containing strict tables with an older version, SQLite will report a schema parse error. However, the database file format is identical to non-strict tables, so recovery is possible.

Older versions of SQLite (prior to 3.37.0) can still read and write strict tables if you set PRAGMA writable_schema=ON immediately after opening the database. This pragma disables schema parse errors, and the old parser effectively ignores the STRICT keyword. The trade-off is that type enforcement is completely disabled in that session. The .dump command in the SQLite CLI also sets writable_schema=ON, so using .dump on a strict-table database with an old SQLite version can silently introduce type-invalid data.

If you suspect corruption from this scenario, reopen the database with SQLite 3.37.0 or later and run PRAGMA quick_check. This command validates the types of all columns in strict tables and reports any mismatches.

The STRICT keyword can be combined with WITHOUT ROWID in any order. The parser accepts both table options as a comma-separated list after the closing parenthesis.

When to Use Strict Tables in Production

Strict tables are not the right choice for every project. SQLite’s flexible typing remains valuable for prototyping, embedded configurations, log storage, and any scenario where the schema evolves rapidly. But for production systems that enforce data contracts, strict tables should be the default.

Consider strict tables when:

Data correctness is critical. Financial transactions, medical records, user authentication data, and inventory systems benefit from type enforcement at the database level. Similar principles apply to protecting systems from data integrity attacks like double-extortion ransomware, where corrupted data can be as damaging as stolen data.
Multiple applications write to the same database. When different services, scripts, or teams write to the same SQLite file, strict tables prevent one writer from silently corrupting data that another reader depends on.
You are migrating from another SQL database. If you are moving from PostgreSQL or MySQL to SQLite for an embedded or mobile use case, strict tables preserve the type enforcement behavior your application already expects.
You want early error detection. Catching a type mismatch at insert time is cheaper than debugging a downstream failure. Strict tables surface these errors during development and testing, not in production.

SQLite database code displayed on a screen

The migration path is straightforward. Create a new strict table with the same schema, copy data with INSERT INTO ... SELECT, and rename tables. Any rows that fail type checking during migration reveal pre-existing data quality issues that were hidden by SQLite’s flexible typing.

Key Takeaways:

Strict tables enforce column types from a predefined set (INT, INTEGER, REAL, TEXT, BLOB, ANY) and reject mismatched data with SQLITE_CONSTRAINT_DATATYPE errors.
The feature requires SQLite 3.37.0 (2021-11-27) or later and is activated by appending the STRICT keyword to CREATE TABLE.
The ANY datatype in strict tables preserves data exactly as inserted, unlike non-strict tables which may convert numeric-looking strings to numbers.
The on-disk format is identical to non-strict tables, enabling safe migration without format changes.
Backward compatibility with older SQLite versions exists via PRAGMA writable_schema=ON, but type enforcement is disabled in that mode, risking silent data corruption.
Use strict tables for production systems where data correctness is critical, multiple writers access the same database, or you are migrating from another SQL database.

More in-depth coverage from this blog on closely related topics:

Sources and References

Sources cited while researching and writing this article: