SQLite STRICT Tables: Why Type Safety Should Be Your Default

SQLite's flexible typing is either a feature or a footgun depending on who you ask. By default, you can declare a column as INTEGER and then happily store the string "hello world" in it. No error, no warning - SQLite just silently accepts whatever you throw at it.

This week, Evan Hahn's post Prefer strict tables in SQLite hit the Hacker News front page, reigniting the debate about whether SQLite's permissive typing is a blessing or a curse. The verdict from the developer community: STRICT mode should probably be your default.

What STRICT Tables Actually Do

Since SQLite 3.37.0 (November 2021), you can add the STRICT keyword to any table definition:

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

With STRICT enabled, SQLite enforces two critical constraints:

1. Type validation on insert/update - Try to insert "twenty-five" into that INTEGER age column and you'll get an error: "cannot store TEXT value in INTEGER column"

2. Valid type names only - In non-strict tables, you can declare columns with completely bogus types like DATETIME, UUID, or BLOBB (note the typo). SQLite just ignores them. STRICT tables only allow: INT, INTEGER, REAL, TEXT, BLOB, and ANY

The ANY type is your escape hatch when you genuinely need dynamic typing - it stores any value type while still requiring valid types everywhere else.

What HN Is Saying

The discussion thread surfaced some strong opinions from developers who've dealt with SQLite's typing quirks in production.

The "make it default" camp:

"I'd like to see STRICT as the default. That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!"

Several commenters shared war stories. One developer had to clean up a project where someone accidentally stored the strings '1' and '0' in a Boolean column across thousands of devices. When your data validation happens at the database level, these bugs get caught immediately instead of silently corrupting your data.

The backwards compatibility argument:

Simon Willison and others pointed out why this can't become the default: SQLite's commitment to backwards compatibility means software written against SQLite 3.53 shouldn't suddenly break on 3.54. That's a reasonable position, but as one commenter noted, the software should ideally "be configured in its best state by default."

The historical context:

An insightful comment explained the origins of SQLite's flexible typing. The original SQLite used dbm for storage - essentially string keys with string values. The code did automatic conversions, and TCL (used as the dev wrapper language) worked the same way. SQLite 3 in 2004 added proper storage types but maintained API compatibility. Hence: dynamic typing by default.

Missing features in STRICT mode:

Some developers wish STRICT mode went further. There's no native DATETIME or BOOLEAN type even in strict tables - you're expected to use TEXT or INTEGER. As one commenter put it: "Well, I would also like a proper datetime/timestamp datatype that isn't just a string."

The Migration Challenge

Here's the catch: you can't ALTER TABLE an existing table to add STRICT. Converting requires:

1. Create a new STRICT table with the same schema
2. Copy all data (which may fail if existing values don't match types)
3. Drop the old table
4. Rename the new one

SQLite's documentation warns extensively about this 12-step process and the data loss risks if done incorrectly. For new tables, just add STRICT. For existing tables, weigh the migration risk against your data integrity concerns.

Why the SQLite Team Disagrees

The SQLite documentation includes a defense of flexible typing. Their argument: flexible typing is genuinely useful for key-value stores, schema-less data imports, and rapid prototyping. They're not wrong - there are legitimate use cases.

But as one HN commenter countered:

"What is least surprising? That INTEGER implicitly accepts 'hello world' without error, or that you can't insert such a value unless you use a keyword like NONSTRICT or a type like ANY? I would wager the vast majority of SQLite users if asked would probably not expect it to work."

The principle of least surprise suggests STRICT should be opt-out, not opt-in.

Practical Recommendations

For new projects: Add STRICT to every table definition. The type validation catches bugs early, and the performance impact is negligible.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total REAL NOT NULL,
    status TEXT NOT NULL
) STRICT;

For existing projects: Use CHECK constraints as an alternative if you can't migrate:

CREATE TABLE users (
    user_id CHAR(36) NOT NULL PRIMARY KEY
        CONSTRAINT user_id_length CHECK (LENGTH(user_id) = 36),
    email_address VARCHAR(255) UNIQUE
        CONSTRAINT email_address_length CHECK (LENGTH(email_address) < 256)
);

Version requirements: STRICT requires SQLite 3.37.0+. If you're targeting systems with older SQLite versions, CHECK constraints are your workaround.

Enable foreign keys too: While you're enforcing data integrity, remember that SQLite also disables foreign key constraints by default. Add PRAGMA foreign_keys = ON; to every connection.

The Broader Pattern

SQLite's STRICT mode is part of a broader trend toward explicit, validated data contracts. TypeScript brought type safety to JavaScript. Rust brought memory safety to systems programming. SQLite STRICT brings type safety to the embedded database that's probably running on your phone, your browser, and about a billion other devices right now.

The SQLite team's commitment to backwards compatibility is admirable and necessary for a library this ubiquitous. But for new code, there's little reason not to add that seven-character keyword to every CREATE TABLE statement.

Sources

• Prefer strict tables in SQLite - Evan Hahn's original article
• HN Discussion - 63+ comments analyzing the tradeoffs
• SQLite STRICT Tables Documentation - Official docs
• The Advantages Of Flexible Typing - SQLite team's counterargument
• SQLite ALTER TABLE Documentation - Migration warnings