Getting Started with Migrator.Net and database refactorings

Database Refactoring is always a pain to be dealt with in any medium-large sized software. There are new tables, columns, primary keys, foreign keys added and removed constantly from a database under development. Some people have a strict plan of creating sql scripts for every change they make in the database. Others have a text file holding a listing of changes to be done on QA and Production machines along with migrating existing data into new structures. Anyway you slice it, It’s a headache all around and if you ignore it, it will eventually bite you in ass.

This is one area that they Ruby crowd has gotten in right (not saying that they haven’t got other things right), but with Migrations, it is amazingly easy to create objects in your database and then easily version your database. Originally an open source utility was written by Marc-Andre Cournoyer called .Net Migrations (or something to that extent), it lived in the castle trunk for awhile but once Marc-Andre went to the ruby crowd Nick Hems picked up the project, dusted it off and placed it on Google Code located here.

Ok, now that we have gotten the history lesson out of the way let’s get started. Earlier this evening the trunk I updated with a major set of refactorings done mostly by Geoff Lane, which we have been toying with for about a month or so. Now that Geoff and Nick fixed a number of bugs, we updated the trunk so everyone can have fun!

One of the best parts of this database refactoring tool is the database support. At the current moment, Migrator.Net supports MsSql Server, MySql, Oracle, PostgreSql and SQLite. You need only specify the connection string for the database and the provider to use in an NAnt task that was developed for Migrator.Net like so:

<migrate provider="MySql" connectionstring="Database=dbname;Data Source=localhost;User Id=username;Password=password;" migrations="Project.Migrations.dll" to="2" />

Specifying a “to” attribute of 2, will migrate your database to the next available version. When you first run migration version 1, a “schemainfo” table is created in the database that keeps track of the current migration version. Then by specifying a 2, the next version is applied to the database.

Where do I create my migrations you ask? Good question!

I create a seperate assembly, in the nant task above it is called Project.Migrations.dll. The migration looks something like so:

 

   1: // Version 1
   2: [Migration(1)]
   3: public class CreateUserTable : Migration
   4: {
   5:     public void Up()
   6:     {
   7:         Database.CreateTable("User",
   8:             new Column("UserId", DbType.Int32, ColumnProperties.PrimaryKeyWithIdentity),
   9:             new Column("Username", DbType.String, 25)
  10:             );
  11:     }
  12:     public void Down()
  13:     {
  14:         Database.RemoveTable("User");
  15:     }
  16: }

 

Ok, so it doesn’t get much easier than this. It has a feel exactly like Migrations for Ruby, so anyone familiar with that should have no problem with this. The first thing to take note of is the Migration attribute with a 1. This denotes what version this migration is. Each time you create a new migration, it has to be the next available number. This is required. Next is the class name which can be whatever you like for readability purposes. Next is the Up() and Down() methods. When migrating TO this version, the code in Up() will be called. When rolling back a change to a previous version, the Down() method will be called. Easy enough.

If you take a look at the Database class, you’ll see that there are many other methods there for creating ForeignKeys, PrimaryKeys as well as other column information to be applied in the database. Column options can also be passed in the Column() ctor overloads.

The project can be found here: http://code.google.com/p/migratordotnet/

The google group where I make silly postings is here: http://groups.google.com/group/migratordotnet-devel

The next thing I think we’ll probably look at is refactoring towards a fluent interface as this is clearly a fit for that type of implementation. Go download it and take a look! Please post any bugs to the tracking list on google code.

Next time I’ll go over rolling back changes and other more complex changes to perform on your database. Enjoy!

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Sean Chambers

I am a Senior software developer from Palm Coast, Florida. An advocate of Domain Driven Design, Behavior Driven Development, creator of FluentMigrator and community activist. I am married to my beautiful wife Erin and am the proud father of two wonderful children. I currently reside at ACI, a local insurance industry/mortgage software company that excels in creating solutions using Agile methodologies.
This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://www.lostechies.com/blogs/joe_ocampo/ Joe Ocampo

    Would you say that this tool lends itself more towards Green Field development then Brown Field?

    Or

    Have you built in the ability that as you change the schema if the table is populated with data that a data migration strategy in initiated?

  • James

    Please I need a simple example on how to use Migrator.Framework to execute or create
    stored procedure, alter stored procedure, and functions. There are many examples for
    creating tables but I can’t any on these. Sql server 2005 is the database I’m
    using. Thanks