EntityFramework Core Database Migration in Azure DevOps Pipeline

EntityFramework Core is a commonly used framework in .NET Core applications to access and manage data in a database like SQL Server. When using code-first approach, migration scripts are generated using CLI tool and then database update command is used to apply the changes to databases. To perform database migration in Azure DevOps pipeline, it is feasible with add-ons.

About EF Core CLI

In development environment, we usually follow these steps to apply database changes in code-first EntityFramework projects:

• Create DbContext and entity classes.
• Use dotnet ef migrations add sub command to generate migration class. There are other sub commands available to list, remove or script SQL statements: 
  

  dotnet ef migrations
  
  
  Usage: dotnet ef migrations [options] [command]
  
  Options:
    -h|--help        Show help information
    -v|--verbose     Show verbose output.
    --no-color       Don't colorize output.
    --prefix-output  Prefix output with level.
  
  Commands:
    add     Adds a new migration.
    list    Lists available migrations.
    remove  Removes the last migration.
    script  Generates a SQL script from migrations.

  The following is one example migration script which increases UserAgent field from varchar(150) to varchar(250):
  

  public partial class IncreaseUserAgent : Migration
      {
          protected override void Up(MigrationBuilder migrationBuilder)
          {
              migrationBuilder.AlterColumn<string>(
                  name: "UserAgent",
                  schema: "flex",
                  table: "FlexActions",
                  type: "nvarchar(250)",
                  maxLength: 250,
                  nullable: true,
                  oldClrType: typeof(string),
                  oldType: "nvarchar(150)",
                  oldMaxLength: 150,
                  oldNullable: true);
          }
  
          protected override void Down(MigrationBuilder migrationBuilder)
          {
              migrationBuilder.AlterColumn<string>(
                  name: "UserAgent",
                  schema: "flex",
                  table: "FlexActions",
                  type: "nvarchar(150)",
                  maxLength: 150,
                  nullable: true,
                  oldClrType: typeof(string),
                  oldType: "nvarchar(250)",
                  oldMaxLength: 250,
                  oldNullable: true);
          }
      }
  

• Call dotnet ef database update sub command to apply the changes to the database. 

This approach works well in a development environment and you can even directly follow the same steps to apply the changes to a production Azure SQL Database on cloud. However if you want to automate the whole approach and add control points to your release processes, you can build into your Azure DevOps pipelines. 

Azure DevOps pipeline design

In Azure DevOps pipeline, we can add a task to generate database migration SQL script. The generated scripts will be included in the artifact. Then in release pipeline, we can use Azure SQL Database deployment task to run the generate SQL script to deploy the changes to your targeted databases on Azure. 

The high-level flow architect looks like the following diagram:

Add Entity Framework Core Migrations Script Generator

First, we need to install an extension to your Azure DevOps project from Visual Studio Marketplace:

Entity Framework Core Migrations Script Generator - Visual Studio Marketplace

Once the extension is installed, we can use it in Azure pipelines.

Navigate to your Azure Pipelines and add all the configurations for this task:

Make sure you specify the right values for Startup Project Path and DbContexts. In Advanced section, ensure dependencies option is checked:

You don't need to specify the version as it will automatically pick up the latest .NET Core version. Please ignore .NET Core 3 text if you are using .NET 5 as the author of this extension has not updated it to .NET 5 but it does work for .NET 5 too.

About the published artifacts

When the build is successful, the following logs will be printed out based on my settings:

Writing 'D:\a\1\a/migrations/ApplicationDbContext.sql'...

Generating migration script completed!

The published artifact look like the following screenshot:

*For my project, the artifact includes the built ASP.NET Core application and the database migration script for ApplicationDbContext class. The latter will be used to apply changes to Azure SQL database. 

The generated migration SQL scripts will be based on all the migration C# classes.  It automatically adds a check to see if each migration was already applied to database previously. The following code snippet is extracted from Kontext database migration script:

BEGIN TRANSACTION;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210407120738_AddBlockReason')
BEGIN
    ALTER TABLE [flex].[FlexSites] ADD [BlockReason] nvarchar(1024) NULL;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210407120738_AddBlockReason')
BEGIN
    ALTER TABLE [flex].[FlexContents] ADD [BlockReason] nvarchar(1024) NULL;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210407120738_AddBlockReason')
BEGIN
    ALTER TABLE [flex].[FlexComments] ADD [BlockReason] nvarchar(1024) NULL;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210407120738_AddBlockReason')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20210407120738_AddBlockReason', N'5.0.3');
END;
GO

COMMIT;

This section basically checks whether migration 20210407120738_AddBlockReason is applied to database. If not, it will then apply the changes to the database and then add the migration history log to table [dbo].[__EFMigrationsHistory] in the target database.

At the moment, Kontext production database only has the following migrations, thus the delta scripts will be executed against the database to add a new filed named BlockReason to three tables.  Once that is done, a new migration history record will be added to table table [dbo].[__EFMigrationsHistory]. Next time when the process runs, nothing will be applied until there is new migrations are added. 

Execute migration script in release pipeline

In your Azure DevOps release pipeline, add a task from task template Azure SQL Database deployment. Configure this task accordingly as the following screenshot shows:

For task property SQL Script, ensure you pickup the right file path.  For example, the following value specifies the generated migration script for ApplicationDbContext class. The path is consistent with the build task.

$(System.DefaultWorkingDirectory)/**/drop/migrations/ApplicationDbContext.sql

For this task, it invokes SQLCMD command tool to execute the SQL statements against Azure SQL database. 

Once the task is completed successfully,  the logs looks like the following screenshot:

Security considerations

As you've noticed in the above approaches, there are several security related items you need to consider before adopting this approach:

• Database credential or connection string is required when deploying to Azure SQL databases.  You can create variables and set them as secrets or use integrated security authentication. 
  
• Access to Azure SQL database is required from Azure DevOps agents. The task will add firewall exception directly using ARM when running the script and it will then remove the firewall exception once it is done. 

If you have other better approach to automate EntityFramework Core code first migrations to databases using Azure DevOps, feel free to make a comment here.