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

131–140 of 188 posts

Re: Prefer strict tables in SQLite

#131

Post by masklinn »

petilon wrote: post #58
masklinn wrote: post #20

Earlier quoted context omitted.

> some data types are not available, such as Date. That’s not a type, you just get a numeric-affinity column.

Right, and that's a serious limitation when in strict mode .

The serious limitation is that you create a column as date, you don’t understand what sqlite does with it, and you start storing strings in there, at which point everything is confused.

You can use comments to preserve intent in strict mode, and that’s strictly more useful than fuzzy mode: it is richer, it is clearer, and it is no less reliable.

Re: Prefer strict tables in SQLite

#132

Post by wvenable »

sgarland wrote: post #102

Wait until you read about its quirks [0]. My favorite: “NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.” 0: https://sqlite.org/quirks.html

I was going to say that really shouldn't be a problem but then I read further:

https://sqlite.org/nulinstr.html

Re: Prefer strict tables in SQLite

#133

Post by port11 »

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

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

Re: Prefer strict tables in SQLite

#134

Post by boredatoms »

firesteelrain wrote: post #122
krick wrote: post #82

Earlier quoted context omitted.

Or even better just SET STRICT_TABLES, or whatever other opt-in feature you want. A couple of such statements in the beginning of your migration script (refactored in a way to be reused across all migration scripts) and you are done. I wouldn't want to learn WTF "2026.1 defaults" are.

I just do it in my code like def create_table(name, cols): return f"CREATE TABLE {name} ({cols}) STRICT;"

That relies on you knowing about what good settings are though

Re: Prefer strict tables in SQLite

#136

Post by nvdc »

poidos wrote: post #36
drdexebtjl wrote: post #32

Earlier quoted context omitted.

“I intended this to be an integer but it could really be anything” is not very useful.

Sure it is. If you encounter something that’s not an int, that could be a signal you have a bug in your writers. Or in the source of the data. That’s useful information compared to “oh, I have some ints and some strings, that’s ANY, everything is ok.”

i have no idea why this would be preferable to failing immediately on write

Re: Prefer strict tables in SQLite

#137

Post by masklinn »

ncruces wrote: post #42
e2le wrote: post #21

Earlier quoted context omitted.

> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html

The downside is that you loose the place to store the metadata that a column is supposed to store a date. Which is why I prefer not to use them.

A comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify.

Domain types would be the best fix, but I do not think legacy tables are the second best.

Re: Prefer strict tables in SQLite

#138

Post by lenkite »

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.

Re: Prefer strict tables in SQLite

#139

Post by mort96 »

masklinn wrote: post #18
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…

> Instead, you're expected to just use a text column and store a textual timestamp. You can actually use an integer column and store Unix timestamps (or floats for subsecond accuracy). But yes, sqlite has very little types support and its default behaviour is very much unityped / dynamically typed which I also dislike. Same with having to enable foreign keys every time you open a connection.

Of course you can store timestamps as integers, but then you lose sqlite's built-in date and time functions.

Re: Prefer strict tables in SQLite

#140

Post by mort96 »

3eb7988a1663 wrote: post #23
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…

'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type. Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc

When the column is an integer column, you don't want strings in it.
Post reply on HN