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

51–60 of 127 posts

Re: A Minimalist Guide to SQLite

#51

Post by takeda »

wongarsu wrote: post #39
hk__2 wrote: post #14

Earlier quoted context omitted.

You don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; and one to join both.

Two tables: Person: {id, name} and Hobbies: {person_id, name}. Then you can "SELECT person.name, array_agg(hobbies.name) FROM person JOIN hobbies ON (person.id = hobbies.person_id)" to get your json representation back (at least with postgresql, "array_agg" isn't in standard SQL) That's a pretty simple join, any real database layout I've seen goes much more complicated and much deeper than that.

Your version is denormalized. It is faster, but also more likely to have misspellings and duplicates. It really depends what is more important.

Re: A Minimalist Guide to SQLite

#52

Post by echlebek »

hasenj wrote: post #48
daveguy wrote: post #41

Earlier quoted context omitted.

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

You are meant to use a storage driver such as aufs or overlay with an attached volume to persist information between container lifetimes.

There are a variety of opinions on the goodness of using docker for running a database.

Re: A Minimalist Guide to SQLite

#53

Post by pingiun »

hasenj wrote: post #48
daveguy wrote: post #41

Earlier quoted context omitted.

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

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

In some systems such as kubernetes, data is not saved on a persistent disk. But by default docker containers can retain data. However this is generally solved by using volumes, which you can read about here: https://docs.docker.com/engine/admin/volumes/volumes/

Re: A Minimalist Guide to SQLite

#54

Post by placebo »

>It's used in systems as important as the Airbus A350 so it comes as no surprise the tests for SQLite 3 are aviation-grade

I stumbled upon SQLite around 2001 and have been using and admiring this gem of a software since that time, but hearing that it is aviation-grade is definitely a surprise to me...

Re: A Minimalist Guide to SQLite

#55

Post by hasenj »

echlebek wrote: post #52
hasenj wrote: post #48

Earlier quoted context omitted.

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.

You are meant to use a storage driver such as aufs or overlay with an attached volume to persist information between container lifetimes. There are a variety of opinions on the goodness of using docker for running a database.

Which translates to: I basically don't want to use it. At least not for development. Not as a means to abstract away the setup of something like Postgres; Docker has its own complications when it comes to setting things up, which I must learn _in addition_ to learning everything related to setting up Postgres.

Re: A Minimalist Guide to SQLite

#56

Post by hasenj »

bschwindHN wrote: post #38
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.

Why a (god forbid) for PostgreSQL but not MySQL? I've used both and compared to SQLite they have around the same setup complexity.

Last time I used MySQL was more than 10 years ago, but my impression from back then is that it's more beginner friendly and easy to setup and use than Postgres.

Re: A Minimalist Guide to SQLite

#57

Post by dotancohen »

hasenj wrote: post #33
danielvf wrote: post #28

Earlier quoted context omitted.

SQLite is absolutely fantastic, but I’ve twice used on sites where I would have used a real DB and twice needed to switch back to a real DB after a few months. The concurrency model just doesn’t match up well with multiple web processes doing work. If you are writing on every request, Murphy’s law says that too many of those 500 visitors will be loading pages at exactly the same time...

It's really weird to have to write on every request. If you're logging user visits for example, you can queue those up in a list (in the code) and flush it to the database every few seconds instead of flushing it on every visit.

> queue those up in a list (in the code)

That won't work in the language that most server-side website code is written in. PHP processes are created and destroyed per HTTP request, and even the FastCGI implementation won't let one share data across requests. PHP does have a sessions feature, but that data is written to either the filesystem (by default) or a database on every request!

Re: A Minimalist Guide to SQLite

#58

Post by dragonwriter »

hasenj wrote: post #56
bschwindHN wrote: post #38

Earlier quoted context omitted.

Why a (god forbid) for PostgreSQL but not MySQL? I've used both and compared to SQLite they have around the same setup complexity.

Last time I used MySQL was more than 10 years ago, but my impression from back then is that it's more beginner friendly and easy to setup and use than Postgres.

More than 10 years ago, that was true.

Not so much for quite a while, though.

Re: A Minimalist Guide to SQLite

#59

Post by dotancohen »

takeda wrote: post #51
wongarsu wrote: post #39

Earlier quoted context omitted.

Two tables: Person: {id, name} and Hobbies: {person_id, name}. Then you can "SELECT person.name, array_agg(hobbies.name) FROM person JOIN hobbies ON (person.id = hobbies.person_id)" to get your json representation back (at least with postgresql, "array_agg" isn't in standard SQL) That's a pretty simple join, any real database layout I've seen goes much more complicated and much deeper than that.

Your version is denormalized. It is faster, but also more likely to have misspellings and duplicates. It really depends what is more important.

The JSON example that it was compared to was also denormalized.

Re: A Minimalist Guide to SQLite

#60

Post by dotancohen »

mistercow wrote: post #18

> Data locality can be greatly improved by storing a SQLite 3 database in memory instead of on disk My understanding (although I can no longer find the page in the sqlite3 docs) was that because of caching, using :memory: is unlikely to make much difference in practice.

Depends where your disk is. Many virtual machines, e.g. Amazon EC2, keep their persistent storage (EBS on AWS) on an external medium, so disk reads have a bit of latency and writes take forever. Keeping the database in memory is a huge benefit on these systems.
Post reply on HN