[Question] Any plans to have "universal" migrations ?

[cdie]

We work in a system that can, depending of some configuration, target SQLite or SQL Server. Some days, we may have the need to target another data store.

Actually, we store migrations aside our persistence models, in a netstandard assembly. We dynamically set the connection strings depending of the selected configuration.
But it seems that migrations are created in a very single provider way.

I think it would be great to have a universal migration description, and then, when applying it, let the data store provider "parse" it to define column type and any other provider specific stuff.

A big step forward has already be done with schema that are ignored for SQLite.
I know it's a breaking change for datastore providers, so maybe you can put it in backlog for version 2.2 or 3.0 ?

[ajcvickers]

@cdie This is an area of Migrations that has been discussed at length over the last few years. It's one of those things where there are areas of tension pulling against each other. For example:

• Fully agnostic migrations verses full fidelity constructs for each provider
• Migrations that are overly abstract verses ones where it is clear exactly what is happening
• The cost of building tooling to support multiple different providers in one application

We have at this point settled on the following approach:

• Things that are naturally supported by multiple/most providers can be done in an agnostic way
• Simple things that are provider dependent, but usually chosen automatically by EF (e.g. column types) are abstracted.
• Things that are provider specific and not simple inferences are not abstracted but instead get provider specific code in the scaffolded migration.
• Make it easy to write conditional code to do different things in the migration depending on which provider is currently being used.

This means that the following approaches should work:

1. Maintain separate parallel migrations for each provider--possibly using different derived DbContext classes
2. Scaffold once for each provider and then diff the generated migrations. Most of the code should be common between all the migrations. Merge the generated migrations into one, adding conditional code where needed.

Personally, I would use the second approach.

There is some additional discussion in issue #9601, which is also tracking updates to the documentation for this area.