Using DbUp

On a recent project there wasn't an agreed way to run DB scripts. So we built a small command line tool using DbUp. We then wrote the scripts and had them as embedded files within the project - DbUp could then access the scripts and run any that needed to be applied.

We also used CommandLine to give us a nice help screen and the ability to specify parameters.

Uses variables to inject values into scripts.

using System.Reflection;
using CommandLine;
using DbUp;
using DbUp.Engine;
using DbUp.Support;

Parser.Default.ParseArguments<Options>(args).WithParsed(o =>
{
    Console.WriteLine($"Deploying to : {o.ConnectionString}");

    try
    {
        EnsureDatabase.For.SqlDatabase(o.ConnectionString);

        var executingAssembly = Assembly.GetExecutingAssembly();
        var result = DeployChanges.To
            .SqlDatabase(o.ConnectionString)
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsPreDeployment, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 0 })
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsScript, new SqlScriptOptions { RunGroupOrder = 1 })
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsReferenceData, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 2 })
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsFunction, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 3 })
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsStoredProcedure, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 4 })
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsView, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 5 })
            .WithScriptsEmbeddedInAssembly(executingAssembly, IsPostDeployment, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 10 })
            .LogToConsole()
            .LogScriptOutput()
            .WithTransaction()
            .WithVariable("password", o.Password)
            .WithVariable("login", o.Login)
            .WithVariable("user", o.User ?? o.Login)            
            .Build()
            .PerformUpgrade();

        if (result.Successful)
        {
            Environment.Exit(0);
            return;
        }

        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine(result.Error);
        Console.ResetColor();

        Environment.Exit(1);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
        Environment.Exit(2);
        throw;
    }
});

static bool IsPreDeployment(string scriptName) => ScriptNameStartsWith(scriptName, "PreDeployment.");
static bool IsPostDeployment(string scriptName) => ScriptNameStartsWith(scriptName, "PostDeployment.");
static bool IsScript(string scriptName) => ScriptNameStartsWith(scriptName, "Scripts.");
static bool IsReferenceData(string scriptName) => ScriptNameStartsWith(scriptName, "ReferenceData.");
static bool IsFunction(string scriptName) => ScriptNameStartsWith(scriptName, "Functions.");
static bool IsView(string scriptName) => ScriptNameStartsWith(scriptName, "Views.");
static bool IsStoredProcedure(string scriptName) => ScriptNameStartsWith(scriptName, "StoredProcedures.");
static bool ScriptNameStartsWith(string scriptName, string startsWith) => scriptName.StartsWith($"{Assembly.GetExecutingAssembly().GetName().Name}.{startsWith}", StringComparison.OrdinalIgnoreCase);

internal class Options
{
    [Option('c', "connectionString", Required = true, HelpText = "SQL server connection string")]
    public string? ConnectionString { get; set; }

    [Option('p', "password", Required = true, HelpText = "SQL server user password")]
    public string? Password { get; set; }

    [Option('l', "login", Required = true, HelpText = "SQL server login")]
    public string? Login { get; set; }

    [Option('u', "user", Required = false, HelpText = "SQL server database user")]
    public string? User { get; set; }
}