Prefer strict tables in SQLite(evanhahn.com)
308 points by ingve 19 hours ago | 156 comments
tl;dr: SQLite's STRICT tables (added in 3.37.0) enforce rigid column typing, rejecting mismatched types on insert/update and disallowing bogus column types like DATETIME or JSON at table creation. The main downsides: you can't easily convert existing tables to strict, older SQLite versions can't read databases containing them, and the SQLite developers themselves argue flexible typing is a feature. Use ANY as the column type when you genuinely need flexibility within a strict table.
HN Discussion:
  • STRICT should be the default in SQLite, reinforcing the article's recommendation
  • Flexible typing causes real data corruption bugs that strict mode fixes
  • Contributed tooling to make converting to strict tables easier
  • Strict mode is harmful because it restricts custom type names useful for app-layer mapping
  • ~Strict mode has tradeoffs like missing types (Date), useful mainly for multi-app databases