11 ms·

Tracking SQLite Database Changes in Git

347 points by vldr 3y ago

smilingemoji 3y ago
I love these sort of articles. No new tools are needed here, it shows you how to get the most out of the tools you already know and use. Awesome!
upon_drumhead 3y ago
That’s a really cool trick! I’m going to have to see if I can apply it to other things at work as well. Maybe a image to ascii and then a diff would be useful…
- klabb3 3y ago
  Here’s an idea for the AI folks: ask it to explain the differences. Imagine something like like: “The same screenshot of an iOS app, but the Subscribe button is clear blue and more prominent” (Although it wouldn’t work directly, since it looks like git runs “text dump” on both independently and then compares the text naively).
  - TikolaNesla 3y ago
    """"AI""""
  - bambax 3y ago
    If the process of image generation / description was fully reversible we could store image descriptions instead of a list of pixels... But if one feeds an image description from chatGPT to Dall-e and back in a loop, how many steps does it take to revert to pure noise? (surely this has been tried? but I couldn't find it)
    - klabb3 3y ago
      I mean there are billions of perceptually distinct images that map to the same “text description”. So text would generally be both lossy and inefficient. > instead of a list of pixels We don’t store lists of pixels. Not even lossless formats like PNG does that. Good ole JPEG has 1:10 - 1:20 compression ratio, ballpark.
Hakkin 3y ago
Alternatively: https://www.sqlite.org/sqldiff.html https://www.sqlite.org/sqldiff.html
- klabb3 3y ago
  That takes two files as input, don’t you have to checkout, copy etc manually? Whereas (IIUC) in the article git runs a text dump on both dbs automatically during git diff, which is an insane UX improvement. (Given that you’re like me and didn’t know about this).
  - Hakkin 3y ago
    True, I suppose the utility is slightly different. The trick in the OP article is definitely neat.
  - rjmunro 3y ago
    You can probably configure git to use this tool when it diffs sqlite files.
hiAndrewQuinn 3y ago
I had no idea you could do this. Phenomenal!
6LLvveMx2koXfwn 3y ago
Are people really adding their database dumps to git? Does that scale? Without this trick, what was the point?
- upon_drumhead 3y ago
  Version control? I have a pile of static-ish SQLite databases for unit tests to run against. I plan on using this for when we modify those databases, it’s legible what the change is.
- samox 3y ago
  The entire database, no. But a table with slowly changing dimensions, or a table that "should not" change, git is a simple solution for snapshotting.
- noirscape 3y ago
  Not scale, no, but git also tends to work pretty well for certain data projects; given git is just a VCS it can track any kind of file (to varying degrees of usefulness - git really doesn't like files that massively reorder themselves when the editor saves them like MSWord or certain FXML generators, it completely mucks up the diffs). For hobby projects, I've absolutely committed an SQLITE database in the past to serve as an HTTP cache; I was relying on an external API with some pretty hefty usage limits but at the same time was also certain that the data I was requesting from that API would never change, so I could just keep all responses around and reuse them forever (and it was easier to cache the response than to write a bespoke serialization format for converting them). Yeah, it's technically a build artifact (because it's not code) but it's better than getting 429 errors on every new dev machine for an hour + it was just hobbyist code, nothing meant for professional use.
- skadamat 3y ago
  Out of the box no, but you can solve the scaling git problem by improving the deduplication: https://xethub.com/assets/docs/faq/how-xet-deduplication-works https://xethub.com/assets/docs/faq/how-xet-deduplication-wor... We're focused on this problem at XetHub. - Llama2 git repo with ~600 GB of model files: https://xethub.com/XetHub/Llama2 https://xethub.com/XetHub/Llama2 - RedPajama git repo with 3.4 terabytes of data: https://xethub.com/XetHub/RedPajama-Data-1T https://xethub.com/XetHub/RedPajama-Data-1T
bspammer 3y ago
You can also use this trick to diff encrypted files, if you have an automatic way of decrypting them
trashburger 3y ago
The really neat part about this is that it still uses the original SQLite files, so it will work retroactively once you add the configuration to your repo. Great article!
lfconsult 3y ago
Awesome...! Thanks for sharing!
devnonymous 3y ago
Ooh, that's a neat trick! The head fake, so to speak is this is a git trick rather than anything special to sqlite. Thanks for sharing.
0x073 3y ago
Is this only visual?
tomashubelbauer 3y ago
I was also exploring something like this a few years back but for Office files. This exact approach seemed like an absolute win to me, but I ended up not using it, because this won't work in the GitHub web UI. This won't be a deal-breaker to many, but people should be aware of it still. In the end I ended up doing this: https://github.com/TomasHubelbauer/modern-office-git-diff/ https://github.com/TomasHubelbauer/modern-office-git-diff/
- deleted 3y ago
  [deleted]
- doubloon 3y ago
  does it works with xlsm? (excel macro enabled?)
  - tomashubelbauer 3y ago
    I am not sure the internal format of XLSM and how the macros are represented within it, but I would assume they are stored in the form of their source code as a plain text file within the ZIP archive that is the XLSM file and if that's the case, my tool would work with them just fine.
    - doubloon 3y ago
      thats pretty awesome
danirod 3y ago
I used to do this trick a couple of years ago to diff the credentials file in Rails codebases, which is encrypted, to keep track of changes to passwords and tokens. Worth to keep in mind that the text representation is virtual and thus the command is run on every operation like a git-diff or a git-show. Therefore, if it takes a while to convert to text, it will also slowdown Git and other tools depending on it.
- sneakerblack 3y ago
  We do the exact same thing to keep track of some credentials we use sops[1] and AWS KMS to separate credentials by sensitivity, then use the git differ to view the diffs between the encrypted secrets Definitely not best practice security-wise, but it works well [1] https://github.com/getsops/sops https://github.com/getsops/sops
redleader55 3y ago
The filter[0] attribute with "smudge" and "clean" would be a better solution for storing SQLite into git. [0] - https://git-scm.com/docs/gitattributes#_filter https://git-scm.com/docs/gitattributes#_filter
- michaelmior 3y ago
  This is what I was expecting. Store the dump in git but then have the binary in the working directory.
- massysett 3y ago
  Why is this better?
  - sshine 3y ago
    Maintaining a big, binary blob will make the git history grow in proportion to the size of the binary blob, not the size of the logical/abstract diff, regardless of how comprehensible the diff looks. To minimize the disk space to only the changes, you want to store the changes and not the complete, binary before/after images. By filtering, you reduce the stored object to the difference.
  - rjmunro 3y ago
    You won't be storing the sqlite file in git. So you won't be storing indexes or un-vacuumed data in git. You also won't be storing a database with some of it's rows locked or anything like that. Git will be able to compress the text better, and compress between versions better. Merging and resolving conflicts would have a chance of working.
- chrismorgan 3y ago
  But the key disadvantage is that now getting started with the repository requires more effort: you must have sqlite3 installed locally, and you must apply certain config values manually (and “remember to run this helper script” or “the first time you try running it it’ll⸺” are still manual). (This is for security reasons—you don’t want commands like clone and diff to be able to execute arbitrary code without your knowledge. Hence the attributes/config split.) Otherwise, you’ll find that your .sqlite file is SQL instead of a SQLite database, and if you try running stuff while it’s in that condition you’ll get “file is not a database” errors from SQLite.
bambax 3y ago
This is an excellent and very clever trick; however what it does is store the sql database in binary format, and use the dump only for the diff. Would it not be simpler to only store the text dump each time? (The dump can be used trivially to rebuild the db should the need arise, and it's human readable). So maybe a pre-commit or pre-staging hook would be even better for this use case?
- Cthulhu_ 3y ago
  It kinda depends on performance, a large database may take a while to rebuild depending on indices and the like. But then, the same can be said for the output of this dump command (and diffing the result). It's probably manageable for a gnucash database (1000 - 100000 record order of magnitude if I were to guess), but for anything larger the dump command will take ages.
  - bambax 3y ago
    If the database is huge you don't want to store every version as a whole blob in git... I don't have experience with truly huge dbs in the gigabytes or terabytes, but up to, say, 250 Mb the dump feels very fast.
michaelmior 3y ago
It's rather annoying that part of the config is stored in your local checked out copy of the repository (.git/config) and part of it is in a configuration file that is checked into version control (.gitattributes).
- KolmogorovComp 3y ago
  The usual workaround is to create a config file, check it and symlink it to .git/config.
  - IshKebab 3y ago
    That sucks because then you can't have project and user-specific configs (e.g. setting your email address for a particular project).
    - avar 3y ago
      There's been occasional talk of implementing something like this on the Git ML & devloper summits (and I've probably been the most excited about it), but for now this is deliberate and symlinking a tracked file to .git/config is a big exploit vector, you're basically giving everyone who's got access to the repository shell access on your computer. That's because some configuration will allow you to execute arbitrary commands on the user's computer (aliases, but also various ..cmd config, etc.). Then there's other config that's also dangerous, but short of arbitrary code execution. So, if git ever does start supporting a "tracked config" it would involve being very careful about the security implications. The approach I've advocated for is similar to how Emacs handles this, i.e. to have the user whitelist certain configuration keys/value types as "safe"). But for now it's not supported at all, one workaround for what you may be trying to do is to do path-based includes. I.e. if the reason you're setting the user and/or E-Mail is e.g. for a personal or work configuration, then you could clone those projects in ~/git/personal or ~/git/work, and have your ~/.gitconfig set the configuration for those accordingly.
      - michaelmior 3y ago
chrismorgan 3y ago
Because it’s fun, here’s an alternative spelling of `echo .dump | sqlite3`: <<<.dump sqlite3 This avoids invoking echo unnecessarily. Every time you do it you might just save a picowatt-hour or two! (It shouldn’t be more expensive. But be sure to spend a few watt-hours benchmarking it rigorously.) This uses two things that shell users are commonly unfamiliar with: • <<<: as `> filename` redirects stdout to the named file and `< filename` pipes the named file into stdin, `<<< word` pipes the word into stdin. (The whitespace is optional, and word follows normal argument splitting so you can do things like `<<<"Hello, world!"`.) • Ordering: although most commonly written at the end of commands, redirections can be placed at the start as well. So if you do things like `2>/dev/null >&2` to suppress all output¹, that bit can go at the start or the end. I’ve become increasingly partial to putting redirections at the start of the line, especially in cases where I’m appending to a file for a while, because a leading `>>filename` supports visual alignment better than a trailing.² And since we’re talking about different ways: as given, this is feeding the SQL to sqlite3 via stdin, but you can also pass it on the command line, after the filename. But things like this in Git (aliases, diff textconv, probably more) work by taking the string and appending the filename to the end, so you need a way of reordering the arguments. The solution is an immediately-invoked function: f() { sqlite3 "$@" .dump; }; f So when you diff mydb.sqlite, it runs `sh -c 'f() { sqlite3 "$@" .dump }; f mydb.sqlite'` or equivalent, which winds up executing `sqlite3 mydb.sqlite .dump`, as desired. I use this technique a number of times in my Git aliases, saving the bother of putting them in separate shell scripts somewhere where path management is a bother, at the cost of maintaining a one-liner with sometimes too many semicolons.³ —⁂— ¹ “Take stderr (2) and redirect it (>) to /dev/null, then take stdout (default/implicit, could also write 1 explicitly) and redirect it (>) to stderr (&2).” There are plenty of other ways of writing this! ² Lists can be a better solution for this specific case, allowing you to redirect to the file only once for a whole bunch of commands: { <<<"Line one" some-command <<<"End of $thing" } > filename ³ My longest is thirteen lines, though half of them barely count as lines. The line from my ~/.config/git/config, within [alias]: # Revise into the commit that last changed File rf = "!f() { if [ $# -eq 0 ]; then REV=\"$(git status --porcelain --untracked-files=no | sed '/^ /d;s/^.. //' | xargs -n1 git rev-list -1 HEAD -- | uniq)\"; NUM_REVS=\"$(echo \"$REV\" | wc -l)\"; if [ $NUM_REVS -ne 1 ]; then >&2 echo Files in the index were not all last modified in the same commit; exit 1; fi; else REV=\"$(git rev-list -1 HEAD -- \"$1\")\"; shift; fi; git revise \"$REV\" \"$@\"; }; f"
samox 3y ago
Sharing a personal project to display git based CSV :D https://app.data-drift.io/41231518/samox/local-datadrift-repo/overview?snapshotDate=2023-10-25&commitSha=105c05f0d9b418cf86e223d59cc4a686b298935f https://app.data-drift.io/41231518/samox/local-datadrift-rep... Not sure how it could be plugged on this stuff, but sharing anyway :D
- hluska 3y ago
  When you hijack someone else’s thread with something unrelated, I find it impossible to trust you enough to get excited about what you did. Smiling faces aside, this is kind of disrespectful to the author. Whereas with a show HN, I would focus on you and do my best to find something positive to say.
  - samox 3y ago
    I felt it was related (database + git is quite a narrow scope), and not concurrent to the post (I did not comment on the DoltgreSQL post for instance). But, I admit I was looking for some personal gratification. Sorry it bothered you.
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
sesm 3y ago
I wonder if Fossil (SCM used to develop SQLite and written by SQLite devs) has a similar feature built-in
kackiel 3y ago
Oh wow. This is indeed a very neat use of git config! Although just storing the text output would probably be nicer? You could probably achieve similar with some git hooks?
foobarbecue 3y ago
The tricky thing about keeping files in git that don't diff well (but can be converted into a representation that does) for me has always been lack of support for filter or hooks in the GitHub web interface. This is a frequent problem for me at work with big xml files.
- ilyt 3y ago
  [dead]
mrpf1ster 3y ago
It would be nice if we finally could have a database that we could use git on to track changes. Dolt looks very promising in this area but only works with MySQL, would be nice to have a SQLite solution. https://www.dolthub.com/ https://www.dolthub.com/
skadamat 3y ago
This is a super neat trick! At work, we're scaling git to terabyte scale and I'm constantly on the search for new ways we can showcase diffs for different file types: https://news.ycombinator.com/item?id=38112345 https://news.ycombinator.com/item?id=38112345
z29LiTp5qUC30n 3y ago
does no one know about _A tables anymore? This has been a solved thing since the 1970s
- jsd1982 3y ago
  Can you elaborate? I've been coding since the 90s and have never heard the term _A table before.
  - z29LiTp5qUC30n 3y ago
    sometimes called audit or journal tables. Every time something is updated or deleted, the entire previous record is inserted into its corresponding _A table with who did it and when (and optionally for what transaction number) so delete from foo results in an insert into foo_A before the delete occurs. so update to bar results in an insert into bar_A before the update occurs and rolling back a delete results in picking the old values out of the _A table and rolling back an update results in picking the old values out of the _A table and inserting the current values into the _A table before the updating the main table.
    - maxisaurus 3y ago
      I guess there's plenty of technical solutions to track changes, like scd or snapshots or audit tables - what I find interesting about git approach it's that it's much more user-friendly vs. SQL archeology to understand what changed?
setheron 3y ago
Is the dump order preserving ? It technically doesn't have to be....
vasergen 3y ago
is similar approach possible for postgress and MySQL?
- simonw 3y ago
  Not for this technique, because this relies on the fact that SQLite databases are a single file that can be checked into Git. MySQL and PostgreSQL use a whole directory full of files. You could try storing that whole thing in Git and then tiring a custom diff command that can load those directories into a temporary database sever and dump out SQL for comparison, but it would be very slow and brittle if you could even get it to work at all. Instead, a better strategy would be to dump your MySQL or PostgreSQL database to plain SQL and store that in your Git repo. Or use the trick in using here for my PostgreSQL database: https://news.ycombinator.com/item?id=38114171 https://news.ycombinator.com/item?id=38114171
  - infamia 3y ago
    > Or use the trick in using here for my PostgreSQL database: https://news.ycombinator.com/item?id=38114171 https://news.ycombinator.com/item?id=38114171 I wonder how this approach handles converting decimal fields, considering SQLite does not have a decimal field type and only has floats (i.e., you could potentially lose some precision during the conversion)?
    - simonw 3y ago
      Probably! I don't deal with decimal fields in my projects so I've not explored that yet.
simonw 3y ago
This approach works by storing the actual SQLite binary files in Git and then using a custom "diff" configuration to dump each file as SQL and compare the result. It's a neat trick, but storing binary files like that in Git isn't as space efficient as using a plain text format. I built my own tooling to solve this problem: https://datasette.io/tools/sqlite-diffable https://datasette.io/tools/sqlite-diffable - which outputs a “diffable” copy of the data in a SQLite database, precisely so you can store it in Git and look at the differences later. I’ve been running that for a couple of years in this repo: https://github.com/simonw/simonwillisonblog-backup https://github.com/simonw/simonwillisonblog-backup - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite-diffable). Here’s an example diff: https://github.com/simonw/simonwillisonblog-backup/commit/72e73b2cdd714fb1f3cd87d6a752971fc6398890 https://github.com/simonw/simonwillisonblog-backup/commit/72...
- tlb 3y ago
  > It's a neat trick, but storing binary files like that in Git isn't as space efficient as using a plain text format. Why is that? I would have expected git's delta compression to work well on sqlite files, since small table changes usually cause only a small number of page changes.
  - dunham 3y ago
    I would think so too. As a quick test I added chinook.db to a fresh repository, committed it, added a row to a table, committed again, and then did a git gc. The resulting pack file, with two revisions of the database, is 329k. The raw database is 864k and gzips to 328k. So it does look like git's delta encoding works with sqlite's blocks.
- lubesGordi 3y ago
  Does this custom diff driver also help git do a merge of two dbs?
- infamia 3y ago
  > I’ve been running that for a couple of years in this repo: https://github.com/simonw/simonwillisonblog-backup https://github.com/simonw/simonwillisonblog-backup - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite- I'm curious, what is the reason you chose not to use pgdump, but instead opted to convert to to sqlite and then dump the DB using sqlite-diffable? On a project I'm working on, I'd like to dump our Postgres schema into individual files for each object (i.e., one file for each table, function, stored proc, etc.), but haven't spent enough time to see if pgdump could actually do that. We're just outputting files by object type for now (one tables, function, and stored procs files). It looks like sqlite-diffable does something similar to what we're looking for (separate files for each object).
emadda 3y ago
You can also convert the data to JSON using sqlite_to_json https://github.com/emadda/transform-x#clis https://github.com/emadda/transform-x#clis
nedt 3y ago
But it's not really a diff no the database itself, just a diff of a full dump that you can use to rebuild the db, but not change an existing one. For example when you do a DELETE the diff does instead have an INSERT less in the dump, which is not exactly a database diff. Depending on the use case that might still be ok.

347 points by vldr 3y ago

I love these sort of articles. No new tools are needed here, it shows you how to get the most out of the tools you already know and use. Awesome!

upon_drumhead 3y ago

That’s a really cool trick! I’m going to have to see if I can apply it to other things at work as well. Maybe a image to ascii and then a diff would be useful…

klabb3 3y ago
Here’s an idea for the AI folks: ask it to explain the differences. Imagine something like like: “The same screenshot of an iOS app, but the Subscribe button is clear blue and more prominent” (Although it wouldn’t work directly, since it looks like git runs “text dump” on both independently and then compares the text naively).
- TikolaNesla 3y ago
  """"AI""""
- bambax 3y ago
  If the process of image generation / description was fully reversible we could store image descriptions instead of a list of pixels... But if one feeds an image description from chatGPT to Dall-e and back in a loop, how many steps does it take to revert to pure noise? (surely this has been tried? but I couldn't find it)
  - klabb3 3y ago
    I mean there are billions of perceptually distinct images that map to the same “text description”. So text would generally be both lossy and inefficient. > instead of a list of pixels We don’t store lists of pixels. Not even lossless formats like PNG does that. Good ole JPEG has 1:10 - 1:20 compression ratio, ballpark.

Hakkin 3y ago

Alternatively: https://www.sqlite.org/sqldiff.html https://www.sqlite.org/sqldiff.html

klabb3 3y ago
That takes two files as input, don’t you have to checkout, copy etc manually? Whereas (IIUC) in the article git runs a text dump on both dbs automatically during git diff, which is an insane UX improvement. (Given that you’re like me and didn’t know about this).
- Hakkin 3y ago
  True, I suppose the utility is slightly different. The trick in the OP article is definitely neat.
- rjmunro 3y ago
  You can probably configure git to use this tool when it diffs sqlite files.

hiAndrewQuinn 3y ago

I had no idea you could do this. Phenomenal!

6LLvveMx2koXfwn 3y ago

Are people really adding their database dumps to git? Does that scale? Without this trick, what was the point?

upon_drumhead 3y ago
Version control? I have a pile of static-ish SQLite databases for unit tests to run against. I plan on using this for when we modify those databases, it’s legible what the change is.
samox 3y ago
The entire database, no. But a table with slowly changing dimensions, or a table that "should not" change, git is a simple solution for snapshotting.
noirscape 3y ago
Not scale, no, but git also tends to work pretty well for certain data projects; given git is just a VCS it can track any kind of file (to varying degrees of usefulness - git really doesn't like files that massively reorder themselves when the editor saves them like MSWord or certain FXML generators, it completely mucks up the diffs). For hobby projects, I've absolutely committed an SQLITE database in the past to serve as an HTTP cache; I was relying on an external API with some pretty hefty usage limits but at the same time was also certain that the data I was requesting from that API would never change, so I could just keep all responses around and reuse them forever (and it was easier to cache the response than to write a bespoke serialization format for converting them). Yeah, it's technically a build artifact (because it's not code) but it's better than getting 429 errors on every new dev machine for an hour + it was just hobbyist code, nothing meant for professional use.
skadamat 3y ago
Out of the box no, but you can solve the scaling git problem by improving the deduplication: https://xethub.com/assets/docs/faq/how-xet-deduplication-works https://xethub.com/assets/docs/faq/how-xet-deduplication-wor... We're focused on this problem at XetHub. - Llama2 git repo with ~600 GB of model files: https://xethub.com/XetHub/Llama2 https://xethub.com/XetHub/Llama2 - RedPajama git repo with 3.4 terabytes of data: https://xethub.com/XetHub/RedPajama-Data-1T https://xethub.com/XetHub/RedPajama-Data-1T

bspammer 3y ago

You can also use this trick to diff encrypted files, if you have an automatic way of decrypting them

trashburger 3y ago

The really neat part about this is that it still uses the original SQLite files, so it will work retroactively once you add the configuration to your repo. Great article!

lfconsult 3y ago

Awesome...! Thanks for sharing!

devnonymous 3y ago

Ooh, that's a neat trick! The head fake, so to speak is this is a git trick rather than anything special to sqlite. Thanks for sharing.

0x073 3y ago

Is this only visual?

tomashubelbauer 3y ago

I was also exploring something like this a few years back but for Office files. This exact approach seemed like an absolute win to me, but I ended up not using it, because this won't work in the GitHub web UI. This won't be a deal-breaker to many, but people should be aware of it still. In the end I ended up doing this: https://github.com/TomasHubelbauer/modern-office-git-diff/ https://github.com/TomasHubelbauer/modern-office-git-diff/

deleted 3y ago
[deleted]
doubloon 3y ago
does it works with xlsm? (excel macro enabled?)
- tomashubelbauer 3y ago
  I am not sure the internal format of XLSM and how the macros are represented within it, but I would assume they are stored in the form of their source code as a plain text file within the ZIP archive that is the XLSM file and if that's the case, my tool would work with them just fine.
  - doubloon 3y ago
    thats pretty awesome

danirod 3y ago

I used to do this trick a couple of years ago to diff the credentials file in Rails codebases, which is encrypted, to keep track of changes to passwords and tokens. Worth to keep in mind that the text representation is virtual and thus the command is run on every operation like a git-diff or a git-show. Therefore, if it takes a while to convert to text, it will also slowdown Git and other tools depending on it.

sneakerblack 3y ago
We do the exact same thing to keep track of some credentials we use sops[1] and AWS KMS to separate credentials by sensitivity, then use the git differ to view the diffs between the encrypted secrets Definitely not best practice security-wise, but it works well [1] https://github.com/getsops/sops https://github.com/getsops/sops

redleader55 3y ago

The filter[0] attribute with "smudge" and "clean" would be a better solution for storing SQLite into git. [0] - https://git-scm.com/docs/gitattributes#_filter https://git-scm.com/docs/gitattributes#_filter

michaelmior 3y ago
This is what I was expecting. Store the dump in git but then have the binary in the working directory.
massysett 3y ago
Why is this better?
- sshine 3y ago
  Maintaining a big, binary blob will make the git history grow in proportion to the size of the binary blob, not the size of the logical/abstract diff, regardless of how comprehensible the diff looks. To minimize the disk space to only the changes, you want to store the changes and not the complete, binary before/after images. By filtering, you reduce the stored object to the difference.
- rjmunro 3y ago
  You won't be storing the sqlite file in git. So you won't be storing indexes or un-vacuumed data in git. You also won't be storing a database with some of it's rows locked or anything like that. Git will be able to compress the text better, and compress between versions better. Merging and resolving conflicts would have a chance of working.
chrismorgan 3y ago
But the key disadvantage is that now getting started with the repository requires more effort: you must have sqlite3 installed locally, and you must apply certain config values manually (and “remember to run this helper script” or “the first time you try running it it’ll⸺” are still manual). (This is for security reasons—you don’t want commands like clone and diff to be able to execute arbitrary code without your knowledge. Hence the attributes/config split.) Otherwise, you’ll find that your .sqlite file is SQL instead of a SQLite database, and if you try running stuff while it’s in that condition you’ll get “file is not a database” errors from SQLite.

bambax 3y ago

This is an excellent and very clever trick; however what it does is store the sql database in binary format, and use the dump only for the diff. Would it not be simpler to only store the text dump each time? (The dump can be used trivially to rebuild the db should the need arise, and it's human readable). So maybe a pre-commit or pre-staging hook would be even better for this use case?

Cthulhu_ 3y ago
It kinda depends on performance, a large database may take a while to rebuild depending on indices and the like. But then, the same can be said for the output of this dump command (and diffing the result). It's probably manageable for a gnucash database (1000 - 100000 record order of magnitude if I were to guess), but for anything larger the dump command will take ages.
- bambax 3y ago
  If the database is huge you don't want to store every version as a whole blob in git... I don't have experience with truly huge dbs in the gigabytes or terabytes, but up to, say, 250 Mb the dump feels very fast.

michaelmior 3y ago

It's rather annoying that part of the config is stored in your local checked out copy of the repository (.git/config) and part of it is in a configuration file that is checked into version control (.gitattributes).

KolmogorovComp 3y ago
The usual workaround is to create a config file, check it and symlink it to .git/config.
- IshKebab 3y ago
  That sucks because then you can't have project and user-specific configs (e.g. setting your email address for a particular project).
  - avar 3y ago
    There's been occasional talk of implementing something like this on the Git ML & devloper summits (and I've probably been the most excited about it), but for now this is deliberate and symlinking a tracked file to .git/config is a big exploit vector, you're basically giving everyone who's got access to the repository shell access on your computer. That's because some configuration will allow you to execute arbitrary commands on the user's computer (aliases, but also various ..cmd config, etc.). Then there's other config that's also dangerous, but short of arbitrary code execution. So, if git ever does start supporting a "tracked config" it would involve being very careful about the security implications. The approach I've advocated for is similar to how Emacs handles this, i.e. to have the user whitelist certain configuration keys/value types as "safe"). But for now it's not supported at all, one workaround for what you may be trying to do is to do path-based includes. I.e. if the reason you're setting the user and/or E-Mail is e.g. for a personal or work configuration, then you could clone those projects in ~/git/personal or ~/git/work, and have your ~/.gitconfig set the configuration for those accordingly.
    - michaelmior 3y ago

chrismorgan 3y ago

Because it’s fun, here’s an alternative spelling of `echo .dump | sqlite3`: <<<.dump sqlite3 This avoids invoking echo unnecessarily. Every time you do it you might just save a picowatt-hour or two! (It shouldn’t be more expensive. But be sure to spend a few watt-hours benchmarking it rigorously.) This uses two things that shell users are commonly unfamiliar with: • <<<: as `> filename` redirects stdout to the named file and `< filename` pipes the named file into stdin, `<<< word` pipes the word into stdin. (The whitespace is optional, and word follows normal argument splitting so you can do things like `<<<"Hello, world!"`.) • Ordering: although most commonly written at the end of commands, redirections can be placed at the start as well. So if you do things like `2>/dev/null >&2` to suppress all output¹, that bit can go at the start or the end. I’ve become increasingly partial to putting redirections at the start of the line, especially in cases where I’m appending to a file for a while, because a leading `>>filename` supports visual alignment better than a trailing.² And since we’re talking about different ways: as given, this is feeding the SQL to sqlite3 via stdin, but you can also pass it on the command line, after the filename. But things like this in Git (aliases, diff textconv, probably more) work by taking the string and appending the filename to the end, so you need a way of reordering the arguments. The solution is an immediately-invoked function: f() { sqlite3 "$@" .dump; }; f So when you diff mydb.sqlite, it runs `sh -c 'f() { sqlite3 "$@" .dump }; f mydb.sqlite'` or equivalent, which winds up executing `sqlite3 mydb.sqlite .dump`, as desired. I use this technique a number of times in my Git aliases, saving the bother of putting them in separate shell scripts somewhere where path management is a bother, at the cost of maintaining a one-liner with sometimes too many semicolons.³ —⁂— ¹ “Take stderr (2) and redirect it (>) to /dev/null, then take stdout (default/implicit, could also write 1 explicitly) and redirect it (>) to stderr (&2).” There are plenty of other ways of writing this! ² Lists can be a better solution for this specific case, allowing you to redirect to the file only once for a whole bunch of commands: { <<<"Line one" some-command <<<"End of $thing" } > filename ³ My longest is thirteen lines, though half of them barely count as lines. The line from my ~/.config/git/config, within [alias]: # Revise into the commit that last changed File rf = "!f() { if [ $# -eq 0 ]; then REV=\"$(git status --porcelain --untracked-files=no | sed '/^ /d;s/^.. //' | xargs -n1 git rev-list -1 HEAD -- | uniq)\"; NUM_REVS=\"$(echo \"$REV\" | wc -l)\"; if [ $NUM_REVS -ne 1 ]; then >&2 echo Files in the index were not all last modified in the same commit; exit 1; fi; else REV=\"$(git rev-list -1 HEAD -- \"$1\")\"; shift; fi; git revise \"$REV\" \"$@\"; }; f"

samox 3y ago

Sharing a personal project to display git based CSV :D https://app.data-drift.io/41231518/samox/local-datadrift-repo/overview?snapshotDate=2023-10-25&commitSha=105c05f0d9b418cf86e223d59cc4a686b298935f https://app.data-drift.io/41231518/samox/local-datadrift-rep... Not sure how it could be plugged on this stuff, but sharing anyway :D

hluska 3y ago
When you hijack someone else’s thread with something unrelated, I find it impossible to trust you enough to get excited about what you did. Smiling faces aside, this is kind of disrespectful to the author. Whereas with a show HN, I would focus on you and do my best to find something positive to say.
- samox 3y ago
  I felt it was related (database + git is quite a narrow scope), and not concurrent to the post (I did not comment on the DoltgreSQL post for instance). But, I admit I was looking for some personal gratification. Sorry it bothered you.

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

sesm 3y ago

I wonder if Fossil (SCM used to develop SQLite and written by SQLite devs) has a similar feature built-in

kackiel 3y ago

Oh wow. This is indeed a very neat use of git config! Although just storing the text output would probably be nicer? You could probably achieve similar with some git hooks?

foobarbecue 3y ago

The tricky thing about keeping files in git that don't diff well (but can be converted into a representation that does) for me has always been lack of support for filter or hooks in the GitHub web interface. This is a frequent problem for me at work with big xml files.

ilyt 3y ago
[dead]

mrpf1ster 3y ago

It would be nice if we finally could have a database that we could use git on to track changes. Dolt looks very promising in this area but only works with MySQL, would be nice to have a SQLite solution. https://www.dolthub.com/ https://www.dolthub.com/

skadamat 3y ago

This is a super neat trick! At work, we're scaling git to terabyte scale and I'm constantly on the search for new ways we can showcase diffs for different file types: https://news.ycombinator.com/item?id=38112345 https://news.ycombinator.com/item?id=38112345

z29LiTp5qUC30n 3y ago

does no one know about _A tables anymore? This has been a solved thing since the 1970s

jsd1982 3y ago
Can you elaborate? I've been coding since the 90s and have never heard the term _A table before.
- z29LiTp5qUC30n 3y ago
  sometimes called audit or journal tables. Every time something is updated or deleted, the entire previous record is inserted into its corresponding _A table with who did it and when (and optionally for what transaction number) so delete from foo results in an insert into foo_A before the delete occurs. so update to bar results in an insert into bar_A before the update occurs and rolling back a delete results in picking the old values out of the _A table and rolling back an update results in picking the old values out of the _A table and inserting the current values into the _A table before the updating the main table.
  - maxisaurus 3y ago
    I guess there's plenty of technical solutions to track changes, like scd or snapshots or audit tables - what I find interesting about git approach it's that it's much more user-friendly vs. SQL archeology to understand what changed?

setheron 3y ago

Is the dump order preserving ? It technically doesn't have to be....

vasergen 3y ago

is similar approach possible for postgress and MySQL?

simonw 3y ago
Not for this technique, because this relies on the fact that SQLite databases are a single file that can be checked into Git. MySQL and PostgreSQL use a whole directory full of files. You could try storing that whole thing in Git and then tiring a custom diff command that can load those directories into a temporary database sever and dump out SQL for comparison, but it would be very slow and brittle if you could even get it to work at all. Instead, a better strategy would be to dump your MySQL or PostgreSQL database to plain SQL and store that in your Git repo. Or use the trick in using here for my PostgreSQL database: https://news.ycombinator.com/item?id=38114171 https://news.ycombinator.com/item?id=38114171
- infamia 3y ago
  > Or use the trick in using here for my PostgreSQL database: https://news.ycombinator.com/item?id=38114171 https://news.ycombinator.com/item?id=38114171 I wonder how this approach handles converting decimal fields, considering SQLite does not have a decimal field type and only has floats (i.e., you could potentially lose some precision during the conversion)?
  - simonw 3y ago
    Probably! I don't deal with decimal fields in my projects so I've not explored that yet.

simonw 3y ago

This approach works by storing the actual SQLite binary files in Git and then using a custom "diff" configuration to dump each file as SQL and compare the result. It's a neat trick, but storing binary files like that in Git isn't as space efficient as using a plain text format. I built my own tooling to solve this problem: https://datasette.io/tools/sqlite-diffable https://datasette.io/tools/sqlite-diffable - which outputs a “diffable” copy of the data in a SQLite database, precisely so you can store it in Git and look at the differences later. I’ve been running that for a couple of years in this repo: https://github.com/simonw/simonwillisonblog-backup https://github.com/simonw/simonwillisonblog-backup - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite-diffable). Here’s an example diff: https://github.com/simonw/simonwillisonblog-backup/commit/72e73b2cdd714fb1f3cd87d6a752971fc6398890 https://github.com/simonw/simonwillisonblog-backup/commit/72...

tlb 3y ago
> It's a neat trick, but storing binary files like that in Git isn't as space efficient as using a plain text format. Why is that? I would have expected git's delta compression to work well on sqlite files, since small table changes usually cause only a small number of page changes.
- dunham 3y ago
  I would think so too. As a quick test I added chinook.db to a fresh repository, committed it, added a row to a table, committed again, and then did a git gc. The resulting pack file, with two revisions of the database, is 329k. The raw database is 864k and gzips to 328k. So it does look like git's delta encoding works with sqlite's blocks.
lubesGordi 3y ago
Does this custom diff driver also help git do a merge of two dbs?
infamia 3y ago
> I’ve been running that for a couple of years in this repo: https://github.com/simonw/simonwillisonblog-backup https://github.com/simonw/simonwillisonblog-backup - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite- I'm curious, what is the reason you chose not to use pgdump, but instead opted to convert to to sqlite and then dump the DB using sqlite-diffable? On a project I'm working on, I'd like to dump our Postgres schema into individual files for each object (i.e., one file for each table, function, stored proc, etc.), but haven't spent enough time to see if pgdump could actually do that. We're just outputting files by object type for now (one tables, function, and stored procs files). It looks like sqlite-diffable does something similar to what we're looking for (separate files for each object).

emadda 3y ago

You can also convert the data to JSON using sqlite_to_json https://github.com/emadda/transform-x#clis https://github.com/emadda/transform-x#clis

nedt 3y ago

But it's not really a diff no the database itself, just a diff of a full dump that you can use to rebuild the db, but not change an existing one. For example when you do a DELETE the diff does instead have an INSERT less in the dump, which is not exactly a database diff. Depending on the use case that might still be ok.