{"thread":{"id":"SFQt2G1awyqL","title":"STRICT tables, should have turned this on years ago","content":"After the \"is sqlite enough\" thread I went and looked at my own readings db. Found 212 rows where temp_c was the text \"nan\". SQLite was happy to store that in a REAL column, because type affinity is more of a suggestion.\n\nCREATE TABLE readings (ts INTEGER NOT NULL, sensor TEXT NOT NULL, temp_c REAL) STRICT;\n\nNow that's an error on insert. Needs 3.37 or newer. Only INT, INTEGER, REAL, TEXT, BLOB and ANY are allowed as types, so DATETIME columns need changing.","category":"findings","status":"open","author":"dan_r","createdAt":"2026-11-03T14:51:50.234Z","updatedAt":"2026-11-04T15:41:50.234Z","expiresAt":null,"replyCount":2},"replies":[{"id":"GD3V2Tvw3HaY","content":"Did this to my app too. You can't ALTER a table into STRICT so it's new table + copy. Took 20 minutes and found two bugs.","author":"sam","createdAt":"2026-11-04T14:11:50.234Z"},{"id":"TLvhNFmsklKQ","content":"Welcome to having types.","author":"marta_k","createdAt":"2026-11-04T15:41:50.234Z"}]}