Efcore: Improve experience deploying databases created by Migrations

[ajcvickers]

This an "epic" issue for the theme of improving the Migrations and deployment experience. Specific pieces of work will be tracked by linked issues.

---

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.

---

• [ ] Migrations Bundles #19693
• [ ] EF Core 2 Migrations in VSTS CD Release #9841
• [ ] dotnet ef migrations script with SDK not on path failing #14225
• [ ] Update-Database in VS' Package Manager Console is very slow on long scripted migrations #16858
• [ ] Update DatabaseErrorPageMiddleware experience to promote migration bundles #20402
• [ ] Generate the script for all migrations that have not yet been applied to a given database #21995
• [ ] SqlServer Migrations: Scripts do not fail on failure #22613
• [ ] Stop all migrations and roll back everything on failure #22616

[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:

https://github.com/dotnet/efcore/issues/329
https://github.com/dotnet/efcore/issues/7681
https://github.com/dotnet/efcore/issues/8695
https://github.com/dotnet/efcore/issues/9841
https://github.com/dotnet/efcore/issues/10750
https://github.com/dotnet/efcore/issues/12911
https://github.com/dotnet/efcore/issues/14349
https://github.com/dotnet/efcore/issues/14531
https://github.com/dotnet/efcore/issues/14746
https://github.com/dotnet/efcore/issues/15174
https://github.com/dotnet/efcore/issues/16175
https://github.com/dotnet/efcore/issues/16406
https://github.com/dotnet/efcore/issues/18624
https://github.com/dotnet/efcore/issues/18840

19693

19694

19740

https://github.com/dotnet/efcore/issues/22613
https://github.com/dotnet/efcore/issues/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.

[ErikEJ]

@syndicatedshannon I assume you are aware that EF Core Power Tools supports exactly that workflow (database first from a .dacpac/ .sqlproj)

[syndicatedshannon]

@ErikEJ - Thank you. Our team has visited that tool often in the past, starting when you first suggested it years ago. We had various challenges along the way. At last visit, I believe we did not see how to make it work for us to get schema revisions applied to our models as part of our toolchain. My recollection is there is an effortful process to replace the model each time, even if we weren't concerned about losing model customizations. Since then we purchased LLBLGen, but I would love to know if I'm missing something.

[syndicatedshannon]

@ErikEJ If you think I'm mistaken, and it does indeed support exactly this workflow, please LMK. Would love to hear I've missed something.

[ErikEJ]

@syndicatedshannon The workflow you describe works fine for me with EF Core Power Tools. You can extend the generated POCO classes with NonMapped properties, as these classes are partial, and you can modify the DbContext model by implementing the partial OnModelCreatingPartial method in a partial DbContext class. But of course if you start treating your DAL as a Domain model, things could start falling apart. Please let me know what is blocking you from adpoting this, or if anything I wrote is unclear :-)

[syndicatedshannon]

Thank you Erik, I will try to understand, and follow up on #4321 when I have a handle on it, referencing your name. Apologize to others for cluttering this topic.

[GeeWee]

Just want to chime in on this, as I've been researching this a bit.
It seems like most of the stuff re: parallel migrations can be solved by taking out exclusive locks.

I can see rails does it with advisory locks for postgres and mysql, FluentMigrator and Flyway does it with sp_getapplock for SQL Server, etc.

It seems that using the database locking can mitigate running migrations in parallel, thus making it safe to run on Startup. I'm not sure if this is true for all databases, but it seems to be for the big ones.

However, this of course only deals with half of the issue, because e.g. for rolling updates there's still an issue if some apps expect a different schema. Of course you can get around this by planning your migrations and releases well.

[ondrej-marinak]

Feedback regarding migrations and deployment experience

Setup:

• Multi-stage CI/CD pipeline
• Build agent on Ubuntu 18.04
• DB server: MariaDB
• multitenancy in the near future (db per tenant)

Recently, I spent a lot of time trying to solve the migrations and deployment case in the Azure Pipelines. I’ve tried to solve a straight forward case i.e. to update a database during the deployment stage.

1. My first idea was to split the whole into two stages, build and deploy.

   • Build stage: generate migration script usingdotnet ef migrations script --output script.sql --idempotent
   • Deployment stage should download the artifact (sql script) and execute it.

   Problem: It’s not possible to execute the script using dotnet ef or using some extensions. The extensions that I tried didn't work. There are probably some extension but are working well only with MS SQL. The solution could be to install db specific executor on the agent machine.

2. The second approach I tried is to migrate directly from assemblies

   • Build stage: nothing to do here
   • Deployment stage: execute dotnet exec --depsfile [path] --runtimeconfig [path] [ef path] etc.

   Problems:

   • Command to execute is not that simple
   • Need to know the path to ef.dll
   • Error saying that Microsoft.EntityFrameworkCore.Design nuget package is not found even if it was referenced in the startup project explicitly

   I also abandoned this approach because I was constantly stumbling upon new hurdles.

3. The third approach, unfortunately, needs to pull and build source code once again in the deployment stage, on the other hand, it is just dotnet ef database update command. I stuck with this approach for now since it's the easiest one and doesn't require a lot of plumbing, I don't like it, though.

[JonPSmith]

Can I add one issue I have had when updating a database with migrations that might be added in a CI/CD solution.

With a client an update to EF Core 3 required some lambda properties to be added to the database. The classic example is adding a FullName string property to replace the lambda property that created the FullName from the FirstName and LastName.

The migration was easy, but updating the thousands of rows to have the correct FullName was a problem. My client's system used 'migrate on startup' and I added code to run code when the specific migration was applied. This code then filled in the new FullName from the FirstName and LastName columns. The problem was Azure WebApp timed out because the ASP.NET Core app took too long to start.

If the CI/CD migration could call code to run after the migration and provide names of migration just applied to the database, then this would handle this sort of issue.

[joaopgrassi]

The migration was easy, but updating the thousands of rows to have the correct FullName was a problem. My client's system used 'migrate on startup' and I added code to run code when the specific migration was applied. This code then filled in the new FullName from the FirstName and LastName columns. The problem was Azure WebApp timed out because the ASP.NET Core app took too long to start.

I think doing this at startup blocking is not very good.. maybe you could create a HostedService and do it in the background so the main app is not blocked. Not sure this is a migration or even CI/CD problem.

[JonPSmith]

Hi @joaopgrassi,

I did consider using a BackgroundService, but that would mean that the application would start with inaccurate data. I deemed that was not a good idea.

The other option was IHostedService, which runs before the main ASP.NET Core is run (since version 3.0). But I thought that Azure would still time out because it wasn't responding to HTTP requests (I didn't try that - its pretty hard to try these things as you have to reset the database every time).

[vzakanj]

@ondrej-marinak we are doing it using the first approach you've listed, only with PostgreSQL.
It boils down to installing PostgreSQL command line tools on the agent and caching it with the Azure DevOps cache task so we don't have to install every time the build runs.

1. use psql to query the _EFMigrationsHistory in the db to get the last migration applied for the environment
2. generate idempotent script from the last applied migration to the latest one
3. after all builds are completed, apply the script with psql again

All of the above are done with PowerShell scripts, but I'd like to see this built into dotnet ef tooling. e.g.

1. not having to manually install psql and query _EfMigrationsHistory + pass the migration as a parameter to dotnet ef migrations script
2. possibly running the generated script with a separate ef command

[nick5454]

@ondrej-marinak I create the ef script in the build pipeline(Cake.DotNetCoreEf) and use the Sql Deploy task in the release and it works fine. The issue I have that if my file has a lot of schema changes it completely fails. Looking to see if I can create a DacPac from a migration after building.

Tried putting a log of GO's in the script and it still fails. Thinking about using the "list" command and somehow doing a foreach on all the migrations. That way in the release I can run all of them and the -idenpotent will decide if it should run individually.

[ajcvickers]

Unfortunately, while we have made good progress in understanding the space here, we are scoping this issue for the 5.0 release. The migrations bundles feature (#19693) will be deferred until after the EF Core 5.0 release. However, several other targeted improvements related to migrations will be included in EF Core 5.0, including:

• SQLite table rebuilds (#329) (Done)
• Better migrations transaction support (#7681)
• Pass the database connection string on the command line (#10750) (Done)
• Better idempotent scripts for SQL Server (#12911) and other providers (#14746)

[JonPSmith]

Can I ask if this might get into a later EF Core 5 release, say 5.1, or will it move to EF Core 6. I only ask this question to know whether I might include information about this in the book that I am updating.

An answer of "don't know" is fine, and in that case I won't mention it in the EF Core 5 update.

[ajcvickers]

@JonPSmith Currently there are no plans for a release between EF Core 5.0 and EF Core 6.0. We're aligned to the yearly cadence of .NET.

[JonPSmith]

Thanks.

Great work on EF Core 5. I will look forward to this feature in the future.

[alrz]

I didn't find this mentioned but it would be nice to also improve version control experience.

Currently if more than one dev add migrations the second dev usually needs to redo changes.

[Saibamen]

@alrz: +1 for this