How to test fluent migrations with an in-process migration runner and a in memory SQLite database

Ok, I found a solution. I have to use the Process method of the runner's processor to perform my own sql query.

It looks like this:

private ServiceProvider CreateServiceProvider()
{
    return new ServiceCollection()
        .AddLogging(lb => lb.AddFluentMigratorConsole())
        .AddFluentMigratorCore()
        .ConfigureRunner(
            builder => builder
                .AddSQLite()
                .WithGlobalConnectionString(@"Data Source=:memory:;Version=3;New=True;")
                .WithMigrationsIn(typeof(MigrationDate20181026113000Zero).Assembly))
        .BuildServiceProvider();
}

[Test]
public void ShouldHaveNewVersionAfterMigrateUp()
{
    var serviceProvider = this.CreateServiceProvider();
    var scope = serviceProvider.CreateScope();
    var runner = scope.ServiceProvider.GetRequiredService<IMigrationRunner>();

    runner.MigrateUp(1);

    string sqlStatement = "SELECT Description FROM VersionInfo";

    DataSet dataSet = runner.Processor.Read(sqlStatement, string.Empty);

    Assert.That(dataSet, Is.Not.Null);
    Assert.That(dataSet.Tables[0].Rows[0].ItemArray[0], Is.EqualTo("Migration1"));
}

This is an old question but an important one. I find it strange that I couldnt find any documentation on this.

In any case here is my solution which I find to be a bit better as you dont need to rely on the runner. Since you dont need that the options open up hugely for constructor arguments.

Firstly make sure you install Microsoft.Data.Sqlite or you will get a strange error.

SQLite in memory databases exist for as long as the connection does - and 1 database per connection on first glance. Actually though there is a way to share the database between connections as long as at least 1 connection is open at all times according to my experiments. You just need to name it. https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings#sharable-in-memory

So to begin with I created a connection that will stay open until the test finishes. It will be named using Guid.NewGuid() so that subsequent connections will work as expected.

var dbName = Guid.NewGuid().ToString();
var connectionString = $"Data Source={dbName};Mode=Memory;Cache=Shared";
var connection = new SqliteConnection(connectionString);
connection.Open();

After that the crux of running the migrations is the same as previously answered but the connection string uses the named database:

var sp = services.AddFluentMigratorCore()
    .ConfigureRunner(fluentMigratorBuilder => fluentMigratorBuilder
        .AddSQLite()
        .WithGlobalConnectionString(connectionString)
        .ScanIn(AssemblyWithMigrations).For.Migrations()
    )
    .BuildServiceProvider();

var runner = sp.GetRequiredService<IMigrationRunner>();
runner.MigrateUp();

Here is a class I use to inject a connection factory everywhere that needs to connect to the database for normal execution:

internal class PostgresConnectionFactory : IConnectionFactory
{
    private readonly string connectionString;

    public PostgresConnectionFactory(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public DbConnection Create()
    {
        return new NpgsqlConnection(connectionString);
    }
}

I just replaced this (all hail dependency inversion) with:

internal class InMemoryConnectionFactory : IConnectionFactory
{
    private readonly string connectionstring;

    public InMemoryConnectionFactory(string connectionstring)
    {
        this.connectionstring = connectionstring;
    }

    public DbConnection Create()
    {
        return new SqliteConnection(connectionstring);
    }
}

where the connection string is the same named one I defined above.

Now you can simply use that connection factory anywhere that needs to connect to the same in memory database, and since we can now connect multiple times possibilities for integration testing open up.

Here is the majority of my implementation:

public static IDisposable CreateInMemoryDatabase(Assembly AssemblyWithMigrations, IServiceCollection services = null)
{
    if (services == null)
        services = new ServiceCollection();

    var connectionString = GetSharedConnectionString();
    var connection = GetPersistantConnection(connectionString);
    MigrateDb(services, connectionString, AssemblyWithMigrations);

    services.AddSingleton<IConnectionFactory>(new InMemoryConnectionFactory(connectionString));

    return services.BuildServiceProvider()
        .GetRequiredService<IDisposableUnderlyingQueryingTool>();
}

private static string GetSharedConnectionString()
{
    var dbName = Guid.NewGuid().ToString();
    return $"Data Source={dbName};Mode=Memory;Cache=Shared";
}

private static void MigrateDb(IServiceCollection services, string connectionString, Assembly assemblyWithMigrations)
{
    var sp = services.AddFluentMigratorCore()
        .ConfigureRunner(fluentMigratorBuilder => fluentMigratorBuilder
            .AddSQLite()
            .WithGlobalConnectionString(connectionString)
            .ScanIn(assemblyWithMigrations).For.Migrations()
        )
        .BuildServiceProvider();

    var runner = sp.GetRequiredService<IMigrationRunner>();
    runner.MigrateUp();
}

private static IDbConnection GetPersistantConnection(string connectionString)
{
    var connection = new SqliteConnection(connectionString);
    connection.Open();

    return connection;
}

Then here is a sample test:

public Test : IDisposable {
    private readonly IDisposable _holdingConnection;
    
    public Test() {
        _holdingConnection = CreateInMemoryDatabase(typeof(MyFirstMigration).Assembly);
    }
    
    public void Dispose() {
        _holdingConnection.Dispose();
    }
}

You may notice that the static factory returns a custom interface. Its just an interface that extends the normal tooling I inject to repositories, but also implements IDisposable.

Untested bonus for integration testing where you will have a service collection created via WebApplicationFactory or TestServer etc:

public void AddInMemoryPostgres(Assembly AssemblyWithMigrations)
{
    var lifetime = services.BuildServiceProvider().GetService<IHostApplicationLifetime>();

    var holdingConnection= InMemoryDatabaseFactory.CreateInMemoryDapperTools(AssemblyWithMigrations, services);

    lifetime.ApplicationStopping.Register(() => {
        holdingConnection.Dispose();
    });
}