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!

 

 

 

 

 

2 thoughts on “Deploying a code first entity framework database in Azure DevOps

  1. Thanks for this tip. But what if you don’t have the connection string on config file, as recommended? what do you suggest to place in this case to generate migration on build?

    My connection strings are in secrets (development machine) and server variables (on host – azure web apps).

  2. That’s a very good question. Have you considered doing variable replacement in the build pipeline? E.g having a placeholder in the settings file, and replacing it with a variable (you would of course have to have access to the variable, either be duplicating it as a build variable, or by moving it to azure key vault or something similar and have both the build server and the web app retrieve it from there).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s