Initial Import for DbUp

CREATE TABLE [dbo].[SchemaVersions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ScriptName] [nvarchar](255) NOT NULL,
	[Applied] [datetime] NOT NULL,
 CONSTRAINT [PK_SchemaVersions_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[SchemaVersions]
           ([ScriptName]
           ,[Applied])
     VALUES
           ('00001_InitialImport.sql'
           , '2025/02/25')
GO

// run the DbUp Migration scripts in the Scripts Folder
// change the connection string in the launchSettings.json for local development
//  in this case, I am using WithScriptsFromFileSystem so I could support multiple databases with this same exe. The WithScriptsFromFileSystem option is also available.

using DbUp.Helpers;
using DbUp;
using CommandLine;

var result = Parser.Default
            .ParseArguments<Options>(args)
            .MapResult(Run, Error);

return result;

static int Run(Options opts)
{
    try
    {
        Console.WriteLine($"Beginning Database Migration with these options {opts}");

        var connectionString = "";
        var databaseName = "";
        if (!string.IsNullOrWhiteSpace(opts.ConnectionString))
        {
            connectionString = opts.ConnectionString;
            if (!connectionString.Contains("Database="))
            {
                throw new Exception($"Connection String must contain `Database=`, it was {connectionString}");
            }

            var startIndex = connectionString.IndexOf("Database=") + 9;
            var colonIndex = connectionString[startIndex..].IndexOf(';');
            databaseName = connectionString.Substring(startIndex, colonIndex);
        }
        else
        {
            throw new Exception("--connectionString is required");
        }

        var scriptsPath = string.Empty;
        scriptsPath = !string.IsNullOrWhiteSpace(opts.ScriptsPath) 
            ? opts.ScriptsPath 
            : throw new Exception("--scriptsPath is required");

        var baseNamespace = typeof(Program).Namespace + databaseName;

        // Pre deployments - if we want to add that capability
        // WriteToConsole("Start executing predeployment scripts...");
        // string preDeploymentNamespace = baseNamespace + ".PreDeployment";
        // var preDeploymentScriptsPath = scriptsPath + "/Migrations";
        // RunMigrations(connectionString, preDeploymentNamespace, preDeploymentScriptsPath, true);

        // Migrations
        WriteToConsole("Start executing migration scripts...");
        var migrationScriptsPath = scriptsPath + "\\Migrations";
        RunMigrations(databaseName, connectionString, migrationScriptsPath, false);

        // Post deployments
        WriteToConsole("Start executing postdeployment scripts...");
        string postDeploymentScriptsPath = scriptsPath + "\\PostDeployment";
        RunMigrations(databaseName, connectionString, postDeploymentScriptsPath, true);
        return 0;
    }
    catch (Exception e)
    {
        WriteToConsole(e.Message, ConsoleColor.Red);
        return -1;
    }
}

static int RunMigrations(string databaseName, string connectionString, string scriptsPath, bool alwaysRun = false)
{
    WriteToConsole($"Executing scripts in {scriptsPath} for {databaseName}");

    var builder = DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsFromFileSystem(scriptsPath)
        .LogToConsole();

    builder = alwaysRun ?
         builder.JournalTo(new NullJournal()) :
         builder.JournalToSqlTable("dbo", "SchemaVersions");

    var executor = builder.Build();
    var result = executor.PerformUpgrade();

    if (!result.Successful)
    {
        throw new Exception(result.Error.ToString());
    }

    ShowSuccess();
    return 0;
}

static void ShowSuccess()
{
    WriteToConsole("Success!", ConsoleColor.Green);
}

static void WriteToConsole(string msg, ConsoleColor color = ConsoleColor.Green)
{
    Console.ForegroundColor = color;
    Console.WriteLine(msg);
    Console.ResetColor();
}

static int Error(IEnumerable<Error> errors)
{
    var result = -2;
    Console.WriteLine("errors {0}", errors.Count());
    if (errors.Any(x => x is HelpRequestedError || x is VersionRequestedError))
        result = -1;
    Console.WriteLine("Exit code {0}", result);
    return result;
}

class Options
{
    [Option('c', "connectionString", Required = true)]
    public string ConnectionString { get; set; } = string.Empty;

    // add an environment folder if/when needed based on https://dasith.me/2020/06/08/database-project-conversion-to-migrations/
    //[Option('e', "environment", Required = true)]
    //public string Environment { get; set; } = string.Empty;

    [Option('r', "reportPath", Required = true)]
    public string ReportPath { get; set; } = string.Empty;


    /// <summary>
    /// Run the migrations in the given path for the database
    /// </summary>
    [Option('s', "scriptsPath", Required = true)]
    public string ScriptsPath { get; set; } = string.Empty;

    public override string ToString()
    {
        return $"{ConnectionString} || {ReportPath} || {ScriptsPath}";
    }
}

{
  "profiles": {
    "DbUpRunner": {
      "commandLineArgs": "--connectionString=\"Data Source=local\\DEVELOPER;Database=DbUp_Import;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True\" --scriptsPath=\"C:\\git\\DbUp\\DbUpRunner\\DbUp_Import\"  --reportPath=\"C:\\git\\DbUp\"",
      "commandName": "Project"
    }
  }
}