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

31–40 of 127 posts

Re: A Minimalist Guide to SQLite

#31

Post by danso »

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.

One advantage that may seem too obvious to mention: using SQLite allows you to work with data using SQL, which is far, far easier to learn (and be powerful with) than trying to learn Python or even R if you are new to languages.

Even as an experienced programmer, I find SQL to almost always more elegant for expressing logic than Pandas or R. My workflow is usually having a script execute a SQL statement to do the heavy data work, and then importing the results of that SQL statement into another programming environment (e.g. R/ggplot2 for visualizations).

Re: A Minimalist Guide to SQLite

#32

Post by zackelan »

flatline wrote: post #24
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.

Sqlite is great but it is a poor substitute for a "real" database where you need any serious constraint enforcement at the database level. Yes, you can enable RI, but it's off by default. There is no field length constraint on text values, no native datetime, boolean, or guid types. You can't structurally alter tables without building a new one and copying the old. If you actually need any of these things you can enf…

> Sqlite is great but it is a poor substitute for a "real" database

This is true, and explicitly acknowledged by the author:

> SQLite does not compete with client/server databases. SQLite competes with fopen().

https://sqlite.org/whentouse.html

If you're deciding between Postgres and SQLite, in most cases you're doing it wrong (there are a few exceptions, such as serving a low-traffic almost-entirely-read dynamic website can be accomplished easily with either). Usually you want to choose between SQLite and "anything else that involves writing directly to the local filesystem".

Re: A Minimalist Guide to SQLite

#33

Post by hasenj »

danielvf wrote: post #28
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.

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.

Re: A Minimalist Guide to SQLite

#34

Post by ww520 »

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.

Much easier to work on data with SQL. I can cleanse, deduplicate, fill in default, update, merge, join, and generate reports with a few SQL.

I had an interest rate app where the backend data collection process consists of a shell script to run curl to download rates from various places as CSV, import CSV into Sqlite, runs a few SQL to clean up/merge/fix default/dedup the data. The process is idempotent and can add new data to existing data. At the end I got a long history of clean up-to-date interest rate data in a Sqlite table.

Re: A Minimalist Guide to SQLite

#35

Post by interfixus »

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.

Despite a lot of habitual nay-sayers, this is absolutely correct. The vast majority of sites simply do not need the black-boxery of handing stuff off to external storage. SQLite will carry the load just fine, and not only be as good as, but an actual improvement.

I have written fairly large web-apps taking fairly decent traffic, and relying solely on SQLite, unless really pressing circumstances required otherwise. Healthy exercise too: Sharpens your focus, "Do I absolutely need to do a write here?".

Re: A Minimalist Guide to SQLite

#36

Post by Something1234 »

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…

Have you tried sqlitebrowser? It's fantastic, I use it a lot.

http://sqlitebrowser.org

Re: A Minimalist Guide to SQLite

#37

Post by wongarsu »

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.

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 reduces the number of headaches you get when trying to use your data in new code.

Scalability is a very different problem that happens to favour PostgreSQL, but most projects indeed never get far enough that this matters.

Re: A Minimalist Guide to SQLite

#38

Post by bschwindHN »

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.

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

Re: A Minimalist Guide to SQLite

#39

Post by wongarsu »

hk__2 wrote: post #14
Avshalom wrote: post #12

Earlier quoted context omitted.

Can but probably won't.

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.

Re: A Minimalist Guide to SQLite

#40

Post by wongarsu »

hk__2 wrote: post #11
Avshalom wrote: post #10

Earlier quoted context omitted.

An sqlite db file is probably as light as your json or csv AND better formed/typed

It’s easier to compare it to CSV than JSON, because the former is tabular while the latter can have deep imbrications that aren’t well represented in SQL(ite).

Most SQL databases are much more like JSON than CSV: deeply nested and interconnected. It's not unusual to join three different tables together to get something you want, which is effectively the same as nested JSON (though a bit more flexible, because it allows for circular dependencies and the like).
Post reply on HN