3 ms·

Repeating myself from several days ago (https://news.ycombinator.com/item?id=37958006 https://news.ycombinator.com/item?id=37958006): 4 years ago I wrote an in

by olvy0 3y ago

Repeating myself from several days ago (https://news.ycombinator.com/item?id=37958006 https://news.ycombinator.com/item?id=37958006): 4 years ago I wrote an internal tool to serialize records of our hierarchical database to a bunch of hierarchical json files, which are then managed in a normal git repo. Each record is serialized to json, "root" entities in the hierarchy are serialized as to contain all their child entities. This is done as the user works - the database is updated and then the correct json file is read, updated and written back. The current SHA is always updated in the database, so there's a way to recognize users worked outside our app with git on the repo, we can detect that and automatically the database based on the diff. There's also an opposite sync. This was/is a very specific solution to our very specific set of problems. So not applicable to the general problem of "versioning a database". In fact users now complain that this duality of database <-> repo is confusing and we should ditch the database. Our users are unfortunately not very technical and sometimes require a lot of hand holding. In theory they're correct but in practice this would mean ditching our ORM and rewriting one from scratch that will work on top of the json files. While keeping the same API outwards. Which is potentially a lot of work. Maybe that will happen, we haven't decided yet. I wrote much more about it here: https://news.ycombinator.com/item?id=25005993 https://news.ycombinator.com/item?id=25005993

scotty79 3y ago
From time to time I'm searching for a database engine for small databases that stores the data as diffable text, just provides up to date indexing and sql on top of that to make the operations convenient and fast. Didn't find any. I keep encountering BerkleyDB but I don't know how good it is for that purpose.
- zffr 3y ago
  For what use-cases do you need this? Have you considered Fossil (link below)? it is a SCM tool built on top of sqlite. I have not used it myself, but I would assume it stores file revisions as diffs, and since its all just sqlite you should be able to run arbitrary queries on it. https://www2.fossil-scm.org/home/doc/trunk/www/index.wiki https://www2.fossil-scm.org/home/doc/trunk/www/index.wiki
  - scotty79 3y ago
    My usecase is silly. Basically small to medium database backed cms websites. I just want to evolve them as the client already is using them to serve and store content. Also have up to date complete backup of them in git. I could do it easily if I just kept data in text files instead of sqlite, but I really like the speed of the database queries.
    - joshspankit 3y ago
      Why not set the text files as the SSOT and have a hook that populates the database when the files change?
      - scotty79 3y ago
        Maybe the other way around? Use database triggers on inserts an updates to store the data in files as well? And create a procedure to update the database from files after git pull?
        joshspankit 3y ago
        I agree that would be easier to implement, but it violates the SSOT principle by introducing an edge case of lost database changes that have not been written to the files (for example during a system error).
- bachmeier 3y ago
  You might want to look at tsv-utils, or a similar project: https://github.com/eBay/tsv-utils https://github.com/eBay/tsv-utils For the SQL part, but maybe a lot heavier, you can use one of the projects listed on this page: https://github.com/multiprocessio/dsq https://github.com/multiprocessio/dsq (No longer maintained, but has links to lots of other projects)
- o11c 3y ago
  HSQLDB does that, but 1. ew, Java, and 2. there are reasons people don't do that. The major problem with text formats is that it means either you have to rewrite the whole file on every change ... or else you have to append arbitrarily and be able to survive that (if you're committing to git you probably are capable of dealing with the storage, at least). If the former is feasible for you (because incremental updates are rare), just use your favorite database's load/dump around every session, and do some fsync testing (or just keep full backups). If the latter compromise is acceptable there might be something you can do with streaming logical replication on some DBs, but again there's a reason people generally don't.

by olvy0 3y ago

scotty79 3y ago

From time to time I'm searching for a database engine for small databases that stores the data as diffable text, just provides up to date indexing and sql on top of that to make the operations convenient and fast. Didn't find any. I keep encountering BerkleyDB but I don't know how good it is for that purpose.

zffr 3y ago
For what use-cases do you need this? Have you considered Fossil (link below)? it is a SCM tool built on top of sqlite. I have not used it myself, but I would assume it stores file revisions as diffs, and since its all just sqlite you should be able to run arbitrary queries on it. https://www2.fossil-scm.org/home/doc/trunk/www/index.wiki https://www2.fossil-scm.org/home/doc/trunk/www/index.wiki
- scotty79 3y ago
  My usecase is silly. Basically small to medium database backed cms websites. I just want to evolve them as the client already is using them to serve and store content. Also have up to date complete backup of them in git. I could do it easily if I just kept data in text files instead of sqlite, but I really like the speed of the database queries.
  - joshspankit 3y ago
    Why not set the text files as the SSOT and have a hook that populates the database when the files change?
    - scotty79 3y ago
      Maybe the other way around? Use database triggers on inserts an updates to store the data in files as well? And create a procedure to update the database from files after git pull?
      - joshspankit 3y ago
        I agree that would be easier to implement, but it violates the SSOT principle by introducing an edge case of lost database changes that have not been written to the files (for example during a system error).
bachmeier 3y ago
You might want to look at tsv-utils, or a similar project: https://github.com/eBay/tsv-utils https://github.com/eBay/tsv-utils For the SQL part, but maybe a lot heavier, you can use one of the projects listed on this page: https://github.com/multiprocessio/dsq https://github.com/multiprocessio/dsq (No longer maintained, but has links to lots of other projects)
o11c 3y ago
HSQLDB does that, but 1. ew, Java, and 2. there are reasons people don't do that. The major problem with text formats is that it means either you have to rewrite the whole file on every change ... or else you have to append arbitrarily and be able to survive that (if you're committing to git you probably are capable of dealing with the storage, at least). If the former is feasible for you (because incremental updates are rare), just use your favorite database's load/dump around every session, and do some fsync testing (or just keep full backups). If the latter compromise is acceptable there might be something you can do with streaming logical replication on some DBs, but again there's a reason people generally don't.