5 ms·
Show HN: Sir – Git-diff-able JSON database on yer filesystem
- yodon 6y agoI've often looked for a simple human readable file-based DB-like system like this. Are there other competing packages like this?
- LVB 6y agoThis is sort of in the space: https://github.com/msiemens/tinydb https://github.com/msiemens/tinydb
- graderjs 6y agoI just had a quick search, didn't turn up much but there is this https://github.com/pksunkara/nosqlite https://github.com/pksunkara/nosqlite I could add a section
- asaibx 6y agoThere's always recutils [0] which uses a plain text database format stored in files called recfiles. [0] https://labs.tomasino.org/gnu-recutils/ https://labs.tomasino.org/gnu-recutils/
- graderjs 6y agoI should add that I was inspired by recutils and considered using it before making this. The appeal of using common Unix tools to play with a database is a motivation for me
- jitl 6y agoI feel like many static site generators have more-or-less filesystem-as-database. Some are more specialized than others. Many SSGs have standardized around the format of `YAML: Metadata\n\n---\n\nContent`, and expose a query interface over the YAML metadata. But, most static site generators don't generally have create/update API - just a read API. A reason you don't see much in the "tree of simple files as my DB" space is that the whole point of it is to be trivially simple to understand, and thus, to implement. Now, with file DBs, it's actually quite important to use atomic writes, so that a crash or concurrent operations don't produce errors. For example, the package posted above can corrupt data if there's concurrent writers to the same data file, because all the writes are non-atomic fs.writeFileSync(). What this database should do instead is write to a tempfile, and then rename the tempfile to replace the destination. That way, you get simple last-write-wins semantics with no possibility of creating invalid JSONs.
- legulere 6y agoDBASE is largely text-based
- leoncvlt 6y agoThis is something I wrote for a small electron application that needed some simple database functionality: https://www.npmjs.com/package/minim-json-db https://www.npmjs.com/package/minim-json-db Like the OP, stores data locally in .json files, and uses a simple MongoDB-inspired API
- graderjs 6y agoThanks! I'll add to related projects
- sillysaurusx 6y agoAmusingly, this is almost identical to how HN’s database works. Or worked. It wasn’t json, but it probably could’ve been.
- XorNot 6y agoI've definitely also written a big chunk of something like this (including JSON) for an internal tool. Just didn't get to realising my folders were generic tables.
- dheera 6y agoI'd go for YAML instead of JSON, or "JSON with trailing commas", or else the diffs become messy.
- jeswin 6y agoYAMLs biggest advantage over JSON: records can be appended (at the end of an existing file). Which you can't do with JSON objects, arrays etc because of the closing bracket at the end.
- dreamer7 6y agoSlightly off topic - can we use git-diff-able databases for faster syncing of clients with servers? Or is there no practical difference? We can return just the latest updated documents by maintaining a lastUpdated timestamp on client and server docs. But this would be at a document level. So, only the fields that have changed would be sent to the client.
- graderjs 6y agoI'm not sure if it would be faster or less data but this is certainly diffable at the document>-field level. Each field gets its own line at least
- zmj 6y agoNever trust a clock.
- andreareina 6y agoYeah wall clock isn't reliable. But if the server could send a global version/logical clock, then the client can say this is the latest version I've got. Which is essentially what tcp, git, etc do.
- joshspankit 6y agoHow about a version chain?
- moonchild 6y agoAn mutch easier solution that gets you 90% of the way is to just use a binary patcher (like bsdiff) with whatever database format you're already using.
- Scaevolus 6y agobsdiff doesn't scale to gigabytes-- vcdiff or rsync will work better
- codenesium 6y agoIs sqlite not diffable?
- jitl 6y agoSQLite database files themselves are binary, which typically isn't diff-able with standard tools, so (eg) git wouldn't show you anything useful unless you configure a special tool for diffing sqlite files. And in fact, there is a sqlitediff.exe tool for diffing sqlite databases: https://sqlite.org/sqldiff.html https://sqlite.org/sqldiff.html But, it would still be a bummer to store them in git because git won't efficiently track changes between sqlite databases, it'll just add the whole binary blob on every commit. oof.
- framecowbird 6y agoThis might be a silly question, but is there a reason why git can't also diff binary files? If I have a huge binary file and I change a few bytes in the middle, what's stopping Git just checking that in as a diff? I don't see what's so special about text... [edit] just did some research and it looks like Git will store the delta of a binary file in its packfiles, just like a text file. The question is just how delta-able sqlite binary files are.
- graderjs 6y agoI know Drew from Dropbox implemented a binary diff for his first version (on a bus) but I don't know the details. I don't know if git can somehow...but I suppose you could always MIME encode it (like email base64 encoding).
- GordonS 6y agoDiffing is about more than just efficient storage though, it's also about showing a history of human-readable changes (which obv you don't get with a binary format).
- graderjs 6y ago
- andreareina 6y agoCould use an example of the database file and diffs. I have vague recollections of a tool that was similarly supposed to make JSON files diffable. IIRC it procedurally built up the object, so {"foo": {"bar": "baz", "quux": [1, 2, 3]}} would be: foo = {} foo.bar = "baz" foo.baz = [] foo.baz[0] = 1 ...
- emmelaich 6y agoAugtool from the Augeas project too. (As well as the mentioned gron)
- emmelaich 6y agoexample $ augtool -r . --noautoload --transform "Json.lns incl /sirdb-example.json" <<EOF > print /files/sirdb-example.json/ > EOF /files/sirdb-example.json /files/sirdb-example.json/dict /files/sirdb-example.json/dict/entry[1] = "_owner" /files/sirdb-example.json/dict/entry[1]/string = "nouser" /files/sirdb-example.json/dict/entry[2] = "username" /files/sirdb-example.json/dict/entry[2]/string = "nouser" /files/sirdb-example.json/dict/entry[3] = "email" .... etc ...
- chousuke 6y agoI find it unfortunate that augeas is so magical. Or rather, the design of its interface is very surprising What's up with requiring "--noautoload"? What does "-r ." do? and the transform invocation starts with the seemingly magical "Json.lns" (where does it come from?) and appears to refer to a file at the root of the filesystem hierarchy? I'd probably use augtool more often myself, but its UI is seriously inscrutable.
- emmelaich 6y agoI agree, fwiw. I'm writing a little program to make this whole lot easier. Answers: by default, augtool loads all the lenses and scans the entire default filesystem (typically /etc); `--noautoload` prevents that. The -r is `--root`; specify the root as the current directory rather than /
- uvic 6y agoWow this was something I was looking for...Add a hireme button to your README.md so you can get hired using wannahireme.com...full disclosure I made the site.
- deleted 6y ago[deleted]
- alilsumpinditto 6y agowhat's the use case? certainly the performance impact will suffer at scale, so what's the usable limit before disadvantages outweigh the advantages of being able to use git?
- graderjs 6y agoI don't know about performance benchmarks to other DBs or scale, but I ran read and write benchmarks and saw 1000s op/s and felt satisfied enough. It won't be faster than filesystem unless you mount the DB directory to a RAM disk. For my use case, speed was not the most important, instead it was using common tools on the DB, being human-readable, in JSON and git diff-able. Also, I wanted small resource usage and small library size.
- joshspankit 6y agoI personally feel like the performance of DBs for smaller projects is a bit of a stale topic. With SSDs at 2GBps and over, that’s on par with early server RAM and there were companies who were able to serve pretty significant loads with 2GBps RAM.
- richie5um 6y agoI wrote an NPM package to convert JSON objects to/from a dotted format. It doesn't (currently) sort the flattened output, but that would be easy to add. https://www.npmjs.com/package/flattenjs https://www.npmjs.com/package/flattenjs
- olvy0 6y agoI wrote an internal tool which is similar but for a more complicated use-case: synchronizing an hierarchical database into an hierarchy of json files, in a git repo, which are then automatically or manually committed by the user. Our internal database is deeply hierarchical and also has a concept of a "folder" record, which users can create and nest. Users have complained for years that there was no change tracking. I surveyed some of the tools mentioned here (and other commercial ones) but they all just flattened the database into json or XML or just raw sql files, which made it very difficult for users to see the changes. We also thought about getting rid of the database and running off of json files, but that would have required completely rewriting our already brittle internal ORM, which uses raw SQL in too many places for performance, and would have been a breaking change. I have a heavy dislike for rewriting or throwing away whole modules, and also dislike breaking changes, having been personally bitten by them many times in the past. And due to other constraints on my team this was unacceptable. It's still on the table, just not for now. Instead I wrote a layer which translates back and forth between the ORM (or the raw database) and the json files in the file system. This is an addon to the application and is written in such a way that is mostly transparent to the users. Each change to a record in the database is translated into the correct json file on disk. Users can commit and push at their leisure. Users can also switch branches / reset to an earlier version, and request the database to be updated from the json files. I had thought many times about open sourcing the basic part of this addon. But I was never sure how common this use-case is. Also, it's actually pretty trivial to write the base part. The hard parts were gluing it to the existing application/ORM, and working around the many edge cases and bugs in our existing application. So, I wonder, how common is this use case (version control of an existing hierarchical SQL database in a user friendly way) in other organizations?
- deleted 6y ago[deleted]
- deleted 6y ago[deleted]
- lootsauce 6y agoI would love to see this open sourced live the marriage of a database, git, and json files!
- dijksterhuis 6y agoI generate bunch of JSON data from my machine learning runs (a bunch of outputs for analysis), so this fits my use case perfectly (inspecting changes of different run settings). Although you already got my upvote for the use of yer... (I use it all the time and rarely see anyone using it, yagetme).
- gumby 6y ago> git-diffable, and therefore versionable You can get this behavior with a SQL database like mysql that has a text dump format. I"ve used this quite effectively to keep relatively small (but important) databases in a git repo. I can definitely appreciate the advantages of an export format that is explicitly designed to support this (e.g. outputting each field on its own line, perhaps using an explicit label in front of it) but even the out-of-the-box format can often be quite effective.