Integration for Flyway

[TheBlueSky]

.NET Aspire issue link

No response

Overview

Flyway is database migration tool that supports myriad of databases. An Aspire integration will help people who use Flyway to run their application(s) with all the database migration steps, eliminating the need to run manual steps, or create custom commands.

Usage example

I have a proof-of-concept implementation, with the usage, in this Gist.

// PostgreSQL example
const string dbName = "db";
var postgres = builder.AddPostgres("postgres", "postgres", "super_secret");
var database = postgres.AddDatabase("myDatabase", dbName);
_ = builder
    .AddFlyway("flyway", "./database")
    .WithImageTag("11")
    .WithReference(database, dbName) // Not quite sure this is the most idiomatic way; we may need to pass more parameters
    .WaitFor(database);

Additional context

Because Flyway works with different database systems, and each system has its own requirement for the connection string, or rather the JDBC connection that Flyway uses, this integration will not be a single integration. What I envision it to be is a per-database-system integration. The bases of the integration will be in a library called CommunityToolkit.Aspire.Flyway, for example, and then their will be an integration for each database system that we want to support, and is supported by Flyway, such as CommunityToolkit.Aspire.Flyway.SqlServer, CommunityToolkit.Aspire.Flyway.PostgreSql, CommunityToolkit.Aspire.Flyway.MariaDb, and so on.

Help us help you

Yes, I'd like to be assigned to work on this item

[aaronpowell]

I think we could tackle it through the .Extensions projects that we have, in the same way that we have things like Adminr and DbGate.

Can the Flyway instance be used to migrate multiple databases at once, or if you had multiple databases you would be expected to have multiple Flyway resources?

I'm assuming that the second argument to AddFlyway is the location for the migration scripts, and then does Flyway store the migration info into the target database?

How would this play out in a publishing scenario? Is it running in a container that would be a sidecar to the deployed database, or is there something else that needs to be considered for deployment?

[TheBlueSky]

I think we could tackle it through the .Extensions projects that we have, in the same way that we have things like Adminr and DbGate.

What decides if something is a "proper" integration, or an extension?

Can the Flyway instance be used to migrate multiple databases at once, or if you had multiple databases you would be expected to have multiple Flyway resources?

The Flyway instance is stateless; the use of container here is to prevent the need to have Flyway pre-installed. It depends on how we do the integration, but nothing prevent using the same instance for multiple databases. In my example, it is 1 Flyway instance for 1 database.

I'm assuming that the second argument to AddFlyway is the location for the migration scripts

The second argument is the location of "Flyway directory", which contains a config file with at least a config parameter that point to the location of the migration scripts. Something like:

# flyway.conf
flyway.locations=filesystem:./migrations

does Flyway store the migration info into the target database?

Yes, precisely; the migration state is stored in the target database.

How would this play out in a publishing scenario? Is it running in a container that would be a sidecar to the deployed database, or is there something else that needs to be considered for deployment?

Nothing specific or different needs to be done during publishing/deployment, because the state of the migration is stored in the database, and the container is just a package of Flyway executable.

Flyway takes many more parameters and does a couple of other operations. The proof-of-concept code I linked to in my main post is to see how things would work. I want to support whatever possible of Flyway parameters, commands, and databases, in the most Aspire way.

[aaronpowell]

What decides if something is a "proper" integration, or an extension?

Standalone integration packages are for something that you could use in isolation from anything else. Adminr and DbGate are great analogies to Flyway here, as while they are things on their own, they aren't really something that you would use without building on top of another database.

For Flyway, it probably would make sense to have a CommunityToolkit.Aspire.Hosting.Flyway package which contains the core logic, but the integration for PostgreSQL, MySQL, SQLite, etc. would be in the CommunityToolkit.Aspire.Hosting.<DbProvider>.Extensions package (with the exception of SQLite as it's our own package to begin with), and that has a reference to the Flyway package. The .Flyway package would contain the core logic and allow for further integrations to be created for DB's that aren't supported, like Google BigQuery (which AFAIK has no integration package).

The Flyway instance is stateless; the use of container here is to prevent the need to have Flyway pre-installed. It depends on how we do the integration, but nothing prevent using the same instance for multiple databases. In my example, it is 1 Flyway instance for 1 database.

Going down the route of using a container is the best way since, as you point out, you don't need Flyway pre-installed to run. Being able to reuse the container is also probably a good idea as that means that there's a reduction in overhead of the number of containers being spun up.

The second argument is the location of "Flyway directory", which contains a config file with at least a config parameter that point to the location of the migration scripts.

How complex is the config file? Is it the sort of thing we can model with the .NET type system so that the config can be generated from Aspire and give a type-safe way of defining everything?

Broadly speaking - it sounds like a valuable integration and would be a good compliment to the Database Projects one. Feel free to submit a PR that we can help refine, or I can get Copilot to build out the structure based off your gist and then you can build on top of that.

[TheBlueSky]

For Flyway, it probably would make sense to have a CommunityToolkit.Aspire.Hosting.Flyway package which contains the core logic, but the integration for PostgreSQL, MySQL, SQLite, etc. would be in the CommunityToolkit.Aspire.Hosting.<DbProvider>.Extensions package (with the exception of SQLite as it's our own package to begin with), and that has a reference to the Flyway package.

I was thinking about is a bit differently. In my opinion, it is better for the extension packages not to take dependency on "non-official" packages. Otherwise, one day we may end up with an extension package pulling a few other packages that the majority of consumers do not need. They way I prefer it, and still implemented as an extension, is to have CommunityToolkit.Aspire.Hosting.Flyway main package, like you suggested, and CommunityToolkit.Aspire.Hosting.Flyway.<DbProvider> packages. What do you think?

How complex is the config file? Is it the sort of thing we can model with the .NET type system so that the config can be generated from Aspire and give a type-safe way of defining everything?

It can get a bit complex 😅. However, for the most basic use case, we can have Aspire generate the file. The challenge will be to identify whether the file exists, and then modify .conf/.toml formatted file.

We can also decide not to do anything with the config file, in this iteration, because I believe we can pass locations, which is the only mandatory config that I know of, as a command-line argument.

Feel free to submit a PR that we can help refine, or I can get Copilot to build out the structure based off your gist and then you can build on top of that.

I can post a draft PR in the next couple of days. I'm using this opportunity to also learn how to contribute to this repo; Copilot can help with the next one :)

[aaronpowell]

I was thinking about is a bit differently. In my opinion, it is better for the extension packages not to take dependency on "non-official" packages. Otherwise, one day we may end up with an extension package pulling a few other packages that the majority of consumers do not need.

While I understand the potential risk, the flip side of it is that we end up with an overload of "micro packages". Currently, the solution has something like 180 projects that it loads, with 51 integrations (at last count), which means we ship 51 NuGet packages. Using a package-per-db model, we would be adding at least another 5 NuGet packages, and following the repo design, at least 10 projects to support it.

I'd much prefer to keep things combined until it is clearly burdensome to package consumers, at which time we can split them into smaller packages and setup a migration strategy for that.

[TheBlueSky]

It took a while, but the PR is ready now.

[TheBlueSky]

Hi @aaronpowell, and happy new year.

I am not sure if you are the one who is supposed to review the PR or other maintainers should do, but the PR that I created for this issue was automatically closed yesterday, because it was deemed stale, by the repo automation.

What now?