Stacking Code

This is post #38 in the Building Neno series. Please click here for a description of the Building Neno project and instructions on how to access the source code for this post.

This is a post I started back in February, didn't finish and eventually forgot. I only found it by running some queries on my database (I seriously need some admin screens). Hopefully finishing it will kickstart me into building Neno again.

If you're like me you've been struggling with .dbproj for a while now wondering why you're using this massive tool to manage a simple database schema. Where I've struggled with it in particular is the deployment of changes to multiple environments. It handles local deployments well (almost magically, provided no data is affected), but for other environments it starts to break down. Things quickly get clunky, manual, and not much fun.

Alternatives

So if .dbproj isn't working out, what else is there? Luckily there are a number of libraries and tools in the .NET space for managing database versioning and migrations. Here are a few of them in no special order.

• DotNetMigrations
• FluentMigrator
• RoundhousE

All of them are fantastic and work brilliantly. It's not hard to find folks on the interwebs raving about them. If you haven't already checked one of them out, I strongly encourage you to do so. They make a tricky area to manage very simple and straight forward. You'll wonder how you ever lived without one.

I might be convincing you to get the hell off my lawn post and go explore NuGet, but if you stick around I hope to demonstrate an even leaner way to manage migrations.

What are Migrations?

Migrations are a structured way to apply (and rollback) incremental schema changes that are easy to share, and to repeat. At their core they work like this...

• Each committed change is a named version.
• Each version has a SQL migration script. The script contains the necessary changes (CREATE, ALTER, DELETE, etc.) to update the last version to this version. (Rails Migrations are written in Ruby and FluentMigrations are written in C#, but in the end it all boils down to SQL.)
• A version table in the database tracks the last migration applied. To get to a particular version the missing migrations are applied and the table is updated. (This makes managing migrations a breeze across different deployment environments -- each database tracks its own version.)
• Some libraries and tools allow for "down" migrations as well as "up" migrations.

The basics of migrations aren't rocket science. Simple... but also powerful. If you don't mind working with SQL files directly, you can manage the whole process with a couple of conventions and a decent script.

The Conventions

A picture is worth a thousand words, so here's a picture of the directory structure my migrations script will be expecting.

The database project is a class library for no other reason than to keep it minimalist and make it look nice in Visual Studio. If you don't like this approach you can always keep the files outside Visual Studio or add them as solution items.

Each version will have a directory -- the directory name will be the name of the version. I'm using "Test1" and "Test2" as an example, but really they can be anything as long as sorting them alphanumerically gives the right order. (In other projects I've used the date and time of the change. For example, "201110122019", which is the date and time of this post.)

Contained in each directory is the up and down migration script. These can be coded by hand, or if you like to keep schema files (similar to .dbproj) you can use a tool to generate them. Usually some manual tweaking is still required whether you use a tool or not.

The Script

I won't bore you with the details of the script (you can view it here). It's based on a batch file I wrote back in 2004 to apply forward only schema changes using SQLCMD. There was some tricky stuff in the batch file working with file names, stuff that is a lot simpler now with PowerShell. However, my PowerShell Kung-Fu isn't strong, so if you spot an easier way to do something, please drop me a line.

One detail I will explain is why I don't use the SQL Server PowerShell cmdlets. I did for a while in another version of this script, but I had to eventually ditch the cmdlets and go back to SQLCMD. My build server is 64 bit, and I have no idea why, but SQL Server only installs snap-ins for 64 bit PowerShell on a 64 bit machine. Unfortunately many of my builds use 32 bit MSBuild (there are some issues with Silverlight and 64 bit MSBuild), so I was forever running into issues with the snap-ins not being available. There are workarounds, but the easiest workaround overall was just to fall back to SQLCMD.

Let's see the script in action! I'll run all my test migrations against an empty database using the Maximum flag.

The script puts together a migration.sql file containing all the SQL it needs to run. If we peek under the hood we can see what happened.

BEGIN TRANSACTION
GO

PRINT "Test1 Up"
GO

PRINT "Test2 Up"
GO

UPDATE [dbo].[Version] SET [Current] = 'Test2'
GO

COMMIT TRANSACTION
GO

Make sure to exclude this file from version control. It's autogenerated and doesn't contain anything that isn't already in version control.

The scripts were put together in the right order, the Version table was updated, and the whole thing was wrapped in a transaction to make sure it all went through or none of it.

Let's tear down all the migrations and go back to zero with the Minimum flag.

We can also target a particular migration with the Target flag.

Finally, let's rollback the latest migration using the (yes, you guessed it) Rollback flag.

Rollback decrements the version by one migration, it's doesn't reverse the last operation done.

In my next Building Neno post I'll demonstrate setting this up with a real project. In a future post I'll show it in action with MSBuild and automatic deployment. 'Til then.