Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Prefer strict tables in SQLite

evanhahn.com

Post reply on HN
Print view View original

151–160 of 188 posts

Re: Prefer strict tables in SQLite

#151

Post by IshKebab »

dfabulich wrote: post #79

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,…

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

Re: Prefer strict tables in SQLite

#152

Post by bhaak »

lenkite wrote: post #138
jll29 wrote: post #9

I'd like to see STRICT as the default. That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!

SQLite has a LOT of footguns that one only discovers over time. Dynamically typed by default, Off-by-Default foreign keys, ID re-use in AUTOINCREMENT, WAL Mode needing explicit enabling to ensure readers are not blocked, double-quote/single-quote issues, positional placeholders & named parameters issues, no TIMESTAMP type in 2026 despite being a CORE feature in SQL-92 standard, etc.

I knew most of he issues that you mentioned ... but 'ID re-use in AUTOINCREMENT'?

What the ...?

Re: Prefer strict tables in SQLite

#153

Post by OskarS »

simonw wrote: post #95

> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this: uvx sqlite-utils transform data.db mytable --strict Or in Python: import sqlite_uti…

Does this trick work with foreign keys? Like, if you have an ON CASCADE DELETE, does it delete a bunch of rows in other tables when converting the table to strict?

Re: Prefer strict tables in SQLite

#154

Post by bhaak »

poidos wrote: post #26
masklinn wrote: post #22

Earlier quoted context omitted.

You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.

Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.

But why aren't you pissed at a database where the columns have types that aren't enforced?

Re: Prefer strict tables in SQLite

#155

Post by echoangle »

chasil wrote: post #149
echoangle wrote: post #147

Earlier quoted context omitted.

Why would you do that? To save some bytes in storage?

The largest table where I've done this had over 50 of these booleans. That's one 64-bit number, or 50 different columns.

But if you do the function based index, you actually increase the stored size, right? Seems like a microoptimization to me that I would only do if I really have to.

Re: Prefer strict tables in SQLite

#156

Post by simonw »

OskarS wrote: post #153
simonw wrote: post #95

> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this: uvx sqlite-utils transform data.db mytable --strict Or in Python: import sqlite_uti…

Does this trick work with foreign keys? Like, if you have an ON CASCADE DELETE, does it delete a bunch of rows in other tables when converting the table to strict?

It uses "PRAGMA foreign_keys=0" and "PRAGMA defer_foreign_keys= ON" before running the transformation, then resets those settings afterwards: https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7d...

That's the pattern recommended by SQLite here: https://www.sqlite.org/lang_altertable.html#otheralter

Re: Prefer strict tables in SQLite

#157

Post by cowboylowrez »

bbkane wrote: post #52
mort96 wrote: post #11

Earlier quoted context omitted.

Yeah it's a really weird design decision. Why would I want the database to let me accidentally insert the wrong type? SQLite is mostly great but its philosophy towards type safety leaves something to be desired. I once had to clean up in a project where someone had accidentally stored the strings '1' and '0' in a Boolean column in code deployed to thousands of devices; not fun. Another thing I dislike is the lack of…

the SQLite team explains their preference for dynamic types here: https://sqlite.org/flextypegood.html (please note that I personally strongly prefer static types, but I still found this an interesting read).

"SQLite began as a TCL extension that later escaped into the wild." case closed, everything else is a rationalization but who doesn't like a good rationalization every now and then?

Re: Prefer strict tables in SQLite

#158

Post by krior »

poidos wrote: post #26
masklinn wrote: post #22

Earlier quoted context omitted.

You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.

Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.

But that is still better than getting a table with typed columns that have been used like ANY columns.

Re: Prefer strict tables in SQLite

#159

Post by simonw »

simonw wrote: post #156
OskarS wrote: post #153

Earlier quoted context omitted.

Does this trick work with foreign keys? Like, if you have an ON CASCADE DELETE, does it delete a bunch of rows in other tables when converting the table to strict?

It uses "PRAGMA foreign_keys=0" and "PRAGMA defer_foreign_keys= ON" before running the transformation, then resets those settings afterwards: https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7d... That's the pattern recommended by SQLite here: https://www.sqlite.org/lang_altertable.html#otheralter

Thanks for the nudge, I just added a new test explicitly covering this: https://github.com/simonw/sqlite-utils/commit/d71420065903ff...

Re: Prefer strict tables in SQLite

#160

Post by fauigerzigerk »

zvrba wrote: post #127
tyre wrote: post #115

Earlier quoted context omitted.

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…

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…

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

Post reply on HN