This project is read-only.

Getting Started with Sharp Migrations

Introduction

Sharp migrations is a framework to help you deal with database changes and versioning over time. The idea comes from the excellent Ruby on Rail's migrations and it is the best way to alter your database in a structured and organised manner.

How it works?

The idea is simple: create a class for every change you want to make on your database. That class will handle which actions to take for applying the new modification and for removing it if necessary in the future. Every class represents a version for our database versioning system.

Cool, can you show me some code?

Sure! Let's suppose you want to create a new table called Artist. This is a schema migration and as I said, you have to write the code for adding the table and for removing it. So take a look at this code:

   public class _001_CreateTableArtist : SchemaMigration { 
      
      public override void Up() {        
         Add.Table("Artist")
            .WithColumns( 
                Column.AutoIncrement("ArtistId").AsPrimaryKey(), 
                Column.String("Name", 120).NotNull()
            ); 
      } 

      public override void Down() { 
         Remove.Table("Artist"); 
      } 
   }


Since this is a schema migration, we created a class that extends SchemaMigration. You have two methods to override: Up() and Down().

The method Up() is called every time you want to change your database one version up. The method Down() is called, well, you can figure out, right?

Can I make changes on data as well?

Yes you can! Suppose you want to add initial values to our Artist table. Since it's a data migration, you will extend the DataMigration class.

   public class _002_PopulateTableArtist : DataMigration {

      public override void Up() {
         Insert.Into("Artist")
	       .Columns("ArtistId", "Name")
	       .Values(1, "Zero 7");
      }

   public override void Down() {
      Delete.From("Artist")
            .Where(
	       Filter.Eq("ArtistId", 1)
            );
      }
   }

I can see that you put the version number as prefix of the class name, is it a rule?

Well, the rule is simpler than that: you can put the version number anywhere in the class name.

The framework will scan the class name until it gets the first number on it. That's it. No ugly attributes or fixed places.

Valid names are:

* 001Foobar
* Foobar001
* Foo001bar
* Foo001bar

Remember: it's the first number! So Migration001foo002 will be the version 1.

And how can I run this stuff?

For now you can use a console application or embed the Runner in your application (so every time it runs, it updates itself).

I do have plans to create a NAntTask and a MSBuildTarget when I have the time so stay tuned.

Last edited Oct 21, 2011 at 1:17 PM by andrecarlucci, version 2