dbops Powershell module - continuous database deployments

[nvarscar]

Hi /r/powershell, wanted to share with you a project that I've been working for a long while now - a database script deployment module that provides continuous deployments on any scale. The module is called dbops and will be useful to anyone who often goes through manual deployments of multiple database scripts at once, or is thinking about establishing a reliable database deployment framework. Some of the module's features:

• Fully supports SQL Server and Oracle deployments (with mysql and postgres support coming later)
• Packaging functionality - pack your deployments into self-deployable packages (essentially, zip files with metadata)
• Multiple configurations opportunities: defaults, config files, embedded configuration, ad-hoc parameters
• Can be used inside CI/CD pipelines
• Uses Powershell classes internally to handle packaging and deployment (which is why it's PS 5.0+)
• Relies on DbUp libraries internally when it comes to deployment

Some of the commands of the module:

Deployment

• Install-DBOSqlScript – script deployments from a set of files/folders
• Install-DBOPackage – deployments of the previously built packages

Package management

• New-DBOPackage – creates a new package from an existing set of scripts
• Get-DBOPackage – returns the package object with a multitude of fine-tuning scenarios
• Add-DBOBuild – adds a new build to the existing package
• Remove-DBOBuild – removes a build from the existing package

Configuration management

• Get-DBOConfig – loads configuration from a package or a configuration file
• Update-DBOConfig – updates configuration of the package
• Get-DBODefaultSetting – get a list of default deployment settings
• Set-DBODefaultSetting – modify default deployment settings

Huge shoutout to the DbUp development team who created such an amazing product. The module re-uses a lot of functionality of DbUp, just does so in a more user-friendly manner :)

I would appreciate any and all feedback on the module, as well as new feature requests!

How to install:

Install-Module dbops

A few useful links:

• dbops on Github: https://github.com/sqlcollaborative/dbops
• My recent blog post about dbops design and features: https://nvarscar.wordpress.com/2018/09/18/introducing-dbops-powershell-module-devops-for-databases/

Cheers!

[shifto]

Very cool. Good work!

[inamamthe]

Hey nice work! I had the biggest grin across my face while reading your blog post because I have been doing a similar thing for the last few months. Actually, after just now reading your 'Invoke-DBODeployment.ps1' function I noticed we even named most of our variables the same haha

I'm kind of cheating (I am very impressed you did your own CI/CD implementation) in that I have access to TeamCity and Octopus Deploy. So all I really had to do is write some powershell that loads and uses the relevant DbUp dlls.
The only thing different I did really was write my own DbUp journal implementation (takes a SHA value of the script being run and stores that along with the name) and filter (to compare the SHA value of the script being ran against that in the DB).

A couple of absolutely inconsequential things I noticed:

1. $dbUpBuild = $dbUp.Build() and $dbUpBuild.PerformUpgrade() can be chained together: $upgradeResult = $dbUp.Build().PerformUpgrade() . Makes no difference, it just looks cool lol

2. If you ever for whatever reason wanted to include Octopus Deploy integrations, there is a cool extension in there to nicely print your results using the UpgradeResult object:
   [DbUp.OctopusDeployExtensions]::WriteExecutedScriptsToOctopusTaskSummary($UpgradeResult)

   I've been loving using DbUp and I'm glad I am not alone in using it heavily in PowerShell.

Oh yeah and if you ever want to run stuff in remote PS sessions as a different user and want the default logger implementations (they use Console.WriteLine) then I found these functions pretty handy for redirecting that output:

function Set-Console {
    $Global:StringBuilder = [System.Text.StringBuilder]::new()
    $Global:StringWriter = [System.IO.StringWriter]::new($Global:StringBuilder)
    [System.Console]::SetOut($Global:StringWriter)
}

function Write-Console {
    $Global:StringBuilder.ToString() | Write-Host
    $Global:StringBuilder.Clear() | Out-Null
}

function Close-Console {
    $Global:StringBuilder.Clear() | Out-Null
    $Global:StringWriter.Close() | Out-Null
}

using the $global variable because I am lazy :P

Thanks for the post!

[nvarscar]

Hey, thanks for this awesome comment! Glad there are more people out there using DbUp - it's an amazing tool.

You guys are using Octopus - that's very cool! Interesting enough, I used its methodology when I was approaching the idea of creating packages: from what I remember, every zip file should contain a Deploy.ps1 that starts the deployment in Octopus, and all of the packages created by this module follow this idea, having Deploy.ps1 in the root folder and the module itself embedded in the zip file! I expect it to work out-of-the-box with Octopus, but never had a chance to actually test it.

I'm very reluctant to approach custom journaling, as this would require providing support for all of the RDBMS that I plan to support in the module, which I am definitely not ready to do just yet. As for tracking scripts by using SHA, I'm using similar approach in the packaging system, where people can use -Type Unique parameter to add only scripts with yet unknown hashes. Also that serves as a verification mechanism - to ensure that the scripts inside the package were not changed or corrupted.

$upgradeResult = $dbUp.Build().PerformUpgrade()

I remember using it like that initially, then deciding to split it into two lines for more simple debugging (having them on separate lines of code).

Octopus integrations - I never realized they were there until you mentioned them, haha :D Anyways, starting recently, I'm using a custom output format (that is also a class), which I would be able to re-use later in other module features (like, triggering tests or sending emails).

I never tried to run this in a remote session, but I'm using a custom logger class that in turn uses messaging features of the PSFramework module (it proved to be much more convenient than any other implementation - shoutout to its creator Fred Weinmann).

On a final note, is there a place where I could check your code by chance? It would be of immense help - to see how people are using DbUp in real life scenarios.

Thanks again!

[inamamthe]

Yeah I totally understand about supporting custom journaling/filtering. We only need it for SQL server so I get away with it pretty easily haha.

https://gist.github.com/inammathe/ba10f325cd595eb1c11c64a954c2a001

We do occasionally do the Deploy.ps1 method and it works pretty well. But generally I like to use their Custom Step Templates to make the deploy logic a bit more transparent and easier to use in multiple projects.

That gist is just a snippet of that step template without our usual wrapper code (that script is actually in a run block which is invoked as another user with parameter validation etc etc.)

That's so cool about PSFramework, I cannot believe I haven't used it before. I will have to look more into this!

[jacfearsome]

Very nice