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.
First, install Sqitch and the necessary database adapter (we’ll be using PostgreSQL)
brew tap theory/sqitch && brew install sqitch_pg
apt-get install sqitch && apt-get install libdbd-pg-perl postgresql-client
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
verify folders, representing a deployment, revertion, and verification script for the users table.
Let’s add appropriate code to the generated files:
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 (
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
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.