Improve experience deploying databases created by Migrations

[ajcvickers]

This is a grouping of related issues. Feel free to vote (👍) for this issue to indicate that this is an area that you think we should spend time on, but consider also voting for individual issues for things you consider especially important.

---

Currently, many developers migrate their databases at application startup time. This is easy but is not recommended because:

• Multiple threads/processes/servers may attempt to migrate the database concurrently
• Applications may try to access inconsistent state while this is happening
• Usually the database permissions to modify the schema should not be granted for application execution
• Its hard to revert back to a clean state if something goes wrong

We want to deliver a better experience here that allows an easy way to migrate the database at deployment time. This should:

• Work on Linux, Mac, and Windows
• Be a good experience on the command line
• Support scenarios with containers
• Work with commonly used real-world deployment tools/flows
• Integrate into at least Visual Studio

The result is likely to be many small improvements in EF Core (for example, better Migrations on SQLite), together with guidance and longer-term collaborations with other teams to improve end-to-end experiences that go beyond just EF.

---

Done in 6.0

• Migrations Bundles #19693

Done in 9.0

• Stop all migrations and roll back everything on failure #22616
• Execution strategy for migration #17578
• Implement database locking for migrations #33731
• Warn on Migrate() when there are pending model changes #33732

Backlog

• Extension point needed after applying migrations programmatically #17568
• Migrations: Improve existing database support #2167
• More streamlined workflow for early stage app development #3053
• Tools: Honor launchSettings.json #8695
• Consider throwing/warning by default if a migration is used against a different provider than it was generated for #15174
• Support migrations for the same DbContext in the same assembly for multiple providers #29343
• Migrations: Provide easy way to get script from latest applied on database to latest in the codebase #15762
• Fully embrace provider-specific migrations #16406
• Tools: Work around different working directory in VS and dotnet #18624
• Revisit tools architecture #18840
• Update DatabaseErrorPageMiddleware experience to promote migration bundles #20402
• SqlServer Migrations: Scripts do not fail on failure #22613
• CheckMigrated #24866
• Migrations Bundles: Provide more info about the bundle #25872
• Migration bundles directly to a docker container #31999
• Sugar for getting a migration script for just the last migration #26155
• Improve Migrations Projects #19694
• Provide Migrations hooks to execute SQL and/or seeding before and after each migration has been applied #24710
• Open up MigrationHistoryTable's model building for provider extensibility (or rethink the design) #34991

[ErikEJ]

sqlpackage / dacpac already does much of this - but sadly currently only for SQL Server.

[BEagle1984]

In our latest project we integrated the DB migrations in the deployment pipeline simply by generating an idempotent migration script (for all migration steps) via dotnet-ef and leveraging sqlcmd to execute it against the appropriate sql server instance. We do this in Jenkins, from a Linux slave.
I honestly don’t think our solution is too bad.

[bricelam]

Some related issues:

• Sqlite Migrations: Table rebuilds #329
• Script-Migration command should begin/commit transactions #7681
• Tools: Honor launchSettings.json #8695
• EF Core 2 Migrations in VSTS CD Release #9841
• Updating different database via CLI (Pass connection string to dotnet ef database update) #10750
• SQL Server Migrations: Idempotent scripts fails with 'invalid column name' (needs EXEC) #12911
• The specified deps.json [\\\\TeamDrive\IT\Team Software\Project\etc...] does not exist #14349
• Consider using NuGet's buildTransitive folder #14531
• Detecting script vs execution in MigrationsSqlGenerator #14746
• Consider throwing/warning by default if a migration is used against a different provider than it was generated for #15174
• RegexMatchTimeoutException for SqlServerMigrationsSqlGenerator #16175
• Fully embrace provider-specific migrations #16406
• Tools: Work around different working directory in VS and dotnet #18624
• Revisit tools architecture #18840
• Migrations Bundles #19693
• Improve Migrations Projects #19694
• Script-Migration -idempotent creates script with errors #19740
• SqlServer Migrations: Scripts do not fail on failure #22613
• Stop all migrations and roll back everything on failure #22616

[macsux]

So I've built something that helps with this that was contributed to project SteelToe. It allows tasks to be bundled with app and launched via special command argument. Ef migration is one of them.
Is you're not familiar with SteelToe, the site is https://steeltoe.io

These are the relevant sources you may consider

https://github.com/SteeltoeOSS/Management/tree/dev/src/Steeltoe.Management.CloudFoundryTasks

https://github.com/SteeltoeOSS/Connectors/blob/dev/src/Steeltoe.CloudFoundry.Connector.EFCore/MigrateDbContextTask.cs

[johnnyreilly]

Thanks for linking to this @AndriySvyryd - great to see this is being actively worked on

[dazinator]

I ended up architecting my app to address these points, and putting all the logic in a reusable library which I might open source in future.

• A lock file in shared storage directory (directory seen by all instances) to ensure only one instance can run the migrations at a time.
• An mvc Action filter which rejects requests by default when there are EF pending upgrades detected and includes a header in the response so that the client can detect that the server requires ef core migrations to be applied and can display a suitable UI.
• A blazor component and http client delegate handler to be used in blazor wasm applications that can auto detect the header in responses from the server and display an Upgrade pending screen, with an option for a deployment admin to login and apply.
• An MVC controller with an Action method that can be called to Apply the pending EF core migrations. This is excluded from the action filter mentioned above that rejects requests and is instead only available when there are pending migrations to apply. I've applied authorization to this method so it requires "deployment admin" role.
• A custom authentication scheme that allows a "deployment admin" to login using a login method that places no dependency on the application database for obvious reasons. This custom auth scheme generates a code and writes it to a file in the storage directory (same location as where the lock file also gets written). A person with access to that directory can then grab the code and use it to log in with which creates a claims principal with Deployment Admin role, and then allows them to see the pending migrations and click a button to apply them (I.e this calls the mvc action method mentioned above)

There was quite a bit to it in the end.
I havent addressed rollback.. but being able to rollback is not necessarily safe if data is going to be lost in the process, so the safest policy for me tends to be to only allow roll forward, and make sure migrations are tested adequately before rolling to a production environment.

One thing I like about this approach is that I can deploy a new version of my application using azure app service for containers, and when the new instance is swapped in and it has pending migrations, anyone currently using the app will now get presented with a UI that says an upgrade is in progress. Once its applied they can use the app again. Contrast this to upgrading the database during the deployment: if the database is upgraded prior to the new version of the app being swapped in, then current users will be using an older version of the app whilst its database is being upgraded to a newer version from under it - which could introduce errors. If the database is upgraded by the deployment process after the newer version of the app is swapped in, then unless the application is taken fully down until the db upgrade is complete then users will be potentially using a new version of the app before the db upgrade is complete which again can cause errors. With my approach, the app is not taken fully offline which allows me to provide a nicer experience for end users whilst the upgrade is taking place. Likewise if there are mvc methods that I know have no dependency on the database I can optionally keep them functional during the upgrade by excluding them from the action filter.

Basically unless EF migrations can be committed in a transaction and the corresponding version of the app swapped in to replace the old version in a single atomic operation, then you've got the potential for version mismatch for some period of time. I dont think the above is possible with app service for containers but if anyone knows different please do share!

[ajcvickers]

@dazinator Thanks for sharing that. There is certainly some interesting things there--we talked about going in this kind of direction before, but robustness has always been a concern. If you do create a package I would be interested in taking a look.

[syndicatedshannon]

This product is pretty strong with this latest 3.0 release. It's great to see, since we decided not to bail on it during the rewrite.

I'm disheartened, though, that this capability is slated for 5.0, but still very little attention is being given to contract-first, conceptual model-first, database-first, or SSDT/.SQLPROJ-authoritative development. Is it just that there are insufficient requests for it, or is EF not intended for that market?

The semantics of up/down are great for early development, and I appreciate that they pull a lot of teams into EF during envisioning stages. Also, the syntax of EF's C# attribute-modelled and fluent definition are well-designed. Even so, this leaves a lot of gaps in both relational and constraint capability and support for collaboration with data designers. SQL code embedded as text strings in fluent definitions (without even schema validation), to me, means something is wrong. Which team writes that code? The team I have to allocate with both expert level C# lambda AND T-SQL background?

I know this all reveals my personal stance on code-first data development. The point is, I wonder where this feature is heading. Who needs to do this; who needs DACPAC-like deployment but wouldn't be better suited to getting SQL schema definitions out of C# and into source-controlled SQL, or a contract-first toolset?

Thanks for listening.