SQLite’s flexible typing is useful until an application accidentally relies on it.

I have seen schemas declare an INTEGER column and then assume that every stored value must be an integer. In an ordinary SQLite table, that assumption is too strong: SQLite can preserve a value that cannot be converted to the column’s preferred type. That flexibility is intentional, but for application data I often want mistakes to fail at the write boundary instead of surfacing later in a query.

STRICT tables give me that option without changing databases.

Here’s the idea:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    payload BLOB
) STRICT;

STRICT is a table option, so I can adopt it table by table. SQLite added it in version 3.37.0, released in 2021.

What STRICT actually changes

A strict table requires every column to declare a type, and the allowed type names are deliberately small:

INT
INTEGER
REAL
TEXT
BLOB
ANY

That means declarations such as VARCHAR(255), BOOLEAN, and DATETIME are not valid column types in a STRICT table. I need to model those values using SQLite’s storage types plus constraints where appropriate.

For example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    enabled INTEGER NOT NULL CHECK (enabled IN (0, 1)),
    created_at TEXT NOT NULL
) STRICT;

This is less decorative than a schema full of application-specific type names, but I like that it makes the storage contract explicit.

Strict does not mean “no coercion”

This is the detail I would not skip.

A STRICT column still uses SQLite’s normal affinity rules to try a conversion. The requirement is that the conversion must be lossless. If it cannot be converted appropriately, the write fails with a datatype constraint error.

Given this table:

CREATE TABLE counters (
    id INTEGER PRIMARY KEY,
    value INTEGER NOT NULL
) STRICT;

this numeric-looking text can be accepted as an integer:

INSERT INTO counters (value) VALUES ('42');

SELECT value, typeof(value) FROM counters;
-- 42 | integer

But arbitrary text cannot become an integer losslessly:

INSERT INTO counters (value) VALUES ('forty-two');
-- datatype constraint error

So I would describe STRICT as rigid storage typing, not as a promise that SQLite refuses every input whose original host-language type differs from the declared column type.

That distinction matters when validating API input. Database constraints are a useful final boundary, but they do not replace application validation.

Use CHECK for domain rules

STRICT answers a storage-type question. It does not know the business meaning of a value.

If an integer represents a retry count, I still need a constraint for its valid range:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    attempts INTEGER NOT NULL DEFAULT 0
        CHECK (attempts >= 0),
    status TEXT NOT NULL
        CHECK (status IN ('queued', 'running', 'done', 'failed'))
) STRICT;

The same applies to booleans. SQLite does not have a separate Boolean storage type, so I normally use an integer with a CHECK constraint:

enabled INTEGER NOT NULL CHECK (enabled IN (0, 1))

For timestamps, I usually choose one representation and make that choice part of the application contract. A TEXT column can hold an ISO-style timestamp, while an INTEGER column can hold Unix time. STRICT ensures the storage class; it does not prove that arbitrary text is a valid date.

If the textual format itself matters, validate it before insertion or add an appropriate CHECK expression when the rule can be expressed reliably in SQL.

ANY is intentionally flexible

Sometimes mixed types are the point. SQLite keeps that use case through the ANY type.

CREATE TABLE settings (
    key TEXT PRIMARY KEY,
    value ANY NOT NULL
) STRICT;

In a strict table, ANY preserves the value and its storage type instead of applying the numeric-looking coercion that can happen with an ANY column in an ordinary table.

For example, a string with leading zeroes stays text:

INSERT INTO settings (key, value)
VALUES ('branch-code', '000123');

SELECT value, typeof(value)
FROM settings
WHERE key = 'branch-code';
-- 000123 | text

That makes ANY useful for genuinely heterogeneous data, but I would not use it merely to avoid deciding on a schema. If a column is always supposed to contain JSON text, IDs, or integers, declaring the real storage type makes errors easier to catch.

INTEGER PRIMARY KEY still has special behavior

STRICT does not erase SQLite’s existing INTEGER PRIMARY KEY semantics.

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT NOT NULL
) STRICT;

The id column is still the rowid alias. Inserting NULL for an INTEGER PRIMARY KEY can still cause SQLite to generate a unique integer value.

Also notice the spelling: INTEGER PRIMARY KEY has the rowid-alias behavior; INT PRIMARY KEY does not. The two type names are both legal in a strict table, but they are not interchangeable for this feature.

This is exactly the kind of small SQLite rule I prefer to make explicit in schema reviews rather than discover through an ORM abstraction later.

Existing constraints keep working

A strict table is not a separate database mode. Most normal SQLite schema features behave as usual:

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    sku TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    UNIQUE (order_id, sku),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) STRICT;

NOT NULL, CHECK, UNIQUE, foreign keys, generated columns, indexes, defaults, collations, and conflict clauses still have their normal jobs. STRICT adds type enforcement rather than replacing those constraints.

That separation is useful when thinking about schema design:

  • the declared type protects the storage class;
  • NOT NULL protects required values;
  • CHECK protects domain rules;
  • UNIQUE protects uniqueness;
  • foreign keys protect relationships.

I get a much clearer schema when each rule is expressed at the layer that actually owns it.

Check whether a table is strict

When inspecting an unfamiliar database, I do not want to infer strictness from the column declarations. SQLite exposes it directly through PRAGMA table_list:

PRAGMA table_list;

Its strict result column is 1 for a strict table and 0 otherwise.

This is useful in migration tests too. A test can verify that a newly created table is actually strict instead of only checking that its columns exist.

SQLite’s integrity checks also validate stored column types for strict tables:

PRAGMA quick_check;
-- or
PRAGMA integrity_check;

That is particularly useful when a database file has moved through unusual tooling or older SQLite environments.

Plan migrations instead of editing the keyword

The obvious question is how to convert an existing flexible table.

I prefer treating this as a data migration, because existing rows may already contain values that violate the new contract. A safe pattern is to create the strict replacement, copy data through it, and only switch tables after the copy succeeds.

A simplified migration looks like this:

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER
) STRICT;

INSERT INTO users_new (id, email, age)
SELECT id, email, age
FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

If dirty data exists, the copy is where I want to discover it. I can inspect and normalize those rows deliberately rather than silently carrying inconsistent types into the new schema.

Real migrations also need to recreate relevant indexes, triggers, foreign-key relationships, and other schema objects. For important databases, I test the migration against a copy of production-shaped data rather than assuming a clean development database represents reality.

Compatibility can be the real blocker

STRICT requires SQLite 3.37.0 or newer to understand the table option normally. The underlying database file format is otherwise unchanged, but an older SQLite parser does not understand a schema containing STRICT.

This matters more than it first appears because an application may not use the SQLite version installed by the operating system. A language runtime, mobile platform, desktop framework, or bundled library can ship its own version.

Before adopting strict tables, I check the version from the actual application connection:

SELECT sqlite_version();

If the database file is shared between several programs, every normal reader and writer needs to be considered. A feature that works in my migration CLI is not enough if an older deployed client must open the same schema.

Test the failures, not only the happy path

A schema feature earns its keep when it rejects the data I do not want. I like migration tests that prove those boundaries explicitly.

For the tasks table above, useful cases include:

-- accepted
INSERT INTO tasks (attempts, status)
VALUES (0, 'queued');

-- rejected by CHECK
INSERT INTO tasks (attempts, status)
VALUES (-1, 'queued');

-- rejected by CHECK
INSERT INTO tasks (attempts, status)
VALUES (0, 'unknown');

-- rejected by strict typing
INSERT INTO tasks (attempts, status)
VALUES ('many', 'queued');

I also test values that SQLite can coerce, because they reveal whether my application expects stricter input semantics than the database itself promises.

For example, if the API must reject the JSON string "42" for an integer field, that belongs in API validation even if SQLite can losslessly store it as integer 42.

When I would use STRICT

For most application-owned tables with a stable schema, STRICT is an easy default for me. It catches a class of accidental writes while preserving the parts of SQLite I already use.

I would be more cautious when a database intentionally stores heterogeneous values, must be opened by old SQLite versions, or is consumed by external software whose SQLite runtime I do not control. Even there, ANY can sometimes preserve the needed flexibility without making every column flexible.

To be fair, ordinary SQLite typing is not broken behavior waiting to be fixed. It is a deliberate design that can be genuinely convenient. STRICT simply lets me choose a different contract where predictability matters more than permissiveness.

In the end, the biggest benefit is not that SQLite starts behaving exactly like PostgreSQL or another server database—it does not. The benefit is smaller and more practical: when my schema says a column stores integers, text, blobs, or real numbers, SQLite can enforce that decision at the point where bad data tries to enter the table.

Database SQLite SQL Schema Design