Patterns for DB Migrations with WaitFor & 9.x

[afscrome]

How are you thinking about doing DB Migrations with WaitFor. I've gone through a few iterations that either don't work, or don't work particularly cleanly.

In my use case, we have a central database that is shared by several applications. (Potentially in the range of 50-100). So I'm trying to build up a reusable component that multiple teams can use to work with this database. Something that can be used like the following:

var sql = builder.AddSqlServer("sql")
    .WithLifetime(ContainerLifetime.Persistent);

var centralDb = sql.AddCentralDb("centralDb");

var app1 = builder.AddExecutable("app1", "pwsh", ".", "-Command", "Write-Host 'Fake App'; Start-Sleep -Seconds 999999")
    .WithReference(centralDb)
    .WaitFor(centralDb);

var app2 = builder.AddExecutable("app2", "pwsh", ".", "-Command", "Write-Host 'Fake App'; Start-Sleep -Seconds 999999")
    .WithReference(centralDb)
    .WaitFor(centralDb);

// etc.

Migrations are done by a powershell script, although it could be any other executable (e.g. dotnet ef database update). The migrator is something like the following:

    private static IResourceBuilder<ExecutableResource> AddMigrator(this IResourceBuilder<SqlServerDatabaseResource> db, string name)
    {
        return db.ApplicationBuilder
            .AddExecutable($"{name}-migrator", "pwsh", ".",
                "-NoProfile",
                "-NoLogo",
                "-NonInteractive",
                "-Command",
                "Write-Host 'Starting Migration'; Start-Sleep -Seconds 3; Write-Host 'Done'"
            )
            .WithReference(db);
    }

Take 1

Simplest option is to have each consuming app wait on both the migrator and the database. This isn't a bad option if you only have one consumer of your database, but if you start having multiple consumers it becomes a bit of a pain passing around both the migrator and the database. It makes for a leaky abstraction if you're trying to make a helper hosting package to abstract that database away. Nor does it fit nicely into the existing hosting builder pattern (unless you start using out variables to return the second resource 🤮)

    public static IResourceBuilder<SqlServerDatabaseResource> AddCentralDb1(this IResourceBuilder<SqlServerServerResource> sql, string name, out IResourceBuilder<ExecutableResource> migrator)
    {
        var db = sql.AddDatabase(name);

        migrator = db
            .AddMigrator(name)
            .WaitFor(db);

        return db;
    }

Take 2

I was hoping to be able to do something like the following, where the migrator waits on SQL, and the database then waits on the migrator completing. This didn't work with the 8.x WaitForDependencies extension as only resources with Environment (executables & containers) could wait on other resources. It has worked intermittently on 9.x, but with #5870 it looks like it is no longer expected to work.

    public static IResourceBuilder<SqlServerDatabaseResource> AddCentralDb2(this IResourceBuilder<SqlServerServerResource> sql, string name)
    {
        var db = sql.AddDatabase(name);
        var migrator = db.AddMigrator(name)
            .WaitFor(sql);

        return db
            .WaitForCompletion(migrator);
    }

As a variant of this, I looked to see if there was an event I could hook into to delay the database resource myself, but I couldn't find anything suitable - the ResourceReadyEvent is just a bit too late to work.

        sql.ApplicationBuilder.Eventing.Subscribe<ResourceReadyEvent>(db.Resource, async (e, c) =>
        {
            var resourceNotificationService = e.Services.GetRequiredService<ResourceNotificationService>();
            await resourceNotificationService.WaitForResourceAsync(migrator.Resource.Name, KnownResourceStates.Finished);
        });

Take 3

I've now gone back to the solution I was using in 8.x of using a Custom Resource. I have been able to replace a lot of the copy & paste from WaitForDependenciesRunningHook.WaitForDependenciesRunningHook with events that plug into 9.x's WaitFor.

This option produces a clean API for consumers to use, however it does leak a bit on the dashboard as it now exposes two database resources. It is however rather awkward to have to break out into making custom resources as there's little documentation about making custom resources that don't inherit from Executable or Container resources (and even less about how to integrate them into WaitFor). Migrations feel like a common enough scenario that you shouldn't need to make a custom resource for migrations.

    public static IResourceBuilder<MigratedDatabase> AddCentralDb3(this IResourceBuilder<SqlServerServerResource> sql, string name)
    {
        var appBuilder = sql.ApplicationBuilder;
        var db = sql.AddDatabase($"{name}-inner");

        var migrator = db
            .AddMigrator(name)
            .WaitFor(sql);

        var migratedDb = appBuilder
            .AddResource(new MigratedDatabase<SqlServerDatabaseResource>(name, db))
            .WaitForCompletion(migrator)
            .WithInitialState(new CustomResourceSnapshot()
            {
                Properties = [],
                ResourceType = "MigratedDb"
            });

        // Forward connection string available event from the inner resource
        appBuilder.Eventing.Subscribe<ConnectionStringAvailableEvent>(db.Resource, async (e, c) =>
        {
            var connectionStringEvent = new ConnectionStringAvailableEvent(migratedDb.Resource, e.Services);
            await appBuilder.Eventing.PublishAsync(connectionStringEvent, c);
        });

        appBuilder.Eventing.Subscribe<BeforeStartEvent>((e, c) =>
        {
            var resourceNotificationService = e.Services.GetRequiredService<ResourceNotificationService>();

            // Have to run this in a non blocking task, otherwise the call to publish
            // BeforeResourceStartedEvent deadlocks with the BeforeStartEvent subscription
            _ = Task.Run(async () =>
            {
                var startEvent = new BeforeResourceStartedEvent(migratedDb.Resource, e.Services);
                await appBuilder.Eventing.PublishAsync(startEvent, c);
                await resourceNotificationService.PublishUpdateAsync(migratedDb.Resource, x => x with
                {
                    State = new(KnownResourceStates.Running, KnownResourceStateStyles.Success),
                });
            }, c);
            return Task.CompletedTask;
        });

        return migratedDb;
    }

public class MigratedDatabase(string name, IResourceBuilder<IResourceWithConnectionString> inner)
    : Resource(name), IResourceWithConnectionString // And Soon 
{
    public IResourceWithConnectionString Parent => inner.Resource;
    public ReferenceExpression ConnectionStringExpression => Parent.ConnectionStringExpression;
}

Alternatives?

Do you have any other recommendations for how to do database migrations with WaitFor? Either modifications to one of the above approaches, or alternative suggestions.

[davidfowl]

You identified the crux of the problem, it's currently impossible to make the database wait (because it currently doesn't have a lifecycle of its own). The simplest solution I can think of that doesn't use a custom resource would be to do something like this: Doesn't work.

[davidfowl]

With that said, you should be able to attach a health check to the database and resources waiting on it will end up waiting on it to be healthy ( so fully migrated ). The dashboard will say running unhealthy instead of waiting but it’s a bit cleaner than a custom resource