Dealing with multiple database types

FluentMigrator allows you to target multiple database types from the same migration project. Every FluentMigrator project is database agnostic and can be run against any of the supported database types.

However, there are times when only some migrations in a project need to be executed against one of the database types. For supporting that scenario FluentMigrator includes the IfDatabase expression.

Using IfDatabase

Suppose you have a migration that executes a script file to create a view:

public class CreateViewsMigration : Migration
{
    public override void Up()
    {
        Execute.Script("CreateViewsMigrationUp.sql");
    }

    public override void Down()
    {
        Execute.Script("CreateViewsMigrationDown.sql");
    }
}

However the project needs to create some views in an SqlServer database and others in an Oracle database, but you want both tasks to be part of the same migration, sharing the same migration number in both databases. You handle this by creating scripts for each database and specifying which one needs to be executed:

public class CreateViewsMigration : Migration
{
     public override void Up()
     {
        IfDatabase("oracle").Execute.Script("CreateViewsOracleMigrationUp.sql");
        IfDatabase("sqlserver").Execute.Script("CreateViewsSqlServerMigrationUp.sql");
     }

     public override void Down()
     {
         IfDatabase("oracle").Execute.Script("CreateViewsOracleMigrationDown.sql");
         IfDatabase("sqlserver").Execute.Script("CreateViewsSqlServerMigrationDown.sql");
     }
}

Additionally, you may want to special-case only one database, while handle all other databases the same way:

public class CreateViewsMigration : Migration
{
     public override void Up()
     {
        var createSchemaExpr = IfDatabase(t => t != ProcessorId.SQLite).Create.Schema("TestSchema");

        IfDatabase(ProcessorId.SQLite).Execute.Sql("ATTACH DATABASE '' AS \"TestSchema\"");
     }

     public override void Down()
     {
         IfDatabase(ProcessorId.SQLite).Delegate(() => Console.Error.WriteLine("To delete a schema in SQLite requires detaching database."));
         IfDatabase(t => t != ProcessorId.SQLite).Delete.Schema("TestSchema");
     }
}

Supported databases

Database Identifier Alternative identifier(s)
Microsoft SQL Server 2019 SqlServer2016(1) SqlServer
Microsoft SQL Server 2017 SqlServer2016(2) SqlServer
Microsoft SQL Server 2016 SqlServer2016 SqlServer
Microsoft SQL Server 2014 SqlServer2014 SqlServer
Microsoft SQL Server 2012 SqlServer2012 SqlServer
Microsoft SQL Server 2008 SqlServer2008 SqlServer
Microsoft SQL Server 2005 SqlServer2005 SqlServer
Microsoft SQL Server 2000 SqlServer2000 SqlServer
Microsoft SQL Server Compact Edition(3) SqlServerCe SqlServer
PostgreSQL Postgres PostgreSQL
PostgreSQL 9.2 Postgres92 PostgreSQL92
PostgreSQL 10.0 PostgreSQL10_0 PostgreSQL
PostgreSQL 11.0 PostgreSQL11_0 PostgreSQL
MySQL 4 MySql4 MySql
MySQL 5 MySql5 MySql, MariaDB
Oracle Oracle
Oracle (managed ADO.NET) OracleManaged Oracle
Oracle (DotConnect ADO.NET) OracleDotConnect Oracle
Microsoft JET Engine (Access) Jet
SQLite Sqlite
Firebird Firebird
Amazon Redshift Redshift
SAP Hana Hana
SAP SQL Anywhere(4) SqlAnywhere16 SqlAnywhere
DB2 DB2
DB2 iSeries DB2 iSeries DB2
  • (1) All integration tests ran without error against an SQL Server 2019 using the SqlServer2016 dialect.
  • (2) All integration tests ran without error against an SQL Server 2017 using the SqlServer2016 dialect.
  • (3) Support for Microsoft SQL Server Compact Edition is being dropped due to Microsoft end-of-life support date passing.
  • (4) Support for SAP SQL Anywhere is being dropped due to SAP not supporting a .NET Core / .NET 5 database driver.