6 ms·

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with <100 users. These programs need a small amount of state for things like tokens, users accounts, and maybe a bit of domain-specific things. 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. Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to exist? Why can't everyone be on a federated server with state that fits in memory/JSON? I guess that's more of a philosophical question about big tech. But I think it's interesting that most of our tech stack choices are driven by projects designed to work at a scale most of us will never need, and maybe nobody needs. As an aside, is there something like SQLite but closer to my use cases? So I guess like the nosql version of SQLite. [0]: https://boringproxy.io/ https://boringproxy.io/ [1]: https://github.com/boringproxy/boringproxy/blob/master/database.go https://github.com/boringproxy/boringproxy/blob/master/datab...

Sammi 5y ago
You seem to be describing leveldb: https://github.com/google/leveldb https://github.com/google/leveldb
root_axis 5y ago
If you care about data normalization and data integrity then SQLite is going to be a much better choice.
- anderspitman 5y ago
  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?
klysm 5y ago
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.
- bob1029 5y ago
  In addition to hiding a ton of complexity, SQLite is actually faster than the filesystem in a surprising range of cases.
  - anderspitman 5y ago
    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.
    - lokedhs 5y ago
      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.
      - anderspitman 5y ago
        As I said before, my use cases, while producing useful software, have yet to require more than is offered by a filesystem.
        klysm 5y ago
        Software that isn’t robust, while still useful, can ruin some days.
- 5y ago
tyingq 5y ago
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.
dkjaudyeqooe 5y ago
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.
- anderspitman 5y ago
  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.
mftb 5y ago
> 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.
anyfoo 5y ago
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.
aduitsis 5y ago
Would Redis be a valid solution for your case?
- twicetwice 5y ago
  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!
  - anderspitman 5y ago
    I agree.
charrondev 5y ago
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.
twicetwice 5y ago
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.
interpenetrate 5y ago
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.
justsomeuser 5y ago
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”.
- anderspitman 5y ago
  > 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?
  - justsomeuser 5y ago
    It's no problem, just a different way of doing the same thing really.

Sammi 5y ago

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

root_axis 5y ago

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

anderspitman 5y ago
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?

klysm 5y ago

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.

bob1029 5y ago
In addition to hiding a ton of complexity, SQLite is actually faster than the filesystem in a surprising range of cases.
- anderspitman 5y ago
  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.
  - lokedhs 5y ago
    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.
    - anderspitman 5y ago
      As I said before, my use cases, while producing useful software, have yet to require more than is offered by a filesystem.
      - klysm 5y ago
        Software that isn’t robust, while still useful, can ruin some days.
5y ago

tyingq 5y ago

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.

dkjaudyeqooe 5y ago

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.

anderspitman 5y ago
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.

mftb 5y ago

> 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.

anyfoo 5y ago

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.

aduitsis 5y ago

Would Redis be a valid solution for your case?

twicetwice 5y ago
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!
- anderspitman 5y ago
  I agree.

charrondev 5y ago

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.

twicetwice 5y ago

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.

interpenetrate 5y ago

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.

justsomeuser 5y ago

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”.

anderspitman 5y ago
> 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?
- justsomeuser 5y ago
  It's no problem, just a different way of doing the same thing really.