How to automate database migrations with Fluent Migrator in Azure DevOps?
What is a Fluent Migrator?
Fluent Migrator is a migration framework for .NET that enables the creation of database migrations by using C#. This basically means that you can create manually C# class for each migration. Fluent Migrator provides a Fluent API which is a really easy interface to construct migrations. Fluent Migrator is a good combination with Dapper which itself doesn't provide any data migration functionalities.
You can find more information and documentation about Fluent Migrator from here.
This blog post shows how to use Fluent Migrator and how to automate database migrations in Azure DevOps.
How to start with Fluent Migrator?
Create a class library project and install the following Nuget packages.
# Install the FluentMigrator package to the project named Migrations.
Install-Package FluentMigrator -ProjectName Migrations
# Install the FluentMigrator.Extensions.SqlServer package to the project named Migrations.
Install-Package FluentMigrator.Extensions.SqlServer -ProjectName MigrationsFluentMigrator.Extensions.SqlServer package provides really nice extensions ex. for data seeding operations.
Example migrations
The first migration (202205060000_AddProductsTable.cs) creates a new table called "Products".
using FluentMigrator;
namespace Migrations
{
[Migration(202205060000)]
public class AddProductsTable : Migration
{
public override void Up()
{
Create.Table("Products")
.WithColumn("ProductId")
.AsInt64()
.PrimaryKey()
.Identity()
.WithColumn("ProductName")
.AsString()
.Indexed();
}
public override void Down()
{
Delete.Table("Products");
}
}
}The second migration (202205061000_AddProductPriceField.cs) adds the Product Price field to the Products table.
using FluentMigrator;
namespace Migrations
{
[Migration(202205061000)]
public class AddProductPriceField : Migration
{
public override void Up()
{
Alter.Table("Products")
.AddColumn("ProductPrice")
.AsDecimal();
}
public override void Down()
{
Delete
.Column("ProductPrice")
.FromTable("Products");
}
}
}As you can see Fluent API is very intuitive and easy to use!
How to execute database migrations locally?
The fluent Migrator Runner Nuget package enables execution of the migrations via C# code but I concentrate on this sample to use the Fluent Migrator .NET CLI tool.
Install Fluent Migration .NET CLI tool
dotnet tool install -g FluentMigrator.DotNet.CliExecute migrations to Local DB
dotnet fm migrate -p sqlserver -c "Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=Products;Connect Timeout=30;Integrated Security=SSPI;" -a "Migrations.dll"After execution, you should see applied migrations in the VersionInfo table.
How to execute database migrations automatically in Azure DevOps?
Overview to flow
In this sample, ADO Pipeline and Database migration are executed with different service principals. Basically ADO Pipeline's service principal has access to KeyVault but it doesn't have Database permissions.
Database migrations YAML-template
This template is responsible for building Visual Studio project which contains C# based database migrations and publishing Migrations.dll artifacts to the Artifact store. The template also installs Fluent Migrator .NET CLI tool and applies pending migrations to the specific Azure SQL database.
The database connection is opened with Azure service principal (ClientId + Client Secret). When you want to open a database connection with the Service principal you should configure the Authentication parameter as "Active Directory Service Principal" and determine the "User Id" and "Password" fields in the connection string. User ID stands for Client ID of Service principal (app registration) and Password is secret of Service principal.
parameters:
- name: databaseServerName
type: string
- name: databaseName
type: string
- name: projectName
type: string
- name: azureDevOpsServicePrincipalName
type: string
- name: keyVaultResourceName
type: string
- name: keyVaultClientId
type: string
- name: keyVaultClientSecretName
type: string
jobs:
- job: DatabaseMigration
displayName: Building migrations
variables:
- name: databaseConnectionString
value: 'Data Source=${{parameters.databaseServerName}}.database.windows.net;Initial Catalog=${{parameters.databaseName}};Connect Timeout=30;Authentication=Active Directory Service Principal;User Id=${{parameters.keyVaultClientId}};Password=$(${{parameters.keyVaultClientSecretName}})'
steps:
- task: NuGetCommand@2
displayName: 'Restore solution'
inputs:
command: "restore"
feedsToUse: "config"
nugetConfigPath: $(Build.SourcesDirectory)\NuGet.Config
externalFeedCredentials: Test-DevOps
includeNuGetOrg: true
restoreSolution: "**/*.sln"
- task: MSBuild@1
displayName: "Build migrations project"
inputs:
solution: "**/${{parameters.projectName}}.csproj"
configuration: release
msbuildArguments: '/p:SkipInvalidConfigurations=false /p:OutDir="$(System.DefaultWorkingDirectory)\build"'
- task: CopyFiles@2
displayName: 'Copy migration artifact'
inputs:
sourceFolder: 'src/${{parameters.projectName}}/obj/Release'
contents: '${{parameters.projectName}}.dll'
targetFolder: $(Build.ArtifactStagingDirectory)
- task: PublishBuildArtifacts@1
displayName: 'Publish migration artifact'
inputs:
pathToPublish: $(Build.ArtifactStagingDirectory)
artifactName: Migrations
- task: AzureCLI@2
displayName: 'Install fluent migrator .NET CLI'
inputs:
azureSubscription: '${{parameters.azureDevOpsServicePrincipalName}}'
scriptType: 'bash'
scriptLocation: 'inlineScript'
inlineScript: |
dotnet tool install -g FluentMigrator.DotNet.Cli
- task: DownloadBuildArtifacts@0
displayName: 'Download migration artifact'
inputs:
buildType: 'current'
downloadType: 'single'
artifactName: 'Migrations'
downloadPath: '$(System.ArtifactsDirectory)'
- task: AzureKeyVault@2
displayName: 'Fetch Migrator client secret from KeyVault'
inputs:
azureSubscription: '${{parameters.azureDevOpsServicePrincipalName}}'
KeyVaultName: '${{parameters.keyVaultResourceName}}'
SecretsFilter: '${{parameters.keyVaultClientSecretName}}'
RunAsPreJob: false
- task: AzureCLI@2
displayName: 'List applied and pending migrations'
inputs:
azureSubscription: '${{parameters.azureDevOpsServicePrincipalName}}'
scriptType: 'bash'
scriptLocation: 'inlineScript'
inlineScript: |
dotnet fm list migrations -p sqlserver -c '$(databaseConnectionString)' -a "$(Build.ArtifactStagingDirectory)/Migrations/${{parameters.projectName}}.dll"
- task: AzureCLI@2
displayName: 'Apply all migrations'
inputs:
azureSubscription: '${{parameters.azureDevOpsServicePrincipalName}}'
scriptType: 'bash'
scriptLocation: 'inlineScript'
inlineScript: |
dotnet fm migrate -p sqlserver -c '$(databaseConnectionString)' -a "$(Build.ArtifactStagingDirectory)/Migrations/${{parameters.projectName}}.dll"Pipeline which orchestrates deployment can then consume this template like this.
jobs:
- template: "jobs/database-migration.yaml"
parameters:
databaseServerName: productsDbServerTest
databaseName: ProductsTest
projectName: Migrations
azureDevOpsServicePrincipalName: Test-DevOps
keyVaultResourceName: TestKeyVault
keyVaultClientId: 00000000-0000-0000-0000-000000000000
keyVaultClientSecretName: DatabaseMigrator-ClientCredentials-Secret-Test
Summary
Fluent Migrator provides varied ways to execute & automate database migrations and it's very easy to use. DotNET CLI contains all the basic features that you need to automate database migrations. I'll use this definitely in the future, especially in projects where Dapper is used instead of Entity Framework.