--- title: Prefer STRICT tables in SQLite | daily.dev description: SQLite's STRICT tables feature enforces rigid type checking, preventing common mistakes like inserting text into integer columns or using invalid column types.... canonical: https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Prefer STRICT tables in SQLite | daily.dev og:description: SQLite's STRICT tables feature enforces rigid type checking, preventing common mistakes like inserting text into integer columns or using invalid column types.... og:url: https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s og:image: https://api.daily.dev/og/posts/Rbj9vKD7S.png og:image:alt: Prefer STRICT tables in SQLite og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Prefer STRICT tables in SQLite **[Hacker News](https://daily.dev/sources/hn)** · 7 min read · 0 upvotes · 0 comments ## Summary SQLite's STRICT tables feature enforces rigid type checking, preventing common mistakes like inserting text into integer columns or using invalid column types. Adding STRICT to a CREATE TABLE statement restricts columns to valid types (INT, INTEGER, REAL, TEXT, BLOB, ANY) and rejects type mismatches on insert/update. The ANY type still allows flexible columns when needed. Downsides include inability to ALTER existing tables to strict mode, a version requirement of SQLite 3.37.0+, and the SQLite team's own preference for flexible typing. The author argues the type-safety benefits outweigh these drawbacks for most use cases. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### What does the STRICT keyword do when creating a SQLite table? Adding STRICT to a CREATE TABLE statement in SQLite enforces rigid column typing, rejecting inserts or updates that put the wrong type into a column, such as text into an INTEGER column, unless the value can be losslessly converted (like the string '123' into an integer). It also restricts column type declarations to only INT, INTEGER, REAL, TEXT, BLOB, or ANY, causing typos or invalid types like DATETIME or UUID to error at table creation instead of being silently accepted. _Developers deciding how strict to make their schema validation can browse related SQLite deep-dives on daily.dev._ ### Which version of SQLite added support for STRICT tables? STRICT tables were introduced in SQLite version 3.37.0, released in November 2021. Databases containing strict tables cannot be opened by older SQLite versions like 3.36.0, even if the strict table itself contains no invalid data, so upgrading matters before adopting the feature in production. _Track version-specific SQLite behavior changes like this on daily.dev before upgrading a production database._ ### Can you convert an existing non-strict SQLite table into a strict one with ALTER TABLE? No, SQLite provides no ALTER TABLE command to make an existing table strict. The workaround is to create a new strict table with the same schema, copy the data over with INSERT INTO ... SELECT, drop the old table, and rename the new one, though this copy step will fail if the old table contains invalid data like text stored in an integer column. _Developers planning schema migrations like this one can find more SQLite migration guidance on daily.dev._ ## Similar posts on daily.dev - [SQLite should have \(Rust-style\) editions](https://daily.dev/posts/sqlite-should-have-rust-style-editions-gbz3t7szn) · Lobsters · 0 upvotes · 0 comments - [ALTER TABLE](https://daily.dev/posts/alter-table-hyw4okihc) · Lobsters · 0 upvotes · 0 comments - [sqlite-utils 4.0a1 has several \(minor\) backwards incompatible changes](https://daily.dev/posts/sqlite-utils-4-0a1-has-several-minor-backwards-incompatible-changes-itttfhqzt) · Simon Willison · 0 upvotes · 0 comments - [Zero Downtime Schema Changes With CrateDB](https://daily.dev/posts/zero-downtime-schema-changes-with-cratedb-9gwpaklud) · CrateDB · 0 upvotes · 0 comments - [SQLite JSON Superpower: Virtual Columns \+ Indexing](https://daily.dev/posts/sqlite-json-superpower-virtual-columns-indexing-awwu21k4f) · Hacker News · 1 upvotes · 0 comments --- Tags: [#database](https://daily.dev/tags/database), [#sqlite](https://daily.dev/tags/sqlite) [View this post on daily.dev](https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Prefer STRICT tables in SQLite","url":"https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s"},"datePublished":"2026-07-11T19:27:54.661Z","dateModified":"2026-09-14T07:25:15.492Z","description":"SQLite's STRICT tables feature enforces rigid type checking, preventing common mistakes like inserting text into integer columns or using invalid column types....","image":"https://media.daily.dev/image/upload/s--1KxV4ohY--/f_auto/v1722860400/public/Placeholder%2007","thumbnailUrl":"https://media.daily.dev/image/upload/s--1KxV4ohY--/f_auto/v1722860400/public/Placeholder%2007","isAccessibleForFree":true,"articleSection":"Hacker News","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Hacker News","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/hn","url":"https://daily.dev/sources/hn"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"database,sqlite","timeRequired":"PT7M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Hacker News","item":"https://daily.dev/sources/hn"},{"@type":"ListItem","position":3,"name":"Prefer STRICT tables in SQLite"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/prefer-strict-tables-in-sqlite-rbj9vkd7s#faq","mainEntity":[{"@type":"Question","name":"What does the STRICT keyword do when creating a SQLite table?","acceptedAnswer":{"@type":"Answer","text":"Adding STRICT to a CREATE TABLE statement in SQLite enforces rigid column typing, rejecting inserts or updates that put the wrong type into a column, such as text into an INTEGER column, unless the value can be losslessly converted (like the string '123' into an integer). It also restricts column type declarations to only INT, INTEGER, REAL, TEXT, BLOB, or ANY, causing typos or invalid types like DATETIME or UUID to error at table creation instead of being silently accepted. Developers deciding how strict to make their schema validation can browse related SQLite deep-dives on daily.dev."}},{"@type":"Question","name":"Which version of SQLite added support for STRICT tables?","acceptedAnswer":{"@type":"Answer","text":"STRICT tables were introduced in SQLite version 3.37.0, released in November 2021. Databases containing strict tables cannot be opened by older SQLite versions like 3.36.0, even if the strict table itself contains no invalid data, so upgrading matters before adopting the feature in production. Track version-specific SQLite behavior changes like this on daily.dev before upgrading a production database."}},{"@type":"Question","name":"Can you convert an existing non-strict SQLite table into a strict one with ALTER TABLE?","acceptedAnswer":{"@type":"Answer","text":"No, SQLite provides no ALTER TABLE command to make an existing table strict. The workaround is to create a new strict table with the same schema, copy the data over with INSERT INTO ... SELECT, drop the old table, and rename the new one, though this copy step will fail if the old table contains invalid data like text stored in an integer column. Developers planning schema migrations like this one can find more SQLite migration guidance on daily.dev."}}]} ```