3 ms·

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 to

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.

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.