Tracking SQLite Database Changes in Git

2023-11-03 637 words 3 minutes views

Contents

SQLite stores data in binary. If you run cat mydb.sqlite, you’ll see a bunch of gibberish that doesn’t resemble structured data at all. If you want to track changes and updates to a database using Git, you won’t be able to see full diffs by default.

You’ll see that the file has changed, but not what changed exactly:

1
2
3
diff --git a/mydb.sqlite b/mydb.sqlite
index f412c72..8f49ea5 100644
Binary files a/mydb.sqlite and b/mydb.sqlite differ

So, is there a way around that? Turns out: there is! Here’s a diff between two states of the SQLite database of GnuCash, which I’m currently trying out to manage my finances. I’ll explain how I got this diff afterwards:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
diff --git a/garritfranke.gnucash b/garritfranke.gnucash
index f412c72..8f49ea5 100644
--- a/garritfranke.gnucash
+++ b/garritfranke.gnucash
@@ -100,18 +100,22 @@ INSERT INTO accounts VALUES('ca11987c1c804da4b47b70d0fda87f10','Strom','EXPENSE'
 INSERT INTO accounts VALUES('b1674455d6ec495c8898bcfb65ef100c','Template Root','ROOT',NULL,0,0,NULL,'','',0,0);
+INSERT INTO accounts VALUES('9d2959ea65fc4f29b02dbc593fa9598a','Ausgleichskonto-EUR','BANK','26cc1292cf3e4f9584c71e7b3ec28479',100,0,'39e1c61538e24572abfcf0f3f72022ac','','',0,0);
 CREATE TABLE budgets(guid text(32) PRIMARY KEY NOT NULL, name text(2048) NOT NULL, description text(2048), num_periods integer NOT NULL);
 CREATE TABLE prices(guid text(32) PRIMARY KEY NOT NULL, commodity_guid text(32) NOT NULL, currency_guid text(32) NOT NULL, date text(19) NOT NULL, source text(2048), type text(2048), value_num bigint NOT NULL, value_denom bigint NOT NULL);
 CREATE TABLE transactions(guid text(32) PRIMARY KEY NOT NULL, currency_guid text(32) NOT NULL, num text(2048) NOT NULL, post_date text(19), enter_date text(19), description text(2048));
+INSERT INTO transactions VALUES('db9eff5ec00145f293c85391becbefa8','26cc1292cf3e4f9584c71e7b3ec28479','','2023-11-01 10:59:00','2023-11-01 11:36:23','TEST');
 INSERT INTO splits VALUES('e45aeb0ac0274c6483f8deb2e7ad3743','10cecb081ac24ab5a369c93f96d293da','d229160352064f8c80090e0a10a57d9c','','Rechnung','n','1970-01-01 00:00:00',0,100,0,100,NULL);
 INSERT INTO splits VALUES('bb9d2818bdc14be9bb916f3efd82e77d','10cecb081ac24ab5a369c93f96d293da','1d93d1e67aed4320bb228c16f4e28092','','Rechnung','n','1970-01-01 00:00:00',25000,100,25000,100,'b94c643ddcda48bcb7fc58626452e825');
 INSERT INTO splits VALUES('62747f45556740fe836c9f2180fe70c9','10cecb081ac24ab5a369c93f96d293da','4d6616d8c6524ead86641559539caf50','','Rechnung','n','1970-01-01 00:00:00',-25000,100,-25000,100,NULL);
+INSERT INTO splits VALUES('029f58c4d85c497c8e06ad4e52090033','db9eff5ec00145f293c85391becbefa8','a0f46eb546e34555ab4d0d3cc32c320f','','','n','1970-01-01 00:00:00',-10000,100,-10000,100,NULL);
+INSERT INTO splits VALUES('433a48cfdd314c94a105b5db9e7839de','db9eff5ec00145f293c85391becbefa8','9d2959ea65fc4f29b02dbc593fa9598a','','','n','1970-01-01 00:00:00',10000,100,10000,100,NULL);
 CREATE TABLE slots(id integer PRIMARY KEY AUTOINCREMENT NOT NULL, obj_guid text(32) NOT NULL, name text(4096) NOT NULL, slot_type integer NOT NULL, int64_val bigint, string_val text(4096), 
 INSERT INTO slots VALUES(84,'d9d25d75a993434597d988baa65670bb','job-rate',3,0,NULL,NULL,'1970-01-01 00:00:00',NULL,250,1,NULL);
 INSERT INTO slots VALUES(85,'38cde72240424e8b9e3ab5d4852c9cf0','job-rate',3,0,NULL,NULL,'1970-01-01 00:00:00',NULL,100,1,NULL);
+INSERT INTO slots VALUES(88,'db9eff5ec00145f293c85391becbefa8','date-posted',10,0,NULL,NULL,'1970-01-01 00:00:00',NULL,0,1,'20231101');
 CREATE TABLE recurrences(id integer PRIMARY KEY AUTOINCREMENT NOT NULL, obj_guid text(32) NOT NULL, recurrence_mult integer NOT NULL, recurrence_period_type text(2048) NOT NULL, recurrence_period_start text(8) NOT NULL, recurrence_weekend_adjust text(2048) NOT NULL);
 CREATE TABLE schedxactions(guid text(32) PRIMARY KEY NOT NULL, name text(2048), enabled integer NOT NULL, start_date text(8), end_date text(8), last_occur text(8), num_occur integer NOT NULL, rem_occur integer NOT NULL, auto_create integer NOT NULL, auto_notify integer NOT NULL, adv_creation integer NOT NULL, adv_notify integer NOT NULL, instance_count integer NOT NULL, template_act_guid text(32) NOT NULL);
 CREATE TABLE lots(guid text(32) PRIMARY KEY NOT NULL, account_guid text(32), is_closed integer NOT NULL);
@@ -234,7 +239,7 @@ INSERT INTO taxtable_entries VALUES(3,'1d459b285fca4de3bb4659744dc0cec5','d22916
 INSERT INTO taxtable_entries VALUES(5,'6def0d3a788d414b818ecdb29ba3dcd1','d229160352064f8c80090e0a10a57d9c',0,100000,2);
 CREATE TABLE vendors(guid text(32) PRIMARY KEY NOT NULL, name text(2048) NOT NULL, id text(2048) NOT NULL, notes text(2048) NOT NULL, currency text(32) NOT NULL, active integer NOT NULL, tax_override integer NOT NULL, addr_name text(1024), addr_addr1 text(1024), addr_addr2 text(1024), addr_addr3 text(1024), addr_addr4 text(1024), addr_phone text(128), addr_fax text(128), addr_email text(256), terms text(32), tax_inc text(2048), tax_table text(32));
 DELETE FROM sqlite_sequence;
-INSERT INTO sqlite_sequence VALUES('slots',87);
+INSERT INTO sqlite_sequence VALUES('slots',88);
 INSERT INTO sqlite_sequence VALUES('taxtable_entries',5);
 CREATE INDEX tx_post_date_index ON transactions(post_date);
 CREATE INDEX splits_tx_guid_index ON splits(tx_guid);

First, add a diff type called “sqlite3” to your config. The simplest way is to just run these commands:

1
2
git config diff.sqlite3.binary true
git config diff.sqlite3.textconv "echo .dump | sqlite3"

Alternatively, you can add this snippet to your ~/.gitconfig or .git/config in your repository:

1
2
3
[diff "sqlite3"]
        binary = true
        textconv = "echo .dump | sqlite3"

Next, create a file called .gitattributes if it’s not already present and add this line:

1
*.sqlite diff=sqlite3

Note that the filename (*.sqlite) may differ from your setup. In my case for example, it should match files with *.gnucash.

And that’s about it! The next time you run git diff or any other command that produces a diff on a sqlite file, you’ll see a nicely formatted diff of the changes.

Source
https://garrit.xyz/posts/2023-11-01-tracking-sqlite-database-changes-in-git
https://stackoverflow.com/a/21789167