4 ms·

Is sqlite not diffable?

jitl 6y ago
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 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 ago
  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.
  - graderjs 6y ago
    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).
  - GordonS 6y ago
    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).
    - graderjs 6y ago
      That's a good point, thank you
  - andrewshadura 6y ago
    AFAIR Sqlite files are normally in append mode.
Jakob 6y ago
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
- djeiasbsbo 6y ago
  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.
simonw 6y ago
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 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 https://github.com/simonw/simonwillisonblog-backup
- jlelse 6y ago
  Thanks for this! I am trying to integrate something similar into my custom CMS.

jitl 6y ago

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 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 ago
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.
- graderjs 6y ago
  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).
- GordonS 6y ago
  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).
  - graderjs 6y ago
    That's a good point, thank you
- andrewshadura 6y ago
  AFAIR Sqlite files are normally in append mode.

Jakob 6y ago

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

djeiasbsbo 6y ago
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.

simonw 6y ago

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 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 https://github.com/simonw/simonwillisonblog-backup

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