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

A Minimalist Guide to SQLite

tech.marksblogg.com

Post reply on HN
Print view View original

41–50 of 127 posts

Re: A Minimalist Guide to SQLite

#41

Post by daveguy »

hasenj wrote: post #19
echlebek wrote: post #16

SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

> I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

On the flip side, setting up a PostgreSQL instance is so easy now with docker that you could go ahead and use it with a fraction of the admin overhead that it used to require:

https://hub.docker.com/_/postgres/

Re: A Minimalist Guide to SQLite

#42

Post by h1d »

xellisx wrote: post #27
hasenj wrote: post #19

Earlier quoted context omitted.

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

Of course you have sites built with bloated software, like Wordpress...

Only if it were just bloated... Global variables to this day.

Re: A Minimalist Guide to SQLite

#43

Post by hexene »

danso wrote: post #29

Very nice! I'm in the middle of writing a book based on SQLite (the overall topic is data analysis with SQL, but SQLite is the medium) while I'm teaching it to students. I used to teach MySQL but it was just so goddamned hard to get it configured correctly on people's computers (I always hated when work had to be done on pre-configured computers lab rather than my own laptop), nevermind the server/client/daemon aspec…

I'm of course not suggesting you switch back to MySQL, but distributing preconfigured MySQL docker images perhaps might've helped with your configuration troubles.

Re: A Minimalist Guide to SQLite

#44

Post by BeetleB »

echlebek wrote: post #16

SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.

Eh. Definitely not reliable in my experience. I don't use SQLite directly, but I use apps that save stuff in it. I've had issues of losing data because the SQLite DB became corrupt.

Although to be fair, I have no idea if this is any less common with other DB's.

Re: A Minimalist Guide to SQLite

#45

Post by bane »

daveguy wrote: post #41
hasenj wrote: post #19

Earlier quoted context omitted.

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

> I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite. On the flip side, setting up a PostgreSQL instance is so easy now with docker that you could go ahead and use it with a fraction of the admin overhead that it used to require: https://hub.docker.com/_/postgres/

The running docker image will still need many more resources than opening up a SQLite dB and running a few queries.

Re: A Minimalist Guide to SQLite

#46

Post by echlebek »

BeetleB wrote: post #44
echlebek wrote: post #16

SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.

Eh. Definitely not reliable in my experience. I don't use SQLite directly, but I use apps that save stuff in it. I've had issues of losing data because the SQLite DB became corrupt. Although to be fair, I have no idea if this is any less common with other DB's.

If you don't think sqlite is reliable, don't ride in an airbus a350 ;)

https://sqlite.org/famous.html

Re: A Minimalist Guide to SQLite

#47

Post by hasenj »

wongarsu wrote: post #37
hasenj wrote: post #19

Earlier quoted context omitted.

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

For me, using PostgreSQL is like using static typing: a bit more work, but it catches a lot of bugs. SQLite favours a very permissive approach, while PostgreSQL favours locking everything down with strict types and strong integrity checks, and it's very easy to verify lots of details of your data before it's accepted into the database. That takes work to set up properly and maintain, but it catches bugs early and red…

Strictly speaking, SQLite is dynamically typed in a sense, because the database does not enforce the values in the rows match what the columns claim about the type.

But from a developer experience point of view, the main difference between dynamic and static typing is the following:

Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document what everything is supposed to be.

In other words, dynamic typing has a high cognitive tax.

With this in mind, I find that PostgreSQL imposes a cognitive tax in terms of maintaining a separate process (potentially on a different machine) that a lot of times is outside your direct control. Not to mention all the strange ways in which it can break.

SQLite just works. There's nothing to configure. So there's a lot less cognitive tax.

There is still of course the tax of having to ensure that all your sql statements are valid and that they return what you expect them to return, but this is also the case with PostgreSQL.

Re: A Minimalist Guide to SQLite

#48

Post by hasenj »

daveguy wrote: post #41
hasenj wrote: post #19

Earlier quoted context omitted.

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

> I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite. On the flip side, setting up a PostgreSQL instance is so easy now with docker that you could go ahead and use it with a fraction of the admin overhead that it used to require: https://hub.docker.com/_/postgres/

Docker is its own nightmare :/

I just find it weird and confusing.

Also, how do you setup a database in docker? Docker does not support persisting data as far as I know. So if you need to restart the thing, all the data will be wiped. At least that's how it seems to be.

Re: A Minimalist Guide to SQLite

#49

Post by takeda »

danso wrote: post #29

Very nice! I'm in the middle of writing a book based on SQLite (the overall topic is data analysis with SQL, but SQLite is the medium) while I'm teaching it to students. I used to teach MySQL but it was just so goddamned hard to get it configured correctly on people's computers (I always hated when work had to be done on pre-configured computers lab rather than my own laptop), nevermind the server/client/daemon aspec…

I suppose you are using SQLite as a tool to teach analysis so this is probably not a big issue.

But I really disliked that my database teacher decided to use MySQL to teach about databases (that was about 10 years ago).

Several times he would talk about specific concept, then mention that MySQL doesn't have it (like CHECK constraint, enforcing foreign constraints, aborting statements through triggers (emulated by throwing exception) etc). If we are going to learn universal database concepts, why not use database that supports them. I personally learn things better when I can try runs out and see how they work.

Sorry for my rant, it just reminded me about that.

Re: A Minimalist Guide to SQLite

#50

Post by geokon »

CJefferson wrote: post #2

I feel this article misses out on "why". I tend to store stuff as csv or json, then slurp it into python to operate on it. It's not clear what benefit putting your csv into sqlite gets you, from this article.

and why SQLite and not something newer like datomic
Post reply on HN