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.
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
Namecolumn toFirstNameandLastName, 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:
- Visit the migration bundles issue to share feature requests and design needs so that we can incorporate the features you need to be successful.
- 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.
Our team use MSSQL with SSDT project. When we release new application version DACPAC is applied to database to do some changes (create tables, columns, some data manipulation, ...). We use SSDT because we use stored procedures. First problem of database deployment is that development process needs to be standardized and all members should follow it. In our team there are several members which deploy stored procedure directly instead of part of deployment process because...
I can't get this to work in Azure DevOps. My yaml config is:
<code>
The error is:
<code>
Any idea about what's wrong here?
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 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.
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...
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
No, bundles are new. Scripting has been around and is covered in the “Scripting” section of the blog post.
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...
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...
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
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.
Is it possible bundle read database configuration from another location, instead appsettings.json ?
You can pass the connection string as a command line parameter.
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...
Will this work with 3rd party DBs (eg: Postgres)?
Bundles will work with any providers that support migrations. So, PostgreSQL is a yes.
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
Does it support transactions? By transaction, I mean executing multiple migrations in a transactional way.
Sounds good. Great work!