Menu

How to automate database migrations with Fluent Migrator in Azure DevOps?

What is Fluent Migrator?

Fluent Migrator is a migration framework for .NET which enables 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 provider 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 Migrations

FluentMigrator.Extensions.SqlServer package provide really nice extensions ex. for data seeding operations.

Example migrations

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");
        }
    }
}

Second migration (202205061000_AddProductPriceField.cs) adds 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?

Fluent Migrator Runner nuget package enables execution of the migrations via C# code but I concentrate in this sample to use Fluent Migrator .NET CLI tool.

Install Fluent Migration .NET CLI tool

dotnet tool install -g FluentMigrator.DotNet.Cli

Execute 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.

undefined

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.

undefined

Database migrations YAML-template

This template is responsible for building Visual Studio project which contains C# based database migrations and publish Migrations.dll artifact to Artifact store. Template also installs Fluent Migrator .NET CLI tool and applies pending migrations to the specific Azure SQL database.

Database connection is opened with Azure service principal (ClientId + Client Secret). When you want to open database connection with Service principal you should configure that Authentication parameter is "Active Directory Service Principal" and determine "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 basic features what you need to automate database migrations. I'll use this definitely in the future especially in the projects where Dapper is used instead of Entity Framework.

Comments