Skip to content

Instantly share code, notes, and snippets.

@reasonableperson
Last active March 26, 2021 01:20
Show Gist options
  • Save reasonableperson/d6badd63455b333e741ee6191c53f6de to your computer and use it in GitHub Desktop.
Save reasonableperson/d6badd63455b333e741ee6191c53f6de to your computer and use it in GitHub Desktop.
sqlite3-git-smudge-filter

If you configure git like this:

git config filter.sqlite3.clean 'sqlite3 %f .dump'
git config filter.sqlite3.smudge 'sqlite3 %f'
echo '*.db filter=sqlite3' >> .git/info/attributes

and you have an sqlite3 database in a *.db file:

sqlite3 chinook.db 'select * from Artist order by random() limit 3'
ArtistId    Name
----------  -------------
82          Faith No More
106         Motörhead
3           Aerosmith

and you commit the database, before updating it with the following query:

sqlite3 chinook.db "update Artist set name = replace(name, 'P', '🅱') where name == 'Deep Purple'"```

then you can version control your database as if it was a plain text file, subject to a performance penalty which only affects the developer:

λ git diff
diff --git a/chinook.db b/chinook.db
index 866826b..ad14030 100644
--- a/chinook.db
+++ b/chinook.db
@@ -419,7 +419,7 @@ INSERT INTO Artist VALUES(54,'Green Day');
 INSERT INTO Artist VALUES(55,'David Coverdale');
 INSERT INTO Artist VALUES(56,'Gonzaguinha');
 INSERT INTO Artist VALUES(57,'Os Mutantes');
-INSERT INTO Artist VALUES(58,'Deep Purple');
+INSERT INTO Artist VALUES(58,'Deep 🅱urple');
 INSERT INTO Artist VALUES(59,'Santana');
 INSERT INTO Artist VALUES(60,'Santana Feat. Dave Matthews');
 INSERT INTO Artist VALUES(61,'Santana Feat. Everlast');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment