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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

21–30 of 364 posts

Re: SQLite is not a toy database

#21

Post by michaelmior »

hobo_mark wrote: post #15
michaelmior wrote: post #11

> There is nothing more convenient than SQLite for analyzing and transforming JSON. You can select data directly from a file as if it were a regular table. Personally I love jq[0] for this purpose. I haven't really used SQLite for working with JSON, but the examples given are very verbose. [0] https://stedolan.github.io/jq/

I never managed to wrap my head around jq syntax however

I agree it can be a little obtuse at times and I still do occasionally find myself having to consult the documentation despite using it pretty regularly for some time. I do also sometimes make use of gron[0] since it integrates well with standard Unix tools. The pipelines I write with gron are probably pretty brittle in the sense that I expect they might be incorrect for some edge cases. But for some quick exploratory analysis, it's a tool I like to have handy.

[0] https://github.com/tomnomnom/gron/

Re: SQLite is not a toy database

#22

Post by petercooper »

andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres.

I've always been surprised WordPress didn't go with SQLite though - it'd have made deployment so much easier for 99% of users running a small, simple blog.

Re: SQLite is not a toy database

#23

Post by kwhitefoot »

tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

Take a look at DB Browser for SQLite: https://sqlitebrowser.org/

It has some plotting built in. Not sure if it is sophisticated enough for what you need but it might be worth a try.

Re: SQLite is not a toy database

#25

Post by busterarm »

Thaxll wrote: post #20
andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html

Most websites/frameworks access their database through a singleton pattern/single-connection anyway.

Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)

Re: SQLite is not a toy database

#26

Post by tfehring »

tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

It's complementary, since unlike R/Pandas it can fit too-big-for-memory data - I've used it ad-hoc to store big-ish datasets to pull into R. Though if you're using Python I think that use case is mostly superseded by Dask.

Re: SQLite is not a toy database

#27

Post by [deleted] »

andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

[deleted]

Re: SQLite is not a toy database

#28

Post by Thaxll »

busterarm wrote: post #25
Thaxll wrote: post #20

Earlier quoted context omitted.

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html

Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)

That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.

Re: SQLite is not a toy database

#29

Post by unnouinceput »

petercooper wrote: post #22
andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres. I've always been surprised WordPress didn't go with SQLit…

At the time when WordPress rolled out SQLite was still in its infancy while MySQL was already a mature DB, hence the choice. These days however they could, at least, do a nice wizard to ask "do you want to run a blog only?" and deploy SQLite instead.

Re: SQLite is not a toy database

#30

Post by petercooper »

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further:

https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten.

https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replication system.

https://sqlite.org/lang_with.html#rcex3 - you can do graph-style queries against SQLite too (briefly mentioned in the article).

https://github.com/aergoio/aergolite - AergoLite is replicated SQLite but secured by a blockchain.

https://github.com/simonw/datasette - Datasette (mentioned at the very end of the OP article) is a tool for offering up an SQLite database as a Web accessible service - you can do queries, data analysis, etc. on top of it. I believe Simon, the creator, frequents HN too and is a true SQLite power user :-)

https://dogsheep.github.io/ - Dogsheep is a whole roster of tools for doing personal analytics (e.g. analyzing your GitHub or Twitter use, say) using SQLite and Datasette.

Post reply on HN