Hacker News Reader

Comment by dfabulich | Hacker News Reader

https://sqlite.org/flextypegood.html explains why this isn't the default (and probably will never be the default).

> rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows.

That doesn't line up with my experience. (In particular, it may not be easy to fix those corrupted rows; the data may be entirely lost.)

> By suppressing easy-to-detect errors and passing through only the hard-to-detect errors, rigid type enforcement can actually make it more difficult to find and fix bugs.

This doesn't line up with my experience at all.

View on HN

These are similar to arguments that people made about MongoDB. You can store anything! And then most people who used it realized that this is actually terrible, in most cases.

It looks like this is an artifact of when SQLite was written and the strong opinion of its author, less so a rigorous engineering principle. Reading this, it sounds like the author has been criticized a lot on this, is digging in their heels no matter what, and will find any supposed justification.

On the other hand, datatypes like JSON or HSTORE (in postgres) can handle what they are advocating for. But opt-in to YOLO typing is nearly always better than opt-out.

I've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar).

Flexible, yes. You can store anything. The downside is, that I've also found "anything". Stuff attached to the wrong "class", wrong datatypes, missing "obligatory" fields, etc, etc.

It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.

>It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.

Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code.

You could of course have a JSON schema somewhere, but in my experience the whole point of representing the schema as data in the database is to support (limited) end-user driven schema changes.

I would use JSON to store data that complies with a schema that can be modified by third parties outside of my control.

> Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code.

The point is that "dynamism" isn't needed in this case. It's used to define fixed parameter sets for different components, so those components could just as well define a class and use json to (de)serialize it.

IOW: Defining new fields, classes and values is worthless unless the underlying component can interpret them. So code changes are needed anyway, and then class is way better and safer to use.

The safety and trust that comes out of a very reliable database setup is, apparently, a misplaced feeling that makes data bugs harder to fix. I really don’t understand their take.

My experience is the opposite: add as many checks and safety rails to <DB> (Postgres, in my case), and you don’t have to go looking for this sort of mistake, which shouldn’t happen in the first place.

Yeah that doesn't make any sense at all. It sounds like a post-hoc justification to me.

> If you find a real-world case where STRICT tables prevented or would have prevented a bug in an application, please post a message to the SQLite Forum so that we can add your story to this document.

Kind of wild that they don't believe this happens.

Also they totally drew the wrong conclusions from their example in Appendix A. The data type was CHECK'd for a column and they are like "oh if only we hadn't enforced checks of this data type, we would have had to verify it when we opened the database!" instead of "thank goodness we have this CHECK'd this data type, it means we are forced to robustly verify it in one place, instead of using unreliable checks in the application code".

SQLite did come from TCL everything-is-a-string world, so this attitude is not surprising. TCL makes for a very good shell language (much better than Bash or Batch), but a rigid systems language it is not.

I was actually expecting that article to say something about performance. In the 90s telecom DBs removed all constraints including primary key from their ingestion tables for speed, and in general constraints are for OLAP not OLTP.

Having said that, given sqlite's tiny type universe I can't imagine TC would be at all slow.

Wow, SQLite not believing in fail-fast systems is disappointng. Never knew SQLite was JS of SQL!

That doesn't do justice to some of the arguments that SQLite authors are making.

In Postgres, if you insert a real number into an int column, the data gets rounded and stored as int. In SQLite, the data is inserted as real.

Neither approach is fail-fast.

Reply on news.ycombinator.com