Database migration for .NET

Migration for .NET first official release is out and supporting MS SQL Server 2000, MySql 4 and Postgre 8 and fully integrated with Castle Generator.

A migration looks like the following:

[Migration(1)]
public class AddPostTable : Migration
{
	public override void Up()
	{
		Database.AddTable("Post",
			new Column("Id", typeof(int), ColumnProperties.PrimaryKeyWithIdentity),
			new Column("Title", typeof(string), 50),
			new Column("Content", typeof(string), 500),
			new Column("PostedAt", typeof(DateTime))
		);
	}
	
	public override void Down()
	{
		Database.RemoveTable("Post");
	}
}

The Up method is called to create, update, insert, etc. and the Down method is called to revert to an older version.

You can run migration, from a project generated with the monorail command line, with the command:

nant migrate

Or, to migrate to a specific version:

nant migrate -D:version=3

One of the coolest feature, that is not in Rails, is automatic transaction support. All migrations are executed inside a transaction and if an exception is thrown, all changes are rolled back. Note that because MySql only support table-level transaction, modifications to the schema won’t be rolled back.

To use, simply update or install the amazing Castle Generator.

By using this tool you understand that this is a beta release and might include some major issues. If you find some, please report them to in Trac.

Thanks to Kevin and Peter for their hard work, patience and support.

Happy migrating!

About these ads

23 Comments

Filed under Castle, Generator, Monorail

23 responses to “Database migration for .NET

  1. Pingback: Zen and the art of Castle maintenance » Archives » Database Migration tool

  2. Wonderful work, mark!
    With your work with migrate and generator, monorail rocks!

  3. This looks amazing, period.
    Does this works on MS SQL as well? That one _has_ DDL transaction support.

  4. Thanks Ayende!

    Yes the migrator is working on MySql 4 and upper, MS Sql 2000 (not tested on 2005 but should work) and Postgre 8

    MS Sql and Postgre both have DDL transaction support, only MySql hasn’t.

  5. Pingback: El blog de Carlos Ble » Why to choose CastleProject, Mono and related technologies

  6. Pingback: Andrew Stopford's Weblog : RoR style database migration for .NET

  7. Darius Damalakas

    We need support for Firebird RDBMS.
    Where should we start implementing it?

    I could not find migration.dll sources in castle repository, so i searched for it in NProject. However, NProjects host QIT.Management.Migrator.dll, which is even more puzzling.

    Where does Migration.ddl come from? ?:)

  8. Great work Marc-Andre, this is exactly what I’ve been looking for.

    Do you know of anyone who has made a tool for retro-generating these from an existing database? We have some dev databases which we need to move to production and I’d like to do it all using scripted migrations if possible. Any suggestions? I could code them all by hand, but it would be a huge amount of work…

  9. Hey Matt, glad you like my project.

    The SchemaDumper could help you on this: http://code.macournoyer.com/migrator/index.fcgi/browser/trunk/app/core/Tools/SchemaDumper.cs

    But from what I can remember, it only supports MySQL for now. You can run it from the Migrator.Console tool with the “dump” options, check the usage by running the console parameterless.

    I hope this can help and thanks for the comment!

  10. I haven’t read the Mozilla License yet so apologies in advance.

    Is there a reason you chose to use Integers for your Migration Numbers on the Migration attribute that is specified on the migration class?

    In our situation we have 3 environs (dev, testing, and prod) which may be at various migrations. Say, prod is @ 10 and dev has moved to 15. However we discover that migration 8 has an issue which needs to be fixed with another migration. I cannot add that as 11 and roll that to prod, because prod is not ready to move from 11 thru 15. Its sinful to modify 8 and rerun it.

    One way is to skip migration numbers and use 10, 20 etc instead of 10, 11, 12. However if the attribute supported real numbers I can add 8.1 and roll that to dev and prod as needed.

    Question: Is it OK to modify the source and make my own dll that handles this situation or is this (real numbers) something you considered and discarded for a reason?

    Also, is there a way to name the SchemaInfo table to something more meaningful to our product? Should I make that change too? Could that be passed into as a parameter to the migrator?

    Your thoughts will be helpful

    thanks

  11. Hi Rams,

    Using real number is not possible. If you have a version 3.5 schema and migrate it to 4 it will be different from another schema which was already at version 4. The version number is unique to the schema version, you don’t want to break this.

    If you want to alter something, add a migration, never ever alter an old one.

    I hope this is helful. As for the other points, I don’t understand clearly what you’re trying to acheive. But you could give a look at the Trac site: http://code.macournoyer.com/migrator, some ppl have posted useful patches (which are not applied yet…)

  12. Hi, this is exactly what I was looking for. I looked into Rails recently and like the concepts very much. However, I just can’t seem to get used to the syntax of Ruby.
    One of the better aspects of RoR is rake and the migrations. It is really easy to get up and running fast and maintain the DB versioned.
    On the other hand, I can’t use MonoRail on the current project I’m doing, but I would like to keep the DB versioned. Is there a way to do this and run “nant migrate” in a project that was not “generated with the monorail command”? I have checked out and installed the generator tool.

  13. You can use either the builtin console or the NAnt task: http://macournoyer.wordpress.com/2006/10/15/migrate-nant-task/

    Glad you like my project Dennis!

  14. I like it very much, but I’m sure I would like it even more if only I could get it to work without needing anything else from the Castle project. I would like it completely stand alone (except nant). The problem probably is that I don’t know jack about Castle (and frankly I don’t need/want to for now either). I’ll look into the sources and try to find with what minimum setup I can get away with. Thanks Again!

  15. Migrator doesn’t have any dependencies except for MySql and Postgre libs. Try the NAnt task.

  16. I was not paying attention. I read the two post too quickly. I did not get the fact you said to check out Migrator from source and build it. Mea culpa!
    Stupid I know I’m almost too ashamed to post this…
    So after rereading the post carefully and doing as told, it is happily migrating databases to points where no one has gone before. Truly amazing stuff.
    Thanks again Marc!!!

  17. Haha, no problems Dennis! I’m really happy this helps you in your day to day work!

  18. Pingback: Database Versioning for .Net « Dyonisius’s Weblog

  19. fornetti

    I do not believe this

  20. Tommy

    Is this project still around? I do not see it anywhere…

  21. Alexwebmaster

    Hello webmaster
    I would like to share with you a link to your site
    write me here preonrelt@mail.ru

  22. Nice post. I learn something more challenging on different blogs everyday. Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s