Introducing DevOps-friendly EF Core Migration Bundles

Jeremy

Today, the EF Core team would like to introduce you to a new feature that shipped in our latest preview release: migration bundles.

Business applications evolve with time. The changes are reflected in your code, your schema, and your data. Updates to code can often be deployed simply by replacing binary files or pointing the load balancer to new nodes. On the other hand, updates that involve changes to the database are inevitably more complex. Not only do the code and data need to remain in synch, but alterations to schema definitions can cause side effects that ripple across tables. The data in existing columns may need to be transformed or even transferred while the changes are applied, and modern applications are expected to run with minimal downtime.

A major benefit of using EF Core is the ability to manage schema changes through a mechanism called migrations. A migration is essentially a mini-snapshot of a “point-in-time” of your database model. After making modifications to your model that may change the schema, you can use the EF Core Command Line Interface (CLI) to capture the snapshot in a migration. The same tool can then be used to point a migration at an existing database and deploy the data definition language (DDL) necessary to bring the database in sync with the model.

Introducing DevOps-friendly EF Core Migration Bundles

Migrations must be able to see the database model to work. When you create or apply a migration, the projects containing the migrations and the DbContext definition are compiled and inspected. At a high level, migrations function in the following way:

  • When a data model change is introduced, the developer uses EF Core tools to add a corresponding migration describing the updates necessary to keep the database schema in sync. EF Core compares the current model against a snapshot of the old model to determine the differences and generates migration source files; the files can be tracked in your project’s source control like any other source file.
  • Once a new migration has been generated, it can be applied to a database in various ways. EF Core records all applied migrations in a special history table, allowing it to know which migrations have been applied and which haven’t.

Until now, there have traditionally been three ways developers apply migrations when new versions of their software are deployed.

Scripting

One approach is to generate SQL scripts from the migrations using the EF Core tools. The benefit of this is that the script can be inspected and modified as necessary prior to deployment. The scripts are pure SQL and can be managed and deployed independently of EF Core, whether via an automated process or the manual intervention of a database administrator (DBA). By default, scripts are specific to the migration from which they were generated and assume you have applied previous changes. They must be run in sequence or the scripts may fail and produce unexpected side effects.

A second option is to generate idempotent scripts. These are scripts that check for the existing version and apply multiple migrations as needed to make the database current. There are tradeoffs to both approaches, but the idempotent option is the safest approach to have a one-size-fits-all script.

Command line interface (CLI)

It is possible to deploy updates directly using the command line tool. This comes with risk because the changes are deployed immediately without giving you the opportunity to inspect the generated migrations. This also requires the tool dependencies (.NET SDK, your source code to compile the model and the tool itself) to be installed on the production servers.

Application startup

It is possible to run migrations as part of your application by calling the Database.Migrate() method. Although this may work, the approach is problematic. In distributed systems, if multiple nodes start at the same time, they may conflict with each other and try to upgrade simultaneously or migrate against a partially updated database. Modifying the schema requires the application to have elevated permissions, but granting those permissions is a security risk. As with the CLI approach, there is no opportunity to review the SQL prior to applying it.

Introducing migration bundles

Scripting remains a viable option for migrations. For those who choose the code approach, and to mitigate some of the risks associated with the command line and application startup approaches, the EF Core team is happy to announce the preview availability of migration bundles in EF Core 6.0 Preview 7. A migration bundle performs the same actions as the command line interface:

dotnet ef database update

The migration bundle is a self-contained executable with everything needed to run a migration. It accepts the connection string as a parameter. It is intended to be an artifact used in continouous deployment that works with all the major tools (Docker, SSH, PowerShell, etc.). It doesn’t require you to copy source code or install the .NET SDK (only the runtime) and can be integrated as a deployment step in your DevOps pipeline. This also decouples the migration activity from your main application so there are no concerns about race conditions and no need to elevate the permissions of your main app. The vision is to have first class building blocks that use migration bundles available in DevOps toolsets like Visual Studio and GitHub Actions. For now, we provide the bundle and you are responsible to run it.

Get started

To work with bundles, you’ll need at least the preview 7 version of tools. You can install it using this command:

dotnet tool install --global dotnet-ef --version 6.0.0-preview.7.21378.4

Do you already have the tool installed? No problem! Simply replace install with update to upgrade:

dotnet tool update --global dotnet-ef --version 6.0.0-preview.7.21378.4

From the command line, in the working directory of your project that contains the EF Core migrations, use:

dotnet ef migrations bundle

To generate the bundle. If you prefer the Visual Studio Package Manager Console, run this command:

Bundle-Migration

Either option will produce an artifact named bundle (i.e., bundle.exe on Windows machines).

By default, the bundle will look for an appSettings.json to find the connection string. Simply run:

./bundle.exe

To deploy your migrations. If you prefer to use an environment variable instead, simply pass the connection string using the --connection switch. For example:

./bundle --connection {$ENVVARWITHCONNECTION}

Obviously (we hope), it makes sense to deploy the bundle as part of your staging and test process prior to applying it to production.

Note: Migrations are EF Core’s “best guess” about the changes required based on the schema and shape of the model. You should always inspect the migration before applying it and update/customize as needed. For example, if you decide to split a Name column to FirstName and LastName, EF Core will generate the changes to drop one column and add the other two, but you will need to customize the migration to populate the new columns with data from the old ones.

Your feedback is important!

There is still work to do with bundles and we need your help. There are two main ways you can contribute to this feature:

  1. Visit the migration bundles issue to share feature requests and design needs so that we can incorporate the features you need to be successful.
  2. Try out the bundles and file any issues that you may encounter so we can address them before the final release.

Thank you for your continued support and we look forward to your feedback!

Sincerely,

Jeremy Likness on behalf of the EF Core team.

22 comments

Leave a comment

  • Richard Deeming

    Nice. Sounds vaguely similar to what I’ve been doing manually since EF6 – have a separate console application project which contains the migrations, and connects as a user with permission to create/modify the database structure, so that the main app doesn’t need those permissions, or any of the migrations.

    But from the name, I was hoping you’d managed to come up with a clever solution for the issue of multiple devs working on the model at the same time, where we need to merge migrations from multiple branches into the main branch. Looks like that’s still out of reach. 🙂

      • Richard Deeming

        Thanks for the link. As far as I can see, the conclusion seems to be, “you can’t” – or at least not without jumping through a series of burning hoops whilst juggling a collection of chainsaws. 🙂

        It was tricky enough updating my code to move from EF6 migrations to EF Core migrations, since it had to support both new and existing databases. I was hoping someone might have come up with a clever solution for the team migrations problem by now.

      • Razvan Goga

        So how would this work? The bundle.exe would come with all the needed dependencies based on what the DBContext assembly uses?

        Genuinely interested in this as I wrote my own exe to be able to apply the ef migration generated sql script to a postgres db (since AzureDevops has no task for Postgres similar to the sql server one.

        The bundle.exe would be a nice out-of-the-box solution for this

      • Gábor Szabó

        I had a similar question, as usually we can put the connection string in the appsettings.{environmentName}.json file (if it doesn’t contain the password, eg. pass-through auth to SQL Server). Environment-specific json files are picked up by the ASP.NET Core runtime by default, so it would be nice to either support this by default or let us configure it when generating the bundle. Pulling the connection string up from where it’s stored when executing the bundle is not always a viable way (it might get logged, for example).

        With some PowerShell scripting I can easily extract the connection string and pass it as parameter to the bundle, but it would be nice to see this supported.

        Thanks for the great work as always!

  • mu88

    That sounds pretty interesting and maybe it would solve one of my current tasks 😉 we have an EF Core 3.1 project with a lot of migrations. Now I’d like to apply these migrations to a SQL Server running inside Docker for our integration tests. The tests itself cannot run within Docker, but the SQL Server can.
    Is it possible to use Migrations Bundles to apply our migrations to SQL Server? If yes, can you give a hint how the Dockerfile would look like?

    • Razvan Goga

      You can do this without the bundles (i’m doing this in my current project).
      You can apply the migrations from code on app startup against a blank docker sql server container.
      For this the app should know that it’s running in intergrations test mode (we have a 4th environment for this along side Develop / Staging / Release).
      You can then run them easily in an Azure Devops pipeline by using Service containers

      • mu88

        Thank you, but that’s exactly what I don’t want to do 😉 with a lot of migrations and EF Core 3.1, applying the migrations on startup takes about half a minute. This is way too long.

  • Marcel

    Great work team! Speaking about the various obstacles involved in migrating data in the article above, are there any plans on the roadmap of the migration bundles approach to tackle the data migrations part of the workflow over just schema migrations? (ie: your Name >> FirstName/LastName example above)

    For example, in the Rails world there are schema migrations and rake tasks that can be run for any one-off processing such as performing data migrations. There is 3rd party gem (Data Migrate) that standardizes this into a single unit.

    It would be super useful to have a first-party solution for this from the EF team. There are multiple complications it would have to deal with, such as the inability to use your C# POCO models to access both the old and new version of the schema, so you have to likely operate using dynamic structures/lookups.

    Realm has a great example (almost an identical FirstName/LastName >> Name one) in their docs about their migration facility (see the ‘Updating values’ section). You can see they also use a dynamic facility to support it:

    let firstName = oldObject!["firstName"] as! String
    let lastName = oldObject!["lastName"] as! String
    newObject!["fullName"] = "\(firstName) \(lastName)"

    If there is an active issue already on this, please link it here as I think it’s a fantastic time to get the ball rolling on the discussion!

  • Gerard Alberts

    At first I thought ‘nice’, but this feature already exists for a long time, right?
    We’re using it in a devops build pipeline like this:

    dotnet ef migrations script -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%/migrate.sql –project %BUILD_REPOSITORY_LOCALPATH%/MyProject.Dal.csproj –startup-project %MyProject.Website.csproj –context MyProject.Dal.MyDbContext –configuration $(BuildConfiguration) –no-build –idempotent

  • Stuart Ballard

    Seems like it ought to be possible – at least for simple scenarios – to skip the whole idea of a series of migrations and just have the bundle include the desired schema needed, or even use the application DLLs to figure it out from the model classes. Then it could examine the existing schema in the target database and add/remove tables, columns, indexes etc as needed, with no need to keep track of migrations for all the intermediate states along the way. I’ve been working with an ORM tool I created myself over quite a few years that did this with only me developing it in my spare time, so it can definitely be done. Is there some reason this isn’t feasible for EF?

    • Jeremy LiknessMicrosoft employee

      For simple scenarios it’s probably fine. It’s more nuanced when you have data changes involved – setting defaults, migrating existing columns, etc. It’s not intended to be a fully automated process but to allow for the nuanced customizations needed for your specific needs.

      • Stuart Ballard

        What I meant was – as far as I can see, EF’s migrations support is only built for the more complex scenarios, and doesn’t seem to provide the simple option I outlined in my original comment. That is – let the code define the model, and don’t do anything else, because the framework will automatically look at the existing database and make whatever changes are necessary to the schema, without any need to define explicit migrations from any previous states.

        Obviously you can’t support complicated situations this way and there would need to be a way to fine tune the process – I needed to add support for custom steps in my own tool as well. But it would eliminate a lot of work if you didn’t have to explicitly build migrations or be aware of all the possible previous states the DB could be in.

  • Dorin Potorac

    I can’t get this to work in Azure DevOps. My yaml config is:

    pool:
      vmImage: ubuntu-latest
    
    variables:
    - group: PROD
    - name: buildConfiguration
      value: 'Release'
    - name: dotNetFramework
      value: 'net6.0'
    - name: dotNetVersion
      value: '6.0.x'
    - name: targetRuntime
      value: 'linux-x64'
    
    - task: UseDotNet@2
      inputs:
        version: $(dotNetVersion)
        includePreviewVersions: true
    - script: dotnet build --configuration $(buildConfiguration)
    - task: CmdLine@2
      displayName: 'Execute EF Migrations 6.22'
      inputs:
        script: |
          dotnet new tool-manifest
          dotnet tool install --global dotnet-ef --version 6.0.0-rc.1.21452.10
          dotnet tool update --global dotnet-ef --version 6.0.0-rc.1.21452.10
          dotnet ef migrations bundle
        workingDirectory: '$(System.DefaultWorkingDirectory)/BackEnd'

    The error is:

    # /usr/bin/bash --noprofile --norc /home/vsts/work/_temp/32(...).sh
    # Creating this template will make changes to existing files:
    #   Overwrite   ./.config/dotnet-tools.json
    # Rerun the command and pass --force to accept and create.
    # You can invoke the tool using the following command: dotnet-ef
    # Tool 'dotnet-ef' (version '6.0.0-rc.1.21452.10') was successfully installed.
    # Tool 'dotnet-ef' was reinstalled with the latest stable version (version '6.0.0-rc.1.21452.10').
    # Run "dotnet tool restore" to make the "dotnet-ef" command available.
    # ##[error]Bash exited with code '1'.

    Any idea about what’s wrong here?