Hacker News Reader

Comment by cjs_ac | Hacker News Reader

> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is.

I find these 'different therefore wrong' takes to be immature.

Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other use cases than those other engines, and therefore has other design decisions.

Ultimately, all computer programs are solutions to problems, and the approach to solving a problem depends on the nature of the problem. A list of grievances and a Boolean judgment is useless without stating the problem that the author is trying to solve.

View on HN

The wacky approach to column types came from SQLite's origins of being closely integrated with Tcl. Knowing that doesn't somehow mean it was a good default worth carrying on for decades.

Firstly, while it's the default, it's not mandatory. As the author discovered you can use STRICT to make the columns typed, and types to be enforced.

The reason it remains unaltered by default is because one of the goals (and accomplishments) of SQLite is that the on-disk-data-file is completely backwards compatible, and cross-platform. This is a very important feature in some situations, and not lightly tossed aside because some old default or system is not in vogue anymore.

Put another way, "default to what's best for all users collectively" is not strictly equivalent to "default to what's most intuitive to unfamiliar users".

The latter is the luxury of end-user software unburdened by decades of legacy compatibility obligations.

STRICT only works for simple types though as the article noted, so you can't do

    CREATE TABLE mytable (
      id INTEGER PRIMARY KEY, 
      created DATETIME, 
      mything JSON
    ) STRICT;

That's the point of STRICT isn't it? 2 of the 3 types in your SQL statement aren't valid sqlite data types.

The point is that they are types supported by most other sql databases and supported as input/output by sqlite functions, but you can't validate that stored data is the correct type.

A lot of people get real obsessive about the "worlds best" thing, take a motorcycle for example, but them themselves lack the technical skill to actually require that level of equipment. Looking for the "best" whatever all the time is a sign you don't know what you are doing.

I've felt this way for a long time, and I enjoy using lower quality things sometimes just to confirm it's worth the investment into something more advanced.

Like violin, I still play my $50 special I bought in 2005, because it sounds fine and I'm terrible so it's Good Enough.

On the other hand, I am building my second computer keyboard that's going to cost me $300+ because, well, I make a living with these things and I use it 60+ hours a week. I have wrist problems, so a better keyboard literally translates into more hours billed.

I would be happy if SQLite offered a native DATETIME type. That has been in the SQL spec since 1992! and is supported by every RDBMS that exists today in the planet. But nooo - its gets stored as TEXT and causes all kinds of issues for folks who don't know this.

Reply on news.ycombinator.com