September 28, 2011 at 06:36

FluentMigrator - Versioned Migration System


Hello. What migrations are and why they are needed is well described in the article Versioned migration of the database structure: basic approaches .
I want to tell you about the versioned migration system: FluentMigrator. Why do I like this particular project? Due to the nice syntax of migrations and the support of various DBMSs. Interested in? Welcome to cat.

The content of the article


About the project
Briefly about the possibilities
Components of the system
How it works
Subtleties
Conclusion

about the project


The project itself is based on GitHub: github.com/schambers/fluentmigrator
My fork: github.com/tabushi/fluentmigrator
Repository change history begins on 12/17/2008
Distributed under license: Apache License 2.0
Written in C # under .Net Framework 3.5

Features briefly


Supported DBMS
  • Jet
  • MySQL
  • Oracle
  • PostgreSQL
  • Sqlite
  • Microsoft SQL Server
Supported Operations
  • Create, delete tables
  • Add, delete, modify columns
  • Creation, removal of primary, foreign keys, indexes
  • Insert, update, delete data (rather modest in features)
  • Checking the existence of schemas, tables, columns, indexes in the database
  • Execution of an arbitrary sql, sql script from a resource or from an external file (when all the previous is not enough)
System components
  • FluentMigrator - the core of the program itself, it implements the entire migration syntax
  • FluentMigrator.Console - console application for starting migrations, has a large number of parameters
  • FluentMigrator.MSBuild - start migrations for MSBuild
  • FluentMigrator.NAnt - start migrations for NAnt
  • FluentMigrator.Runner - the core script installation program, is the basis for the previous three projects, which are essentially small shells in a 1 .cs file. It contains all the logic for performing migrations and converting migrations to sql for the required DBMS. Using it is very easy to write your runner with blackjack with beautiful windows and the required functionality.
  • FluentMigrator.SchemaDump - I have not personally used it, but I looked at it specifically for this article. It is designed to save the database structure in sql file. Implemented only for MS SQL Server.
  • FluentMigrator.Tests - tests, where without them. Uses NUnit

How it works


Now let's take a closer look at the migration file. So, the migration file may contain the following:
  • migration
  • profiles
  • version table description
  • sql scripts as embedded resources
  • maybe something else that I have not yet encountered
More on these entities

We will consider migration by the following example:
using System;
using FluentMigrator;

namespace ExampleDatabaseMigrations
{
  [Migration(2011091900)]
  public class ExampleMigration : Migration
  {
    public override void Up()
    {
      if (!Schema.Table("EXAMPLE_TABLE").Exists())
      {
        Create.Table("EXAMPLE_TABLE")
          .WithColumn("ID").AsInt16().NotNullable().PrimaryKey("PK_EXAMTABL_ID")
          .WithColumn("NAME").AsAnsiString(100).NotNullable()
          .WithColumn("SHORT_NAME").AsAnsiString(50).Nullable()
          .WithColumn("START_DATE").AsDate().NotNullable()
          .WithColumn("END_DATE").AsDate().Nullable();
        Insert.IntoTable("IDX_EXAMTABL_NAME")
          .Row(new {Id = 1, Name = "TEST", Start_Date = new DateTime(2011, 9, 19)});
      }
      if (!Schema.Table("EXAMPLE_TABLE").Index("IDX_EXAMTABL_NAME").Exists())
      {
        Create.Index("IDX_EXAMTABL_NAME")
          .OnTable("EXAMPLE_TABLE")
          .OnColumn("NAME").Ascending();
      }
      if (!Schema.Table("EXAMPLE_TABLE").Index("IDX_EXAMTABL_STARDATE_ENDDATE").Exists())
      {
        Create.Index("IDX_EXAMTABL_STARDATE_ENDDATE")
          .OnTable("EXAMPLE_TABLE")
          .OnColumn("START_DATE").Ascending()
          .OnColumn("END_DATE").Ascending();
      }
    }

    public override void Down()
    {
      if (Schema.Table("EXAMPLE_TABLE").Exists())
        Delete.Table("EXAMPLE_TABLE");
    }
  }
}

* This source code was highlighted with Source Code Highlighter.

In the example, I did not indicate anywhere the scheme for creating the table \ indexes, but of course there is such a possibility.
The numbering of migrations should go in ascending order, which is logical. But simply in order to number, it seemed to us not convenient, so the numbering was adopted for the form yyyyMMddxx, where: yyyy - year, MM - month, dd - day, xx - number in order from 00 to 99. Thus, the order is kept and the numbering says more about the time the migration was created.
This example gives an approximate idea of ​​how migration looks, and we will stop here.

Profile template

using FluentMigrator;

namespace ExampleDatabaseMigrations
{
  [Profile("Example")]
  public class ExampleProfile : Migration
  {
    public override void Up()
    {
      //do something
    }

    public override void Down()
    {
      //empty, not used
    }
  }
}

* This source code was highlighted with Source Code Highlighter.

As you can see, the profile also inherits from the Migration class, but is marked with the Profile attribute. Only the Up method is executed in the profile (although in the example in the documentation on github, the Down method is also filled, maybe it was executed sometime). A migration file can contain an unlimited number of profiles with different names.

What is the difference between a profile and migration except for attributes? The fact that:
  1. the profile is executed only if it is explicitly set, specifying its name during the migration;
  2. the profile is always executed after successful migrations; if there was nothing to install from the migrations, the profile is still executed.
Thus, the profile can be used for any service functions. Collect statistics or something. We use it to start the procedures of recompiling invalid database objects, which became such due to changes in the structure.

Most recently, a request was made at the github to add a profile and migration hybrid - profiles that have a migration number. The fact is that a person used the profile to upload test data to the developers database when necessary, and since the structure of the database changed when migrations were added, he had to change the profile each time. This is an example of a failed profile application. He was advised to use migrations for this, inside of which, through if, to check some condition, for example, an environment variable, because command line options in migration are naturally not available.

Version table

using FluentMigrator.VersionTableInfo;

namespace ExampleDatabaseMigrations
{
  [VersionTableMetaData]
  public class ExampleVersionInfo : IVersionTableMetaData
  {
    public string SchemaName { get { return "EXAMPLE_SCHEMA"; } }
    public string TableName { get { return "EXAMPLE_VERSION_TABLE"; } }
    public string ColumnName { get { return "EXAMPLE_VERSION_COLUMN"; } }
  }
}

* This source code was highlighted with Source Code Highlighter.

Thus, even in the same scheme, you can have several different tables with versions for different projects, or keep the table with versions separate from the scheme with the project.
about what will happen if you set an empty string instead of the circuit name is written above.

Embedded Scripts

Embedded scripts are regular sql scripts, the files of which are connected to the project and have the property “Build Action” = “Embedded Resource”. I don’t need to talk more about them.

How it works


Next, suppose we are doing upward migrations, i.e. install the latest version of the database structure.

Work algorithm
  1. The program (Runner) is passed parameters that contain, in short, data about the database, the migration file and the task itself (in fact, there are many parameters, and what I indicated above is set by more than three parameters).
  2. Runner connects to the database and selects the maximum number in the version table, looks at the migration file and performs the migration.
  3. In the event of an error, runner rolls back the migration on which it stumbled (with some exceptions, which will be discussed later in the subtleties section ).
One migration

Migration is divided into 2 stages
  1. Execution of the Up () method (or Down (), in the case of setting migrations down), which adds expressions to the list of expressions
  2. Convert expressions to sql and submit for execution in the database

Subtleties


Changes are not always rolled back, since not all DBMSs support rollback of DDL operations. For such DBMSs, it is desirable to make migrations as small as possible, from one operation. If this one operation is not installed, then it will not need to be rolled back.

It seems that initially the project was conceived for writing migrations once for all supported DBMSs (and for basic simple operations this works). Nevertheless, it is worth admitting that the DBMS capabilities and syntax are very different (for example, some DBMS use auto-increment fields, while others use sequencing), therefore, recently, a function with the speaking name IfDatabase has become available inside migrations.

Conclusion


The project is open source, so you should not expect miracles from it. Check what happened on the required DBMS, as it may happen that something is not implemented for this DBMS. Describe bugs - fix, or help yourself fix them.

PS: I want to ask you to speak out about what else you would be interested to know. I will write about it.
Tags:

Also popular now: