Sunday, 24 June 2018

VSTS and EF Core Migrations

Let me start by telling you a dirty secret. I have an ASP.Net Core project that I build with VSTS. It is deployed to Azure through a CI / CD setup in VSTS. That part I'm happy with. Proud of even. Now to the sordid hiddenness: try as I might, I've never found a nice way to deploy Entity Framework database migrations as part of the deployment flow. So I have [blushes with embarrassment] been using the Startup of my ASP.Net core app to run the migrations on my database. There. I said it. You all know. Absolutely filthy. Don't judge me.

If you care to google, you'll find various discussions around this, and various ways to tackle it. Most of which felt like too much hard work and so I never attempted.

It's also worth saying that being on VSTS made me less likely to give these approaches a go. Why? Well, the feedback loop for debugging a CI / CD setup is truly sucky. Make a change. Wait for it to trickle through the CI / CD flow (10 mins at least). Spot a problem, try and fix. Start waiting again. Repeat until you succeed. Or, if you're using the free tier of VSTS, repeat until you run out of build minutes. You have a limited number of build minutes per month with VSTS. Last time I fiddled with the build, I bled my way through a full month's minutes in 2 days. I have now adopted the approach of only playing with the setup in the last week of the month. That way if I end up running out of minutes, at least I'll roll over to the new allowance in a matter of days.

Digression over. I could take the guilt of my EF migrations secret no longer, I decided to try and tackle it another way. I used the approach suggested by Andre Broers here:

I worked around by adding a dotnetcore consoleapp project where I run the migration via the Context. In the Build I build this consoleapp in the release I execute it.

Console Yourself

First things first, we need a console app added to our solution. Fire up PowerShell in the root of your project and:

md MyAwesomeProject.MigrateDatabase
cd .\MyAwesomeProject.MigrateDatabase\
dotnet new console

Next we need that project to know about Entity Framework and also our DbContext (which I store in a dedicated project):

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add reference ..\MyAwesomeProject.Database\MyAwesomeProject.Database.csproj

Add our new project to our solution: (I always forget to do this)

cd ../
dotnet sln add .\MyAwesomeProject.MigrateDatabase\MyAwesomeProject.MigrateDatabase.csproj

You should now be the proud possessor of a .csproj file that looks like this:

<Project Sdk="Microsoft.NET.Sdk">


    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.1" />

    <ProjectReference Include="..\MyAwesomeProject.Database\MyAwesomeProject.Database.csproj" />


Replace the contents of the Program.cs file with this:

using System;
using System.IO;
using MyAwesomeProject.Database;
using Microsoft.EntityFrameworkCore;

namespace MyAwesomeProject.MigrateDatabase {
    class Program {
        // Example usage:
        // dotnet MyAwesomeProject.MigrateDatabase.dll "Server=(localdb)\\mssqllocaldb;Database=MyAwesomeProject;Trusted_Connection=True;"
        static void Main(string[] args) {
            if (args.Length == 0)
                throw new Exception("No connection string supplied!");

            var myAwesomeProjectConnectionString = args[0];

            // Totally optional debug information
            Console.WriteLine("About to migrate this database:");
            var connectionBits = myAwesomeProjectConnectionString.Split(";");
            foreach (var connectionBit in connectionBits) {
                if (!connectionBit.StartsWith("Password", StringComparison.CurrentCultureIgnoreCase))

            try {
                var optionsBuilder = new DbContextOptionsBuilder<MyAwesomeProjectContext>();

                using(var context = new MyAwesomeProjectContext(optionsBuilder.Options)) {
                Console.WriteLine("This database is migrated like it's the Serengeti!");
            } catch (Exception exc) {
                var failedToMigrateException = new Exception("Failed to apply migrations!", exc);
                Console.WriteLine($"Didn't succeed in applying migrations: {exc.Message}");
                throw failedToMigrateException;

This code takes the database connection string passed as an argument, spins up a db context with that, and migrates like it's the Serengeti.

Build It!

The next thing we need is to ensure that this is included as part of the build process in VSTS. The following commands need to be run during the build to include the MigrateDatabase project in the build output in a MigrateDatabase folder:

cd MyAwesomeProject.MigrateDatabase
dotnet build
dotnet publish --configuration Release --output $(build.artifactstagingdirectory)/MigrateDatabase

There's various ways to accomplish this which I wont reiterate now. I recommend YAML.

Deploy It!

Now to execute our console app as part of the deployment process we need to add a CommandLine task to our VSTS build definition. It should execute the following command:

dotnet MyAwesomeProject.MigrateDatabase.dll "$(ConnectionStrings.MyAwesomeProjectDatabaseConnection)"

In the following folder:


Do note that the command uses the ConnectionStrings.MyAwesomeProjectDatabaseConnection variable which you need to create and set to the value of your connection string.

Give It A Whirl

Let's find out what happens when the rubber hits the road. I'll add a new entity to my database project:

using System;

namespace MyAwesomeProject.Database.Entities {
    public class NewHotness {
        public Guid NewHotnessId { get; set; }

And reference it in my DbContext:

using MyAwesomeProject.Database.Entities;
using Microsoft.EntityFrameworkCore;

namespace MyAwesomeProject.Database {
    public class MyAwesomeProjectContext : DbContext {
        public MyAwesomeProjectContext(DbContextOptions<MyAwesomeProjectContext> options) : base(options) { }

        // ...
        public DbSet<NewHotness> NewHotnesses { get; set; }

        // ...

Let's let EF know by adding a migration to my project:

dotnet ef migrations add TestOurMigrationsApproach

Commit my change, push it to VSTS, wait for the build to run and a deployment to take place.... Okay. It's done. Looks good.

Let's take a look in the database:

select * from NewHotnesses

It's there! We are migrating our database upon deployment; and not in our ASP.Net Core app itself. I feel a burden lifted.

Wrapping Up

The EF Core team are aware of the lack of guidance around deploying migrations and have recently announced plans to fix that in the docs. You can track the progress of this issue here. There's good odds that once they come out with this I'll find there's a better way than the approach I've outlined in this post. Until that glorious day!

Saturday, 16 June 2018

VSTS... YAML up!

For the longest time I've been using the likes of Travis and AppVeyor to build open source projects that I work on. They rock. I've also recently been dipping my toes back in the water of Visual Studio Team Services. VSTS offers a whole stack of stuff, but my own area of interest has been the Continuous Integration / Continuous Deployment offering.

Historically I have been underwhelmed by the CI proposition of Team Foundation Server / VSTS. It was difficult to debug, difficult to configure, difficult to understand. If it worked... Great! If it didn't (and it often didn't), you were toast. But things done changed! I don't know when it happened, but VSTS is now super configurable. You add tasks / configure them, build and you're done! It's really nice.

However, there's been something I've been missing from Travis, AppVeyor et al. Keeping my build script with my code. Travis has .travis.yml, AppVeyor has appveyor.yml. VSTS, what's up?

The New Dawn

Up until now, really not much. It just wasn't possible. Until it was:

When I started testing it out I found things to like and some things I didn't understand. Crucially, my CI now builds based upon .vsts-ci.yml. YAML baby!

It Begins!

You can get to "Hello World" by looking at the docs here and the examples here. But what you really want is your existing build, configured in the UI, exported to YAML. That doesn't seem to quite exist, but there's something that gets you part way. Take a look:

If you notice, in the top right of the screen, each task now allows you click on a new "View YAML" button. It's kinda Ronseal:

Using this hotness you can build yourself a .vsts-ci.yml file task by task.

A Bump in the Road

If you look closely at the message above you'll see there's a message about an undefined variable.

#Your build definition references an undefined variable named ‘Parameters.RestoreBuildProjects’. Create or edit the build definition for this YAML file, define the variable on the Variables tab. See
- task: [email protected]
  displayName: Restore
    command: restore
    projects: '$(Parameters.RestoreBuildProjects)'

Try as I might, I couldn't locate Parameters.RestoreBuildProjects. So no working CI build for me. Then I remembered Zoltan Erdos. He's hard to forget. Or rather, I remembered an idea of his which I will summarise thusly: "Have a package.json in the root of your repo, use the scripts for individual tasks and you have a cross platform task runner".

This is a powerful idea and one I decided to put to work. My project is React and TypeScript on the front end, and ASP.Net Core on the back. I wanted a package.json in the root of the repo which I could install dependencies, build, test and publish my whole app. I could call into that from my .vsts-ci.yml file. Something like this:

  "name": "my-amazing-project",
  "version": "1.0.0",
  "author": "John Reilly ",
  "license": "MIT",
  "private": true,
  "scripts": {
    "preinstall": "yarn run install:clientapp && yarn run install:web",
    "install:clientapp": "cd MyAmazingProject.ClientApp && yarn install",
    "install:web": "dotnet restore",
    "prebuild": "yarn install",
    "build": "yarn run build:clientapp && yarn run build:web",
    "build:clientapp": "cd MyAmazingProject.ClientApp && yarn run build",
    "build:web": "dotnet build --configuration Release",
    "postbuild": "yarn test",
    "test": "yarn run test:clientapp && yarn run test:web",
    "test:clientapp": "cd MyAmazingProject.ClientApp && yarn test",
    "test:web": "cd MyAmazingProject.Web.Tests && dotnet test",
    "publish:web": "cd MyAmazingProject.Web && dotnet publish MyAmazingProject.Web.csproj --configuration Release"

It doesn't matter if I have "an undefined variable named ‘Parameters.RestoreBuildProjects’". I now have no need to use all the individual tasks in a build. I can convert them into a couple of scripts in my package.json. So here's where I've ended up for now. I've a .vsts-ci.yml file which looks like this:

queue: Hosted VS2017

- task: geek[email protected]2
  displayName: install yarn itself
    checkLatest: true
- task: [email protected]
  displayName: yarn build and test
    Arguments: build
- task: [email protected]
  displayName: yarn publish:web
    Arguments: 'run publish:web --output $(build.artifactstagingdirectory)/MyAmazingProject'
- task: [email protected]
  displayName: publish build artifact
    PathtoPublish: '$(build.artifactstagingdirectory)'

This file does the following:

  1. Installs yarn. (By the way VSTS, what's with not having yarn installed by default? I'll say this for the avoidance of doubt: in the npm cli space: yarn has won.)
  2. Install our dependencies, build the front end and back end, run all the tests. Effectively yarn build.
  3. Publish our web app to a directory. Effectively yarn run publish:web. This is only separate because we want to pass in the output directory and so it's just easier for it to be a separate step.
  4. Publish the build artefact to TFS. (This will go on to be picked up by the continuous deployment mechanism and published out to Azure.)

I much prefer this to what I had before. I feel there's much more that can be done here as well. I'm looking forward to the continuous deployment piece becoming scriptable too.

Thanks to Zoltan and props to the TFVS team!