Database source control, versioning, and environment management with Sqitch

4 minute read

View the source code here

We’ve been using source control for application code for years. Even infrastructure can be coded and versioned controlled. But some engineers are still oblivious to the need for version control around databases that house crucial data their applications rely on. Crazy, right? I use Sqitch, which touts itself as “sane database change management” (and I agree) as a lightweight database version control tool for all of my production databases. In this tutorial I’ll walk through how to get setup with Sqitch and manage a log of changes for a sample table.

Setup

First, install Sqitch and the necessary database adapter (we’ll be using PostgreSQL)

OSX:

brew tap theory/sqitch && brew install sqitch_pg

Debian:

apt-get install sqitch && apt-get install libdbd-pg-perl postgresql-client


Database Schema

First, we have to initialize sqitch. In your terminal, run:

mkdir schema && cd schema
sqitch init myproject --uri https://myproject --engine pg

We use the uri argument as a unique identifier for our project, and pg to let Sqitch know we’re using the PostgreSQL engine.

Next, let’s add a basic user table using sqitch. In your terminal, run:

sqitch add users -n 'add table definition'

You’ll see Sqitch generated a users.sql file in the deploy, revert, and verify folders, representing a deployment, revertion, and verification script for the users table.

Let’s add appropriate code to the generated files:


Database Changes

Let’s add a sample change to our database. In /myproject/schema, run:

sqitch add users-email-col -n 'add users.email'

Same as before, we have 3 users-email-col.sql files generated. Lets add the code:

Sqitch runs changesets sequentually, so first the users table will be created (users.sql), then the email column will be added (users-email-col.sql).


Managing Environments in Sqitch

Sqitch manages changes via a hash table in the target database, which means we need to point Sqitch to the correct database for dev, staging, and production environments so it can manage changes appropriately. We can do this with the sqitch.config file and the target property. Edit your config file to look like so:

Now, you can deploy to the appropriate environments by specifying your target. For example:

sqitch deploy -t dev


Conclusion

And that’s it! Now you’re managing every change to your database, and can deploy changes to multiple environments at different target locations.


If you enjoyed this article, please help out with a like, a share, or a comment. It fuels my focus to write more of it, thanks!

James Mensch is the Director of Engineering at Threatcare and the CEO at Magnifai. I believe in building intelligent products, using data to drive decisions, and engineering for social impact. I write about some of the cool stuff I do with tech, productivity and motivation psychology, and my social innovation projects. Connect with me on LinkedIn or say hi on Twitter.