Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

FluentMigrator rolling back to a Not Nullable column?

Given the following Migration:

[Migration(1)]
public class Mig001 : Migration
{
    public override void Up()
    {
        Alter.Table("foo").AlterColumn("bar").AsInt32().Nullable();
    }

    public override void Down()
    {
        Alter.Table("foo").AlterColumn("bar").AsInt32().NotNullable();
    }
}

The migrator alters a column and makes it nullable and on the rollback it does the reverse and makes it non nullable again.

Lets say data has been added to foo since the migration; there are now rows with null in bar column.

If it is rolled back then the operation will fail, is there any way in fluentmigrator to handle this scenario? Or what is the best practice.

like image 385
user1838662 Avatar asked Jun 10 '13 18:06

user1838662


2 Answers

The short answer is to set a default value for all columns that have a nullable value. You can do this just with sql using the Execute.Sql expression. This should be before the Alter.Table expression.

public override void Down()
{
    Execute.Sql("update foo set bar = 0 where bar is null");
    Alter.Table("foo").AlterColumn("bar").AsInt32().NotNullable();
}

The long answer is that it is a lot of work to always ensure that you can roll back a migration and are you sure you need to do that?

For example, if the up action is to create a table and the down action is to drop it, should you save the data in a temp table so that it doesn't disappear? For most use cases the down action is used when deploying in test environments or when rolling back a failed deploy and it is quite rare that you would roll back a migration after it has been deployed.

like image 154
Daniel Lee Avatar answered Sep 17 '22 12:09

Daniel Lee


Here is an alternate way of performing the migration that does not require direct SQL execution.

public override void Down()
{
    Update.Table("foo").Set(new { bar = 0 }).Where(new { bar = (int?) null });
    Alter.Table("foo").AlterColumn("bar").AsInt32().NotNullable();
}
like image 33
Lucas Avatar answered Sep 18 '22 12:09

Lucas