Monday 28 March 2011

Automated database deployments to AppHarbor

AppHarbor is trying to provide .NET developers with similar goodness that Heroku offers to their Ruby counterparts. At the moment deployment to AppHarbor has at least 6 distinct stages:
  • Code gets pushed to AppHarbor
  • Code gets compiled
  • Unit tests get run
  • Config transformations get applied
  • Configuration Variables get applied
  • Code gets actually deployed to a web server
At least one obvious stage is missing which is the database deployment. In .NET world there is nothing that would be even close to Ruby on Rails migrations but we can do something about that.
There is a small library called DbUp that simply makes sure that a given set of SQL scripts gets executed only once. This the ”up” part of RoR migrations. Now the question is when the database upgrade should be performed. There are basically 2 ways. Either before the new code gets deployed or after. I prefer the former option because as long as the database changes are non-breaking the app is fully functional at all times. If the database upgrade fails then the new version of the application won’t be deployed.
If the upgrade is done after the new version of the application is deployed then it needs to be done at the startup which prevents users from using the application for some period of time. What is more if the database upgrade fails then the new version of the application needs to be rolled back. In most cases the new code won’t work with the old database schema.
At the moment AppHarbor doesn’t have an explicit step when database upgrade can be performed. So to work around this problem I created a simple MsBuild target called UpgradeDatabaseInProduction that gets run when the application is successfully compiled. All the target does is extract production connection string from Web.Release.config and pass it to DbUp console app that performs the upgrade.


<target Name="UpgradeDatabaseInProduction"
AfterTargets="MvcBuildViews"
Condition="'$(Configuration)'=='Release'">
<calltarget Targets="UpgradeDatabase" />
</Target>



<target Name="UpgradeDatabase">
<xmlpeek XmlInputPath="$(MSBuildThisFileDirectory)Web.$(Configuration).config"
Query="/configuration/connectionStrings/add[@name='database']/@connectionString">
<output TaskParameter="Result" ItemName="ConnectionString" />
</XmlPeek>
<exec Command="$(MSBuildThisFileDirectory)..\libs\dbconsole-custom\DbUp.Console.exe -cs "@(ConnectionString)" -d $(MSBuildThisFileDirectory)SqlScripts" />

<warning Text="Database upgraded" />
</Target>


This works only because AppHarbor build servers can talk  to AppHarbor database servers. To be honest it’s more of a hack than a proper solution :) but at least I can deploy the whole app (code + database changes) with single git push which is simply priceless.

3 comments:

  1. That's cool. I've been doing this on appication start so no need for MSBuild and it works even locally in the same way so other devs are always running on the latest version.

    I'm using Migrator.NET, but the same could be done with DBUp.

    ReplyDelete
  2. Migrator.NET looks interesting but the project itself seems to be abandoned. Last release 2 years ago and it's still using SVN.

    As I mentioned database upgrade at application startup causes issues hence my conscious decision to perform it before the new code gets deployed. Same can be done during development, simply remove the condition from the msbuild target.

    ReplyDelete
  3. @pawel i think that migrator is still alive. Checkout here ;-)https://github.com/migratordotnet/Migrator.NET

    ReplyDelete