Hacker News Reader

Comment by iagovar | Hacker News Reader

As a non-dev intruder I have to say that I love SQLite. I do a lot of data-analysis and it makes everything easy, from fast SQL Wizardry to sharing the DB just coping a file! Just how amazing is that?!

It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited!

View on HN

I work in data analysis too and SQLite is to me what MS Excel is to other people in the industry. I have development background so I never picked up in MS Excel to same degree as my colleagues from statistics backgrounds. In corporate organisations where DBAs monitor the use of their database with an eagle eye SQLite is a fantastic option to analyse several millions of lines of data on your desktop. I find DB Browser for SQLite (DB4S)[0] is invaluable when using SQLite.

[0]https://sqlitebrowser.org/

Excel is nice as a simple sandbox, and PowerQuery is amazing IMO, the problem is that it's basically impossible to work with large datasets. If you didn't know about it I recommend you to play a bit with it.

And check SQLiteStudio for Windows, is nice too.

I handle hundreds of millions of rows in excel trivially.

PowerPivot is extremely amazing.

PowerBI is quite nice too.

Most people don't realize that Excel (like PowerBI) has an in-memory, compressed, column store database inside of it.

Loading hundreds of millions of rows into it takes a while, but given a commensurate amount of RAM and a reasonable data model (single table or star schema), performing aggregations with a pivot table is pretty snappy.

Well, for me with my personal PC with a quad core and 24gb DDR3, it gets unusable for about 1 million rows, but it's true that most of my datasets have around 10 columns or so.

You should be using PowerPivot in Excel.

Here's a video showing rapid pivots of 100m row dataset

https://youtu.be/0WwFJ0Zg3d8?t=2285

Mmm, I was using PowerQuery and then use the editor to do the transformations (don't remember exact names sorry), and I just couldn't cope with it.

Does SQLiteStudio let you cancel an unexpectedly slow query part way through? DB Browser for SQLite doesn't seem to, and I don't really know what I'm doing so often accidentally set off a query that can't use indices without doing "EXPLAIN QUERY PLAN" first.

It does.

> DB Browser for SQLite

If it helps, our development builds for quite a few months have:

https://nightlies.sqlitebrowser.org/latest/

Our recent 3.12.0-alpha1 release does as well:

https://nightlies.sqlitebrowser.org/latest/

The first beta release for 3.12.0 should be out next week. There's not much change in it though (mostly language string changes), as the alpha1 has turned out to be really stable. :)

My tools of choice are similar: sqlite, DB browser, Jupyter. I've found that DB browser struggles with analytical queries on tables over a few GB though. Still an invaluable exploratory tool.

Check SQLiteStudio. DBeaver also supports SQLite as far as I remember, but it's a bit more cumbersome and really geared towards mysql etc.

I used to use this but if you have a JetBrains sub you can open these files directly. Or even better if its a full subscription DataGrip is fantastic and they recently added MongoDB support. Which is not perfect but good enough!

For Windows also check HeidiSQL (open source, very feature-rich)

Thanks for this. I used to be a longtime user of HeidiSQL but drifted away because I landed up in an Oracle environment. At the time I drifted away SQLite was not supported. I look forward to being re-acquainted with HeidiSQL.

I have a lot of Excel-like use cases for which SQLite would be a great fit if only it had a decimal type.

As in like Java Bigdecimal, with a BigInteger on the left side, and a fixed precision on the right?

As in other database systems such as Postgres. Even a C# style decimal floating point type would be sufficient.

Basically--it's a struct containing a byte array, weight, scale, and sign, and all the arithmetic operations are implemented in software. So it's really slow, and each RDBMS has to implement this type from scratch, or find a vendor lib that provides a suitable one, because C/C++ standard libraries don't provide one.

It does have a decimal type.

No, that's just an alias for numeric type affinity which results in a binary floating point type if there's a decimal point.

There's also a long tradition of "faking" decimals with integers. I have always found that to be extremely tedious and error prone.

I have developed SQLiteDecimal: http://chiselapp.com/user/lifepillar/repository/sqlite3decim...

Still very rough, but usable.

> WARNING: this library is NOT ready for production. Use at your own risk!

How far off do you reckon it is from being "production" ready?

Asking because we've been adding useful SQLite extensions as optional extras in our (sqlitebrowser.org) installer. Can add yours too, if you reckon the code is reasonably cross platform and shouldn't cause (many) weird issues. :)

If you like SQLite for data analysis, you might want to check out DuckDB https://github.com/cwida/duckdb which is billed as "SQLite for analytics."

SQLite is a row store, which is best for OLTP (point queries, inserting/updating/deleting one or a few rows at a time), while DuckDB is a column store, which means the data layout has values from the same column stored contiguously, making aggregation queries (GROUP BY) perform much better.

This is great! Thanks for sharing!

Wow, why isn't DuckDB not more widely known!?!?! Looks incredible!!!

I was wondering the same--I guess because it's fairly new (2018) and it came out of a database research group at a European university, rather than a SV tech firm. Therefore, limited marketing budget.

By the way, here's a YT video of a talk given by one of the DuckDB implementers about why they made it, what it's for, and how it works: https://www.youtube.com/watch?v=PFUZlNQIndo

SQLite is started by some Aussie devs right ?

SQLite was started by Dwayne Richard Hipp from North Carolina. 20 years ago.

> Aussie devs

Not SQLite itself. One of the DB Browser for SQLite dev's is an Aussie though. :)

> It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited!

Why naive? That's the experience of all of us with SQLite.

Most of my web apps’ databases are an SQLite file. It’s more than enough for the ammount of traffic they serve and the db files are easy to set up and backup..

I also always wondered why sqlite isn't used more in websites. Especially if you split heavy write workloads to a separate database file it scales quite far.

There's no reasonable way to share a SQLite database between processes on separate machines or VMs.. What website doesn't use at least 2 instances for HA?

How do you make sure you don't lose data in a SQLite DB?

What’s not reasonable about TiDB?

Not sure about parent’s case, but I’ve used it for small blog-type applications where the only writes are from me (the admin) creating new entries. The rest is read only and behind caches, cdns, etc. The blog is effectively a singleton. Analytics and comments are handled by 3rd party providers.

Many websites just run on a single machine somewhere, with maybe some caching in front or off-loaded static files. Even HN is as far as I know still just a single box.

I throw together plenty of simple sites intended for personal use or just a small group. I don't need nor want the added complexity of going for HA. I can easily spin up a cheap VPS with an SSD and scale amazingly far with one instance and a SQLite DB. When it's for personal use or small group it's not a problem to take it offline for maintenance/deploys. I don't even use a load balancer (tho I often throw nginx in front to do mTLS and compression). I can build a site like that in 30 minutes.

I deal with massive scale all day every day for work (kuberntes/openshift/public clouds) so I find the simplicity amazingly refreshing.

I might also say the performance of my "single box" websites is also amazingly good. I've had even developer friends ask me how I get response times so fast, and they are often surprised when I tell them.

Just use DRDB and Linux-HA to replicate the disks hosting the master SQLite database and the standby database.

You can use a regional GCP disk that replicates over all zones in a region (https://cloud.google.com/compute/docs/disks#repds). You cannot mount the same disk to many VMs, but you can force mount the disk to a new VM.

How do you architect this ? This works only for single process web-apps right ? With no HA or failover ?

Right! But as I said, the low traffic volume doesn’t require more than this kind of architecture.

I do regular file backups and have a cron job restarting the web apps each 20 minutes, it’s enough for now. I would love to have a more solid architecture, but it matches the traffic volume and it’s good enough..

TBH, it'll work for a lot of multi-process apps, so long as the app runs on a single server and the app doesn't do anything foolish like write to the database on every page hit. For read-mostly applications -- like a blog, wiki, or even a small web forum -- SQLite is a surprisingly adequate database.

I can't even tell you how many times a dev came to me with some weird question that was very simply answered by "just use sqlite."

I've never thrown more than 20GB of data at it, but it always handles things admirably, and it often (to me) makes the right calls about the level of complexity required (accepts column type defs but really just doesnt care if it maps to string.)

SQLite handles dozens of DB databases surprisingly well, especially in a single user for data analysis scenario.

I have done several multi-terabyte queries on it. One time I had a race with another dev to see who could get the right answer on a 3TB dataset. I was finished before his spark cluster had even spun up.

I totally agree.

I'm a front-end dev that can wing backend from time to time, and I use SQLite as much as possible. On multiple projects now I've run into complications due to complexity or environments, and adding a simplified local development backend with sqlite kept down time to a minimum.

SQLite is awesome.

Same. The last time I was this excited about an RDBMS in your pocket was when BerkleyDB released their Java database in a single JAR file. I am not sure when they initially released it but I remember doing some hobby projects in 2003.

I do occasionally use Apache Derby[0] in similar manner to SQLite. Mostly when data types are critical. Works well when using DBeaver or any SQL editor that allows you to add JDBC connections.

[0]https://db.apache.org/derby/

At my job we started using H2 which much like SQLite has shareable databases. We now wish more databases were this simple. Its just too efficient to share a db in its total current state and someone with a little more experience or who does not have tunnel vision can probe and resolve issues. Plus you can always go between states of broken vs working.

Is there any easy GUI for H2?

I use DataGrip for all database

https://www.h2database.com/html/quickstart.html

It's basic but enough to start.

Reply on news.ycombinator.com