Why Your Next SQLite Table Should Be Strict

SQLite has long been the darling of embedded databases, prized for its zero-configuration simplicity and speed. But for developers accustomed to the rigid type systems of PostgreSQL or MySQL, SQLite's default type system can feel like a walk on a tightrope without a net. Historically, SQLite has treated column types as mere suggestions. If you declared a column as an INTEGER and then inserted the string 'garbage', the database would happily store it.

This dynamic approach has its defenders, including the SQLite core team. But in production application development, silent type coercion is a liability. It allows malformed data to bypass database constraints and poison your state, only to surface later as runtime exceptions in your application code.

Fortunately, since version 3.37.0, released in late 2021, the engine has offered a built-in solution: STRICT Tables. By appending a single keyword to your table definition, you can opt into a traditional, rigid type system that catches type mismatches at the database boundary.

The Mechanics of Strictness

Enabling strict mode is straightforward. You simply append the STRICT keyword to the end of your CREATE TABLE statement:

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

This single keyword fundamentally alters how SQLite validates your schema and data. In a standard table, SQLite allows you to declare columns with completely made-up types. Statements like CREATE TABLE events (id UUID, payload JSON) execute without a hitch, even though SQLite has no native concept of a UUID or JSON type. It simply maps these to a default affinity.

In a strict table, this leniency disappears. SQLite restricts you to exactly six allowed datatypes:

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

If you attempt to create a strict table with a bogus type like DATETIME or VARCHAR, the engine immediately throws an error. This prevents typos and misunderstandings about SQLite's supported types right at the schema migration stage.

Type Coercion and the ANY Escape Hatch

Strict tables do not completely eliminate type conversion; instead, they make it predictable. SQLite still attempts to coerce input data into the target column's type using standard affinity rules. If you insert the string '123' into an INTEGER column, SQLite will successfully convert and store it as the integer 123.

However, if the conversion cannot be performed losslessly, the operation fails. Trying to insert 'twenty-five' or 'O.99' (with a typo letter 'O' instead of a zero) into a numeric column will raise an SQLITE_CONSTRAINT_DATATYPE error and abort the transaction.

For scenarios where you genuinely need dynamic typing, SQLite provides the ANY datatype. A column declared as ANY in a strict table can accept any data type, but it behaves differently than a column in a non-strict table. In a standard table, a column will try to convert numeric-looking strings into numbers. In a strict table, ANY preserves the input exactly as it was received:

-- In a strict table
CREATE TABLE strict_demo (val ANY) STRICT;
INSERT INTO strict_demo (val) VALUES ('000123');
-- Stored as TEXT: '000123'

-- In a non-strict table
CREATE TABLE legacy_demo (val ANY);
INSERT INTO legacy_demo (val) VALUES ('000123');
-- Coerced and stored as INTEGER: 123

This preservation is highly useful when handling raw identifiers or codes where leading zeros are semantically meaningful.

The Developer's Migration Path

If you are building a new application, defaulting to strict tables is highly recommended. But if you are managing an existing database, migrating to strict tables requires careful execution.

SQLite does not support altering an existing table to make it strict. To upgrade a table, you must perform a manual migration: create a new strict table, copy the data over, drop the old table, and rename the new one.

-- 1. Create the new strict table
CREATE TABLE users_strict (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
) STRICT;

-- 2. Copy the data
INSERT INTO users_strict SELECT * FROM users;

-- 3. Swap the tables
DROP TABLE users;
ALTER TABLE users_strict RENAME TO users;

This migration is a double-edged sword. If your legacy non-strict table contains invalid data (for example, a text string in an integer column), the INSERT INTO ... SELECT statement will fail with a constraint error. While this is exactly the validation you want, it means you must clean or cast your legacy data before executing the migration.

Another critical detail involves primary keys. In a strict table, columns that are part of the PRIMARY KEY are implicitly NOT NULL. However, if you use INTEGER PRIMARY KEY (specifically INTEGER, not INT), inserting a NULL value will still trigger SQLite's auto-increment behavior, generating the next unique integer. This is a subtle but important distinction: INTEGER PRIMARY KEY acts as an alias for the internal rowid, whereas INT PRIMARY KEY does not.

To ensure your database remains healthy after these migrations, you can run PRAGMA integrity_check or PRAGMA quick_check. In strict tables, these commands are upgraded to verify that the type of the content in every column matches its declared type, reporting errors if anything is amiss.

Compatibility and Trade-offs

Before migrating your entire database layer, you must consider backward compatibility. The STRICT keyword was introduced in SQLite 3.37.0 (November 2021). While the underlying database file format remains identical, older versions of SQLite cannot parse the STRICT keyword and will fail to open the database unless you use specific workarounds like PRAGMA writable_schema=ON.

If your application runs in environments with older system-provided SQLite libraries (such as older Linux distributions or mobile operating systems), you must ensure your runtime is linked against a modern SQLite version.

For legacy environments where upgrading SQLite is impossible, you can mimic some of this validation using CHECK constraints:

CREATE TABLE users_legacy (
    name TEXT,
    age INTEGER CHECK (typeof(age) = 'integer' OR age IS NULL)
);

While this approach works, it is verbose, harder to maintain, and lacks the comprehensive schema-level protections that strict tables provide out of the box.

A Clear Path Forward

SQLite's flexible typing was a pragmatic design choice for its original use cases, but modern software engineering favors explicit schemas. Catching type mismatches at the database layer prevents corrupt data from silently spreading through your application state.

Unless you are building a pure key-value store or importing highly unstructured CSV data, there is little reason to avoid strict tables. For new projects, make STRICT your default. For existing systems, the effort to clean your data and migrate to strict schemas will pay dividends in long-term reliability.