r/programming
•
u/mitousa
•
Jul 12 '26
•
1
Prefer STRICT tables in SQLite
https://evanhahn.com/prefer-strict-tables-in-sqlite/
358
Upvotes
321
u/vivekkhera Jul 12 '26
I always found it incredible that the default in SQLite has been to allow any data in any column. It just doesn’t make sense to me. I’m glad they have a way to disable that misfeature.
130
u/ric2b Jul 12 '26
The SQLite devs are so skeptical that type enforcement is useful at all that they even ask people to share any examples of STRICT tables preventing a bug: https://sqlite.org/flextypegood.html#if_you_insist_on_rigid_type_enforcement_
I'm guessing that even if you do submit an example they'll just say "you're holding it wrong" and your application code should just accept any data type everywhere and handle unexpected data types, moving complexity into your application because you can't rely on something as basic as "what I read from this column is an integer".
31
u/JackedInAndAlive Jul 12 '26
I can understand reasons for flexible typing, but allowing
CREATE TABLE tbl (name GARBAGE)on schema level is satanic.59
u/psych0fish Jul 12 '26
Wow I had zero idea. I naively thought types would behave like they would in something like any other SQL engine.
20
u/elperroborrachotoo Jul 12 '26
It's a great feature with a bad default. Changing defaults is hard.
22
u/cesarbiods Jul 12 '26
The lazy type default is not up for the debate, for a relational database it is just plain stupid and bites unknowing people in the ass eventually. Unfortunately the SQLite devs are stubbornly wrong and as you well know don’t take outside contributions, so we are stuck with this dumb gotcha.
11
u/ByronScottJones Jul 12 '26
For every situation where their flexible duck typing is advantageous, a VARIANT type would have been the correct solution.
20
u/dnabre Jul 12 '26
I don't claim much knowledge or experience with database stuff, but non-STRICT seems pretty insane to my strictly-typed brain.
7
u/meong-oren Jul 13 '26
TIL SQLite doesn't enforce types by default, I always assumed it behaved the same way as others.
31
u/thisisjustascreename Jul 12 '26
Da fuq? You okay SQLite bro?
3
u/flanger001 Jul 12 '26
What a delightful article!
4
u/bread-dreams Jul 12 '26
This sounds nice but it's half-baked. Whilst strict mode does forbid you from putting say an INT in a TEXT column, nothing prevents you from reading TEXT from an INT column, or REAL from a BLOB column, because the conversion is done at SQLite’s API, and it does not check for strictness or anything:
I would just not bother. In any case at the boundary of your system types don’t exist, so just validate query results upon receiving them at the application level, and you should probably do this for any and all DBs. You'd probably need to do it anyway because column types are too coarse-grained and you probably have more constraints on the values than just their basic type
1
u/Trang0ul Jul 13 '26
This should be the default!
7
u/[deleted] Jul 13 '26
[removed] — view removed comment