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- Edit
sqitch/{deploy,verify,revert}/some/change.sql prove t/sqitch.t
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.
Freelance Perl/Testing/Agile consultant and trainer. See http://www.allaroundtheworld.fr/ for our services. If you have a problem with Perl, we will solve it for you.
And don't forget to buy my book! http://www.amazon.com/Beginning-Perl-Curtis-Poe/dp/1118013840/
Maybe you want to take a look at DBIx::SchemaChecksum, which works perfectly with Postgres (or other DBs that allow schema changes to happen inside a transaction). It does not support downgrading, though...
I was using sqitch, but it does too much, and it breaks too often, and I don't need it to manage revisions for me, I use git. But I do need it to work with git, when 2 people add a migration, I don't need it to break and require me to revert one of the migrations and manually rearrange the plan
In the end it turned out to be far more work than it was worth, so I wrote something that does what we need, and haven't looked back
There's no documentation (but it's similar to sqitch, and should be easy to read the code)
https://gist.github.com/markwellis/2925aaec5fd393e7384a goes in bin/db_manager.pl
and an example config https://gist.github.com/markwellis/743eddf6a2e3e9b53021 goes in migrations/config.json
you'll also need to create migrations/plan (empty file) migrations/deploy/ migrations/revert/ migrations/verify/
It also supports tunnelling over ssh, so you can connect to a server on a firewalled machine
It's not got tests, and it's got no documentation, and I've only used it with mysql, but it works for us.