Skip to content

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.

Screenshot 2019-07-08 at 22.35.04.png

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

Screenshot 2019-07-08 at 22.41.37.png

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.

One thought on “Update SQL Database using EF Core and Azure DevOps Leave a comment

  1. 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.

    Like

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

%d bloggers like this: