Show HN: Sir – Git-diff-able JSON database on yer filesystem

Is sqlite not diffable?

SQLite 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

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.

I would export the DB and use that for your diff/version control:

  sqlite3 yourdb.sqlite .dump

If you really want to track the binary e.g. inside git, but still see textual diffs you would put that into your .gitconfig:

  [diff "sqlite3"]
    textconv = sqlite3 $1 .dump

I wrote a tool that dumps a SQLite database out to disk as diffable JSON, precisely so you can easily record changes made to the database over time: https://github.com/simonw/sqlite-diffable

I have a demo of it running against my blog's database here: https://github.com/simonw/simonwillisonblog-backup

To expand on this, you can now even export to markdown and basically have a nice overview of the database. I use this in my notes which are all written in markdown because markdown tables are painful to organise per hand.

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

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

Thanks for this! I am trying to integrate something similar into my custom CMS.

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

AFAIR Sqlite files are normally in append mode.

That's a good point, thank you