Using DbUp with Octopus Deploy

Ensuring your database is up to date as part of your CI/CD pipeline can be tricky. Here is a helpful way to manage it with DbUp

Create a Console App - with Program.cs like:

static int Main(string[] args)
{
  var config = new ConfigurationBuilder()
                   .AddJsonFile("appsettings.json", optional: false)
                   .Build();
  var connectionString = config.GetConnectionString("MyConnection");

  Console.WriteLine(connectionString);

  var upgrader =
      DeployChanges.To.SqlDatabase(connectionString)
          .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
          .LogToConsole()
          .Build();

  var result = upgrader.PerformUpgrade();

  if (!result.Successful)
  {
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(result.Error);
    Console.ResetColor();
#if DEBUG
    Console.ReadLine();
#endif
    return -1;
  }

  Console.ForegroundColor = ConsoleColor.Green;
  Console.WriteLine("Success!");
  Console.ResetColor();
  return 0;
}

Create an appsettings.json file

{
  "ConnectionStrings": {
    "MyConnection": "Server=...;"
  }
}

Install all needed packages:

Install-Package dbup-sqlserver
Install-Package Microsoft.Extensions.Configuration.FileExtensions
Install-Package Microsoft.Extensions.Configuration.Json
Install-Package System.Configuration.ConfigurationManager

Install-Package dbup-consolescripts

The last package is a handy tool that lets you say something like:

PM> New-Migration "AddBarColumn"

In the Package Manager Console This creates the script file automatically with a naming convention that ensures it is at the end of the list of scripts to be executed as it prefixes the filename with the current time based on YYYYMMDDhhmmss format

Edit the .csproj file to make sure all scripts in this folder get picked up as an Embedded Resource:

<ItemGroup>
  <EmbeddedResource Include="Scripts\*.sql" />
</ItemGroup>

Enter your sql update script, e.g:

/* Migration Script 220513123142_AddBarColumn.sql */
ALTER TABLE Foo
ADD Bar varchar(255);

Publish this project as part of your CI/CD build pipeline in somewhere like TeamCity, Azure DevOps, Github Actions etc.

From Octopus docs:

• Run the dotnet publish command on the project (don’t forget the output path).
• Run octo pack to package the output path (or use the Octopus Deploy build server plugin).
• Push the package to Octopus Deploy using the octo push command (or use the Octopus Deploy build server plugin).

If successfully built and packaged the next step is to create a new “Run Script” step as part of the deploy process in Octopus

PowerShell script to run

# How you reference the extracted path
$packagePath = $OctopusParameters["Octopus.Action.Package[MyProjectDbUp].ExtractedPath"]

$dllToRun = "$packagePath\DbUpMyProjectConsoleApp.dll"

# How you run this .NET core app
dotnet $dllToRun

Remember to reference the PackageId to match the PackageId stated in the build process

Ensure you enable the feature to set Structured Configuration Variables

Set up your Project Variables with connection strings for each environment. The format for matching the appsettings.json file is: ConnectionStrings:MyConnection

This should now let you update databases in different environments as part of your CI/CD pipeline.

This can obviously be taken to a more advanced level by generating reports or requiring specific approval by DBA’s.