Update SQL Database using EF Core and Azure DevOps

If you write an app you probably need a way to store data. If you do it using a SQL Database (eg. SQL-Server) you need a way to change your Database-Schema sooner then later.

There are several options on how to do this in the Microsoft eco-system. Here I describe a very easy and simple way on how I do it for my personal project over at https://tauchbolde.ch.

Goal

First of all my app is a ASP.Net Core MVC 2.2 app using EF Core 2.2 with SQL-Server. More precisely with Azure SQL-Server. The application is hosted on Azure AppService. I use Azure DevOps to fully automate my build and release pipeline.

My goal was to automatically run the schema-migration as part of my automated deployment. The solution should be straight forward and transparent. It also should not need much of custom code.

Solution

I’m using EF Core’s Migrations to add migrations as soon as my schema needs to change. Normally I commit this migration together with my modified ORM model classes. Migrations are generated and added to the project using the following command:

dotnet ef migrations add

These migrations can be run on the command line using the EF CLI command:

dotnet ef database update

All pretty standard so far.

Instead of running the above commands when deploying and hope they will succeed I like to do it more SQL-like and more transparent. Therefore I maintain one single SQL-Script that updates any schema-version to the latest version.

Such an SQL-Script can easily be tested locally or on test-servers and with different datasets. If something goes wrong on production system’s migration we can fix the SQL-Script manually and are good without building a new software package.

Lucky me EF Core has this feature built into. I use a command line like this to generate the SQL-Script:

dotnet ef migrations script -i -o ../../sql/update_to_latest.sql

The generated SQL-Script contains the IF-Statements to check which migrations needs to be applied and which are already. It also takes care of updating the migration-history-table.

My build-job copies the sql/update_to_latest.sqlscript into the build artifact.

For my release-jobs I’ve created a Task Group called “Migate DB to latest”. I used the task group so I can re-use this step on all my environments (actually stage and prod). The Task Group contains one step of type Azure SQL Database deployment and executes the above SQL-Script.

Finally I’ve added this Task-Group to each environments release-steps.

Important: To get the connection to your SQL-Server working you have to add the connection in Azure Service Connections.

You can find my real-world docs over at GitHub.