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 UserAgentfield 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.
infoThe following steps use Azure DevOps portal to implement the pipelines. You can also directly use YAML.
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
infoAIf you are familiar with dotnet ef migrations script command, you can directly use it to generate a migration SQL script file without relying on this extension. For simplicity, we just use this extension to do the same.
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 Advancedsection, 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 ApplicationDbContextclass. 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 BlockReasonto 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.
warning To avoid unexpected changes, it is always a good practice to review the generated scripts before releasing it to production; and it is also a good practice to add a step to backup your target databases before applying migration scripts to the database.
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.