FAQ | FluentMigrator documentation

This FAQ will answer most common questions. Please open an issue if your question isn't answered here.

Why does the `Migrate.exe` tool say `No migrations found`?

Possible reasons:

Migration class isn't public
Migration class isn't derived from IMigration (or Migration)
Migration class isn't attributed with MigrationAttribute
The versions of the Migrate.exe tool (FluentMigrator.Console package) and the FluentMigrator package(s) referenced in your project are different.

How can I run FluentMigrator.DotNet.Cli with a .NET 5.0 assembly?

The FluentMigrator.DotNet.Cli contains an --allowDirtyAssemblies switch that will allow you to load your 5.0 assemblies in a .NET Core 3.1 context. We're working on .NET 5.0 support.'

Why do I get `System.IO.FileLoadException: Could not load file or assembly 'FluentMigrator ...`?

If you get a stack trace like the following:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileLoadException: Could not load file or assembly 'FluentMigrator, Version=3.2.1.0, Culture=neutral, PublicKeyToken=aacfc7de5acabf05". Could not find or load specific file.

Then the most likely explanation is that you installed the FluentMigrator.DotNet.Cli command line tool globally, with a specific FluentMigrator.dll version in your global tool directory, and are loading a migrations assembly that references a different version of FluentMigrator.dll.

The common workaround is to install the tool locally, rather than globally (applies to .NET Core SDK 3.0 and later).

What are the supported databases?

Database	Identifier	Alternative identifier(s)
Microsoft SQL Server 2019	SqlServer2016⁽¹⁾	SqlServer
Microsoft SQL Server 2017	SqlServer2016⁽²⁾	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~~⁽³⁾	~~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~~⁽⁴⁾	~~SqlAnywhere16~~	~~SqlAnywhere~~
DB2	DB2
DB2 iSeries	DB2 iSeries	DB2

⁽¹⁾ All integration tests ran without error against an SQL Server 2019 using the SqlServer2016 dialect.
⁽²⁾ All integration tests ran without error against an SQL Server 2017 using the SqlServer2016 dialect.
⁽³⁾ Support for Microsoft SQL Server Compact Edition is being dropped due to Microsoft end-of-life support date passing.
⁽⁴⁾ Support for SAP SQL Anywhere is being dropped due to SAP not supporting a .NET Core / .NET 5 database driver.

How can I run migrations safely from multiple application servers?

Many server-side applications are load balanced and run multiple instances of the same application simultaneously from multiple web servers. In such a scenarios, if you choose to run migrations in-process (as opposed to an external migration runner), then there is an added risk of multiple processes trying to run migrations at the same time.

FluentMigrator does not automatically handle this scenario because the default transactional behavior is not enough to guarantee that only a single process can be running migrations at any given time. There are, however, some workarounds.

Database-Dependent Application Locking

This style of solution depends upon MaintenanceMigrations. Two Maintenance Migrations are created: One with BeforeAll to atomically acquire a lock, and one AfterAll stage to release the lock.

This example is for SQL Server 2008 and above and uses sp_getapplock to aquire a named lock before all migrations are run, and sp_releaseapplock to release the lock after all migrations are finished.

[Maintenance(MigrationStage.BeforeAll, TransactionBehavior.None)]
public class DbMigrationLockBefore : Migration
{
    public override void Down()
    {
        throw new NotImplementedException("Down migrations are not supported for sp_getapplock");
    }

    public override void Up()
    {
        Execute.Sql(@"
            DECLARE @result INT
            EXEC @result = sp_getapplock 'MyApp', 'Exclusive', 'Session'

            IF @result < 0
            BEGIN
                DECLARE @msg NVARCHAR(1000) = 'Received error code ' + CAST(@result AS VARCHAR(10)) + ' from sp_getapplock during migrations';
                THROW 99999, @msg, 1;
            END
        ");
    }
}

[Maintenance(MigrationStage.AfterAll, TransactionBehavior.None)]
public class DbMigrationUnlockAfter : Migration
{
    public override void Down()
    {
        throw new NotImplementedException("Down migrations are not supported for sp_releaseapplock");
    }

    public override void Up()
    {
        Execute.Sql("EXEC sp_releaseapplock 'MyApp', 'Session'");
    }
}

In the above SQL Server example, we need to use TransactionBehavior.None on the Maintenance Migration while specifying the @LockOwner parameter to Session, which means that the locking behavior applies to the entire Session rather than a single transaction.

While the above is specific to SQL Server, similar concepts may available in other database providers.

PostgreSQL has Advisory Locks
SQL Anywhere has Schema Locks
Oracle has DBMS_LOCK.ALLOCATE_UNIQUE
DB2 has LOCK TABLESPACE (with the caveat that every table in your migration is in the same tablespace)

External Distributed Lock

If your database doesn't provide a means of acquiring an exclusive lock for migrations, it is still possible to achieve this functionality by using an external service for acquiring a distributed lock.

For example, Redis provides a way to perform Distributed locks so that different processes can operate on a shared resource in a mutually exclusive way. This scenario can be baked into a BeforeAll/AfterAll pair of Maintenance Migrations, as demonstrated above, to acquire an exclusive lock for the duration of the migration running.

As an alternative to Maintenance Migrations, which are by necessity specified in different classes, you could simply wrap the MigrateUp() call in code that acquires and releases the lock. Consider this pseudo-code which relies on RedLock.net:

async RunMigrationsWithDistributedLock(IMigrationRunner runner)
{
    var resource = "my-app-migrations";
    var expiry = TimeSpan.FromMinutes(5);

    using (var redLock = await redlockFactory.CreateLockAsync(resource, expiry)) // there are also non async Create() methods
    {
        // make sure we got the lock
        if (redLock.IsAcquired)
        {
            runner.MigrateUp();
        }
    }
    // the lock is automatically released at the end of the using block
}

How can I execute a stored procedure using Oracle?

If you get ORA-00900: Invalid SQL Statement when executing a stored procedure, then chances are you need to wrap your stored procedure in a PLSQL block:

  Execute.Sql("DBMS_UTILITY.EXEC_DDL_STATEMENT('Create Index Member_AddrId On Member(AddrId)');");

becomes:

  Execute.Sql(@"
BEGIN
  DBMS_UTILITY.EXEC_DDL_STATEMENT('Create Index Member_AddrId On Member(AddrId)');
END;");

How do I get the name of a SQL Server database?

Not all databases have a "name". Writing migrations that use a name therefore cannot be truly database-agnostic. That said, the following will show an example of getting the name so that you can perform an ALTER DATABASE command. Note that to ALTER DATABASE [YourDatabaseName], you need to switch to the [master] database first via USE [master]. Then, since FluentMigrator does not call sp_reset_connection, you need to switch back to the database being migrated. If you do not, the ensuing migrations will be run in the wrong database!

In the below example, we show how to enter a SQL Server database into single-user mode, in order to perform some maintenace tasks.

Use dynamic SQL:

    public class EnterDatabaseSingleUserModeState : Migration
    {
        public override void Up()
        {
            /* Before you set the database to SINGLE_USER, verify that
            the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When
            this option is set to ON, the background thread that is
            used to update statistics takes a connection against the
            database, and you will be unable to access the database in
            single-user mode. For more information, see
            ALTER DATABASE SET Options (Transact-SQL). */
            this.Execute.Sql(@"
              DECLARE @DbName sysname = DB_NAME();
              DECLARE @SqlCommand NVARCHAR(MAX) = '
USE [master];
SET DEADLOCK_PRIORITY 10;
DECLARE @AutoUpdateStatisticsAsync BIT = CAST(0 AS BIT);
IF EXISTS (
    SELECT NULL
    FROM sys.databases WHERE name = @DbName AND is_auto_update_stats_async_on = CAST(1 AS BIT)
)
BEGIN
    ALTER DATABASE [' + @DbName + '] 
    SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
    SET @AutoUpdateStatisticsAsync = CAST(1 AS BIT);
END;
ALTER DATABASE [' + @DbName + ']' + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Here is where you put your administrative commands.

IF (@AutoUpdateStatisticsAsync = 1)
BEGIN
    ALTER DATABASE [' + @DbName + '] 
    SET AUTO_UPDATE_STATISTICS_ASYNC ON;
END;
';
              
              EXEC(@SqlCommand);
              SET @SqlCommand NVARCHAR(MAX) = 'USE [' + @DbName + ']';
              EXEC(@SqlCommand);
            ");
    }

Certificate error for SQL Connection

Since the use of Microsoft.Data.SqlClient version 4.0.0 connections are encrypted by default in .NET. You might start getting the exception: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.). If fixing a valid certificate isn't a feasable path, you can always disable the encryption by adding Encrypt=False to the connection string. Then the connection won't be encryptet, and thus no certificate needed. As a note; you can also add TrustServerCertificate=True to the connection string, if you have a self-signed certificate or similar. But it's probably a better idea to fix a real certificate, or skip encryption all together.

SQLite specific topics

Connection Pooling

When working with FluentMigrator with the SQLite processor, by default SQLite will run with connection pooling enabled. When connection pooling is enabled, SQLite will keep a lock on the database file even after connection.Close() is called on the relevant DB connection. This is important to know in case you need to perform any cleanup after FluentMigrator has run where deleting the database file is required. In this scenario you would receive an IO exception if you attempt to delete or move the file as SQLite still has a lock on the file.

You can disable connection pooling however by adding Pooling=False; as a parameter in your connection string. With pooling disabled, you can then safely delete or move the database after the FluentMigrator processor is disposed.

Why does the Migrate.exe tool say No migrations found?