Migrations Bundles

[bricelam]

We currently have dotnet ef migrations script and database update to apply migrations. Unfortunately, they each have their shortcomings.

migrations script is pretty good at producing SQL scripts (there are some known issues with the scripts), but figuring out what to do with those scripts may not be obvious. Publish from Visual Studio wraps these scripts up in a web deployment package and uses MSDeploy to run them on the server. This works well for SQL Server on Windows, but nothing else.

database update is a lot better at applying migrations. It doesn't have any of the issues the SQL script does. Unfortunately, it requires the .NET Core SDK, your app's source code, and a direct connection to the production database. Having all of these things available where you need them is't always possible.

I propose we introduce a new migrations bundle command. This would create a self-contained EXE that you could use to apply migrations. It would behave the same as database update and let you specify the connection string. This would make SSH/Docker/PowerShell deployment a lot easier. You wouldn't need to copy your source files to a server inside the firewall and you wouldn't need to install the .NET Core SDK. Here is an example of what it might look like to use SSH:

dotnet ef migrations bundle
scp bundle [email protected]:./
ssh [email protected] "./bundle --connectionString ${DEPLOYMENT_CONNECTION_STRING}"
ssh [email protected] "rm bundle"

Taking this further, Visual Studio could do all of this for you like they do today on MSDeploy but when you're deploying to Docker. They could even create a new SSH deployment experience.

---

TODO

• Add --output to allow renaming the bundle
• Match TargetFramework, RuntimeIdentifier, and SelfContained of startup project by default
  • Add --no-self-contained
• --self-contained doesn't work

  error NETSDK1031: It is not supported to build or publish a self-contained application without specifying a RuntimeIdentifier. You must either specify a RuntimeIdentifier or set SelfContained to false.

• --runtime doesn't work between Windows and other OSes. We look for *.exe based on the current runtime instead of the target runtime.
• Add automated tests
• Add --force to overwrite an existing bundle [BUNDLES] -Force option is missing #25271
• Refine command output [BUNDLES] Suggestion to show the full path of the bundle .exe file #25274
• --configuration and --runtime optoins are repeated in help

[tibitoth]

@bricelam Why not use dacpac? I have not seen any better approach for MSSQL.

We try to embrace sqlproj (in this sense it's DB first) + reverse engineer code first on every change + dacpac migrations.

[bricelam]

dacpac is perfect for DB-first; keep using it there. However, it's fundamentally different from Migrations which are designed for a code-first workflow. Also, dacpac is SQL Server-only and a big part of these bundles is to improve the experience when using other databases like PostgreSQL, MySQL, and Oracle.

Fun fact, the first prototype of Migrations was based on dacpac.

[ErikEJ]

FWIW, "EF Core Power Tools" supports Reverse Engineering directly from a dacpac / SQL Database project (.sqlproj)

[leealexander]

migrations script is pretty good at producing SQL scripts (there are some known issues with the scripts)

Could you elaborate on the issues?

database update is a lot better at applying migrations.

In what way(s) is update better?

I take it the SQL isn't the same if I was to look at trace logs of the two different update methods?

[bricelam]

The SQL is the same, the differences (and issues) stem from things like when transactions begin and end, the if blocks (some statements aren’t allowed inside), and how the script is parsed (some objects are created at the beginning of the script but the parser/compiler errors because later statements reference them.

[bricelam]

Items to address issues with the script: #7681 #12911 #14746 #19740

[ajcvickers]

Team note: make sure to consider how bundles interact with continuous deployment systems. See #9841.

[ajcvickers]

Unfortunately, while we have made good progress in understanding the space here, we are deferring release of the migrations bundles feature until after the EF Core 5.0 release to provide more time for feedback. The current plan is to get something into preview near the beginning of the EF Core 6.0 milestone so that we have enough time for feedback, integration, and testing before committing.

[ivanmonteiro]

I'm not sure if it is the right place, but here are my considerations:
It would be interesting if the migrations bundle could be built and run on both Windows/Linux platforms.

Currently I'm developing using a Windows machine and deploying to a Linux server. Also, my CI workflow uses Linux machines under the covers. So being able to choose the target platform would be great.

[bricelam]

@sabbadino bundles currently work the same way that dotnet ef database update does. It needs to build your application service provider in order to discover and instantiate the DbContext. It does this by calling Program.CreateHostBuilder. So every time you run the bundle, that method is executed.

[sabbadino]

So in opinion that is totally useless.. when i build i want to make a single bundle that will evaluate what updates to apply at deploy time

If i got it correctly, the way it works is

1. i should make a bundle for each db i want to target (cicd, test, prd)
2. build agent must reach any of the db of the different environments.

Not a real world situation

[pinkfloydx33]

It builds an Exe that you pass a connection string to and it evaluates what migrations are missing and applies them. It can also rollback or only migrate to a specific migration (via commandline).

I build a bundle in CI and attach the single executable as an artifact which I then use during CD without having to build anything again. Not sure what you're talking about. They work as you expect them to

[julielerman]

Also, FWIW, the migration file is idempotent...it includes all the migrations and then based on that evaluation @pinkfloydx33 explained, executes what's needed

[ivanmonteiro]

1. i should make a bundle for each db i want to target (cicd, test, prd)

No. Ideally you build the bundle only once. Then you can pass to the bundle.exe a connection string or it gets the connection string from the appsettings.{Environment}.json file.

2. build agent must reach any of the db of the different environments.

Also, from my experience with the migrations bundle, when building the migrations bundle no connection to the database is needed in order to create the bundle.exe.
Now, the bundle.exe needs to connect to the database in order to apply the migrations, of course.
As mentioned by @julielerman you can generate a migration script (dotnet ef migrations script --idempotent) instead of the bundle if it suits your needs and just run that sql file in ci/cd or manually.

@sabbadino Here is the documentation on how to apply migrations:
https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying?tabs=dotnet-core-cli

[sabbadino]

Hi, i Will double check .. if the problem shows up actually, i will post the call stack

[chinthakaherath]

Is there any guide or document on how to run efbundle.exe in the azure release pipeline?

[SirMrJames]

Is there any guide or document on how to run efbundle.exe in the azure release pipeline?

I'm having the same issue, did you ever figure that out?

[ErikEJ]

Just run a script with the command line in your yaml pipeline:

- pwsh:  .\efbundle.exe --connection 'Data Source=(local)\MSSQLSERVER;Initial Catalog=Blogging;User ID=myUsername;Password=myPassword'
  failOnStderr:  true

[SirMrJames]

Just run a script with the command line in your yaml pipeline:

- pwsh:  .\efbundle.exe --connection 'Data Source=(local)\MSSQLSERVER;Initial Catalog=Blogging;User ID=myUsername;Password=myPassword'
  failOnStderr:  true

Perhaps I'm doing it wrong.
I've built an artifact for the efbundle, and whenever I try to run it in a release pipeline using powershell nothing happens. No error, but no migrations are applied.

Interestingly, if I don't publish it as an artifact, and instead run it in the pipeline that I create the bundle it works fine. So I suppose the issue is with the artifact. But I'm not sure.

[fredriksjoholm]

Is there any way to run bundles in a distributed settings while preventing two bundles from applying changes to the database simultaneously? What I'm trying to do is to apply my migrations from an init container in a Kubernetes cluster. I've looked into using Kubernetes jobs in order to ensure that each bundle only runs once, but this approach seems to have its own drawbacks as well.

[roji]

@fredriksjoholm preventing concurrent migrations isn't something that EF does - you'll have to take care of it outside, where you're applying the migrations (e.g. in Kubernetes). Note that in general, applying migrations is seen as part of deploying a new version of your application; this is already a process that should happen once and in a controlled fashion (you don't want to have two different application deployments racing concurrently!).

For context, EF could try to make sure that migrations aren't executed concurrently by e.g. taking a lock on the database's migrations history table. The main problem with that is that migrations may involve multiple transactions (e.g. since SQL Server doesn't support various DDL statements in migrations), and so the lock would be released in the middle of the transaction. But once again, even regardless of the technical difficulties, this is something that should be handled at a higher level, in your deployment pipeline.

[phillebaba]

Maybe I can add some more context to the question. How would one solve this type of migration when applications are deployed with a tool like FluxCD or ArgoCD. In these situations there is not "CD Pipeline" to speak of that could run the DB migration before the actual applications are deployed. Instead new manifests are applied to the cluster as new commits are reconciled. In these situation the only real option is to run the migration inside of the cluster.

Even in a situation where a migration was done in a CD pipeline there are no guarantees that the CD pipeline is going to run once. A couple of providers will allow triggering of multiple pipelines for the same commit, and most others will default to running the same pipeline in parallel for different commits. So it seems that the risk concurrency risk is present no mater where it is run.

My guess is that there are a lot of EF users out there who are in a similar situation, deploying their .NET applications with a GitOps tool into a Kubernetes cluster. Right now if you google "kuberentes entity framework" the first result you get is this blog post. I would think that it would be preferable to maybe right an official guide for how one would solve this instead of hoping that the first result gives the correct answer. I would be happy to contribute with my Flux knowledge if there is interest in writing such a guide.

[roji]

Even in a situation where a migration was done in a CD pipeline there are no guarantees that the CD pipeline is going to run once. A couple of providers will allow triggering of multiple pipelines for the same commit, and most others will default to running the same pipeline in parallel for different commits. So it seems that the risk concurrency risk is present no mater where it is run.

I may be misunderstanding you, but if multiple deployments really are executing concurrently, wouldn't that mean that e.g. an actual production server might end up with mixed application files from two versions, as files are being copied from both onto it?
It's hard for me to see how this could be workable: the actual act of copying the files to production - and executing migrations - seems like it must be protected by some sort of concurrency control seems necessary at the deployment stage.

But maybe I'm misunderstanding what you're describing - if so, more detail/context could help.

[...] I would think that it would be preferable to maybe right an official guide for how one would solve this instead of hoping that the first result gives the correct answer. [...]

I don't personally think the official EF documentation needs to address all possible scenarios with all possible technologies - there are many cluster and deployment technologies out there, and practically speaking we couldn't cover them all (nor would we be quailfied to do so). I don't see the problem with external documentation and blogs addressing scenarios such as this, especially since there's lots of diversity in exactly how people configure their clusters and deployment...

[phillebaba]

@roji I guess its difficult to explain without digging too deep into Kubernetes. I do agree with you that attempting to document every single deployment solution is not feasible. The issue is that the recommended solution would not work at all for other deployment solutions. Would a compromise be to collect a set of resources to point end users to known working solutions for different platforms?

[OskarKlintrot]

@phillebaba I don't understand how this has anything to do with EF really? Isn't it down to each CI/CD-solution to document? There're more tools than just EF bundle that you only want to run once pre- or post-deployment so it would make more sense if the CI/CD-provider documents how to do it rather than each individual tool. As an example, if I deploy db-changes (which there is a plethora of tools to choose from, DbUp is quite popular) I would read in the Azure DevOps or GitHub Actions (my providers) docs to understand how I would make sure that if one deployment is already running then the next in line waits for that one to finish or get cancelled before the next deployment runs. I don't see why I would look in the EF documentation for how to use GitHub Actions (as an example)?

[phillebaba]

@OskarKlintrot I think that the issue is that people expect problems such as migration concurrency to be solved in a project, without having to rely on other solutions for this. Additionally the documentation actually takes some sort of opinion, but only to the degree of "here is the keys to the car, but I am not telling you how to drive it".

I guess this is the documentation that states how things should be done with CI.

Bundles can be generated as part of your CI process and easily executed later as part of your deployment process.

So if you actually read my initial question instead of just repeating the same response, you would realize that it is a lot more nuanced than just saying solve it with Azure DevOps Pipelines or GitHub Actions. The question is what do you do when you are deploying in a context where these types of pipelines are not easily available. Here the answer seems to be not EFs problem, which is fine but I think it will affect adoption of EF. Then again I am not really a .NET developer so how would I know. All I know is that I get a lot of questions about how this should be solved with EF with no real good answer out there other than that some other tool should solve this.

[roji]

@phillebaba what kind of answer or capability do you think EF could provide to help with this? At the end of the day, EF can only produce a migration bundle or SQL script, but it's entirely up to the user's environment how those will get applied/executed, and from where. Some very concrete suggestions would help understand what you have in mind.

[phillebaba]

Honestly adding a DB lock before running the migration would solve these issues. Technically this could be done by developer beforehand but I do not think that it should be the developers responsibility

There are tools like Liquibase that already do this and work without any issue with init containers in Kubernetes.
https://www.liquibase.com/blog/using-liquibase-in-kubernetes

[roji]

@phillebaba I addressed the issues with that here.

[ElanHasson]

Is there a way to make this work with dotnet pack?

[roji]

@ElanHasson can you provide more context on what you want to make work with dotnet pack?

[ElanHasson]

@ElanHasson can you provide more context on what you want to make work with dotnet pack?

I wrote pack, but I was thinking it should have been publish.

I was curious if dotnet publish could be used to run the bundler instead of the normal behavior.

Just trying to avoid modifying build scripts :)

[ErikEJ]

"Normal behaviour" IS dotnet publish 😄 ErikEJ/EFCorePowerTools#1078 (comment) @ElanHasson

[ElanHasson]

Ahh I see. This is a bit more than I need @ErikEJ but thank you.
I'm using EF only for migrations, in my https://github.com/dotnet/orleans project via web-scheduler/web-scheduler#159

I'll just run dotnet ef bundle in my CI.

I'm just saying it would have been cool to have a publish target that ran it built-in.

[Gary-Elphick-Star]

Are migrations bundles fully supported now? If is, is there an Azure Pipelines deployment task specifically for it (to Azure SQL)?
Currently we use SqlAzureDacpacDeployment@1 to run migrations scripts on some of our environments.

[BenjaminAbt]

@Gary-Elphick-Star due to the authentication (Managed Identity) you have to run them in an Azure CLI task.

- task: AzureCLI@2
  displayName: Azure SQL Schema Deployment
  inputs:
    azureSubscription: ${{ parameters.azureConnectionName }}
    scriptType: pscore
    scriptLocation: inlineScript
    inlineScript: |
      ${{ parameters.sqlServerDatabaseBundlePath}}\${{ parameters.sqlServerDatabaseBundleName }} `
        --connection 'Server=${{ variables.SqlServer }};Database=${{ variables.SqlDatabase }};Authentication=Active Directory Default'

They work great.
There is just no documentation how to run them on a Linux Agent.

[Gary-Elphick-Star]

@BenjaminAbt
Thank you for the info and even more so for how quick you replied :-).

[Gary-Elphick-Star]

To follow that, I've just seen we were using the wrong authentication type on our SqlAzureDacpacDeployment@1 tasks, which it looks like we link to AAD. So we might stick to that for a short time as we get used to migrations through the pipelines a little more.
https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/reference/sql-azure-dacpac-deployment-v1?view=azure-pipelines