Check Table Exist Before Create Table On FluentMigrator

Question

I'm looking for a way to write a extension method for fluent migrator check before create table, something like this

Schema.TableIfNotExist("table").InSchema("dbo").WithColumn("Id").AsInt32().NotNullable()

is it possible to write a extension method ?

Burlingame

Have you tried using an try catch block? Or wrap the create table as am extension method with the try catch block inside the method. – Ette

Answer 1

Yes you can

public static class Ex
{
    public static IFluentSyntax CreateTableIfNotExists(this MigrationBase self, string tableName, Func<ICreateTableWithColumnOrSchemaOrDescriptionSyntax, IFluentSyntax> constructTableFunction, string schemaName = "dbo")
    {
        if (!self.Schema.Schema(schemaName).Table(tableName).Exists())
        {
            return constructTableFunction(self.Create.Table(tableName));
        }
        else
        {
            return null;
        }       
    }
}

You are going to have two caveats (that I know of):

1. If you call the function in the same migration more than once, the first one will succeed, the others would fail, since the exists check requires the schema context to be updated with the new tables, which doesn't happen until the next migration (or until the current migration successfully executes).
2. If you use AutoReversingMigrations, the expression won't be automatically mapped to its coresponding down statments. Meaning that the implicit down won't have any effect.

---

To use toe above extension and your example, you would do

public override void Up()
{
    this.CreateTableIfNotExists("table", table => table.WithColumn("Id").AsInt32().NotNullable());
}

Crouch

This also works for columns. You can define the function as such public static IFluentSyntax CreateColumnIfNotExists(this MigrationBase self, string table, string column, Func<ICreateColumnOnTableSyntax, IFluentSyntax> columnCreator, string schema = "dbo") and then use it like this: this.CreateColumnIfNotExists("Foo", "Bar", column => column.OnTable("Foo").AsDecimal());. There may be a better way to do this that doesn't involve duplicating the table declaration, but I couldn't figure it out. – Assignable

@MorganThrapp You would do something like this perhaps: public static IFluentSyntax CreateColIfNotExists(this MigrationBase self, string tableName, string colName, Func<IAlterTableColumnAsTypeSyntax, IFluentSyntax> constructColFunction, string schemaName = "dbo") { if (!self.Schema.Schema(schemaName).Table(tableName).Column(colName).Exists()) { return constructColFunction(self.Alter.Table(tableName).AddColumn(colName)); } else return null; } – Crouch

Then call it like this: this.CreateColIfNotExists("Table", "Col", col => col.AsInt32().Nullable().Indexed()); – Crouch

Right, that's what I'm doing. I was just attempting to let future readers know that it was possible in case they (like me) found this question while looking for the ability to do this with columns. – Assignable

I only wrote this because you wrote "There may be a better way to do this that doesn't involve duplicating the table declaration, but I couldn't figure it out", anyway 'ts all good. – Crouch

Oh, I completely missed that part. That is way better, thanks for the suggestion! – Assignable

thanks all for this very great examples! – Pertinacity