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

111–120 of 188 posts

Re: Prefer strict tables in SQLite

#111

Post by thaumasiotes »

alwa wrote: post #62
sinfulprogeny wrote: post #43

Earlier quoted context omitted.

Unless you don't know it exists of course

Is it better to learn that it exists through surprise , by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?

> Is it better to learn that it exists through surprise, by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?

The premise of your question doesn't work. If one person is going to learn about the option and a second one isn't, the better system for that to occur in is the one in which more people end up with correct behavior.

In this case, that means surprising the guy with existing (buggy) software; he'll have to fix the bugs. It means not bothering the guy who's starting a greenfield project. Neither of them will be subject to this bug.

The alternative, which you seem to be saying you prefer, is that the guy with buggy software should continue to have bugs, and the greenfield guy should also have bugs.

Re: Prefer strict tables in SQLite

#112

Post by thaumasiotes »

Asooka wrote: post #46
ezst wrote: post #31

Earlier quoted context omitted.

Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.

Well no, the software should be configured in its best state by default. Otherwise you run into the case where the user has to read the documentation like a legal contract to find all the footguns that need disabling. If STRICT is strictly better, than that should be the default. The correct approach here would be for the user to pass along a "compatibility version" tag when first connecting, which would set the defa…

> It may be too late for sqlite, but if I were designing an API that had to remain stable for decades now, I would put an enum with possible versions in a header and require the user to pick one.

I read an essay about XML once where the author noted that he got a warm feeling inside whenever he saw the opening tag:
He noted that there were no higher versions. (Though they're now up to 1.1.) But he considered that the inclusion of a version number from the beginning of the standard was a shining example of why they hadn't needed a new version.

Re: Prefer strict tables in SQLite

#113

Post by thaumasiotes »

panzi wrote: post #104
sheept wrote: post #99

Earlier quoted context omitted.

Why not store them as integers? Representing dates is a UI responsibility

I also want to be able to debug the database in an SQL console with nice looking date-times.

You can already do that: https://sqlite.org/lang_datefunc.html

Re: Prefer strict tables in SQLite

#114

Post by noisy_boy »

ezekiel68 wrote: post #71

Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to…

> Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry

Sometimes you can build a successful business out of doing so: https://aeron.io/

Re: Prefer strict tables in SQLite

#115

Post by tyre »

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

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.

Re: Prefer strict tables in SQLite

#116

Post by bch »

bch wrote: post #66
gunapologist99 wrote: post #51

Earlier quoted context omitted.

That was your language's driver "helping" you. There is no SQLite octal type.

I'll see if i can find the case - my description was a bit hand-wavy because it was a while ago, and just drawing on memory -- not that your explanation couldn't be true, but I thought I exercised that possibility. For your part, you'll remain sceptical it wasn't the driving language when I tell you it was Tcl, which is indeed the origin of this "manifest type" we're discussing :)

I can't replicate atm, but I now think it had to do w scientific notation, not octal - so "1e234..." into a text column. And istr "strict" solving my problem, but...

Re: Prefer strict tables in SQLite

#117

Post by Rohansi »

lacunary wrote: post #84
Rohansi wrote: post #77

Earlier quoted context omitted.

> I never took SQLite seriously for the very reason that field types were not enforced by default. I can understand not taking it seriously if it was completely unsupported but I'm pretty sure most databases don't have perfect default configs. Even PostgreSQL needs configuration for optimal performance because the defaults are for low (minimum) spec systems. > then eventually adding nearly all the reliability facilit…

tuning defaults for performance feels much more acceptable than tuning defaults for correctness

It's for backwards compatibility, as always. You also need to enable foreign keys or they aren't enforced. You also need to tune for performance (enable WAL, adjust cache size, etc.).

The SQLite documentation is very up front about these things [1] and more. It should only be an issue if you're the type of person who never reads any documentation.

[1] https://sqlite.org/quirks.html

Re: Prefer strict tables in SQLite

#118

Post by akoboldfrying »

Rendello wrote: post #80
ezekiel68 wrote: post #71

Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to…

Even foreign keys weren't available before 2009, and are still not enabled by default: https://sqlite.org/foreignkeys.html

Thanks, I've been using SQLite for a while and I did not realise that FKs are disabled by default! Fortunately not on anything critical, still... Yikes.

Re: Prefer strict tables in SQLite

#120

Post by win311fwg »

frollogaston wrote: post #101
win311fwg wrote: post #60

Earlier quoted context omitted.

> Yeah my DB is the one place I want strict types. Well also RPCs. Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting…

But one DB one app is fairly common with Postgres too, particularly if you're adhering to a services deliniation. Guess if your code enforces types at DB insert time, the DB doesn't need to, but one of those is more likely to change than the other.

> But one DB one app is fairly common with Postgres too

That is common today, but remember that Postgres is now 40 years old. It is so old that it was originally based on QUEL rather than SQL. Back then database servers were designed to be what we now think of as the "API server". That necessitates runtime input validation same as your "API server" needs input validation today. If you were designing Postgres from scratch now you would do a lot of things differently, but it was built for its time.

> Guess if your code enforces types at DB insert time

It would be unusual for your programming language to magically turn strings into ints, or the like, so you can prove statically that your code won't insert the wrong thing. Duplicating the same thing at runtime doesn't buy you anything. Maybe if you are still trying to futz around with Javascript, but SQLite was designed for statically-typed programming languages.

Post reply on HN