Page 1 of 1

Testing DB migrations that change data (and down migrations)

Posted: Sun Sep 12, 2010 6:19 am
by jarofgreen
So I previously posted about a DB interface I ended up working on in a project with PHPUnit tests ... viewtopic.php?f=50&t=118688 ... and now I'm curious how people test Migrations. Not ones like "Add this column" cos that's easy. But how do you test ones that take data in an old schema and migrate it to a new schema? 'Cos it seems to me you would have create the database up to a fixed point, add data, then run just that migration, then check it again - or something? Anyway, I have thoughts but I'm curious to hear how other people handle this ... and as also said in that post, I also really want to work out a neat way of testing down migrations before I start using them.

Re: Testing DB migrations that change data (and down migrati

Posted: Wed Sep 15, 2010 12:58 pm
by josh
Yep. Say the live site is at version 50 and I've created 50 more (51-100). Before I push the next update, I pull down the production database to a staging server of some sort, and just do a dry run. My migrations run forwards only. If there was a screw up I would DROP & restore. I'd do the update during non peak hours and take down the site for it.

Re: Testing DB migrations that change data (and down migrati

Posted: Wed Sep 15, 2010 5:31 pm
by jarofgreen
I actually meant testing in an automated sense. Some people might argue automated testing is to catch regression bugs so as migration code will never be run again there is no point.

But I would argue that it is also about testing and proving code works as you code. Also, if your software is being run on several servers there may be several different data sets to worry about. And if your releasing software for others to manage you want to be sure it's good.

So all in all, there have been times when I really want to automatically test a migration, which was what I was trying to ask about (sorry for not being clear)

Re: Testing DB migrations that change data (and down migrati

Posted: Wed Sep 15, 2010 7:50 pm
by Eran
If only the data changes and the structure stays the same, that would be pretty difficult - at the least in the general case. If the structure changes as well, what I usually do is test new functionality that depends on the structure changes.

Re: Testing DB migrations that change data (and down migrati

Posted: Thu Sep 16, 2010 1:09 pm
by josh
jarofgreen wrote:I actually meant testing in an automated sense.
Migrations are already automated, if you don't allow backwards migrations (no need for these anyways, unnecessary complexity) - then there is one path only to test. The only problematic part is making the migrations run for each upgrade. If you released the following versions & schema versions:

Software - Database
v1.0 - v1
v2.0 - v10
v3.0 - v20

Your migration might work when running from database versions v1 - v10, but might not work for database versions v1 - v20 (if and only if your migration uses the "Model" objects from software version v3.0 which issue different SQL in v3.0 than they did in software version v2.0). If you keep things simple, and use plain SQL, that can't happen. Thats why the guy who wrote the database refactoring book tells you to do it this way. I had to go & learn the hard way to prove it to myself.

In your case you allow forwards and backwards migrations, which means you have 100x more paths to test than I do. Like I said I have no advice for you in this situation then, other than good luck & let us know what you come up with...

My testing process is to decide which legacy versions are supported. Then I test upgrading from each of those legacy versions, to the current version - doing so with a cleverly crafted sample data set that is crafted to contain different data formatting issues like you mentioned. So if I am supporting upgrades from v1 or v2, then I need only run two SVN checkouts, and type "php dbrefactor.php" after each check out. This is trivial to automate with a shell or batch script if so desired.