What is a good way (or tool) for SQLite database version control (schema only)?

Question

Can someone suggest a good way (or tool) for version control of SQLite database (schema only)? I'm trying to control a version of an SQLite database, and the only option I can find involves using GIT to control the versions of the whole file, but I'm not interested in the data at the moment, just a schema change.

Any suggestions?

Thanks: -)

Answer 1

I have a double answer. If your sqlite is light enough and often updated, you can add it to the repository without unnecessary consequences / problems.

But readability is reduced for diff, as it is stored as binary.

sqlite3 git diff

Here's how to get git to show that you are well versed:

https://gist.github.com/peteristhegreat/a028bc3b588baaea09ff67f405af2909

git config diff.sqlite3.textconv 'sqlite3 $1 .dump' echo '*.db diff=sqlite3' >> $(git rev-parse --show-toplevel)/.gitattributes 

Now that your sqlite db file changes, you can see the change with git diff .

If you want to see only the difference in the schema, just change the .dump to .schema , and it should only make create calls and skip inserts.

convert sqlite3 to and from the repository with clean / smudge

If you want your db file to go to the repository as sql instead of db, you can use the clean / smudge mechanisms in git.

https://git-scm.com/book/en/v2/Customizing-Git-Git-Attributes#Keyword-Expansion

https://github.com/gilesbowkett/git-smudge-and-clean

I have not tried it yet, but basically whenever you come across a file that is db, git writes a stripped-down version of the file (as SQL commands) using sqlite3 $1 .schema . And then when you check this file from your database, it is converted back to db with cat $1 | sqlite3 cat $1 | sqlite3 .

sqlite3 always saves the latest file

Correct way to track sqlite3 binary in git?

 .gitattributes mysqlite3.db merge=keepTheir 

Hope this helps.

Answer 2

There is a command line utility registered here on SQLite.org called sqldiff.exe. It provides various options, including circuit comparison. To configure git to use sqldiff instead of the built-in diff tool, check out this discussion: How do I view 'git diff' using a visual demarcation program? . Unfortunately, this does not seem to be a trivial task.

Edit: It seems that the only way to get the sqldiff tool is to download the full source (all the way to the bottom of the download page) and compile it.

Answer 3

From the sqlite documentation, you can extract schema information from a hidden sqlite_master table. ( https://www.sqlite.org/fileformat2.html#sqlite_master )

You can save this content in a text file in GIT, this should give you the ability to track changes in the circuit.