Delete.Table.IfExists

[katlimruiz]

All DROP TABLE commands should do a If Exists check first (I'm using Sql Server 2012).

[tommarien]

@katlimruiz I beg to differ, the entire idea of doing migrations is expecting your database to be in a given state. If you execute a drop table, you do it because you know the table exists

[tommarien]

But maybe adding an if exists to the fluent api could give the user a choice, but i am agains auto opt-in

[colinangusmackay]

The user should at least have a choice of doing if-exists style checks before certain operations.

I agree that you should expect the database to be in a certain state, but reality is that on any given project many people may be writing migrations, interfering with each other and having to go back to alter existing migrations after others have already run in the migration then updating migration numbers and all sorts of things in order to get the job done quicker. Or simply because they are a junior member of the team and they make mistakes. Or because various people in various branches of development are looking at different views of the database with different sets of migrations run in. At the end of all that the migrations are messier but they work over a wide range of systems in various initial states including each developers' personal database, the test, UAT, DEMO, Release-Candidate and, of course, live databases.

[eldersantos]

I vote for a IfExists on a drop table, I agree in the case the database sometimes is not perfect as expected.
But, to be consistency with the migration library, it should be included to the other db objects, like Indexes, contraints, Views, etc

[migol]

I would differ on it as well, I'm working on a very big heathcare project and we just switched to using FluentMigrator as our database change support. Problem is that lots of hospitals have differences in databases and we need sometimes to remove a table if it exists.

I agree with @eldersantos that DeleteIfExists should be supported across all the schema elements.

[tommarien]

@migol @eldersantos Like already said i am open to accept a pull considering this ;)

[jordan-ware]

For those working with SQL Server, in the mean-time you can create an extension method like below:

public static void DropTableIfExists(this IExecuteExpressionRoot execute, string tableName)
{
   execute.Sql(string.Format("IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}')) BEGIN DROP TABLE [{0}] END", tableName));
}

And call it: Execute.DropTableIfExists("TableName");

It falls short of a real solution where all database providers are considered, and does not take schemas into consideration.

[eloekset]

I copy stuff like this around all my migration steps:

        private void AddForeignKeyConstraintIfNotExists(string foreignKeyTable, string foreignKey, string primaryKeyTable, string primaryKey)
        {
            string fkName = $"FK_{foreignKeyTable}_{primaryKeyTable}";

            if (!ForeignKeyExists(fkName, foreignKeyTable))
            {
                Create.ForeignKey(fkName)
                    .FromTable(foreignKeyTable).InSchema(TdmSchema).ForeignColumn(foreignKey)
                    .ToTable(primaryKeyTable).InSchema(TdmSchema).PrimaryColumn(primaryKey);
            }
        }

        private void CreateTdmSchemaIfNotExists()
        {
            if (!Schema.Schema(TdmSchema).Exists())
            {
                Create.Schema(TdmSchema);
            }
        }

        private bool ColumnExists(string tableName, string columnName)
        {
            return Schema.Schema(TdmSchema).Table(tableName).Column(columnName).Exists();
        }

        private bool TableExists(string tableName)
        {
            return Schema.Schema(TdmSchema).Table(tableName).Exists();
        }

        private bool ForeignKeyExists(string foreignKeyName, string foreignKeyTableName)
        {
            return Schema.Schema(TdmSchema).Table(foreignKeyTableName).Constraint(foreignKeyName).Exists();
        }

I even use more specialized helper methods to create a clustered key different from primary keys for tables that have uniqueidentifier as PK:

        private void CreateDefaultsPrimaryKeyClusteredAndAccountIndex(string tableName, string primaryKeyColumnName)
        {
            Execute.Sql($"ALTER TABLE [{TdmSchema}].[{tableName}] ADD CONSTRAINT [DF_{tableName}_{primaryKeyColumnName}] DEFAULT (NewID()) FOR [{primaryKeyColumnName}]");
            Execute.Sql($"ALTER TABLE [{TdmSchema}].[{tableName}] ADD CONSTRAINT [DF_{tableName}_CreatedAt] DEFAULT (GetUtcDate()) FOR CreatedAt");
            Execute.Sql($"CREATE CLUSTERED INDEX [CI_{tableName}] ON [{TdmSchema}].[{tableName}] (CreatedAt)");
            Execute.Sql($"ALTER TABLE [{TdmSchema}].[{tableName}] ADD CONSTRAINT [PK_{tableName}] PRIMARY KEY ([{primaryKeyColumnName}])");
            Create.Index($"[IDX_{tableName}_AccountId]").OnTable(tableName).InSchema(TdmSchema).OnColumn(AccountId).Ascending();
        }

        private void CreatePrimaryKeysClusteredAndAccountIndex(string tableName, List<string> primaryKeyColumns)
        {
            string joinedColumns = string.Join(",", primaryKeyColumns.Select(c => $"[{c}]").ToList());
            Execute.Sql($"ALTER TABLE [{TdmSchema}].[{tableName}] ADD CONSTRAINT [DF_{tableName}_CreatedAt] DEFAULT (GetUtcDate()) FOR CreatedAt");
            Execute.Sql($"CREATE CLUSTERED INDEX [CI_{tableName}] ON [{TdmSchema}].[{tableName}] (CreatedAt)");
            Execute.Sql($"ALTER TABLE [{TdmSchema}].[{tableName}] ADD CONSTRAINT [PK_{tableName}] PRIMARY KEY ({joinedColumns})");
            Create.Index($"[IDX_{tableName}_AccountId]").OnTable(tableName).InSchema(TdmSchema).OnColumn(AccountId).Ascending();
        }

        private ICreateTableColumnOptionOrWithColumnSyntax WithCreatedAtColumn(ICreateTableColumnOptionOrWithColumnSyntax createTableSyntax)
        {
            return createTableSyntax.WithColumn(CreatedAt).AsDateTimeOffset();  // Default constraint added along with PK
        }

That makes it easy to do stuff like:

        private void CreateForeignKeysIfNotExists()
        {
            AddForeignKeyConstraintIfNotExists(InvoiceLine, "InvoiceId", Invoice, "InvoiceId");
            AddForeignKeyConstraintIfNotExists(InvoiceDocument, "InvoiceId", Invoice, "InvoiceId");
            AddForeignKeyConstraintIfNotExists(OrderDocument, "OrderId", Order, "OrderId");
        }

        private void CreateTablesIfNotExists()
        {
            if (!TableExists(Invoice))
            {
                var invoiceTable = Create.Table(Invoice).InSchema(TdmSchema)
                    .WithColumn(AccountId).AsGuid().NotNullable()
                    .WithColumn("InvoiceId").AsGuid().NotNullable()
                    .WithColumn("InvoiceDate").AsDateTimeOffset().NotNullable()
                    .WithColumn("InvoiceDateString").AsString(10).NotNullable()
                    .WithColumn("InvoiceNo").AsInt32().NotNullable()
                    .WithColumn("ContactPersonId").AsGuid().Nullable()
                    .WithColumn("ContactPerson").AsString(255).Nullable()
                    .WithColumn("DueDate").AsDateTimeOffset().Nullable()
                    .WithColumn("CurrencyCode").AsString(7).Nullable()
                    .WithColumn("CustomerId").AsGuid().Nullable()
                    .WithColumn("CustomerNo").AsInt32().Nullable()
                    .WithColumn("ResponsibleId").AsGuid().Nullable()
                    .WithColumn("Responsible").AsString(255).Nullable();

                invoiceTable = WithCreatedAtColumn(invoiceTable);
                CreateDefaultsPrimaryKeyClusteredAndAccountIndex(Invoice, "InvoiceId");
            }
    }

That's what I like about FluentMigrator as opposed of writing SQL scripts by hand. It's so easy to write reusable helper methods for common patterns in C# and make things a bit more typesafe and less error prone.

[fubar-coder]

Querying the current state of the database doesn't work well with SQL script generation. We have to find a middle ground between PR #664 and this solution.

[jzabroski]

@fubar-coder
Some databases have begun adding "DROP TABLE IF EXISTS [schema].[table]" syntax.

I think if the ask is to support this syntax, the complexity is low.

Note: I had a real use case for this just now :)

I have a customer with tables named SomeData_2018Q2, SomeData_2018Q3, etc. that are driven by a quarterly process. However, in my test environment, that quarterly process doesn't run, making it annoying to run migrations since I have to tag them with specific environments as opposed to running the same migrations in all environments.

[jzabroski]

Workaround:

        private void DropTableIfExists(string schemaName, string tableName)
        {
            this.Execute.Sql($"DROP TABLE IF EXISTS [{schemaName}].[{tableName}];");
        }

[jzabroski]

Databases supporting DROP TABLE IF EXISTS

MySQL https://dev.mysql.com/doc/refman/8.0/en/drop-table.html
MariaDB https://mariadb.com/kb/en/library/drop-table/
SAP HANA https://help.sap.com/viewer/0991e2320f5940d988ed32b995d28a44/1.4/en-US/88b927b8a3cd4598ae24742826d6ac3b.html
SQL Server 2016+ https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
Informix 14.1 https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0736.htm
Snowflake https://docs.snowflake.net/manuals/sql-reference/sql/drop-table.html
Postgres http://www.postgresqltutorial.com/postgresql-drop-table/
SQLite https://www.sqlitetutorial.net/sqlite-drop-table/

Databases with equivalent idioms

Note: might be hard to code-generate.

Oracle https://stackoverflow.com/a/1801453/1040437

No Support

Firebird https://stackoverflow.com/a/49963337/1040437