If we have a git repository where there is a binary sqlite3 file, it is difficult to see what changed by only using a git diff command. But there is a

How to Use git diff with an sqlite3 Database

submited by

Style Pass

2024-07-11 09:00:03

If we have a git repository where there is a binary sqlite3 file, it is difficult to see what changed by only using a git diff command. But there is a neat git trick (learned from here) we can use to overcome this: we use git’s built-in textconv mechanism to dump the sqlite3 database on-the-fly so that we can use the standard git diff to see the changes.

sqlite3 is a small but powerful database that, unlike its more powerful counterparts like MySQL and PostgreSQL, stores its data in a single binary file. And just like its more powerful counterparts, we can use the Structured Query Language (SQL) to query the database.

There are times that an sqlite3 database file is committed to a git repository. For example, a customer was using an internal tool that stored a small user database in a sqlite3 database file. This file was committed to the repo because it was a good place to keep it. This worked great when only a single developer was working on the database, but started getting tricky once more than that single developer was operating on the file.

Normally, git is really good at figuring out and displaying what changed in a file, but only for text files – not for binary files. And sqlite3 files are indeed binary files. So in this particular case, it became more and more difficult to handle changes that were done to this database.