Managing Sqitch with Make

If you, like me, have worked on any project in rapid development, you've had the joy of dealing with changes to your database. Tables get added. Tables get removed. Columns get renamed. Constraints get added. Stored procedures change their parameters. Refactoring code is easy (if you have discipline, source code control, and an effective testing strategy), but refactoring your database seems more difficult.

In the good old days, you may have had a directory full of little .sql files with DDL for schema changes and insert, add, delete, and update statements to mangle data.

Sqitch is a database change management system designed and developed by the inestimable David Wheeler. David is also responsible for pgTAP, which provides usable automated testing of PostgreSQL databases.

Sqitch gives you a command-line tool to manage database changes. You can add new changesets, apply them, and even verify them. (In practice, verification happens automatically; Sqitch encourages you to write pgTAP-style tests to verify that your changes do the right thing. If your tests fail, Sqitch rolls back the change. Obviously this works best on mature database systems such as PostgreSQL.)

I've used DBIx::Class::DeploymentHandler on other projects successfully, but there was always a little mismatch between how I prefer to work (maintaining DDL by hand) and how DBIC tends to want you to work (manage your schema as a set of DBIC classes and let it generate the DDL for you). That's mostly personal preference, but I do believe the Sqitch documentation is easier to understand at the moment as well.

The only substantive complaint of any sort I have with Sqitch at all is that its idea of an ideal directory structure in which to store migration files is different from my idea. (This may not be true of the most recent versions.) You can override its notions with environment variables, but I use it on multiple projects.

A while back, on a previous project, Allison said something like "What if we had a Makefile just to automate away the common commands that we're all typing all the time?" These commands did things like "Run a development version of the web server" or "Recreate the testing database" or "Update all of the dependencies we're using with Carton." While I hate the use of Make as a requirement to build CPAN modules, Make solved a real problem for us.

The problem is: how do you create a consistent set of commands, scoped to the project, which collect common operations and configure external tools to work as the project desires? We had a little directory, tools/, because the kind of people who work on Unix-like systems and have no problem writing Perl code are the kind of people who have directories full of useful, single-purpose tools. Yet we wanted something a little different.

Makefiles are unholy unions of dependency-based procedural programming and shell commands. They're not really either one, but at least they let you extend them so that you can type make command and have something reasonable happen. You're not cluttering up your shell alias list with aliases for every project you have in development and you don't have to remember paths or invocations special to every project. You get a little bit of consistency so your brain can spend its power on more important differences.

Here's what I have for the current project:

PSQL=psql -h localhost -U mpuser
PG_LATEST_DDL=db/current/myproj_ddl.sql
PG_TEST_DATA=tools/test_db_build/test_db_fixtures.sql
SQITCH_CONFIG=config/sqitch/sqitch.conf
HAS_TEST_ENVIRONMENT:=$(shell perl -Ilib -MMyProj::Config -e 'print MyProj::Config->new->allow_testing_db')

sqitch_cttester:
    @echo sqitch deploy cttester
# Makefiles are tricky about indentation
ifeq ("$(HAS_TEST_ENVIRONMENT)", "1")
    @SQITCH_CONFIG=${SQITCH_CONFIG} sqitch -d cttester -u cttester deploy; true
    @SQITCH_CONFIG=${SQITCH_CONFIG} sqitch -d cttester -u cttester status  >/dev/null 2>&1
else
    @echo not in test environment, skipping
endif

sqitch_myproj:
    @echo sqitch deploy myproj
    @SQITCH_CONFIG=${SQITCH_CONFIG} sqitch deploy; true
    @SQITCH_CONFIG=${SQITCH_CONFIG} sqitch status  >/dev/null 2>&1

sqitch_deploy: sqitch_myproj

sqitch_add:
    SQITCH_CONFIG=${SQITCH_CONFIG} sqitch add $(name)
    vim "config/sqitch/deploy/$(name).sql" "config/sqitch/revert/$(name).sql" "config/sqitch/verify/$(name).sql"
    git add "config/sqitch/deploy/$(name).sql" "config/sqitch/revert/$(name).sql" "config/sqitch/verify/$(name).sql"

First, the Makefile defines a couple of environment variables used throughout the file. The most important are SQITCH_CONFIG, which controls the location of the project-specific Sqitch configuration file and HAS_TEST_ENVIRONMENT which returns a boolean representing whether the current environment allows the use of a separate testing database. (The production server has no testing database. Development servers do.)

I only ever use the sqitch_deploy and sqitch_add commands. The deployment command tells Sqitch to deploy the most recent migrations it knows about in its plan. Sqitch's status command returns a boolean representing the success or failure (so that the exit code of make sqitch_deploy is useful and that make command doesn't think that the target failed).

The sqitch_add command helps add a new database migration. It takes one argument, so invoke it with make sqitch_add name=new_migration_name. Not only does it add the migration, it opens the new files Sqitch has created in vim (it should use the $EDITOR environment variable, but I haven't needed to care about anyone other than Vim users yet) and then adds all of the saved files to git's index.

This saves me a few dozen keystrokes and a few seconds every time I make a database change. If that sounds trivial to you, good. A few keystrokes and a few seconds are trivial. My brainpower isn't trivial. Those keystrokes and seconds mean the difference between staying in the zone and fumbling around trying to remember commands I don't use all day every day. They save me minutes every time I use them, if you count the friction of switching between "How do I do this in Sqitch again? What's the directory layout here?" and "What was I really working on?"

Automating silly tasks which take up a few seconds here and there is valuable if you can get rid of that friction. Sqitch has helped remove that friction from database migrations. Automating Sqitch with a simple Makefile has gone even further.