I've been using Entity Framework Code First and its migrations for many years, before .NET Core was a thing. Back then we often had the application run its own migrations on startup to bring the database up to date. This is something some of the samples do even today.

But it's not a good approach. There are several reasons why I think so:

  1. The older the app gets, the more migrations it needs to go through typically. This means it takes longer and longer.
  2. If the app is run on several instances, they will all attempt to run the migrations.
  3. The app must have a lot of privileges on the database in order to run the migrations, not something we always want.

Actually a little surprising I never wrote an article on this.

A better approach

Instead of running the migrations in the app code, we will run them in our release pipeline. There are a few different approaches here, but this is the one we often use in our projects:

  1. Build a migration SQL file in the build phase.
  2. Run the SQL file against the database in the release phase.
  3. Deploy the application.

Building the migration SQL file

This is quite easy with the dotnet ef CLI:

dotnet tool restore
dotnet ef migrations script -i -s Project.Api -p Project.Sql -o $(Build.BinariesDirectory)/Sql/Migration.sql  --configuration Release

The -i flag creates an idempotent script. It removes the need to figure out what version the database is currently in.

We also specify the startup project -s and SQL project -p as we store the EF context in a separate project.

The -o parameter specifies the output path. It uses an Azure Pipelines built-in variable here.

This produces us a single SQL file that we can then publish as a build artifact.

Deploying the migrations

We use PowerShell and the SqlServer module for the deployment. Any tool that will allow you to run SQL against the database will work fine.

# Parameters
$tenantId = ""
$migrationScriptPath = ""
$sqlServerFqdn = "yourdatabase.database.windows.net"
$sqlDb = "yourdatabase"

$accessToken = Get-AzAccessToken -ResourceUrl "https://database.windows.net/" -TenantId $tenantId -AsSecureString

Invoke-Sqlcmd -ServerInstance $sqlServerFqdn -Database $sqlDb -AccessToken $accessToken.Token -InputFile $migrationScriptPath

This can be run in an Azure PowerShell step with the appropriate service connection.

Naturally the agent running the release must have network visibility to the database. If you are using public hosted agents, you'll most likely need to add a firewall exception for the IP address of the agent running the release for its duration.

Summary

With this approach, our app does not need to run migrations on startup. It will now start faster. It can be deployed on multiple instances. And its privileges on the database can be reduced to only the necessary ones.

Thanks for reading :)