Today I’ve been reading about Sqitch, a database management system designed by David Wheeler; who is also the author of the pgTap suite that I also recently looked at. What’s quite attractive is that Sqitch can be used with pretty much any DBMS and version control system.

The concept appears to be that database changes are scripted both ways; a “deploy” script containing SQL for the changes we want to make, a “revert” script containing the SQL to revert our changes, and also a “verify” script containing some SQL to check the “deploy” scripts work.

Making modifications to a script is essentially done by renaming the existing scripts with a version number, and copying the “deploy” script into both the “deploy” and “revert” scripts of the new version and editing from there. This is all done with the Sqitch “rework” command.

Sqitch also allows you to make one set of changes dependant on other changes, preventing application of changes out of order. All the scripts are then stored in a version control system of your choosing and can be deployed and managed directly through the command line tool sqitch (which has obviously been influenced by git).

It looks like a nice way of managing database changes, but I like to be able to see all scripts side by side and I’m not sure how I feel about segmenting every set of changes into separate files. I guess for most DDL this is fine, but when it comes to procedural language functions I want them in one place organised by relevance not deployment order. Of course there’s nothing stopping us using Sqitch purely for deployment change management whilst keeping the current development copies of scripts in a separate VCS, perhaps with some “glue” in between to create the “deploy” and “revert” scripts from diffs for us (not sure if we could automate the verify scripts).

I need to do a little more thinking on exactly how to make use of it for us, but there’s an excellent tutorial using Git and PostgreSQL here.