Deploying a code first entity framework database in Azure DevOps

I spent most of Friday banging my head against Azure’s new devops experience, trying to get a database migration set up as part of a web app deployment. The project was a .net core 2.1 web site with an Entity Framework database, and we hit a surprising number of hurdles along the way. Hopefully, this write-up will help others in the same situation save some time.

Our solution, at least for the intents and purposes of this post, is made up of a web app project containing the business logic and a .net standard class library with the EF code first classes (note that this is a separate database project, which most tutorials fail to address).

The first step of setting up the pipeline is creating a build in azure devops:

azure-devops-new-build

We set it up against our source code provider and started out with the “Asp.net core” template – in fact, we did not have to alter any of the defaults for it to work straight out of the box.

Getting the database up and running was another story, however. Articles, tips and tutorials online are a bit outdated, and provide solutions which no longer work or are no longer necessary (e.g adding Microsoft.EntityFrameworkCore.Tools.DotNet to the DB project, which is no longer required and generates a build warning).

Generate migration script

The first step is to generate the migration script as part of the build, which the release step(s) will run against the database further down the line.

We gave up getting the built in .net core task to work with entity framework (we could not get past the error message ‘No executable found matching command “dotnet-ef”‘ regardless of what we tried), so we fell back to a good ol’ command line task:

command-line-task

And for your copying needs:

dotnet ef migrations script -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql --project EfMigrationApp.Database\EfMigrationApp.Database.csproj --startup-project EfMigrationApp\EfMigrationApp.csproj -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql --project EfMigrationApp.Database\EfMigrationApp.Database.csproj --startup-project EfMigrationApp\EfMigrationApp.csproj

You will obviously need to replace the project names with your own.

A quick breakdown of the command:

dotnet ef migrations script: the command to generate a migration script

-i: i is for idempotent, ie the script generated can be run multiple times on the same database without conflicts.

-o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql: the migration script will be placed in the artifact staging directory, along with the rest of the build output

–project EfMigrationApp.Database\EfMigrationApp.Database.csproj: the project containing the database definition

–startup-project EfMigrationApp\EfMigrationApp.csproj: instructs EF that this is the start up project of the app.

Run migrations in the release pipeline

I’m sure there are many ways to run sql scripts in the release step (both command line tasks and powershell tasks could be utilized), but we landed on the predefined “Azure SQL Publish” task, which we added after the web app deploy task:

release-db

Fill in the db details according to your project, and the deployment package section with these values:

Action: Publish

Type: SQL script file

Sql script:

$(System.ArtifactsDirectory)/_$(Build.DefinitionName)/drop/migrate.sql (note the underscore before the build.definitionname variable – I suspect there’s a system variable we could use instead)

And that’s basically it – running the build and release pipeline will deploy the web app first, then migrate the database according to your latest EF code goodness. Enjoy!