Comment by vsroy | Hacker News Reader

Comment by vsroy | Hacker News Reader

Parent Full thread

Context: I see a bunch of people here recommending SQLite. I have a suggestion, try out LMDB. It's kind of like noSQL SQLite. It's a simple in-process K/V store with a few features like compound keys that allow you to model relational data well enough.

I recently used lmdb for webhighlighter.com (specifically the wrapper: https://www.npmjs.com/package/node-lmdb), and it was a fantastic decision.

A lot of people here say "use SQLite for small projects". But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me.

For example: In my application, people can leave comments on a (what is effectively) a post. A SQL-native solution might have a table for comments, with foreign keys to post IDs. That's 10x more engineering then I want to do for an MVP. I just store all the comments as an array on a post. This means reads read all the comments and writes require reading all the comments, appending, and then re-writing. That's totally fine, and will probably scale me to 100x my current traffic.

LMDB-JS is great. It allows you to serialize arbitrary JS objects to LMDB using the message pack encoding system. This makes for some super concise code.

Here's my entire data layer: - Interface: https://github.com/vedantroy/grape-juice/blob/main/site/app/... - Implementation: https://github.com/vedantroy/grape-juice/blob/main/site/app/...

TL;DR -- I won't use SQLite, for, I don't know, my first 10K users?

> But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me.

Key-value stores are not a replacement for SQL, they solve a simpler problem. If you actually can do with a key value store I would argue a SQL table with two columns key and value with key being a primary key will do just fine. And using it needs simple select and 'insert on duplicate key update' statements. The effort for migrations won't be higher than whatever you had to configure for your key-value store.

However, if you can't actually do with a key-value store you will end up writing a lot of the features SQL provides in application code. Indices, joins, grouping, ordering, etc. You might not notice it at first, but you will blow up complexity in your business logic reinventing existing SQL features and chances are high you're doing it worse and less performant than what e.g. Postresql offers out of the box.

SQL is just such a powerful tool that is at the same time incredibly easy to use for simple scenarios.

Added benefit of using SQLite, you can migrate to a more powerful SQL database later without having to reengineer your whole data layer.

You can get pretty far using LMDB, specifically, its compound-key feature is quite good, and gets you a decent chunk of the relational model. Combined with Message Pack encoding, you can get an extremely concise data layer.

https://github.com/kriszyp/lmdb-js/discussions/170

I think there's a reason Tail Scale used a literal JSON file for up till 150 MB. of data: https://tailscale.com/blog/an-unlikely-database-migration/

So, I think the unconventional wisdom is correct.

Flashback to when leveldb was hot in the node ecosystem

KV stores are fun but you end up writing a lot of queries and indexes manually

Have you seen SQLightning and/or LumoSQL?

* https://github.com/LMDB/sqlightning

* https://github.com/LumoSQL/LumoSQL

SQLightning was the initial project combining SQLite3 with an LMDB backend. It seemed to be more an experimental/Proof-of-Concept thing, and isn't maintained.

LumoSQL is an alternative project (maintained), providing a SQLite3 front end with various optional storage backends. One of which is LMDB.

Note - I'm not affiliated with either project, I just remembered they exist. :)

Reply on news.ycombinator.com