Hacker News Reader

SQLite hides a ton of complexity that lives in the filesystem. It’s incredibly hard to do robust IO correctly with the APIs we have.

I almost always choose SQLite for persisting to disk over JSON files. It essentially removes a large class of bugs and is robust enough that I’m not worried about introducing new problems.

In addition to hiding a ton of complexity, SQLite is actually faster than the filesystem in a surprising range of cases.

I never questioned whether it was faster or had more features. My whole point is maybe a lot of programs don't need what it offers.

Most people don't need everything SQLite offers, but almost all programs needs some of its features. And even if you literally do nothing more than storing rows of text, the API SQLite offers is still more convenient than most filesystem API's once you go beyond the bare minimum.

That is to say that there are remarkably few usecases where SQLite isn't better than plain file access.

As I said before, my use cases, while producing useful software, have yet to require more than is offered by a filesystem.

Software that isn’t robust, while still useful, can ruin some days.

SQLite hides a ton of complexity that lives in the filesystem

Since they are using Go, couldn't you say the same thing about the Golang std library? As long as they know how to use a local file as database (do the swap, flush, etc...) I don't see the problem.

What makes you think SQLite uses go? (It's written in C[0])

[0]: https://www.sqlite.org/index.html

Was saying that Go's std lib also "hides a ton of complexity that lives in the filesystem" for you.

No because it doesn’t give you IO abstractions as robust as SQLite.

Do you have an example of a problem I'm likely to encounter at the scale I described in my comment?

If you are doing IO in a non-atomic manner (seems probable), it’s a matter of time before something happens and a file gets messed up. The frequency of that occurring is certainly a function of scale, but I would argue it’s always a bad idea to deal with your persistent state non-atomically.

Additionally, having a schema (even if it’s trivial) protects you from other software problems. It’s much easier to reason about upgrades. And more importantly, if your application complexity suddenly breaches the data complexity threshold where JSON files become a lot more difficult, you already have all the tools you could need at your disposal with SQLite.

> If you are doing IO in a non-atomic manner (seems probable)

No. I typically use a single mutex for the entire database, and as I said haven't had any performance issues. I'm confident for many classes of software this can scale to at least 100 users (maybe with some slight performance tweaks), which is sufficient for many federated services.

A mutex doesn’t give you atomic or durable IO. I’m referring to proper use of fsync and friends.

Your solution works to a point, but it will not be as robust as SQLite. Its ACID powers are incredibly valuable in the real world where things fail or are unreliable. Also the ability to do complicated queries comes in handy sooner than you would think.

Of course you have to then mess around with SQL but you only have to write it once and encapsulate it somewhere and you're done.

I never questioned whether it was faster or had more features. My whole point is maybe a lot of programs don't need what it offers.

> This can all live in memory, but needs to be persisted to disk on writes. I've reached for SQLite several times, and always come back to just keeping a struct of hashmaps[1] in memory and dumping JSON to disk. It's worked great for my needs.

I do a very similar thing for many of my at-home projects. They generally have a single-user, me, and it works great.

Look up "Prevayler" and the "system prevalence" pattern. It introduces occasional data journaling, and so it exists somewhere in between "serialize and persist the whole thing at once" and a full blown DBMS. But I've experimented with system prevalence in the past and decided that just "serializing and persisting the whole thing at once" like you've mentioned was simply a better solution.

For a recent project I picked a similar approach, but a directory full of JSON files, where the filename is the key and the value is the contents of the file. This gets me two improvements over the single massive object persisted to disk in a single file: ACIDish for free from the filesystem, and a large reduction in bytes (potentially) written to disk since you don't have to rewrite a large file every time a single key changes, and this service potentially has frequent writes to a subset of keys.

This latter point matters a lot to me since the disk in question is a Raspberry Pi's SD card, which I've heard have a tendency to wear out quickly, so I wanted to avoid writing to it unnecessarily. And yeah, this system is fragile, not actually ACID, etc etc… but I'm the only one who relies on it (it's not even accessible on the public internet, only over my Wireguard VPN), so it doesn't matter if it breaks!

I will probably rewrite this service later, backed by either SQLite or Postgres, but writing it this way saved me a fair bit of thinking and lines of code, which was great for getting it off the ground and usable to me in like two hours.

Most relational databases offer ACID, for one thing.

And also a query language, which is easy to write and modify queries for, presenting a great benefit over writing large amounts of boilerplate code for looping over records and accessing the right indices every time (keep abstracting that and you'll end up with your own relational database system).

I often import data into SQLite just to work with it, without necessarily even an application in between. Depending on the nature of the data it's either that or Matlab.

For Vanilla Forums we have a file based configuration interface with a bunch of utility methods for writing/reading too it. This is the default and what you really want in a single environment either hosted or in development.

In our cloud infrastructure though we have 10k+ distributed sites so the interface reads/writes to an API backed by a database.

A major benefit to the confit file is that you can just open it in a text editor and muck around during development.

There is a key difference. With the JSON version you are constantly serialising and parsing the entire file, even if you need to write just a single byte.

With SQLite, you can read or write a few bytes without having to process the others.

Also, SQLite would allow observing the current state by querying the file, with the JSON version you would need to keep dumping the state every few seconds.

But I agree with you that most languages have solid support for JSON and it prevents you having to break down those tree structures into rows and then back again. For typed languages the struct becomes the schema.

SQLite indexes and joins also would not be a benefit if your state is small enough that queries are just filter functions with “full table scans”.

> There is a key difference. With the JSON version you are constantly serialising and parsing the entire file

Only serializing. The db is kept in memory, and only deserialized on startup.

But why is this a problem at the scale I'm working with?

It's no problem, just a different way of doing the same thing really.

You seem to be describing leveldb: https://github.com/google/leveldb

BerkeleyDB maybe? DBM files used to be pretty commonly used on Unix boxes. Sendmail, for example, uses DBM files.

Or LMDB, LevelDB, RocksDB, etc, if performance is important.

If you care about data normalization and data integrity then SQLite is going to be a much better choice.

At the scale I described in my comment, I do not care about normalization. Can you give an example of where I'm likely to lose data integrity?

Would Redis be a valid solution for your case?

This adds another service you now need to deploy and maintain. For small personal projects, I definitely would like to avoid that if I can!

I agree.