Database migrations with Fluent Migrator

Fluent Migrator makes the process of writing database migrations much easier and less error prone by removing the need to write sql scripts. Instead, all schema changes are written in C# files, utilising methods in the Fluent Migrator library. This post describes the required steps to setup Fluent Migrator, in the context of creating a user table.

• Install the Fluent Migrator package into the migrations project - more information on installation can be found on the project site
• In an appropriate location, add a new C# file to become the first migration

• The file should implement Migration, from the Fluent Migrator package
• Decorate the class with the Migration attribute, and as this is the first migration in the project it takes the argument 1
• Every migration class requires an Up and a Down method, to commit the schema changes and rollback if an error occurs in the process

• In the Up method, a User table is scripted for, with an Id field (a primary key) and a Name field
• In the Down method the rollback script is written to delete the user table

• A Salary table is added to the schema that has the field UserId, which references the Id on the User table above
• A foreign key is created for the Salary table using Create.ForeignKey
• This foreign key is deleted just as easily, as shown in the Down method

To run the migration, add a post-build event into the project where the migration resides, then rebuild the project. See under Command Line Runner on this link for an example. Any future migrations should be created in a similar fashion, however care should be taken to increment the number passed in with the migration attribute.

One point to note is that Fluent Migrator can not be used to create a database - only the schema. Hence, for automated database creation it is necessary to write a separate sql script to do this, then ensure it runs in the post build events. Despite this however, generally Fluent Migrator makes the process of migrations (especially in an Agile environment) much simpler and easier.