Just a Theory

Black lives matter

Posts about Sqitch

Sqitch v1.0.0

Sqitch Logo

After seven years of development and hundreds of production database deployments, I finally decide it was time to release Sqitch v1.0.0, and today’s the day. I took the opportunity to resolve all known bugs in previous releases, so there’s no new functionality since v0.9999. Still, given the typical attention given to a significant milestone release like 1.0.0, my employer published a blog post describing a bit of the history and philosophy behind Sqitch.

The new site goes into great detail describing how to install Sqitch, but the important links are:

  • CPAN — Install Sqitch from CPAN
  • Docker — Run Sqitch from a Docker container
  • Homebrew — Homebrew Sqitch on macOS
  • GitHub — Sqitch releases on GitHub

Thanks to everyone who helped get Sqitch to this point, I appreciate it tremendously. I’m especially grateful to:

Thanks a million for all your help and support!

Removing Sqitch Deprecations

Ahead of the release of Sqitch v1.0 sometime in 2019, I’d like to remove all the deprecated features and code paths. Before I do, I want to get a sense of the impact of such removals. So here’s a comprehensive list of the deprecations currently in Sqitch, along with details on their replacements, warnings, and updates. If the removal of any of these items would create challenges for your use Sqitch, get in touch.

What would be removed:

  • The core configuration and directory-specification options and attributes:

    • --engine
    • --registry
    • --client
    • --top-dir, top_dir
    • --deploy-dir, deploy_dir
    • --revert-dir, revert_dir
    • --verify-dir, verify_dir

    The preferred solution is configuration values at the target, engine, or core level (settable via the options on the target, engine, and init commands, or via the config command).

    But I admit that there are no overriding options for the directory configurations in the deploy/revert/verify/rebase/checkout commands. And I’ve used --top-dir quite a lot myself! Perhaps those should be added first. If we were to add those, I think it’d be okay to remove the core options — especially if I ever get around to merging options to allow both core and command options to be specified before or after the command name.

  • The @FIRST and @LAST symbolic tags, which were long-ago supplanted by the more Git-like @ROOT and @HEAD, and warnings have been emitted for at least some of their uses for six years now.

  • Engine configuration under core.$engine. This configuration was supplanted by engine$engine four years ago, and came with warnings, and a fix via the sqitch engine update-config action. That action would also go away.

  • The core database connection options:

    • –db-host
    • –db-port
    • –db-username
    • –db-name

    These options were supplanted by database URIs over four years ago. At that time, they were adapted to override parts of target URIs. For example, if you have a target URI of db:pg://db.example.com/flipr, you can specify that target, but then also pass --db-name to just change the database name part of the URI. I’ve found this occasionally useful, but I don’t think the complexity of the implementation is worth it.

  • The old target options, which were renamed “change” targets back when the term “target” was adopted to refer to databases rather than changes. Sqitch has emitted warnings for five years when the old names were used:

    • The --onto-target and --upto-target options on rebase were renamed --onto-change and --upto-change.
    • The --to-target and --target options on deploy and revert were renamed --to-change.
    • The --from-target and --to-target options on verify were renamed --from-change and --to-change.
  • The script-generation options on the add command were deprecated four years ago in favor of --with and --without options, with warnings for the old usages:

    • --deploy became --with deploy
    • --revert became --with revert
    • --verify became --with verify
    • --no-deploy became --without deploy
    • --no-revert became --without revert
    • --no-verify became --without verify

    The same change replaced the template-specification options with a single --use option:

    • --deploy-template $path became --use deploy=$path
    • --revert-template $path became --use revert=$path
    • --verify-template $path became --use verify=$path

    The corresponding config variables, add.deploy_template, add.revert_template, and add.verify_template were replaced with a config section, add.templates. No warnings were issued for the old names, though.

  • The set-* actions on the engine and target commands were replaced three years ago (engine change, target change) with a single alter action, with warnings, and able to be passed multiple times:

    • set-target became alter target
    • set-uri became alter uri
    • set-registry became alter registry
    • set-client became alter client
    • set-top-dir became alter top-dir
    • set-plan-file became alter plan-file
    • set-deploy-dir became alter deploy-dir
    • set-revert-dir became alter revert-dir
    • set-verify-dir became alter verify-dir
    • set-extension became alter extension
  • The data hashed to create change IDs was modified six years ago. At that time, code was added to update old change IDs in Postgres databases; no other engines were around at the time.

If removing any of these features would cause trouble for you or the organizations you know to be using Sqitch, please get in touch.

Flaked, Brewed, and Docked

I released Sqitch v0.9998 this week. Despite the long list of changes, only one new feature stands out: support for the Snowflake Data Warehouse platform. A major work project aims to move all of our reporting data from Postgres to Snowflake. I asked the team lead if they needed Sqitch support, and they said something like, “Oh hell yes, that would save us months of work!” Fortunately I had time to make it happen.

Snowflake’s SQL interface ably supports all the functionality required for Sqitch; indeed, the implementation required fairly little customization. And while I did report a number of issues and shortcomings to the Snowflake support team, they always responded quickly and helpfully — sometimes revealing undocumented workarounds to solve my problems. I requested that they be documented.

The work turned out well. If you use Snowflake, consider managing your databases with Sqitch. Start with the tutorial to get a feel for it.

Bundle Up

Of course, you might find it a little tricky to get started. In addition to long list of Perl dependencies, each database engines requires two external resources: a command-line client and a driver library. For Snowflake, that means the SnowSQL client and the ODBC driver. The PostgreSQL engine requires psql and DBD::Pg compiled with libpq. MySQL calls for the mysql client and DBD::mysql compiled with the MySQL connection library. And so on. You likely don’t care what needs to be built and installed; you just want it to work. Ideally install a binary and go.

I do, too. So I spent the a month or so building Sqitch bundling support, to easily install all its Perl dependencies into a single directory for distribution as a single package. It took a while because, sadly, Perl provides no straightforward method to build such a feature without also bundling unneeded libraries. I plan to write up the technical details soon; for now, just know that I made it work. If you Homebrew, you’ll reap the benefits in your next brew install sqitch.

Pour One Out

In fact, the bundling feature enabled a complete rewrite of the Sqitch Homebrew tap. Previously, Sqitch’s Homebrew formula installed the required modules in Perl’s global include path. This pattern violated Homebrew best practices, which prefer that all the dependencies for an app, aside from configuration, reside in a single directory, or “cellar.”

The new formula follows this dictum, bundling Sqitch and its CPAN dependencies into a nice, neat package. Moreover, it enables engine dependency selection at build time. Gone are the separate sqitch_$engine formulas. Just pass the requisite options when you build Sqitch:

brew install sqitch --with-postgres-support --with-sqlite-support

Include as many engines as you need (here’s the list). Find yourself with only Postgres support but now need Oracle, too? Just reinstall:

brew reinstall sqitch --with-postgres-support --with-oracle-support

In fact, the old sqitch_oracle formula hasn’t worked in quite some time, but the new $HOMEBREW_ORACLE_HOME environment variable does the trick (provided you disable SIP; see the instructions for details).

I recently became a Homebrew user myself, and felt it important to make Sqitch build “the right way”. I expect this formula to be more reliable and better maintained going forward.

Still, despite its utility, Homebrew Sqitch lives up to its name: It downloads and builds Sqitch from source. To attract newbies with a quick and easy method to get started, we need something even simpler.

Dock of the Bae

Which brings me to the installer that excites me most: The new Docker image. Curious about Sqitch and want to download and go? Use Docker? Try this:

curl -L https://git.io/JJKCn -o sqitch && chmod +x sqitch
./sqitch help

That’s it. On first run, the script pulls down the Docker image, which includes full support for PostgreSQL, MySQL, Firebird, and SQLite, and weighs in at just 164 MB (54 MB compressed). Thereafter, it works just as if Sqitch was locally-installed. It uses a few tricks to achieve this bit of magic:

  • It mounts the current directory, so it acts on the Sqitch project you intend it to
  • It mounts your home directory, so it can read the usual configuration files
  • It syncs the environment variables that Sqitch cares about

The script even syncs your username, full name, and host name, in case you haven’t configured your name and email address with sqitch config. The only outwardly obvious difference is the editor:1 If you add a change and let the editor open, it launches nano rather than your preferred editor. This limitation allows the image ot remain as small as possible.

I invested quite a lot of effort into the Docker image, to make it as small as possible while maximizing out-of-the-box database engine support — without foreclosing support for proprietary databases. To that end, the repository already contains Dockerfiles to support Oracle and Snowflake: simply download the required binary files, built the image, and push it to your private registry. Then set $SQITCH_IMAGE to the image name to transparently run it with the magic shell script.

Docker Docket

I plan to put more work into the Sqitch Docker repository over the next few months. Exasol and Vertica Dockerfiles come next. Beyond that, I envision matrix of different images, one for each database engine, to minimize download and runtime size for folx who need only one engine — especially for production deployments. Adding Alpine-based images also tempts me; they’d be even smaller, though unable to support most (all?) of the commercial database engines. Still: tiny!

Container size obsession is a thing, right?

At work, we believe the future of app deployment and execution belongs to containerization, particularly on Docker and Kubernetes. I presume that conviction will grant me time to work on these improvements.

  1. Well, that and connecting to a service on your host machine is a little fussy. For example, to use Postgres on your local host, you can’t connect to Unix sockets. The shell script enables host networking, so on Linux, at least, you should be able to connect to localhost to deploy your changes. On macOS and Windows, use the host.docker.internal host name. ↩︎


In the last few years, I’ve not had a lot of time to hack on my open-source projects, including Sqitch. Last week’s call to adopt my modules garnered an unexpected quantity of interest in helping to maintain Sqitch specifically. It’s little different from my other Perl modules, being designed as a standalone app rather than a software development library. It deserves care and feeding from more than a single maintainer.

So I’m very pleased to announce two changes to the Sqitch ecosystem:

  1. I’ve moved all my Sqitch-related code, including Sqitch itself, from my personal GitHub account to the new “Sqitchers” GitHub organization. In addition to myself, the organization has four other owners: Dave Rolsky, Shawn Sorichetti, Curtis Poe, and Ștefan Suciu. However, I’d really like to balance all this great Perl talent with a few database folks. Even better to get some non-white-dudes involved. If that’s you, and you’d like to help Sqitch continue to improve, drop me a line.

  2. I’ve created a new mail list, sqitch-hackers, for folks who want to hack on Sqitch itself. This is an open list, like the existing sqitch-users list: anyone can subscribe and participate in the discussion of how to improve Sqitch, get hints for hacking on it, talk about approaches to implementing features, etc.

I’ll likely make a brain dump of stuff I’d like to see happen with the project and the community. Do join and send us your ideas, too!

Sqitch has become a pretty important tool for a lot of people, far and way my most-starred project on GitHub. It deserves a broader coalition of people to care for it going forward. I hope these changes help to galvanize the community to take it on collectively.

Sqitch on FLOSS Weekly

Yours truly was feature in this week’s episode of FLOSS Weekly, talking about Sqitch. I feel pretty good about this interview, despite continually banging on my legs, the table, and the mic. It’s interesting to try to communicate what Sqitch is about purely by talking.

If it’s enough to get you interested in giving a try, try installing it and using working through one of the tutorials:

Sqitch Goes Vertical

I released Sqitch v0.996 today. Despite the minor version increase, this is a pretty big release. I’m busy knocking out all the stuff I want to get done for 1.0, but the version space is running out, so just a minor version jump from v0.995 to v0.996. But a lot changed. A couple the biggies:

Goodbye Mouse and Moose, Hello Moo

If you’re not a Perl programmer, you probably aren’t familiar with Moose or its derivatives Mouse and Moo. Briefly, it’s an object system. Great interface and features, but freaking huge—and slow. Mouse is a lighter version, and when we (mostly) switched to it last year, it yielded a 20-30% speed improvement.

Still wasn’t great, though. So on a day off recently, I switched to Moo, which implements most of Moose but without a lot of the baggage. At first, there wasn’t much difference in performance, but as I profiled it (Devel::NYTProf is indispensable for profiling Perl apps, BTW), I was able to root out all trace of Moose or Mouse, including in CPAN modules Sqitch depends on. The result is around a 40% speedup over what we had before. Honestly, it feels like a new app, it’s so fast. I’m really happy with how it turned out, and to have shed some of the baggage from the code base.

The downside is that package maintainers will need to do some work to get the new dependencies built. Have a look at the RPM spec changes I made to get our internal Sqitch RPMs to build v0.996.

MySQL Password Handling

The handling of MySQL passwords has also been improved. Sqitch now uses the $MYSQL_PWD environment variable if a password is provided in a target. This should simplify authentication when running MySQL change scripts through the mysql client client.

Furthermore, if MySQL::Config is installed, Sqitch will look for passwords in the client and mysql sections of your MySQL configuration files (~/.my.cnf, /etc/my.cnf). This should already happen automatically when executing scripts, but Sqitch now tries to replicate that behavior when connecting to the database via DBI.

Spotting the $MYSQL_PWD commit, Ștefan Suciu updated the Firebird engine to use the $ISC_PASSWORD when running scripts. Awesome.

Vertically Integrated

And finally, another big change: I added support for Vertica, a very nice commercial column-store database that features partitioning and sharding, among other OLAP-style functionality. It was originally forked from PostgreSQL, so it was fairly straight-forward to port, though I did have to borrow a bit from the Oracle and SQLite engines, too. This port was essential for work, as we’re starting to use Vertical more and more, and need ways to manage changes.

If you’re using Vertica, peruse the tutorial to get a feel for what it’s all about. If you want to install it, you can get it from CPAN:

cpan install App::Sqitch BDD::ODBC

Or, if you’re on Homebrew:

brew tap theory/sqitch
brew install sqitch_vertica

Be warned that there’s a minor bug in v0.996, though. Apply this diff to fix it:

 @@ -16,7 +16,7 @@ our $VERSION = '0.996';
 sub key    { 'vertica' }
 sub name   { 'Vertica' }
-sub driver { 'DBD::Pg 2.0' }
+sub driver { 'DBD::ODBC 1.43' }
 sub default_client { 'vsql' }
 has '+destination' => (

That fix will be in the next release, of course, as will support for Vertica 6.

What Next?

I need to focus on some other work stuff for a few weeks, but then I expect to come back to Sqitch again. I’d like to get 1.0 shipped before the end of the year. To that end, next up I will be rationalizing configuration hierarchies to make engine selection and deploy-time configuration more sensible. I hope to get that done by early October.

Managing Sqitch with Make


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?”

Nice application of a Makefile to eliminate boilerplate. A couple of notes, though:

Nice post. A couple comments and questions:

  • As of Sqitch v0.990, you can pass the --open-editor option to the add command to have the new files opened in your editor.

  • If you want to add a pgTAP test with a new change, see this post.

  • What is the call to sqitch status for? Since its output just goes to /dev/null, I don’t understand the point.

  • Also as of v0.990, you can specify Sqitch targets. The -d, -u, and other options then override values in the target URI.

  • I really want to get Sqitch to better understand and work with VCSs. An example would be to have it automatically git add files created by sqitch add. Another might be a Git config setting pointing to the Sqitch config file. Alas, I don’t know when I will have the tuits to work on that.

Lots of room for growth and improvement in Sqitch going forward. You post provides more food for thought.

Templating Tests with Sqitch

Back in September, I described how to create custom deploy, revert, and verify scripts for various types of Sqitch changes, such as adding a new table. Which is cool and all, but what I’ve found while developing databases at work is that I nearly always want to create a test script with the same name as a newly-added change.

So for the recent v0.990 release, the add command gained the ability to generate arbitrary script files from templates. To get it to work, we just have to create template files. Templates can go into ~/.sqitch/templates (for personal use) or in $(sqitch --etc-path)/templates (for use by everyone on a system). The latter is where templates are installed by default. Here’s what it looks like:

> ls $(sqitch --etc-path)/templates
deploy  revert  verify
> ls $(sqitch --etc-path)/templates/deploy
firebird.tmpl  mysql.tmpl  oracle.tmpl  pg.tmpl  sqlite.tmpl

Each directory defines the type of script and the name of the directory in which it will be created. The contents are default templates, one for each engine.

To create a default test template, all we have to do is create a template for our preferred engine in a directory named test. So I created ~/.sqitch/templates/test/pg.tmpl. Here it is:

SET client_min_messages TO warning;
RESET client_min_messages;

SELECT no_plan();
-- SELECT plan(1);

SELECT pass('Test [% change %]!');

SELECT finish();

This is my standard boilerplate for tests, more or less. It just loads pgTAP, sets the plan, runs the tests, finishes and rolls back. See this template in action:

> sqitch add whatever -n 'Adds whatever.'
Created deploy/whatever.sql
Created revert/whatever.sql
Created test/whatever.sql
Created verify/whatever.sql
Added "whatever" to sqitch.plan

Cool, it added the test script. Here’s what it looks like:

SET client_min_messages TO warning;
RESET client_min_messages;

SELECT no_plan();
-- SELECT plan(1);

SELECT pass('Test whatever!');

SELECT finish();

Note that it replaced the change variable in the call to pass(). All ready to start writing tests! Nice, right? If we don’t want the test script created – for example when adding a column to a table for which a test already exists – we use the --without option to omit it:

> sqitch add add_timestamp_column --without test -n 'Adds whatever.'
Created deploy/add_timestamp_column.sql
Created revert/add_timestamp_column.sql
Created verify/add_timestamp_column.sql
Added "add_timestamp_column" to sqitch.plan

Naturally you’ll want to update the existing test to validate the new column.

In the previous templating post, we added custom scripts as for CREATE TABLE changes; now we can add a test template, too. This one takes advantage of the advanced features of Template Toolkit. We name it ~/.sqitch/templates/test/createtable.tmpl to complement the deploy, revert, and verify scripts created previously:

-- Test [% change %]
SET client_min_messages TO warning;
RESET client_min_messages;

SELECT no_plan();
-- SELECT plan(1);

SET search_path TO [% IF schema %][% schema %],[% END %]public;

SELECT has_table('[% table or change %]');
SELECT has_pk( '[% table or change %]' );

[% FOREACH col IN column -%]
SELECT has_column(        '[% table or change %]', '[% col %]' );
SELECT col_type_is(       '[% table or change %]', '[% col %]', '[% type.item( loop.index ) or 'text' %]' );
SELECT col_not_null(      '[% table or change %]', '[% col %]' );
SELECT col_hasnt_default( '[% table or change %]', '[% col %]' );

[% END %]
SELECT finish();

As before, we tell the add command to use the createtable templates:

> sqitch add corp_widgets --template createtable \
  -s schema=corp -s table=widgets \
  -s column=id -s type=SERIAL \
  -s column=name -s type=TEXT \
  -s column=quantity -s type=INTEGER \
  -n 'Add corp.widgets table.'

This yields a very nice test script to get you going:

-- Test corp_widgets
SET client_min_messages TO warning;
RESET client_min_messages;

SELECT no_plan();
-- SELECT plan(1);

SET search_path TO corp,public;

SELECT has_table('widgets');
SELECT has_pk( 'widgets' );

SELECT has_column(        'widgets', 'id' );
SELECT col_type_is(       'widgets', 'id', 'SERIAL' );
SELECT col_not_null(      'widgets', 'id' );
SELECT col_hasnt_default( 'widgets', 'id' );

SELECT has_column(        'widgets', 'name' );
SELECT col_type_is(       'widgets', 'name', 'TEXT' );
SELECT col_not_null(      'widgets', 'name' );
SELECT col_hasnt_default( 'widgets', 'name' );

SELECT has_column(        'widgets', 'quantity' );
SELECT col_type_is(       'widgets', 'quantity', 'INTEGER' );
SELECT col_not_null(      'widgets', 'quantity' );
SELECT col_hasnt_default( 'widgets', 'quantity' );

SELECT finish();

I don’t know about you, but I’ll be using this functionality a lot.

Sqitch on Target

At the end of the day last week, I released Sqitch v0.990. This was a pretty big release, with lots of changes. The most awesome addition, in my opinion, is Named Deployment targets.

In previous versions of Sqitch, one could set default values for the database to deploy to, but needed to use the --db-* options to deploy to another database. This was fine for development: just set the default on localhost and go. But when it came time to deploy to other servers for testing, QA, or production, it was a bit of a PITA. At work, I ended up writing deployment docs that defined a slew of environment variables, and our operations team needed to adjust those variables to deploy to various servers. It was ugly, and frankly a bit of a pain in the ass.

I thought it’d be better to have named deployment targets, so instead of changing a bunch of environment variables in order to set a bunch of options, we could just name a target and go. I borrowed the idea from Git remotes, and started a database URI spec (mentioned previously) to simplify things a bit. Here’s how it works. Say you have a PostgreSQL Sqitch project called “Flipr”. While doing development, you’ll want to have a local database to deploy to. There is also a QA database and a production database. Use the target command to set them up:

sqitch target add dev db:pg:flipr
sqitch target add qa db:pg://sqitch@qa.example.com/flipr
sqitch target add prod db:pg://sqitch@db.example.com/flipr

Like Git remotes, we just have names and URIs. To deploy to a database, just name it:

sqitch deploy dev

Want to deploy to QA? Name it:

sqitch deploy qa

This works with any of the commands that connect to a database, including revert and status:

sqitch revert --to @HEAD^^ dev
sqitch status prod

The great thing about this feature is that the configuration is all stored in the project Sqitch configuration file. That means you can commit all the connection URIs for all likely targets in directly to the project repository. If they change, just change them in the config, commit, and push.

Targets don’t always have to be configured in advance, of course. The names essentially stand in for the URIs, so you can connect to an unnamed target just by using a URI:

sqitch log db:postgres://db1.example.net/flipr_export

Of course there are still defaults specific to each engine. I generally like to set the “dev” target as the default deploy target, like so:

sqitch config core.pg.target dev

This sets the “dev” target as the default for the PostgreSQL engine. So now I can do stuff with the “dev” target without mentioning it at all:

sqitch rebase --onto HEAD^4

Named targets may also have a couple other attributes associated with them:

  • client: The command-line client to use for a target.
  • registry: The name of the Sqitch registry schema or database, which defaults to, simply, sqitch.

Now that I’ve started using it, I can think of other things I’d like to add to targets in the future, including:

Pretty cool stuff ahead, IMO. I’m grateful to work for letting me hack on Sqitch.

Sqitch Templating

Last week saw the v.980 release of Sqitch, a database change management system. The headline feature in this version is support for MySQL 5.6.4 or higher. Why 5.6.4 rather than 5.1 or even 5.5? Mainly because 5.6.4 finally added support for fractional seconds in DATETIME columns (details in the release notes). This feature is essential for Sqitch, because changes often execute within a second of each other, and the deploy time is included in the log table’s primary key.

With the requirement for fractional seconds satisfied by 5.6.4, there was nothing to prevent usage of SIGNAL, added in 5.5, to mimic check constraints in a trigger. This brings the Sqitch MySQL implementation into line with what was already possible in the Postgres, SQLite, and Oracle support. Check out the tutorial and the accompanying Git repository to get started managing your MySQL databases with Sqitch.

The MySQL support might be the headliner, but the change in v0.980 I’m most excited about is improved template support. Sqitch executes templates to create the default deploy, revert, and verify scripts, but up to now they have not been easy to customize. With v0.980, you can create as many custom templates as you like, and use them as appropriate.

A Custom Template

Let’s create a custom template for creating a table. The first step is to create the template files. Custom templates can live in `sqitch –etc-path`/templates or in ~/.sqitch/templates. Let’s use the latter. Each template goes into a directory for the type of script, so we’ll create them:

mkdir -p ~/.sqitch/templates/deploy
mkdir -p ~/.sqitch/templates/revert 
mkdir -p ~/.sqitch/templates/verify

Copy the default templates for your preferred database engine; here I copy the Postgres templates:

tmpldir=`sqitch --etc-path`/templates
cp $tmpldir/deploy/pg.tmpl ~/.sqitch/templates/deploy/createtable.tmpl
cp $tmpldir/revert/pg.tmpl ~/.sqitch/templates/revert/createtable.tmpl
cp $tmpldir/verify/pg.tmpl ~/.sqitch/templates/verify/createtable.tmpl
chmod -R +w ~/.sqitch/templates

Here’s what the default deploy template looks like:

-- Deploy [% change %]
[% FOREACH item IN requires -%]
-- requires: [% item %]
[% END -%]
[% FOREACH item IN conflicts -%]
-- conflicts: [% item %]
[% END -%]


-- XXX Add DDLs here.


The code before the BEGIN names the template and lists dependencies, which is reasonably useful, so we’ll leave it as-is. We’ll focus on replacing that comment, -- XXX Add DDLs here., with the template for a CREATE TABLE statement. Start simple: just use the change name for the table name. In ~/.sqitch/templates/deploy/createtable.tmpl, replace the comment with these lines:

CREATE TABLE [% change %] (
    -- Add columns here.

In the revert template, ~/.sqitch/templates/deploy/createtable.tmpl, replace the comment with a DROP TABLE statement:

DROP TABLE [% change %];

And finally, in the verify template, ~/.sqitch/templates/verify/createtable.tmpl, replace the comment with a simple SELECT statement, which is just enough to verify the creation of a table:

SELECT * FROM [% change %];

Great, we’ve created a set of simple customized templates for adding a CREATE TABLE change to a Sqitch project. To use them, just pass the --template option to sqitch add, like so:

> sqitch add widgets --template createtable -n 'Add widgets table.'
Created deploy/widgets.sql
Created revert/widgets.sql
Created verify/widgets.sql
Added "widgets" to sqitch.plan

Now have a look at deploy/widgets.sql:

-- Deploy widgets


CREATE TABLE widgets (
    -- Add columns here.


Cool! The revert template should also have done its job. Here’s revert/widgets.sql:

-- Revert widgets


DROP TABLE widgets;


And the verify script, verify/widgets.sql:

-- Verify widgets


SELECT * FROM widgets;


Custom Table Name

What if you want to name the change one thing and the table it creates something else? What if you want to schema-qualify the table? Easy! Sqitch’s dead simple default templating language, Template::Tiny, features if statements. Try using them with custom variables for the schema and table names:

SET search_path TO [% IF schema ][% schema %],[% END %]public;

CREATE TABLE [% IF table %][% table %][% ELSE %][% change %][% END %] (
    -- Add columns here.

If the schema variable is set, the search_path, which determines where objects will go, gets set to $schema,public. If schema is not set, the path is simply public, which is the default schema in Postgres.

We take a similar tack with the CREATE TABLE statement: If the table variable is set, it’s used as the name of the table. Otherwise, we use the change name, as before.

The revert script needs the same treatment:

SET search_path TO [% IF schema ][% schema %],[% END %]public;
DROP TABLE [% IF table %][% table %][% ELSE %][% change %][% END %];

As does the verify script:

SET search_path TO [% IF schema ][% schema %],[% END %]public;
SELECT * FROM [% IF table %][% table %][% ELSE %][% change %][% END %];

Take it for a spin:

> sqitch add corp_widgets --template createtable \
  --set schema=corp --set table=widgets \
  -n 'Add corp.widgets table.'
Created deploy/corp_widgets.sql
Created revert/corp_widgets.sql
Created verify/corp_widgets.sql
Added "corp_widgets" to sqitch.plan

The resulting deploy script will create corp.widgets:

-- Deploy corp_widgets


SET search_path TO corp,public;

CREATE TABLE widgets (
    -- Add columns here.


Cool, right? The revert and verify scripts of course yield similar results. Omitting the --set option, the template falls back on the change name:

-- Deploy widgets


SET search_path TO public;

CREATE TABLE widgets (
    -- Add columns here.


Add Columns

Template variables may contain array values. The default templates takes advantage of this feature to list dependencies in SQL comments. It works great for custom variables, too. For the purposes of our CREATE TABLE template, let’s add columns. Replace the -- Add columns here comment in the deploy simple with these three lines:

[% FOREACH col IN column -%]
    [% col %] TEXT NOT NULL,
[% END -%]

We can similarly improve the verify script: change its SELECT statement to:

SELECT [% FOREACH col IN column %][% col %], [% END %]
  FROM [% IF table %][% table %][% ELSE %][% change %][% END %];

Just pass multiple --set (or -s) options to sqitch add to add as many columns as you like:

> sqitch add corp_widgets --template createtable \
  -s schema=corp -s table=widgets \
  -s column=id -s column=name -s column=quantity \
  -n 'Add corp.widgets table.'

Behold the resulting deploy script!

-- Deploy corp_widgets


SET search_path TO corp,public;

CREATE TABLE widgets (
    name TEXT NOT NULL,
    quantity TEXT NOT NULL,


You still have to edit the resulting file, of course. Maybe NULLs should be allowed in the name column. And I suspect that quantity ought be an integer. There’s that pesky trailing comma to remove, too. The verify script suffers the same deficiency:

-- Verify corp_widgets


SET search_path TO corp,public;
SELECT id, name, quantity, 
  FROM widgets;


Still, these templates remove much of the grudge work of adding CREATE TABLE changes, giving you the scaffolding on which to build the objects you need.

Upgraded Templates

We call Sqitch’s templating language “default” because it can be replaced with a more capable one. Simply install Template Toolkit to transparently upgrade your Sqitch templates. Template Toolkit’s comprehensive feature set covers just about any functionality you could want out of a templating system. It’s big and complex, but relatively straight-forward to install: just run cpan Template, cpanm Template, yum install perl-Template-Toolkit, or the like and you’ll be in business.

We can resolve the trailing comma issue thanks to Template Toolkit’s loop variable, which is implicitly created in the FOREACH loop. Simply replace the comma in the template with the expression [% loop.last ? '' : ',' %]:

[% FOREACH col IN column -%]
    [% col %] TEXT NOT NULL[% loop.last ? '' : ',' %]
[% END -%]

Now the comma will be omitted for the last iteration of the loop. The fix for the verify script is even simpler: use join() VMethod instead of a FOREACH loop to emit all the columns in a single expression:

SELECT [% column.join(', ') %]
  FROM [% IF table %][% table %][% ELSE %][% change %][% END %];

Really simplifies things, doesn’t it?

Better still, going back to the deploy template, we can add data types for each column. Try this on for size:

[% FOREACH col IN column -%]
    [% col %] [% type.item( loop.index ) or 'TEXT' %] NOT NULL[% loop.last ? '' : ',' %]
[% END -%]

As we iterate over the list of columns, simply pass loop.index to the item() VMethod on the type variable to get the corresponding type. Then specify a type for each column when you create the change:

> sqitch add corp_widgets --template createtable \
  -s schema=corp -s table=widgets \
  -s column=id -s type=SERIAL \
  -s column=name -s type=TEXT \
  -s column=quantity -s type=INTEGER \
  -n 'Add corp.widgets table.'

This yields a much more comprehensive deploy script:

-- Deploy corp_widgets


SET search_path TO corp,public;

CREATE TABLE widgets (
    name TEXT NOT NULL,
    quantity INTEGER NOT NULL


Go Crazy

The basics for creating task-specific change templates are baked into Sqitch, and a transparent upgrade to advanced templating is a simple install away. I can imagine lots of uses for task-specific changes, including:

  • Adding schemas, users, procedures, and views
  • Modifying tables to add columns, constraints and indexes
  • Inserting or Updating data

Maybe folks will even start sharing templates! You should subscribe to the mail list to find out. See you there?

Data Deployment with Sqitch

I’ve been thinking about data migrations. I love how well Sqitch works for schema changes, but so far have avoided data changes. Some data ought to be managed by the deployment process, rather than by end-user applications. Lists of countries, for example. Yet none of our Sqitch-managed databases include INSERTs, UPDATEs, or DELETEs in deploy scripts. Why not? Two reasons:

  1. These are mainly Postgres ports of existing Oracle databases. As such, I’ve written independent migration scripts that use oracle_fdw to copy data from Oracle. It made no sense to commit hard-coded changes to the deploy script, even for static data, as it was all to be copied from the old production Oracle databases — often months after I wrote the migrations.

  2. These projects include extensive pgTAP unit tests that expect to run many times against an empty database with no side effects. Having different data in testing than in production increases the likelihood of unforeseen behavioral variations. Better to expect no data in tests, freeing them to focus on units of behavior without regard to preexisting data.

Now that we have multiple Sqitch-deployed databases in production, the time has come to address these issues.

Deploy Hooks for External Sources

I propose to resolve the one-time migration requirement with deploy hooks. The idea is similar to Git hooks: Before or after any sqitch deploy, one or more hook scripts can run. The impetus was to ensure some higher level of consistency after every deploy. For example, a post-deploy hook might grant privileges on all tables in a database. Another might run VACCUM; ANALZYE;.

But we could also use it for one-time data migrations. An option to deploy will disable them, which would be useful for development and test databases. Once the migration has been run in production, we just delete the migration hook scripts from the project. Sqitch won’t record hook executions, so adding or removing them will be no problem.

I like this approach, as Sqitch will automatically run migration scripts, but hooks will not change the interface of Sqitch itself. And it’s more generally useful. Hell, I might want deploy hook script that sends an email notification announcing a deployment (though that might require adding support for non-SQL scripts). There are all kinds of things for which hooks will prove useful.

Changes for Static Data Maintenance

For data that must be tied to the deployment process, there are two complications that have prevented me from simply managing them in normal Sqitch changes:

  1. There might be side-effects to the deployment. For example, a foreign key constraint to the users table, to identify the users who added rows to the database. But in a new database, perhaps there are no users — and Sqitch doesn’t create them, the app does. Chicken, meet egg.

  2. The initial data set might derived from some external source, such as another database. Consequently, none of that data was defined in Sqitch deploy scripts. This situation complicates future updates of the data. We can add data via Sqitch in the future, but then we don’t have the canonical list of all rows that should exist in all such databases.

However, I can think of no alternative that does not over-complicate Sqitch itself. I considered adding another change-related script type, named “update”, to complement the existing deploy, verify, and revert scripts. But oftentimes a data change would not be tied to a schema change, so the corresponding deploy script would be a no-op. Seems silly.

I also considered adding a completely separate command specifically for deploying data changes. Yet these data migrations are exactly like schema changes: Sqitch must execute them in the proper order relative to other changes, record successful or failed deployment, and be able to revert them when required. The only difference is what’s defined in them: data modification rather than definition.

Through several drafts of this post, I have come around to the idea that I should change nothing in Sqitch with regard to data deployments. A better solution than the above, I believe, is organizational.

Data Deploy Best Practice

Let the best practice for data deploys be this: they should be contained in Sqitch changes, but such changes should contain only data modifications. No change script should both define a table and insert its initial rows. Keep the table and its data in separate changes — keep DML separate from DDL.

For our list of countries, we might have a change named “countries”, which creates the countries table, and another, named “country_data”, which inserts the appropriate data into that table. Where necessary and appropriate, these changes may use conditional paths to bring the data up-to-date and in sync across deployments.

Conditions must deal with side-effects, such as foreign key constraints. Where possible, such side effects ought be removed from deployment-managed data. For tracking the user or users who added data to a database, for example, one can use the tools of the source code repository (git log, git blame) to assign blame. Other side-effects may be more necessary, but to the extent possible, deployed data should be independent.

Naturally, unit tests must expect static data to be present, and be updated when that data changes. We are, after all, talking about infrequently-updated data. Frequently-updated data should have separate interfaces provided by applications to change the data. Otherwise, how static is it, really?

Requiring Reworked Sqitch Changes

I recently discovered a mildly annoying bug in Sqitch, the Git-inspired database schema change management app I’ve been working on for the past year. One of its key features is the ability to “rework” changes. For example, if you have a change that defines a function change_password(), and discover sometime after release that it has a bug (maybe the hashing algorithm is too weak), you can “rework” it – essentially modify it in place – and save some headaches. Check out the “In Place Changes” section of the (PostgreSQL, SQLite, Oracle, or MySQL (coming soon) tutorials for detailed examples of how it works.

The bug was about what happens when one adds a new change that depends on a reworked change, but just specifies it by name, such as change_password:

sqitch add meow --requires change_password

This added the change fine, but at deploy time, Sqitch complained that there were multiple instances of a change in the database. Of course, that’s true, because change_password will have been deployed twice: once for the original version, and the second time for the reworked version. This was inconsistent with how it looked up changes in the plan, where it would just return the first instance of a change in the plan. So I changed it so that dependency lookups in the database also return the first instance of the change. I believe this makes sense, because if you require change_password, without specifying which instance you want, you probably want any instance, starting with the earliest.

But what if you actually need to require a specific instance of a reworked change? Let’s say your plan looks like this:


change_pass [change_pass@v1.0]

The third change is change_pass, and it has been reworked in the sixth change (requiring the previous version, as of the @v1.0 tag). If you want to require any instance of change_pass, you specify it as in the previous example. But what if there were changes in the reworked version that you require? You might try to require it as-of the symbolic tag @HEAD:

sqitch add meow --requires change_password@HEAD

This means, “Require the last instance of change_password in the plan.” And that would work…until you reworked it again, then it would be updated to point at the newer instance. Sqitch will choke on that, because you can’t require changes that appear later in the plan.

So what we have to do instead is add a new tag after the second instance of change_pass:

sqitch tag rehash

Now the plan will look like this:


change_pass [change_pass@v1.0]

Now we can identify exactly the instance we need by specifying that tag:

sqitch add meow --requires change_password@rehash

Meaning “The instance of change_password as of @rehash.” If what you really needed was the first version, you can specify the tag that follows it:

sqitch add meow --requires change_password@v1.0

Which, since it is the first instance is the same as specifying no tag at all. But if there were, say, four instances of change_pass, you can see how it might be important to use tags to specify specific instances for dependencies.

For what it’s worth, this is how to get around the original bug referenced above: just specify which instance of the change to require by using a tag that follows that instance, and the error should go away.

Looking for the comments? Try the old layout.

Notes on Upcoming Sqitch Improvements

I was traveling last week, and knowing I would be offline a fair bit, not to mention seriously jet-lagged, I put my hacking efforts into getting MySQL support into Sqitch. I merged it in yesterday; check out the tutorial if you’re interested in it. I expect to release v0.980 with the MySQL support in a couple of weeks; testing and feedback would most appreciated.

There is a caveat, though: it requires MySQL v5.6.4. So if you’re stuck with an older MySQL, it won’t work. There are two reasons to require v5.6.4:

  • The microsecond precision support in DATETIME values, added in v5.6.4. This makes it much easier to keep things in the proper order (deployments usually take less than a second).
  • The SIGNAL functionality, introduced in v5.5. This allows the schema to mock a check constraint in the Sqitch database, as well as make it much easier to write verify tests (as described in the tutorial and figured out on StackOverflow).

But if you can afford to take advantage of a relatively modern MySQL, give it a shot!

The next release also makes a backwards-incompatible change to the SQLite engine: the default Sqitch database is no longer $db_dir/$db_name-sqitch.$suffix, but $db_dir/sqitch.$suffix. In other words, if you were deploying to a db named /var/db/myapp.db, Sqitch previously kept its metadata in /var/db/myapp-sqitch.db, but now will keep it in /var/db/sqitch.db. This is to make it more like the other engines (MySQL defaults to a database named “sqitch”, and Postgres and Oracle default to a schema named “sqitch”).

It’s also useful if you use the SQLite ATTACHDATABASE command to manage multiple database files in a single project. In that case, you will want to use the same metadata file for all the databases. Keep them all in the same directory with the same suffix and you get just that with the default sqitch database.

If you’d like it to have a different name, use sqitch config core.sqlite.sqitch_db $name to configure it. This will be useful if you don’t want to use the same Sqitch database to manage multiple databases, or if you do, but they live in different directories.

I haven’t released this change yet, and I am not a big-time SQLite user. So if this makes no sense, please comment on this issue. It’ll be a couple of weeks before I release v0.980, so there is time to reverse if if there’s consensus that it’s a bad idea.

But given another idea I’ve had, I suspect it will be okay. The idea is to expand on the concept of a Sqitch “target” by giving it its own command and configuration settings. Basically, it would be sort of like Git remotes: use URIs to specify database connection and parameter info (such as the sqitch database name for SQLite). These can be passed to database-touching commands, such as deploy, revert, log, and the like. They can also be given names and stored in the configuration file. The upshot is that it would enable invocations such as

sqitch deploy production
sqitch log qa
sqitch status pg://localhost/flipr?sqitch_schema=meta

See the GitHub issue for a fuller description of this feature. I’m certain that this would be useful at work, as we have a limited number of databases that we deploy each Sqitch project to, and it’s more of a PITA for my co-workers to remember to use different values for the --db-host, --db-user, --db-name and friends options. The project itself would just store the named list of relevant deployment targets.

And it alleviates the issue of specifying a different Sqitch database on SQLite or MySQL, as one can just create a named target that specifies it in the URI.

Not sure when I will get to this feature, though. I think it would be great to have, and maybe iovation would want me to spend some time on it in the next couple of months. But it might also be a great place for someone else to get started adding functionality to Sqitch.

Oh, and before I forget: it looks like Sqitch might soon get CUBRID support, too, thanks to Ștefan Suciu. Stay tuned!

Looking for the comments? Try the old layout.

Sqitch Mail List

Just a quick post to announce that I’ve set up a Sqitch Google Group. I’ve been getting a lot more email about it lately, and a fair bit of it should be shared more generally. So if you’re interested in Sqitch, sign up! (Don’t like web forums? Me neither. Feel free to subscribe by email, instead.)

Looking for the comments? Try the old layout.

Agile Database Development Tutorial

I gave a tutorial at PGCon a couple weeks back, entitled “Agile Database Development with Git, Sqitch, and pgTAP.” It went well, I think. The Keynote document and an exported PDF have been [posted on PGCon.org]Agile Database Development, and also uploaded to Speaker Deck. And embedded below, too. Want to follow along? Clone the tutorial Git repository and follow along. Here’s the teaser:

Hi, I’m David. I like to write database apps. Just as much as I like to write web apps. (Maybe more!) How? Not by relying on bolted-on, half-baked database integration tools like migrations, I’ll tell you that!. Instead, I make extensive use of best-of-breed tools for source control (Git), database unit testing (pgTAP), and database change management and deployment (Sqitch). If you’d like to get as much pleasure out of database development as you do application development, join me for this tutorial. We’ll develop a sample application using the processes and tools I’ve come to depend on, and you’ll find out whether they might work for you. Either way, I promise it will at least be an amusing use of your time.

Looking for the comments? Try the old layout.

Sqitch on Oracle

I found myself with a little unexpected time at work recently, and since we use Oracle (for a few more months), I decided to port Sqitch. Last night, I released v0.970 with full support for Oracle. I did the development against an 11.2 VirtualBox VM, though I think it should work on 10g, as well.

Sqitch is available from the usual locations. For Oracle support, you’ll need the Instant Client, including SQL*Plus. Make sure you have $ORACLE_HOM set and you’ll be ready to install. Via CPAN, it’s

cpan install App::Sqitch DBD::Oracle

Via Homebrew:

brew tap theory/sqitch
brew install sqitch-oracle

Via ActiveState PPM, install ActivePerl, then run:

ppm install App-Sqitch DBD-Oracle
PGCon 2013

There are a few other minor tweaks and fixed in this release; check the release notes for details.

Want more? I will be giving a half-day tutorial, entitled “Agile Database Development,” on database development with Git, Sqitch, and pgTAP at on May 22 PGCon 2013 in Ottawa, Ontario. Come on up!

Looking for the comments? Try the old layout.