Database migrations are an increasingly common pattern for managing and automating the creation of your projects database schemas. Typically each migration has 3 elements:
- A Unique Id – each new migration is given a numeric identifier higher than the previous one.
- An UP component – describing the table / row / column / key – you want to create
- A DOWN component – which exactly reverses the change you’re are making in the UP component.
Thus by running each of the migrations in order you can go from an empty database, to the schema required by your project, or if an error occurs you can simply roll back any number of migrations to get to a known stable state.
The advantages of this approach may not be immediately obvious, but I’d say the main advantages are:
- No seperate SQL scripts, db exports etc. All database migrations are contained within the project and can be reviewed and managed by the team – No DBA Required.
- Easy deployment onto as many servers as required – most medium to large projects will have a number of environments, minimally dev, test and live. By creating DB migrations it’s simple to keep each of these environments in synch.
- As the project grows, database migrations can be created as required, meaning you can easily rollout new changes to live projects.
- Easy rollback – if you rollout a patch containing a migration, you can instantly roll it back without discovering you don’t have rollback scripts.
- Roll outs to the live db are usually possible with no downtime
You can see that the migration is decorated with a number in this case 3. You can see the UP migration creates the UserRoles table and a number of Foreign Keys. You should also see that the DOWN migration reverses this change, deleting the keys then deleting the table itself.
Thus if a problem occurs during the creation of this table, FluentMigrator can rollback the migration, as described in your DOWN migration.
Running the migrations
Migrations on their own are of little value if you can’t run them against a target database. FluentMigrator integrates with both MSBuild and Nant, meaning you can run migrations as part of your build process. What’s nice about this is that you can only run in DB changes if all your unit tests etc pass, and if you have a more sophisticated build script you can control which db migrations are rolled out onto different environments.
Readers of my blog will be familiar with my love of Nant and my Nuget package Nant.Builder. So it won’t come as a great surprise to find out I’ve added a FluentMigrator target into my Nant.Builder scripts.
You can set up the appropriate values in Nant.xml, you can see a working sample in the Nant.Builder.Sample this uses sqlite (note it will only run on 64bit windows).
As far as best practice goes, as I demonstrate in the sample project I’d advise:
- Keep all the migration classes in a separate project
- Number the migration classes in line with their migration number ie Mig012_CreateTableX – makes it easier to manage them once you have a few migrations.
Here’s a snap of our migrations on one of our large projects, as you can see after iteration 18 it occurs to us to start creating folders per iteration, allowing to keep things a bit more ordered:
Fluentmigrator takes a lot of the pain out of managing your database schema over multiple environments. If there’s a negative it’s that the project is a little bit flakey on environments other than Sqlserver, I’ve tried it on SqlserverCe and Sqlite and it’s not worked or only worked after poking at the code for a while.
On the positive side the project is being actively maintained and updated, I had a minor update accepted and committed within a few days of posting it. So download it and get involved.