Entity Framework Code First Migrations on VSTS

Recently I came across a requirement to deploy a code first project on VSTS (Visual Studio Team Services), the project had Entity Framework running against SQL Server running on Azure.

I have been spending quite a bit of time getting my feet wet in VSTS, as far as I can tell, it is a very powerful tool and I am enjoying my exposure so far. Every time I have been hit with a requirement in release processes or build processes it has been easy enough to get to the solution. The Azure integration is pretty awesome too.

This article assumes that you are comfortable in creating both build and release definitions to build a project using Entity Framework.

After you have completed this walkthrough, the build and release flow should look similar to this:

Project Setup

My example project is going to be a very simple example just to have enough to run the migrations. I have created a single azure function that uses a different assembly as the data access layer, nothing special and by no means an example of how to write a function application.

The context:

public class ExampleContext : DbContext
{
   public ExampleContext() : base("DBConnectionString")
   {
   }
   public DbSet<SimpleModel> SimpleModels { get; set; }
}

One thing to mention here, there are templates and defaults that might suggest using a base constructor call that names the connection string similar to base(“name=DBConnectionString”) this ends up causing some issues later on when it comes to executing the migrations.

The Build

The first thing we need to do is get a build process going for the function application. It will need to perform the steps outlined earlier in the article. Picking the Azure Web template will get you most of the pieces required to quickly get the build going.

Once the build is running, we need to extract the assembly that contains the entity framework migrations as well as the migrate.exe that comes with the entity framework NuGet package.

Copying the assembly out is a simple process, in the example below I am copying out all of the assemblies that exist in the DAL project matching the current build configuration. This could be improved by copying the exact assembly by name instead of blanket copying the directory contents.

I have specifically copied the assembly files to a migration folder, by the end of the process I am aiming to have both the migrate.exe and the assembly in the same artifact so when our release pulls it down, it is in a sensible state to execute.

The next step is copying out the migrate.exe into the migration folder. migrate.exe is pulled down when retrieving the Entity Framework NuGet package is restored so it is a relatively simple step to pull it out of the packages directory as seen below.

The end result should give you a finished artifact that looks like this:

That concludes the build process, the next thing we will look at is the release process where we actually execute the migrations.

The Release

The next step is to actually execute the migrations via the migrate executable that we bundled up earlier. There are a few parameters that I will be using in this release definition, I tend to bring in server credentials from Azure Key Vaults as secret variables which is a great way to have a distinct separation between release administrators and infrastructure owners.

The first step of the release process for me is to set the connection string so that it is available for the build agent. In my case, this connection string is a user has permissions that Entity Framework will not have at runtime. One thing to mention about this connection string approach is that it is most likely possible to do this via certificate access, I struggled in a recent client project to connect to the Azure SQL database by Service Principal while maintaining the ability to add external users from Azure AD. If this isn’t a requirement in your release process, I highly recommend looking at the most recent Microsoft Docs to confirm whether there is a safer authentication than connecting via SQL admin at release time.

Once we have the connection available for the build agent, we are able to go ahead and execute our migrations against the target database as seen below:

In this case, I am using the artifact that I have pulled into the SimpleTest directory but this would be what your artifact folder was assigned when you set the release artifacts up. You can see that I am targetting the migration directory that we created at release time, this folder has all of the required components that we need to execute the migrations; the migrate executable and the migration assembly.

To create our constrained user, I created a small PowerShell script that sits in a deployment repository. I am a big fan of source controlling as much of the release process as possible, sure it is possible to do inline PowerShell scripts but why should the release pipeline fall out of code standards and peer review?

Param(
[Parameter(Mandatory=$True)]
[string]
$dbConnectionString,
[Parameter(Mandatory=$True)]
[string]
$dbUsername,
[Parameter(Mandatory=$True)]
[string]
$dbPassword
)
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($dbConnectionString)
$query = "CREATE USER [$dbUsername]
WITH PASSWORD = '$dbPassword';
ALTER ROLE db_datareader ADD MEMBER [$dbUsername];
ALTER ROLE db_datawriter ADD MEMBER [$dbUsername];
ALTER ROLE db_ddladmin ADD MEMBER [$dbUsername];
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

This is a very simple script, I am sure there is a SQL pro out there that can optimise this query into a single line but it has done the job perfectly for me. I usually have a deployment repository alongside the code repository for a project that contains these scripts and ARM templates, it is useful to structure it that way because it means that updates to the deployment code/process does not mean that the code needs to be deployed.

There are various ways to set the connection strings on your web application. The most popular way that I have seen is to do it via PowerShell, I mostly use the following plugin https://pascalnaber.wordpress.com/2016/07/27/vsts-task-to-deploy-appsettings-and-connectionstrings-to-an-azure-webapp/ which essentially allows you to build up an object of values in the build agent and it translates that object into app settings/connection strings.

Summary

I truly hope that this has helped you out in some way, I spent quite a bit of time trying to automate this process at the beginning of the project. I am a big fan of automating as much as possible at build and release time so that projects end up with as little developer interaction as possible, remember, CI/CD should work for you, not the other way around.

As a further thinking point — I ended up using the connection string mentioned above only at release time, that means that the user that Entity Framework uses at runtime is unable to execute migrations and has a stricter set of permissions. This means that we are able to confirm that the migrations are complete at the point of release giving a much more consistent audit trail for release success and failures.