|
GettingStarted
Getting Started with MigratorDotNet
IntroductionMigratorDotNet is a database versioning system much like Ruby on Rail's Migrations. Its purpose is to automate your database changes, and help keep those changes in sync throughout your environments. Supported Databases
Supported Execution Modes
OverviewThe basic concept here is that you create an assembly that contains your "Migrations". The Migrations are classes that derive from the Migration class. Upon deriving from Migration you will need to write the code necessary to "Up" that migration or "Down" that migration. When you migrate to that version of the database, the Up() method will be called and executing whatever database modifications need to be ran in order to bring your database to that version. When you migrate in reverse to a previous version, the Down() method will be called executing database modifications in order to undo that set of database changes. Each Migration should be a very small incremental step in the database. Common sizes of Migrations are creating a table and adding one or more columns to a table, altering the data of a table, running a custom query with ExecuteQuery on tables. Keep your migrations as small as possible as this will facilitate easier steps between versions, much like you would in a traditional version control system such as VSS or SVN. A sample migration would be as follows: using Migrator.Framework;
using System.Data;
namespace DBMigration
{
[Migration(20080401110402)]
public class CreateUserTable_001 : 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");
}
}
}Where the Migration Attribute indicates the order (or in version 0.8 and greater) the timestamp of this migration. Installation and Configuration With Visual StudioHere are the steps to add migrations to an existing solution in Visual Studio:
e.g. File Name: CreateUser_001.csClass Name: CreateUser_001 Title: Migrate Up Command: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\MSBuild.exe Arguments: build.proj /t:migrate Initial Directory: $(ProjectDir) Check the "Use Output window" box. Title: Migrate To Version Command: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\MSBuild.exe Arguments: build.proj /t:migrate Initial Directory: $(ProjectDir) Check the "Use Output window" box Also check the "Prompt for arguments" box. You'll use that to specify which version you want to go to. (e.g. 0 for all the way back down).
|
► Sign in to add a comment
I don't understand how to prefix C# class names with numbers. You mention using the "" prefix, but I don't see this in your example and it doesn't seem to work for me. Is this possible?
Can't classes start with a character? That might be a workaround... Or perhaps just start them all with m, for MigratorDotNet?.
Can migrator.net be used to migrate data?
2.3.4 of Rails/ActiveRecord? recently added a "rake db:seed" to the template for a new rails project. Perhaps Seed base class could be created that does something similar in this project.
How is this useful/different from just SQL Scripting? How does this automate anything? What if you need to alter the Table? How does this save data before you alter? This looks like a tool for people who dont know sql and only C#?
@rex.whitten: The main difference I see is, that for SQL Scripting you need to write all the boiler code yourself. If you want to execute your scripts you either have to fire up your database admin tool and do it manually or write a tool that reads in all your scripts and executes them in the right order. Also, if you have several clients with different versions of your database schema, you have to somehow track what version they have and when you update you have to make sure, that you only apply updates they don't already have. With migrator.NET you only have to specify the database connection string and the version of the schema you want to apply, the tool does all the rest for you.
As for saving data before altering a table: It doesn't (automatically). But you can write statements like "EXPORT TABLE users TO 'c:\temp'" as raw sql and let migrator.NET execute this as well.
A couple of problems with the code sample above... "ColumnProperties?" should read "ColumnProperty?", and the Up() and Down() methods need to be declared with the "override" directive, e.g.:
namespace DBMigration { [Migration(20080401110402)] public class CreateUserTable_001 : Migration { override public void Up() { Database.AddTable("User", new Column("UserId", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity), new Column("Username", DbType.AnsiString, 25) ); } override public void Down() { Database.RemoveTable("User"); } } }Also, that's "Database.AddTable?", not "Database.CreateTable?"...
Is there any way to create a first migration to actually create the database? From what I've seen browsing the code (and running a few tests using ExecuteNonQuery?), it looks like the base assumption is that the database must exist.
Am I missing something?
This is very cool, but 2 questions: (1) Is there a way to include arbitrary SQL in the Up and Down? I am a little advanced in my TSQL knowledge and I sometimes like to do things that definitely would not be supported by a database-agnostic tool like this (2) I haven't opened it up and looked at the code, but is the Database class designed in such a way that we could provide a secondary implementation that can analyze differences between VCS versions? I'd love to be able to use git bisect to analyze where something changed or broke.
You can run arbitrary SQL scripts. I have successfully done this in the past by creating a separate directory for my SQL scripts, adding them to resource (.resx) file and then grabbing them from the resource file in my migrations. However, one thing to keep in mind here... Since it's using ADO.NET, you won't be able to execute batches like you can using SSMS or sqlcmd, as the GO keyword will have no meaning for ADO.NET. As a result, you'll just have to break-up your SQL in to multiple files and execute each one. I never found this to be that big of a deal.
I think you also hit on one of the weaknesses of the migration model. Since you aren't storing SQL scripts for your DB objects in source control, you can't really track SQL changes. IMO, that is one of the few weakness. If it was really that important to you, I'd recommend auto-generating SQL scripts for all of the objects in your DB each time you release a new version. You can then store those scripts in your VCS and compare the changes from version to version.