.NET database migration toolkit

Question

My current pet project is a language-independent database migration library (Wizardby on Google Code). It's pretty much inspired by ActiveRecord Migrations, but has a few niceties. For instance, does some basic "type inference" so you don't have to specify the type of a FK column. It's also smart enough to generate "downgrade" scripts given only "upgrade" sequence. Although migrations are written in a special DSL, this tool is primarily aimed at .NET projects. It's also database-platform-independent.

Here's a quick glimpse of the syntax:

  migration "Blog" revision => 1:
    type-aliases:
      type-alias N type => String, length => 200, nullable => false, default => ""

    defaults:
      default-primary-key ID type => Int32, nullable => false, identity => true

    version 1:
      add table Author:
        FirstName type => N
        LastName type => N
        EmailAddress type => N, unique => true
        Login type => N, unique => true
        Password type => Binary, length => 64, nullable => true

      add table Tag:
        Name type => N

      add table Blog:
        Name type => N
        Description type => String, nullable => false

      add table BlogPost:
        Title type => N
        Slug type => N
        BlogID references => Blog
        AuthorID references => Author

      add table BlogPostTagJunction primary-key => false:
        BlogPostID references => BlogPost
        TagID references => Tag

    version 2:
      add table BlogPostComment:
        BlogPostID references => BlogPost
        AuthorEmailAddress type => N
        Content type => String, nullable => false

    version 3:
      add table Media:
        TypeID type => Int32
        Name type => N
        MimeType type => N
        Length type => Int32
        BlogPostID nullable => true, references => BlogPost
        BlogPostCommentID nullable => true, references => BlogPostComment

      add table User:
        Login type => String, length => 200, nullable => false
        Password type => Binary, length => 64, nullable => false

        index IX_Login columns => [ID, [Login, desc]], unique => true

    version 4:
        add table Forum:
          Name type => String, length => 200, nullable => false
        add column ModeratorUserID nullable => false, references => User

    version 5:
        remove index IX_Login table => User

    version 6:
        add index IX_Login table => User, columns => [ID, [Login, desc]], unique => true

    version 7:
        BlogAuthorJunction primary-key => false:
            BlogID references => Blog
            AuthorID references => Author

        execute native-sql upgrade-resource => InsertSeedData, downgrade-resource => DeleteSeedData

I'm aware of other migration libraries out there, but hey, it's a pet project!

The question is: what features do you expect from database migration toolkits in general and what can you say about this particular puppy syntax-wise?

Answer 1

I like this syntax. In your sample you focused on changing structure. But what about data manipulation?

It's very often when in migration I have to modify data (for example add some dictionary data).

Answer 2

I'd like to see the ability to verify that each revision has been applied to a database. So say for example version 3 added the table 'Media'. Since then versions 4 & 5 have been added to the database but somewhere along the line 'Johnny Q Expert' deleted the table 'Media'. Now comes version 6 which needs to alter the 'Media' table (which no longer exists) - a verify function could be useful which ensures a culmination of all changes made in versions 1 thru 5 are present in the database so the next version can be applied correctly.