Migrations with Database.NET

Find working examples on GetHub

Database.NET currently only supports a relational database style structure. Relational databases are not great at dynamically adding and removing columns from tables, renaming tables or columns, changing column types, etc.

Because we often will need to change our POCO classes we need to have a method of changing the schema of the underlying tables. This process is simple in most cases but can get very complex if large changes are needed.

In the walk through we will be adding a property to the Order class used in the NavigationProperties walk through. If you are not already familiar with that I recommend you read through it.

There are two options when making changes to the schema of the database.

  1. Make the changes in your POCO classes and DbContext class then deleting the existing database file. This is fine when you don't need to keep the existing data, but in most cases that data has value.
  2. Make the changes in your POCO classes and DbContext class then use a migration class to adjust the existing data to match the new schema.

A migration class allows low level access to the database tables in Database.Net. You can change names, update values, move data from one table to another, and a host of other things you may find usefull.

A simple migration class can be seen in the following example.

    
public class AddOrderNumberMigration : IMigration
{
    public int MigrationOrder => 1;

    public void Run(SchemaAlter schemaAlter, MigrationTransaction transaction)
    {
        schemaAlter.AlterTable<Order>("Orders", transaction);
    }
}
        
    

The migration class is fairly straight forward. First it must implement the IMigration interface. Second the interface enforces the existence of the MigrationOrder property and the Run method.
The MigrationOrder property simply tells Database.Net in which order Migrations are to be run. As your code changes it is likely that you will need many Migration classes and having them run in a specific order with prevent issues from arising in different versions of your database files.
The MigrationOrder is also used internally as the database version. By default the database version is 0. If we were to run the this migration it would then be set to 1. If another migration is created it will then be whatever that number is. This process ensures that the same migration is never run more than once against the same database file.
The Run method is where all the magic happens. It is passed two variables for you to use. The SchemaAlter variable is a class that contains all the functions needed to modify your tables. The second is a special transaction class that needs to be passed into each method you use in the SchemaAlter class.

In the previous example we are calling AlterTable to change the structure of the Orders table. We pass in the type Order as a generic parameter and then the string "Order" to identify the table being changed.
Basically this function tells Database.Net to look at the structure of the table Named Orders and make it match the POCO class Order, in this case it will add the column OrderNumber as an integer.
The previous examle will leave the OrderNumber values as null. If you want to have a default value at the time this column is created use the following code.

    
public class AddOrderNumberMigration : IMigration
{
    public int MigrationOrder => 1;

    public void Run(SchemaAlter schemaAlter, MigrationTransaction transaction)
    {
        var i = 1000;
        schemaAlter.AlterTable<Order>("Orders", (row, order) => {
            order.OrderNumber = i;
            i++;
        }, transaction);
    }
}
        
    

This AlterTable method has an overload that contains an action delegate that will be called for every row in the table. The variables passed to the delegate are of type Row and Order.
The Row variable is a simple key value pair that has all the values in the row. The Order parameter is the corresponding Order class that is generated from that data. In the example we didn't need any data from the Row variable but we were able to set a default value for the OrderNumber using the Order variable.

An example of using the Row variable can be seen below.

    
public class AddOrderNumberMigration : IMigration
{
    public int MigrationOrder => 1;

    public void Run(SchemaAlter schemaAlter, MigrationTransaction transaction)
    {
        schemaAlter.AlterTable<Order>("Orders", (row, order) => {
            order.OrderNumber = ((int)row["OrderId"]) + 1000;
        }, transaction);
    }
}
        
    

The above code gets the row's OrderId value and adds 1000 to it then sets it as the new OrderNumber value.

When altering a table's schema a new table is created and all the data from the old one is moved over. Once all the data is coppied to the new table the old table is dropped. Large tables may take a long time to migrate because of this.
There are many other migration that can be done with the AlterTable method. I encorage you to look at them in your own codes migrations.

To apply the migration so that it will run with your database file is opened you need to add it to your DbConfig as seen in the example below.

    
public class ShopContext : DbContext
{
    public ShopContext(string filePath)
        : base(filePath, TextEncodingType.ASCII, new IMigration[] {
            new AddOrderNumberMigration() //Adds the order number if it is not there 
        })
    {

    }

    public DbSet Orders { get; set; }
    public DbSet Items { get; set; }
    public DbSet OrderItems { get; set; }
}
        
    

Multiple migrations can be added to the IMigration array and each will be called in the order of the MigrationOrder property.

Finally, you don't need any special code to prevent a migration from running more than once. Once it has been run against a database file it will not run again.