Script for Database Patching at Deploy Time
My current project (BiteStats, a simple report of your google analytics data) uses a basic system where there are numbered patches, and a patch_history table with a row for every patch that was run, showing the version number and a timestamp. When I deploy the code to production, I have a script that runs automatically to apply the patches.
// get current patch level
$stmt = $db->query('select max(patch_number) as last from patch_history');
$result = $stmt->fetch();
$last_patch = $result['last'];
echo "current db patch level: $last_patch\n";
// get list of patches
$patches = glob(APPLICATION_PATH . '/../db/patch-*.sql');
foreach($patches as $patch_file) {
$pattern_matches = array();
preg_match('/patch-([0-9]{3})\.sql$/',$patch_file, $pattern_matches);
if($pattern_matches[1] > $last_patch) {
echo "running $patch_file\n";
$cmd = 'mysql -h ' . escapeshellarg($db_params['host'] )
. ' -u ' . escapeshellarg($db_params['username'] )
. ' -p' . escapeshellarg($db_params['password'] )
. ' ' . escapeshellarg($db_params['dbname'] )
. ' 2>&1 < ' . escapeshellarg($patch_file);
exec($cmd, $output, $retval);
if($retval != 0) {
var_dump($output);
exit;
}
}
}
This is actually a ZF application (as you might be able to guess) and I have one controller bootstrapped to run from the CLI. The code shown here resides in a action in the CliController, which makes it perfect for use in a scripted deployment - this project uses phing to deploy, which I am really enjoying using. I can also just call the script directly if I've updated a copy of the code in a development or staging area and there are new patches.
dbdeploy in phing (which in turn is a copy of dbdeploy for ant) does something really similar. Except that the migrations are called–.sql :)
Lars: Since I’m using phing anyway, I should really look into dbdeploy some day
Seconded: I use phing and dbdeploy, and it’s really easy to use. Just make a .sql file in a directory which contains all delta’s (or patches), make sure it has an undo part as well, and run the dbdeploy method. Works like a charm :)
Does the glob() function always give you the patches in the correct order, alphabetically sorted?
I think you are also missing the query where you add the applied patch levels to the patch_history table, or is this done inside the patches?
@Michael K: glob() will always return the results in alphanumeric order for the full pathname.
@Lorna: Wouldn’t it also make sense to wrap the patch files in begin and end transactions before passing it to the command line so that failures are rolled back safely?
Obviously in MySQL this will only work for InnoDB tables, and even then there is caveats, but your only other alternative is to take a database snapshot first.
I tend to use a similar method to you when doing my database patches but I’ve yet to find a light-weight recovery solution I’m really happy with.
MickaelK is right, the glob ordering isn’t perfect but for a small project with precisely 4 patches, this is currently “good enough”.
Ben: Transactions would be sensible if the database supported them – although perhaps having each patch make that decision would be more flexible. Each patch inserts its own patch_history row, since they can also be run manually.
I don’t have rollback in this solution and that’s an active decision on my part that I will take the risk of needing to live fix over the trouble of writing rollback patches. If this project starts averaging more than one hit a day, I might feel differently about it :)
As I say, not perfect but it is practical and it beats 90% of what I see so I thought I’d share. Seeing these comments has made me think and I might make some improvements – thanks everyone!
disclaimer: I am not underestimating the universe’s ability to produce idiots, the point I’m trying to make is that I haven’t managed to make any deploy mistakes using this approach. Yet. Once upon a time, a long time ago, I went onto a conference sta