Preferring STRICT Tables in SQLite

Preferring STRICT Tables in SQLite

Use STRICT Tables to Enforce Data Integrity

SQLite's STRICT tables prevent common data integrity issues by enforcing rigid typing, ensuring that only the declared data type can be stored in a column. By appending STRICT to the end of a CREATE TABLE statement, developers can stop SQLite from allowing text to be inserted into integer columns or other type mismatches that occur by default in non-strict tables.

-- Non-strict tables allow any type in any column
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- Works fine

-- Strict tables enforce the declared type
CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage'); -- Error: cannot store TEXT value in INTEGER column

Lossless conversions are still permitted; for example, the string '123' can be stored in an INTEGER column because it can be perfectly converted to an integer.

Preventing Invalid Column Definitions

Strict tables eliminate the possibility of creating columns with non-existent or misspelled data types. In a standard SQLite table, types like DATETIME, JSON, or UUID are accepted despite not being native SQLite types. In a STRICT table, only the following types are permitted:

  • INT / INTEGER
  • REAL
  • TEXT
  • BLOB
  • ANY

Attempting to use any other type name during table creation will result in an error, preventing typos and misunderstandings of SQLite's supported types.

Flexibility with the ANY Type

For scenarios where a column must remain flexible—such as a key-value store or an audit table tracking changes across different field types—the ANY type can be used within a strict table. This allows the column to accept any data type while the rest of the table remains rigidly typed.

CREATE TABLE tbl (value ANY) STRICT;
INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');

Trade-offs and Limitations of STRICT Tables

While STRICT tables improve predictability, they come with specific technical constraints:

Version Requirements and Compatibility

Strict tables were introduced in SQLite version 3.37.0 (November 2021). Databases containing strict tables cannot be read by versions of SQLite older than 3.37.0, creating a potential compatibility hurdle for environments with legacy SQLite installations.

Migration Challenges

Existing tables cannot be converted to strict via an ALTER TABLE command. Migrating a non-strict table to a strict one requires creating a new strict table and copying the data over:

  1. Create a new strict table with the same schema.
  2. Copy data using INSERT INTO ... SELECT * FROM ....
  3. Drop the old table and rename the new one.

If the existing data contains type mismatches, the migration will fail, requiring the developer to clean or cast the data first.

Performance Considerations

Strict tables introduce a small amount of overhead because SQLite must validate data types during INSERT and UPDATE operations. However, empirical tests suggest this overhead is negligible in most practical applications, and some argue that avoiding column affinity mismatches could potentially improve performance.

Community Perspectives on Flexible vs. Rigid Typing

There is a significant divide between the SQLite developers and many users regarding the utility of flexible typing. The official SQLite documentation argues that flexible typing is an advantage, suggesting that it prevents data loss during imports of messy data (like CSVs) and makes it easier to spot certain types of bugs.

Community members have expressed varying views:

"I’ve personally encountered many bugs where an unexpected data type caused subtle headaches. I’d much rather these mistakes explode loudly."

"If one application stores a string into a numeric column that breaks everyone else... the main use case for SQLite is embedded databases... the application's code knows what to expect in each column."

Conversely, some users argue that strict mode restricts the ability to use more meaningful column type names that could be used for mapping in the application layer (e.g., using Rust's sqlx crate).

For those unable to use STRICT tables due to version constraints or the need for specific constraints, CHECK constraints can be used as a manual alternative to enforce data correctness.

Sources