Database migration for dynamic named connections

I am implementing the new Migration functionality but have hit a bit of a glitch.

I am working on a web service for a back-office system (not public-facing). It captures engineering test data, allowing engineers to create ad-hoc Sqlite databases to store their results. These are attached as named connections, and can be made available for other engineers on the network to share data.

The problem that I’ve hit is that when someone creates a new DB to capture their data, I need to run a migration on it (all DBs have the same schema). The way that migrations for named connections seems to work is that the connection names have to be hard-coded in the migration class rather than being dynamically assigned.

Is there a work-around for this situation? Or would it be possible to extend the Migrator class to allow the connection name or Connection String to be passed, either as a constructor parameter or as a parameter to the Run() method?

You can access the base.DbFactory from the Migration class so you could use that to open any named connection from within your Up() and Down() implementations. But you could just run it with a different DbFactory configured to the DB you want to run it on given it’s easy enough to run in code with your configured DB.

Here’s the integration test we’re going to include in our new Jamstack templates in the next release which uses the App’s ConfigureDb to resolve its configured IDbConnectionFactory but you could replace it to use a OrmLiteConnectionFactory created at runtime.

[TestFixture, Explicit, Category(nameof(MigrationTasks))]
public class MigrationTasks
{
    IDbConnectionFactory ResolveDbFactory() => new ConfigureDb()
        .ConfigureAndResolve<IDbConnectionFactory>();

    Migrator CreateMigrator() => new(
        ResolveDbFactory(), typeof(Migration1000).Assembly); 
    
    [Test]
    public void Migrate()
    {
        var migrator = CreateMigrator();
        var result = migrator.Run();
        Assert.That(result.Succeeded);
    }

    [Test]
    public void Revert_All()
    {
        var migrator = CreateMigrator();
        var result = migrator.Revert(Migrator.All);
        Assert.That(result.Succeeded);
    }

    [Test]
    public void Revert_Last()
    {
        var migrator = CreateMigrator();
        var result = migrator.Revert(Migrator.Last);
        Assert.That(result.Succeeded);
    }

    [Test]
    public void Rerun_Last_Migration()
    {
        Revert_Last();
        Migrate();
    }
}

Which is the same as running the command-line tasks, except you’ll be able to debug it when running it in code if you need to:

$ npm run migrate
$ npm run revert:last
$ npm run revert:all

That’s just what I was looking for, thanks :+1:

2 Likes