FluentMigrator rolling back to a Not Nullable column?

Question

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.

Gq

Answer 1

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.

Slime

I'm guessing that would not work if bar was a foreign key column? Can a Down method be left blank? – Gq

If it is a foreign key column then the sql query will have to be a bit smarter and find the correct value to set. Or do you have a foreign key value that would work as a default? – Slime

Unfortunately there isn't a default foreign key value. '0' Would point to a record in the parent table that doesn't exist. – Gq

Is it possible to write an sql query that will find the correct parent record? If it is then you can run it with Execute.Sql. If it isn't then you'll have to do it manually I guess. – Slime

Answer 2

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();
}

Pluck

Answer 3

Old topic but you can do this to give existing rows a (new) value:

            migration.Alter.Table("foo")
                .AlterColumn("bar")
                .AsDateTime()
                .NotNullable()
                .SetExistingRowsTo(DateTime.UtcNow)
            ;

Buoyant

but does SetExistingRowsTo add where bar is null condition? Or it uses all rows... – Bourgeoisie

SetExistingRowsTo will set all existing rows, as it's intent was to make adding non null columns with no default simple. We'd need an additional helper like SetNullRowsTo or something of that ilk, but to be honest, in a case of updating an existing column from null to non null, I think using the method suggested by Lucas is the best approach and wont result in clogging up the api with spurious helper methods. – Scripture