Run Entity Framework Code First Migrations from TeamCity with psake

This is going to be a short post about how to run EF 6 Migrations from TeamCity using psake. To follow this post you will need some experience using EF Code First and Migrations. And for the TeamCity-part at the end some knowledge about buildsteps and parameters in TeamCity is expected.

There is some PowerShell/psake stuff going on here. I will continue to build on this post/code later to get to a place where we can deploy an WebAPI, with EF Migrations without downtime with the help of deployment-slots in Azure WebApps. I have decided to go with the full .NET Framework v.4.6.2 since that is still what most of my projects are on. Hopefully most of the thoughts and concepts will be “concept compatible” with .NET Core and EF Core. I have created a GitHub repository where I will collect all example-code from my blogs. You can find it at https://github.com/danmusk/blog.

So, time to get flying.

Projects and nugets

First of I put all the Entity Framework specific code in its own project, MigrationsDemo.Sql. Here I will put the DbContext, the entities for the database and the migration-files will be in this project.

In the API-project, MigrationsDemo.API, I have installed Swashbuckle to enable Swagger so we can test the API and database without much effort. I have also installed a nuget for psake. Usually I would have this in a separate build-project.

Entity Framework 6.1.3

After installing the nuget package for Entity Framework 6.1.3 in the MigrationsDemo.Sql-project, we can navigate to the packages-folder in the solution directory. Inside the Entity Framework-folder we will find another folder named tools.

This .exe-file is a command-line tool that you can run instead of using the Package Manager Console inside Visual Studio. To learn more about this tool you can read more on Microsofts own page Entity Framework migrate.exe.

Buildscript

While Microsofts docs tells you that you can copy the migrate.exe to the place where you have the dll from your EF-project, I do some PowerShell-magic (not written by me) to traverse the packages folder and find the newest folder for Entity Framework and find the migrate.exe file-path. The PowerShell script that does this can be found in the helper.ps1 file.

For more details about using psake with TeamCity I highly recommend Fabian Ruffins course on Continuous Integration with psake and TeamCity: Getting Started from Pluralsight.

build.ps1

By default what this script does is the following:

  • Defaults for task, configuraton (debug, release) and connectionString
  • Contains variables specific for this project eg. solution-filename, EF-projectname
  • Restore all nugets for solution-file
  • Find and import psake-powershell-module from packages-folder
  • Invoke the psake-buildscript, default.ps1

default.ps1

This file is based on psake and defines the different build-tasks to be run. In this project I have defined 3 buildtasks; Init, Compile, MigrateDatabase. The default task is set to be the Compile task. The Init task checks that all tools, variables and settings are valid. The Compile task compiles the solution using msbuild and place the result in a .build folder. And finally, the MigrateDatabase task runs migrate.exe and targets the .dll specificed as databaseProjectDll in build.ps1. In this project it’s databaseProjectDll = "MigrationsDemo.Sql.dll".

Run migration on localdb

To run the buildscript open up a powershell console window, navigate to the solution folder and run .\build.ps1.


Without any arguments the build.ps1 restores all nugets and compiles the solution.

If you take a peek inside the build.ps1 file you will see that one of the arguments you can pass in is a connectionstring. This is the connectionstring to the database we want to run the migration. I have declared a default connectionstring for local development.
Server=(LocalDB)\MSSQLLocalDB;Integrated Security=true;Initial Catalog=WarehouseMigrationsDemoDb;

So if we want to run the MigrateDatabase task it will look like
.\build.ps1 -Task MigrateDatabase

Now the buildscript will compile the solution and after that run the following command for us
migrate.exe MigrationsDemo.Sql.dll /startupDirectory="C:\Git\blog\EF6Migrations\.build\temp" /connectionString="Server=(LocalDB)\MSSQLLocalDB;Integrated Security=true;Initial Catalog=WarehouseMigrationsDemoDb;" /ConnectionProviderName="System.Data.SqlClient" /verbose

The result should then look something like this

Run in TeamCity

For the buildscript to run in TeamCity you will probably want to swap the connectionstring with something else, maybe using a parameter from TeamCity. The parameter can then hold a connectionstring to a database in Azure for example. The buildstep for running the MigrateDatabase-task in TeamCity can be like

And the parameterlist for the TeamCity-project

This will then run the build.ps1 file with the following arguments
.\build.ps1 -Task "MigrateDatabase" -BuildConfiguration "Release" -ConnectionString "MyConnectionStringForDatabaseServer"

Some challenges

Our team has successfully deployed and migrated development-environments using this approach for some time now. But when it comes to production there are still some caveats. The migration needs to run before we deploy the application (MVC og API), but when we run the migration first, then the application might break until the deployment of the application is completed and the model backing the database is in sync with the _MigrationHistory in the database. This can result in application errors. We have some ideas of how we can work around this and achieve zero-downtime deployments, but that is something for another blogpost.

How do you perform deployments that involves non-trivial database-changes? Any thoughts or experiences you want to share, please leave a comment below. Happy migrations.