Within the last week, 3 different people have asked me how to deploy their database schemas in their CI/CD pipeline using Entity Framework Core Code First. I figured that’s a good sign to write a full blog post!
When looking at DevOps best practices, my DB schema should be “checked in” to source control right along side my code. This way, my DB schema is versioned right alongside my code. And whenever someone checks in code, I want builds to automatically kick off, unit tests run, and if everything looks good, start automatically deploying my app (code and DB schema) to my different environments. Dev, QA, etc., all the way to Prod with no human intervention except for approval gates. Using EF Core Code First, there are two ways I can achieve this DevOps nirvana. One way is by letting EF automatically deploy my migrations on app startup, and the other is to run my migrations manually from the command line.
Setting up your project for migrations
No matter which way you want to go, if you are doing EF Code-First, you need to make sure migrations are enabled. Let’s get the basic setup out of the way and let’s create a visual studio project using .NET Core with EF Core.
Install Entity Framework Core
Create a .net core web app and add EFCore connecting to a SQLServer database.
- Launch Visual Studio, File > New Project > ASP.NET Core Web Application
- Right click your project and select Manage NuGet Packages
- Click Browse, then search for Microsoft.EntityFrameworkCore.SqlServer, select it and click Install
- Now in your NuGet window, search for Microsoft.EntityFrameworkCore.Tools and install it
Create a simple model and DBContext
Next let’s create a simple model for our web app to use.
- Right click your project and select Add > New Folder and name your new folder Domain
- Right click your Domain folder and click Add > Class
- Create a User class
- Add an Id, FirstName and LastName to the User class.
- Create a DemoContext class under the Domain folder
Register your context with Dependency Injection
In order for us to use our DBContext in this MVC Core app, we need to register the DBContext for dependency injection. We also need to add the connection string to the configuration.
- In you Startup.cs file, add using statement for EntityFrameworkCore
- In your Startup.cs file, add the following code to the ConfigureServices method
- Add “Server=(localdb)\\mssqllocaldb;Database=DemoDB;Trusted_Connection=True;MultipleActiveResultSets=true” as the DefaultConnection to the ConnectionStrings section to appsettings.json
Enable Migrations
Let’s now create our initial migration of our DB Schema
- Open the Package Manager Console window by going to Tools > NuGet Package Manager > Package Manager Console
- in your Package Manager Console, type Add-Migration InitialCreate
Notice how this creates the initial creation migration and puts it under the newly created Migrations folder. Any time your domain object model changes, make sure you run Add-Migration <migration name>
Create your CI/CD pipeline to auto deploy your EFCore migrations
There are two ways to enable this. One is to let EFCore do its thing and just let it auto deploy your migrations on app startup. The other is to manually call the migrations using command line commands.
Let EF Core Code First Auto Deploy Migrations on App Startup
The simplest way of deploying the DB using EF is to just let EF handle everything and automatically migrate any new migrations on app startup. This way, I don’t have to change my build or my release in any way. When new code is built, and then deployed to an environment. The next person hitting the app will cause the app to start, which will then automatically call any migrations that haven’t been called yet.
To do this, let’s add a DemoContext to the parameter list of the Configure method in Startup.cs and then, call context.Database.Migrate()
And……….
That’s it. Now, every time you make a change to your db schema by changing the domain object model, run Add-Migration from the Package Manager Console. This will create migrations which will be added to the Migrations folder. When the new code is checked in, build is kicked off, unit tests are run and if everything looks good, Release Management can pick up those new bits and deploy them into the different environments. Now, when the environment is hit for the first time after deployment, context.Database.Migrate() is called which will run all the migrations that haven’t been run yet in the correct order, and your new DB schemas will automatically be deployed.
Super simple. With just one line of code (context.Database.Migrate()) my DB migrations are totally taken care of for me and I literally don’t have to change anything in my build or release pipeline.
Deploy Migrations Manually in the Release Pipeline
As much as I like deploying code using the above method (did I mention how ridiculously easy it is), there are those that want finer grained control over how their database scripts are created and run. Maybe all DB scripts have to be reviewed by DBA’s. Or maybe they want complete control over what those scripts do. Or maybe they want to take backups and do other stuff before applying schema changes. Whatever the reason, for those that fall in this camp, you can have the build create migration scripts for you via the build. And then when it’s time to deploy, the deployment can run those migration scripts.
Building Migration Scripts
In order to build the migration scripts, we will need to use the EF Tools for Command Line Interface. These tools are provided in Microsoft.EntityFrameworkCore.Tools.DotNet. At the time of writing this blog, the only way to add this into the project is by manually updating the csproj file. I couldn’t add them via the NuGet Package Manager Console or the NuGetPackage Manager GUI.
To add Microsoft.EntityFrameworkCore.Tools.Dotnet manually, do the following:
- Right click your project and select Edit
- Add <DotNetCliToolReference Include=”Microsoft.EntityFrameworkCore.Tools.Dotnet” Version=”1.0.0” /> to the csproj
Now, modify your build by adding a command line task to call dotnet ef migrations script –p <path to your csproj with migrations> -o $(build.artifactstagingdirectory)\migrations\scripts.sql –i
Next add a publish task to publish the generated sql script
This build will now create two artifacts. One for the web app and one for the migration scripts. Next, we can configure the release to use this migration script. In Release Management, I added a Execute Azure Sql task (since I’m hosting my DB as Azure SQL, I can use this task, otherwise, I can use any task that calls my sql script).
In the SQL Script field, make sure you select the sql script that was created by the build. If before deployment you need to do a manual intervention where DBA’s verify the script, you can do that here. Basically, whatever your workflow is for deploying DB’s, you could implement that workflow here.
Conclusion
When looking at DevOps best practices with EF Core Code First and ASP.NET Core, you can either let EF automatically handle the DB migrations on app startup or have the build generate the migration script and during your release, run the migration script. The pros for letting EF handle everything is how simple the process is. You add one line of code on app startup and voila, you are done. The cons for this approach is that the user used by the app for DB connections needs to have admin permissions which increase the attack surface area. In some cases, this might not be a big deal. In others, it can be a total deal breaker. If I had to chose a way, I would recommend having the build generate your migration script and running that script during your release.
Thank you for putting this all together.
I’ve followed your configurations in order to update the build to generate the migrations script. The csproj file contains and I can see that it is restored as part of the Restore step of the build, however, when the command line task runs in order to run the dotnet ef command, I continue to get this error:
No executable found matching command “dotnet-ef”
Any suggestions of what else could be missing?
Ok, I’ve answered my own question, however, it might be worth noting in your article.
The project I needed to run the migrations in was a sub-directory, so I had to modify the Working Folder in the Advanced section of the Command line step, such that I wasn’t at the root of $(Build.SourcesDirectory).
Thanks
Thanks! Great suggestion.
hlattanzio: Could you please provide me more insight into how you resolved it. Screenshots and command examples will help. I am facing the same issue
Awesome blog – I have a few questions though.
If you go with the option of letting the build pipeline run the migration scripts won’t you be faced with a couple of problems?
1. Will the ‘ef migrations script’ command generate scripts for ALL migrations all of the time or will it just do the latest migration
2. When you execute the scripts in this fashion, does it invalidate the fact we’ve recorded which scripts have been run previously and therefore you potentially risk executing old migrations?
Hi Joe
1. The ef migrations script command should only generate you the scripts that you will need to go from one version to another
2. It will only generate the scripts you need so you won’t be running scripts multiple times
@Joe: notice the -i switch in
dotnet eg migration script -icommand. It generates idempotent script, that contains all migrations, but only those migrations will be executed that has not been already applied. It check entries in table [__EFMigrationsHistory] which is populated after each migrations.a quick one:
when ef generates migration Script since it has no idea about SQL azure connection string at the time of generating the script how possibly know about changes need of production database.
I assume we have local database connection string when committing our code
thanks
When using EFCore, it creates a version table in your DB and keeps track which version the DB is at (or which migrations have been run). When running the script, it will only run the correct sequence of migration scripts to get you to whatever version your versions table says you are on, to the most current one.
In other words, the script contains all migrations, but it checks [__EFMigrationsHistory] in the target database do determine, which migrations have to be applied. important is the
-iswitchHello Abel, great presentations on the possibilities of the Azure DevOps projects.
I’ve been running into some of the same problems expressed above but using .net EntityFramework 6.2.0 (not Core).
For one, it looks like context.Database doesn’t have the Migrate method (though I set AutomaticMigrationsEnabled = true; in the Migrations.Configuration method.) Still, it looks like this easy approach isn’t working on the non-Core version even in my local dev environment… still have to run Update-Database.
Also, it would be great to have some walkthrough’s on adding a non-LocalDb database in the DevOps project Release. Looks pretty clear to me at the moment that the basic SampleWebApp doesn’t support LocalDB. I’m hoping the addition of database support will be added soon to the basic templates.
Let me know if there are any good examples of this already. I’ll be researching the current VSTS course offerings.
thanks
Hi Charles,
I’ll write up a blog post for you on using EF (Not EF core). Quick answer, you should be able to do all the same things I talked about in this blog. The exact call is a little different but the same thing exists. I’ve made a note to make sure I talk about connecting to non local db’s. Stay tuned, should have something up in about 2 weeks.
It’s all good Abel. This was a good excuse to learn something about Asp.Net Core (and EntityFrameworkCore). Creating a separate SQL database and applying the connection string was the step I was missing.
On my VSTS Build EFCore Migrations Command I was getting an Error:”No executable found matching command “dotnet-ef””. I already have “Microsoft.EntityFrameworkCore.Tools.DotNet”: “1.1.5” included in my project.json file. Any help will be appreciated
Hi Sujit,
Did you got any solution for this issue. I am facing same problem. Please let me know If you know the solution.
Thanks,
Ankit
Hi Abel,
Great post. I’m keen to try it out but hesitate because of my following scenario:
2 x CI pipelines.
1 is for my asp.net core app
1 is for my core solution as an dotnet pack and nuget push
The app uses the packages from the core solution pack. The datacontext and migrations are within a project inside the core solution so these are deployed separate to the app.
Reading through your blog, the only way I can think to use the CI pipelines would be to follow all the steps within the core package pipeline except for the final sql azure execution step.
The execution step should be added to the app deployment so it can perform the db update in sync with any mode l changes held within the latest package push. What I’m not sure about though is whether or not it’ll be possible for me to add the step for execution of an artefact from another pipeline.
Hoping to try it out and see!
Many Thanks
Ben
Generating script from CI fine but facing issue with cd task, not picking the resources/sqldefaultvalue.sql path
incorrect syntax
BEGIN
..\Resources\SQLDefaultValues.sql;System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089;Windows-1252
END;
can anybody help
Legend! Works really well.
Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
Thank you for this very clear tutorial, I just want to know if someone here could fix this error :
No executable found matching command “dotnet-ef”
Thank you in advance
Question: does
contact.Database.Migrate()reverse the migrations if say, deployed an earlier release? How should this case be handled?Thanks heaps – easy to follow and works like a charm!
I’m getting “Value cannot be null. Parameter name: contentRootPath” when I try to run “dotnet ef migrations add InitialCreate” from command line. What should I do to make it run from command line?