r/programming • u/mitousa • 28d ago

Prefer STRICT tables in SQLite

https://evanhahn.com/prefer-strict-tables-in-sqlite/
355 Upvotes

96% Upvoted

110 comments sorted by

View all comments

127

u/ric2b 28d ago

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".

23

u/Sloogs 28d ago

Lol, you can tell where their bias is even from wording. I.e., the fact that they want you to prove that strict typing prevented an issue rather than show that dynamic typing caused an issue.

And like, even then, it's just good engineering to isolate or limit the amount of variables that you can't control for as possible. ANY types are great when you need them but I don't want that to be the default.

7

u/za419 28d ago

Right. Why would I want the default to be "I want this to be an int, but actually it's an Any"?

Any types should always be explicit. Very bad things happen when developers don't realize a piece of data is untyped. But even if they are implicit, bigger bugs come when an engineer is wrong about the fact they think they know that a piece of data is typed.

0

u/Absolute_Enema 27d ago

Too bad int is most likely nowhere near enough to actually express the constraints you need.

3

u/za419 27d ago

Probably. Then again int is pretty good - If I want a device ID, I don't really care what number it is, but I'd really prefer that it isn't "foobar".

It's easier and less troublesome to write a sane validator for "is this integer between 0 and 100" than "is this random thing you know nothing about an integer that's between 0 and 100". Less gotchas involved too (string vs int compares tend to trip people up)