SQLite STRICT Tables for App Developers
A website-reading example that summarizes SQLite STRICT tables and when app developers should use them.
SQLite · STRICT tables · database schema · web read · developer docs
Last updated:
@skill:web:read Read ```json { "type": "website", "embed_id": "ba10b9f4-6d3e-495e-a5d6-7658434e52b1", "url": "https://www.sqlite.org/stricttables.html" } ``` and summarize what STRICT tables change for application developers. Include when they are useful and one migration caveat.
Introduced in version 3.37.0, STRICT tables allow [SQLite](wiki:SQLite) to move away from its traditional "flexible" typing system toward a rigid system similar to [PostgreSQL](wiki:PostgreSQL) or [Oracle Database](wiki:Oracle_Database). ### Changes for Application Developers The `STRICT` keyword, added to the end of a `CREATE TABLE` statement, enforces several new rules: * **Mandatory Datatypes:** Every column definition must include a datatype; datatypeless columns are no longer permitted. * **Restricted Type Names:** You can only use six specific types: `INT`, `INTEGER`, `REAL`, `TEXT`, `BLOB`, and `ANY`. * **Lossless Coercion:** SQLite still attempts to coerce data (e.g., converting the string `'123'` to an integer `123`), but if the conversion cannot be done losslessly (like inserting `'abc'` into an `INT` column), it now throws an `SQLITE_CONSTRAINT_DATATYPE` error instead of silently storing the original string. * **The `ANY` Datatype:** A new `ANY` type is introduced for columns that truly need to store mixed data. Unlike flexible tables, `ANY` in a `STRICT` table preserves the exact input without any numeric conversion (e.g., the string `'000123'` remains a string rather than becoming the integer `123`). * **Implicit Constraints:** `PRIMARY KEY` columns in `STRICT` tables are implicitly `NOT NULL`. ### When They Are Useful STRICT tables are particularly beneficial in the following scenarios: * **Data Integrity:** When you want to ensure that a "dirty" input (like an empty string or a non-numeric identifier) doesn't pollute an integer column. * **Matching Other Systems:** If your application is designed to be database-agnostic, using `STRICT` makes SQLite's behavior more predictable and consistent with other enterprise SQL engines. * **Debugging:** It catches type-mismatch bugs at the insertion layer rather than letting them persist as silent data corruption that causes issues during later calculations. ### Migration Caveat: Restricted Type Names One significant caveat when migrating existing schemas is that **standard SQL type names like `VARCHAR`, `NUMERIC`, or `DATETIME` are not allowed.** If you are converting an existing table that uses `VARCHAR(255)`, you must change it to `TEXT`. Similarly, `BOOLEAN` or `DATETIME` columns must be converted to `INT`, `REAL`, or `TEXT` depending on how you store those values. **Next Steps:** * Would you like to see a code example of a `CREATE TABLE` statement using `STRICT`? * Are you interested in learning about the `WITHOUT ROWID` optimization which can be combined with `STRICT`?