I have a Sqlite db in a Git repository. Today I wanted to do a diff of a view in two different commits. I did it this way:
$ sqlite3 -list file.sqlite "SELECT * FROM contact_list_detailed" >/tmp/newlist
$ git checkout 51c24d13c file.sqlite
$ sqlite3 -list file.sqlite "SELECT * FROM contact_list_detailed" >/tmp/oldlist
$ git checkout -- file.sqlite
$ diff /tmp/oldlist /tmp/newlist
It works and I could script it if I want. But are there any "nice" ways of doing this with hooks?
Here is how to use git's textconv feature for showing diffs between versions of the sqlite file. It just does a dump, so it may not be super efficient for large databases. No hook necessary.
That link seems to be no longer available, so I'm using the the archived version instead.
The gist of it is, in the git attributes file (.gitattributes, or .git/info/attributes), add a pattern match to force sqlite3 diffs (assuming your database files have the extension .sqlite3):
*.sqlite3 diff=sqlite3
Then in your git config file (~/.gitconfig or .git/config):
[diff "sqlite3"]
binary = true
textconv = "echo .dump | sqlite3"
If you just want to track schema changes, use .schema instead of .dump.
In case one really wants to track the binary database files in git there are some problems.
Since sqlite databases might differ without the data stored within having changed the output of git status is not really helpful to determine if one should commit and git diff does only show something like Binary files a/foo.sql and b/foo.sql differ.
To get proper output from git diff there are basically two approaches to compare respective files:
textconv to convert files to plain text as shown in the answer by Biran Minton.I'll outline the second approach below using sqldiff that comes with sqlite.
As with the textconv approach one needs to change the attributes and config files.
attributes:
*.sql* diff=sqldiff
config:
[diff "sqldiff"]
command = gitsqldiff
The gitsqldiff string above refers to a wrapper script that is required to arrange the parameters given by git for consumption by sqldiff.
It has to be executable and reachable via the PATH environment variable (putting it in ~/bin should be fine).
Because (as of now) the exit value of sqldiff is always 0 and thus rather useless we have to check what it prints to give the user feedback - especially in the case nothing in the database has changed according to sqldiff which produces no output at all.
To do so and show the complete output to the user we use a trick that redirects the output to an additional file descriptor and stdout via tee.
gitsqldiff:
#!/bin/sh
echo "$1:"
# Duplicate sqldiff's output for consumption by wc and stdout.
# This enables us to check for an empty output but still see
# sqldiffs messages if there are any.
sqldiff "$2" "$5" 2>&1 | {
tee /dev/fd/3 |
if [ $(wc -c) -eq 0 ]; then
echo " nothing changed according to sqldiff"
fi
} 3>&1
This of course does not make sql files first-class citizens in a git repository but might facilitate a workflow that works nevertheless.
You would use HEAD and HEAD^ to access the previous and current revisions; see git post-commit hook - script on committed files for an example.
Use git show to extract files to a temporary directory without overwriting the working copy.
I wouldn't store binary files in git unless absolutely necessary.
You can avoid many hassles if you create a text file of SQL commands with sqlite3 file.sqlite .dump and put that into git, having the binary database only as a generated file.
(But then you have to care about regenerating the SQL file when necessary.)
