I was once under the belief that Microsoft was working on an official, ruby-like, Migration framework. However, I haven't been able to find any additional information (or even the original source of my belief).
Does anyone know any information about an official migration framework or possibly an open source one?
This .NET Database Migration Tool Roundup [1] provides a good rundown of the options.
The options listed are:
Another alternative is the Migrator.Net [1] project (at time of writing it is in version 0.7 [2]). You write migration classes with the following syntax [3]:
using Migrator.Framework;
using System.Data;
namespace DBMigration
{
[Migration(20080401110402)]
public class 001_CreateUserTable : Migration
{
public void Up()
{
Database.CreateTable("User",
new Column("UserId", DBType.Int32, ColumnProperties.PrimaryKeyWithIdentity),
new Column("Username", DBType.AnsiString, 25)
);
}
public void Down()
{
Database.RemoveTable("User");
}
}
}
…and the migration classes run in a sequential order in a MSBuild [4] or NAnt [5] script.
[1] http://code.google.com/p/migratordotnet/Here's a another modern, popular one: Fluent Migrations [1]
[1] https://github.com/schambers/fluentmigratorRoundhousE [1]. Basically some of the same idioms as Tarantino where you write regular SQL scripts (some companies like ours are into that). See the wiki on github "Professional Database Versioning and Change Management" [2]
The least amount of configuration for RoundhousE (RH) is to provide it a database name or connection string. It will execute against the current folder looking for migration scripts:
rh /d bob
You can provide RoundhousE a folder with the folders (all optional):
and it will execute everything in those folders. The items in the Up folder are only executed one time. That is where you put DDL/DML changes. The rest are considered any time scripts, which means when RH has detected the file has changed it will run it. Except the permissions folder, which is considered an every time set of scripts in case you do any auto wiring of permissions.
RH is extremely configurable, everything from the names of the folders above to the name of the tables where RH keeps migration information.
Versioning is done how you want, just provide RH a dll to pull the file version from, or an xml file and the xpath to the version information. We version based on source control, so you can always see the scripts as they were in source. This means we allow for multiple repositories to version the database as well.
It is a single file executable, but also comes with both an embeddable DLL and MSBuild DLL.
It has the idea of a refresh database, which means you can update your database without leaving Visual Studio!
RoundhousE + NHibernate == Rebuild Your Database without leaving Visual Studio!
[3]
And the disclaimer: I have had a substantial part in writing this tool because we couldn't find anything else that would meet auditing concerns, provide us with a low amount of maintenance, and be a little smarter about migrations.
[1] http://roundhouse.googlecode.comMy consulting company is using Migrator.NET [1] for a handful of .NET projects in production. One of my colleagues [2] committed some substantial enhancements back to the project to make it more production ready. There's definitely still room for improvement in it, but it was the best of the bunch when we made our choice a year ago.
[1] http://code.google.com/p/migratordotnet/You might find the MultiFunctionMachine [1] project useful.
The entry on how to get started with migrations is here [2]. This looks promising:
[1] http://www.assembla.com/wiki/show/machineusing System;
using System.Collections.Generic;
using Machine.Migrations;
public class CreateUserTable : SimpleMigration
{
public override void Up()
{
Schema.AddTable("users", new Column[] {
new Column("Id", typeof(Int32), 4, true, false),
new Column("Name", typeof (string), 64, false, false),
new Column("Email", typeof (string), 64, false, false),
new Column("Login", typeof (string), 64, false, false),
new Column("Password", typeof (string), 64, false, false),
});
}
public override void Down()
{
Schema.DropTable("users");
}
}
There is some amount of support for migrations in the open source project Subsonic [1], but that's not from Microsoft even if the project owner now works for them. Us, rather.
As one of the devs on IronRuby, I can say with certainty that the IronRuby project isn't working on anything like this -- why should we, when you can already get migrations directly through ActiveRecord? :)
[1] http://subsonicproject.com/In case you're still interested, I've just released Alpha 1 of octalforty Wizardby [1], a database migration framework.
[1] http://code.google.com/p/octalforty-wizardbyIv used Migrator.NET 0.8 and Subsonic 2.1, which are supposed to be two of the best and neither is robust enough to to be used for anything serious at the moment. Both require you to tweak the source code to even get relatively simple things migrating.
Database.RemoveColumn("Person", "Birthdate")
gives SQLite error near ")": syntax error
. It generated invalid SQL. - Dmytrii Nagirniak
It's not from MS, but The Castle Project [1] aims to bring a lot of Rails functionality to the CLR languages. A sub-project of this is a migration engine. I have not used it so I can not recommend it, but it exists.
[1] http://Castleproject.orgHere at Red Gate we've now got a migrations solution that uses SQL Compare and SQL Source Control. This just expresses the migrations in SQL, although you can leave most of the transitions for the SQL Compare engine to deal with itself.
SQL Source Control 3.0 Beta build available [1]
It's currently an early access build, so we're keen to get as much feedback as possible.
[1] http://www.red-gate.com/MessageBoard/viewtopic.php?t=14107Manatee is available on github [1].
You define your migrations using json
{
up: {
create_table: {
name: "categories",
timestamps: true,
columns: [
{ name: "title", type: "string" },
{ name: "description", type: "text" }
]
}
}
}
[1] https://github.com/robconery/manatee