Ability to programmatically migrate

[georgehemmings]

Is your feature request related to a problem? Please describe.
I have a long term project that uses Roundhouse for migration. RH can be used programmatically like this:

var migrate = new Migrate().Set(c =>
{
  // Blah
});

migrate.Run();

Describe the solution you'd like
To be able to use grate programmatically. A nuget package that can be installed into console projects to support this.

Thanks for all your effort on RH and grate!

[hoangthanh28]

Hi @erikbra
Need your thoughts on this matter. I would suggest making some refactoring in the structure like below:

project structure (prototype)

Register the service in DI.

var builder = WebApplication.CreateBuilder(args);
builder.AddGrateMigration(grateConfiguration => {
    // general configuration of grate like transaction, schema, etc
    grateConfiguration.UseTransaction();
    grateConfiguration.UseGrateSchema("grate");

    // this extension method will be located in grate.sqlserver package.
    // same for UseSqlServer, UsePostgreSql, UseMySql, UseOracle, UseSqlite
    grateConfiguration.UseSqlServer(cfg => {
        cfg.WithDatabaseName("your database here");
        cfg.WithConnectionString(builder.Configuration.GetConnectionString("DefaultConnection"));
        cfg.WithAdminConnectionString(builder.Configuration.GetConnectionString("AdminConnection"));
    });
});

With this configuration, grate will register the hosted service in aspnetcore app and run the migration accordingly or we can trigger the migration programmatically when needed.

var services = builder.Services;
var grateMigrator = services.GetRequiredService<IGrateMigrator>();
await grateMigrator.Migrate("your_new_database_here");

I would like to get your view on that, if everything looks good, I will implement this feature in grate.

Thanh Tran.
Thanks.

[erikbra]

Hi, @georgehemmings and @hoangthanh28, I see your input here. I maintained RoundhousE for the last couple of years, and that was split into multiple packages like this. I made the decision not to do this for grate, as I thought it was very complex, and I didn't feel that it was necessary. But I'm willing to be convinced otherwise :)

I'm just curious to know a little bit about your workflow. The setup you mention above, with running grate on application startup resembles how Entity Framework does its migrations (at least previously), and this is one of my arguments against EF. It's not very convenient to run database migrations on application startup, as in most environments, you will run multiple instances of each application, and then, which one should run the migration and not, and you probably want to start the new version of your application before taking down your old one (think, if you are running in Kubernetes, e.g, it always starts a new pod before killing the old one). This will lead to problems for the old/existing version of the application, which expects the old schema, if there are changes.

I'm not unwilling per se to add this split of grate into multiple packages, I just want to know that the usage scenario is a common one, and understanding others' workflow is a good input here. Are you able to tell me a bit more about your setup/workflow on application deployment?

[hoangthanh28]

Hi Erik (@erikbra )
That's good point otherwise, let me explain my workflow then.
My application is a multi-tenancy architecture, with the highest database isolation (each tenant has their database), which means that whenever a new tenant onboard, the database must be created and migrated with the appropriate version, suppose the version should be exact with the current version of service.
Currently, I need to setup 2 dedicated pipelines:

1. init container in k8s (runs when new deployment) - works like a charm.
2. runtime database migration, I need to create a new azp and trigger via webhook - which causes some out-of-process problems like network latency, intermittent, and losing traceability. With this proposal, I am trying to clear this burden. So when onboarding a new tenant, I just trigger the grate to migrate the appropriate version on the new database.

[erikbra]

Thank you for your explanation, @hoangthanh28. So, just to reiterate, you don't run grate automatically at application startup as part of deployment, you want to trigger it programatically without a new version of your application code, because you trigger it for a new database/schema/configuration (new tenant)?

That makes sense, I haven't thought of that.

I was initially very sceptical to maintaining libraries, as the binary compatibilities were quite a hassle, especially when the whole .net core/.net standard/.net framework transition took place, but now the number of TFMs for dotnet has been significantly reduced, so I'm more willing to do it now.

@georgehemmings and @hoangthanh28 , what .net versions would you need support for? Nothing lower than .net6?

[hoangthanh28]

Hi Erik.
I run both at startup and runtime. For startup, I use the init container in k8s deployment manifest, for runtime, I would like to get rid of out-of-process ( like mentioned above) by implementing this proposal.
Please assign this ticket to me, I'm happy to implement this feature.

[hoangthanh28]

Quick update @erikbra.
The implementation has been pushed to my github, I will spend time on unit tests and documents.
Please have a look first, I'm happy to walk you through and discuss if any doubts.

[georgehemmings]

We have a .NET Framework 4.7.2 WinForms application specifically for provisioning new databases and upgrading existing ones. Our clients self-host their databases.

It runs a few other scripts before and after RH migrate, such as password hash migration etc. The technician running the app just sets the connection details and presses "go".

The app could be ported to .NET 8.

I hope that gives you a better understanding of our need for programmatic migration.

[erikbra]

This should be fixed by #417 (thanks again, @hoangthanh28 ). I'm working my best to get out a 1.6 release now, it's long overdue, but I wanted to take the extra time to think this through some times. The surface we expose to users is much greater when providing an API than just the command line, and users will expect a stable API in the nuget packages as well.

But, we're getting there, hang in tight :)

[erikbra]

FYI, @georgehemmings - this is released and live on Nuget:

https://github.com/erikbra/grate/releases/tag/1.6.0
https://www.nuget.org/packages/grate.sqlserver#readme-body-tab (and friends - further link in the readme for this)