Upgrading Database Schema’s in .NET

November 21st, 2008 by Xerxes Leave a reply »

This post is the first what I intend to be an open-ended series of posts about my current experience working with a pet development project for learning purposes.


Not long ago, I set myself the task of wanting to experiment with some new/upcoming projects in terms of .NET development. The original intention started off basically as me being interested in finding out what they were and how they were built.

More lately, however, I’ve given myself a pet-project with the task of building it using TDD and more importantly, to try out these different projects and work out a solution which i’m comfortable has zero-friction (or very little). I’m not asking a lot – just for proper SoC and at the end i’d (theoretically) have a project which is a first-round example of TDD and proper design.

  • I want to provide multiple UIs for the core application (Web, Win, WPF) – I’ve seen a number application architectures which employ practices to separate presentation from logic, but ALL of them either failed over time or never actually tried moving to a different presentation platform (at which point they too probably would have failed). For this reason, I wanted to build something which (from day zero) has been built in parallel with several UIs
  • I want to be able to rip out the database and put a new one in – This is more academic than anything else. IMO there are a lot of unsubstantiated claims floating around the software world that proper abstraction will *easily* allow you to just swap datastores on a whim. Well I want to put that to the test.

So in pursuit of these goals, i’ve started writing my simple note-taking application, and over a very disparate period of weeks i’ve done a lot of reading and discovering of tools for the ORM, migration and object model.

In terms of data migration, I was impressed with what MigratorDotNET was capable of. It basically allowed me to speak one programming language, and with a little NAnt scripting I was able to generate a database migration system.

using System;
using System.Data;
using Migrator.Framework;

namespace DBMigrations
{
	[Migration(1)]
	public class _0001_CreateNoteTable : Migration
	{
		public override void Up()
		{
			Database.AddTable("Note", new Column[]
              	{
              		new Column("NoteId", DbType.Guid, ColumnProperty.PrimaryKey | ColumnProperty.NotNull, "NEWID()"),
              		new Column("NoteTitle", DbType.String, 64, ColumnProperty.NotNull),
              		new Column("NoteData", DbType.String, 32768)
              	});
		}

		public override void Down()
		{
			Database.RemoveTable("Note");
		}
	}
}

Not that I strictly had a major problem with the thought behind the approach offered by MigratorDotNET, but the two things that I didn’t like about the system:

  • Database schema names are defined as strings – any typo’s aren’t picked up at compile time, they’re only picked up at unit-test time (if you’re diligent). You could work-around this by using string constants to define the column and table names, but then you’d had to maintain that list too.
  • Changes to the database had to be run independently of the application. In my case i’d used the NAnt provider to perform migration, but I had the intention of integrating it into the application so that it could self-upgrade without the need for an external component.

At the time I started the project, this system seemed fine, and I was able to write tests for the migration using a base generic class:

	[TestFixture]
	public class MigrationTester<T%gt; where T: Migration, new()
	{
		public virtual void AssertMigrateUp(T migration) {}
		public virtual void AssertMigrateDown(T migration) { }

		[Test]
		public void Test_Migrate()
		{
			MockRepository repos = new MockRepository();
			ITransformationProvider mockDB = (ITransformationProvider)repos.Stub(typeof(ITransformationProvider));

			T migration = new T()
			{
				Database =
					(ITransformationProvider)
					MockRepository.GenerateStub(typeof(ITransformationProvider))
			};


			Exception thrownException = null;
			try
			{
				migration.Database.BeginTransaction();
				migration.Up();
				AssertMigrateUp(migration);

				migration.Down();
				AssertMigrateDown(migration);
			}
			catch (Exception ex)
			{
				thrownException = ex;
			}
			finally
			{
				migration.Database.Rollback();
				if (thrownException != null)
				{
					throw new Exception(String.Format("Failed to migrate up and down for migration {0}", typeof(T).ToString()), thrownException);
				}
			}
		}
	}

	public class _0001_CreateNoteTableTest : MigrationTester<_0001_CreateNoteTable>
	{
		public override void AssertMigrateUp(_0001_CreateNoteTable migration)
		{
			Assert.IsTrue(migration.Database.TableExists("Note"));
			Assert.IsTrue(migration.Database.ColumnExists("Note", "NoteId"));
			Assert.IsTrue(migration.Database.ColumnExists("Note", "NoteTitle"));
			Assert.IsTrue(migration.Database.ColumnExists("Note", "NoteData"));
			Assert.IsTrue(migration.Database.ColumnExists("Note", "NoteId"));
			
			// ...etc... //
		}
	}

So the first part was done – I had a system for migrating my database and a means for testing it.

Be Sociable, Share!