Testing your sqitch changes
When you work on larger projects, you'll often find that database changes are hard. Multiple developers, working on the same project, changing the same tables, can be difficult. Database migration tools often (but not always), come with one or more standard flaws:
- Reliance on migration numbers (in other words, two or more developers commit migration number 6 and get a conflict)
- Reliance on an ORM, such as
DBIx::Class(sucks for the Python devs)
- Reliance on something other than the Data Definition Language, or DDL (sucks when your custom tool can't represent the stored procedure you want to define)
There are plenty of other ways database migration tools fail, but the best standard tool I've worked with so far is sqitch. Its documentation needs some work, including more explanations of how to deal with common failure modes, but it avoids the above problems and provides you with a rich set of tools to make database migrations easier for large teams.
However, its common failure modes include:
- Creating a "revert" change that doesn't revert.
- "verify" scripts that work now but don't work in the future (for example, when MySQL is changed to "strict" mode)
- Hard-to-comprehend error messages about changes not appearing in the plan (usually a merge conflict that can be solved with reverting some changes and re-deploying)
There's work being done on some of these issues, but the first two can be mitigated with a simple test script I wrote.
The following is what I use on the Veure project (which is coming along nicely) and is hacked together for PostgreSQL. You'll need to adjust it for your system.
The way it works is simple:
sqitch add some/change
Note that we don't commit our changes to git (or your preferred source control). Nor do we deploy our changes. Instead,
t/sqitch.t creates a test database and deploys all changes, verifies them, and then reverts them. Only if this test passes do you commit and deploy your changes.
I've a more complicated version I've written for ZipRecruiter, a client we've been helping (check 'em out. They're great to work for) and it's helped find plenty of interesting issues.