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.
Deploying databases in your CI/CD pipeline with EF Core Code First
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.
Create a simple model and DBContext
Next let’s create a simple model for our web app to use.
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.
Enable Migrations
Let’s now create our initial migration of our DB Schema
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:
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.