19

Can anyone suggest a good way (or tool) to version control a SQLite database (schema only)? I'm trying to version control a SQLite database and the only option I can find involves using GIT to version control the whole file, but I'm not interested in the data at this point, just the schema changes.

Any suggestions?

Thanks :-)

Aang
  • 191
  • 1
  • 3
  • Are you trying to track changes for development or is this something you are trying to incorporate in to an application? – BenCamps Nov 19 '15 at 20:32
  • I'm trying to track changes for development. Trying to find something that works very similar to Redgate's SQL Source Control tool. – Aang Nov 23 '15 at 17:04

4 Answers4

21

I have a two fold answer. If your sqlite is light enough and infrequently updated, you can add it into the repository without too many repercussions/issues.

But readablity goes down for diffs since it is stored as a binary.

sqlite3 git diff

Here is how to get git to show you diffs nicely:

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 when your sqlite db file changes you can see the change with git diff.

If you wanted to only see the diff of the schema, you just change .dump to .schema and it should only do the create calls and skip the inserts.

sqlite3 conversion in and out of the repository with clean/smudge

If you want your db file to get pushed into the repository as sql instead of as a 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 haven't tried it yet, but basically whenever you run across a file that is a db, git commits the stripped down version of the file (as sql commands) using the sqlite3 $1 .schema export. And then when you checkout that file from your database, it gets converted back to a db using cat $1 | sqlite3.

sqlite3 always keep the newest file

Right way of tracking a sqlite3 binary file in git? 

.gitattributes

mysqlite3.db merge=keepTheir

Hope that helps.

Community
  • 1
  • 1
phyatt
  • 16,890
  • 3
  • 51
  • 70
  • 1
    On my setup, the git diff gave an error `Error: near "/": syntax error fatal: unable to read files to diff`. The fix was to move `sqlite3 $1 .dump` to a separate shell script and refer to it from textconv setting. – Eero Sep 15 '16 at 11:57
  • sounds like you have a permissions issue or the file doesn't exist. Have you tried just typing `sqlite path/to/filename.db .dump` – phyatt Sep 15 '16 at 12:01
  • 2
    Same problem as @Eero. This worked instead: `git config diff.sqlite3.textconv 'f() { sqlite3 "$@" .dump; }; f'` – Justin M. Keyes Oct 24 '17 at 13:47
4

There is a command line utility documented here here on SQLite.org called sqldiff.exe. It provides various options including comparing schema. To configure git to use sqldiff instead of the built in diff tool check out this discussion: How do I view 'git diff' output with a visual diff program?. Unfortunately it looks like its not a trivial task.

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

Community
  • 1
  • 1
BenCamps
  • 1,444
  • 14
  • 24
  • Thanks for the suggestion, unfortunately, sqldiff "does not (currently) display differences in TRIGGERs, VIEWs...". I have several of those I need to version control also. – Aang Nov 23 '15 at 17:06
1

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

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

Luis Carvalho
  • 81
  • 3
0

FWIW, I've been tracking a 13MB / 20k row SQLite database in Git using the Makefile excerpted below. Two files, schema.sql and data.sql are stored in Git and can be diffed as text files per usual.


SQLITE=sqlite3
SCHEMAFILE=schema.sql
DATAFILE=data.sql
DBFILE=my_database.db
TEMPDB=temp.db

dump: $(DBFILE)
    @$(SQLITE) $(DBFILE) .sha3sum >sha3_before
    $(SQLITE) $(DBFILE) ".schema --indent" | grep -v sqlite_sequence >$(SCHEMAFILE)
    $(SQLITE) $(DBFILE) .dump | grep ^INSERT | grep -v sqlite_sequence >$(DATAFILE)
    @rm -f $(TEMPDB)
    @cat $(SCHEMAFILE) $(DATAFILE) | $(SQLITE) $(TEMPDB)
    @$(SQLITE) $(TEMPDB) .sha3sum >sha3_after
    @cmp sha3_before sha3_after
    @rm sha3_before sha3_after
    @rm $(TEMPDB)

database:
    rm -f $(DBFILE)
    cat $(SCHEMAFILE) $(DATAFILE) | $(SQLITE) $(DBFILE)

The dump step generates new schema.sql & data.sql files, then generates a new database from them and compares the SQLite .sha3sum of the database before and after to validate that it is getting an accurate copy.

In my application, this database is used as a read-only .db file (essentially a product catalog), so it has a single source of updates. My update cycle is:

   make database
   # update the data in the database as needed
   make dump
   git commmit schema.sql data.sql

The only problem I'm having with this process is that the .schema command sometimes adds a formatted comment field after CREATE VIEW ... statements each time I generate it and doesn't remove the old one. So there's a gradual accretion of comments in my schema.sql file. I mitigate that by viewing the git diff and manually adjust before committing.

mm2001
  • 5,235
  • 4
  • 36
  • 35