Getting Started with Migrator.Net and database refactorings
Database Refactoring is always a pain to be dealt with in any medium-large sized software. There are new tables, columns, primary keys, foreign keys added and removed constantly from a database under development. Some people have a strict plan of creating sql scripts for every change they make in the database. Others have a text file holding a listing of changes to be done on QA and Production machines along with migrating existing data into new structures. Anyway you slice it, It’s a headache all around and if you ignore it, it will eventually bite you in ass. This is one area that they Ruby crowd has gotten in right (not saying that they haven’t got other things right), but with Migrations, it is amazingly easy to create objects in your database and then easily version your database. Originally an open source utility was written by
Marc-Andre Cournoyer called .Net Migrations (or something to that extent), it lived in the castle trunk for awhile but once Marc-Andre went to the ruby crowd Nick Hems picked up the project, dusted it off and placed it on Google Code located here.
Ok, now that we have gotten the history lesson out of the way let’s get started. Earlier this evening the trunk I updated with a major set of refactorings done mostly by Geoff Lane, which we have been toying with for about a month or so. Now that Geoff and Nick fixed a number of bugs, we updated the trunk so everyone can have fun! One of the best parts of this database refactoring tool is the database support. At the current moment, Migrator.Net supports MsSql Server, MySql, Oracle, PostgreSql and SQLite. You need only specify the connection string for the database and the provider to use in an NAnt task that was developed for Migrator.Net like so:<migrate provider="MySql" connectionstring="Database=dbname;Data Source=localhost;User Id=username;Password=password;" migrations="Project.Migrations.dll" to="2" />
</p>
1: // Version 1
2: [Migration(1)]
3: public class CreateUserTable : Migration
4: {
5: public void Up()
6: {
7: Database.CreateTable("User",
8: new Column("UserId", DbType.Int32, ColumnProperties.PrimaryKeyWithIdentity),
9: new Column("Username", DbType.String, 25)
10: );
11: }
12: public void Down()
13: {
14: Database.RemoveTable("User");
15: }
16: }
</font>
Ok, so it doesn’t get much easier than this. It has a feel exactly like Migrations for Ruby, so anyone familiar with that should have no problem with this. The first thing to take note of is the Migration attribute with a 1. This denotes what version this migration is. Each time you create a new migration, it has to be the next available number. This is required. Next is the class name which can be whatever you like for readability purposes. Next is the Up() and Down() methods. When migrating TO this version, the code in Up() will be called. When rolling back a change to a previous version, the Down() method will be called. Easy enough.
If you take a look at the Database class, you’ll see that there are many other methods there for creating ForeignKeys, PrimaryKeys as well as other column information to be applied in the database. Column options can also be passed in the Column() ctor overloads.
The project can be found here: http://code.google.com/p/migratordotnet/
The google group where I make silly postings is here: http://groups.google.com/group/migratordotnet-devel
The next thing I think we’ll probably look at is refactoring towards a fluent interface as this is clearly a fit for that type of implementation. Go download it and take a look! Please post any bugs to the tracking list on google code.
Next time I’ll go over rolling back changes and other more complex changes to perform on your database. Enjoy!