DbUp Setup for Easy Management and DB Upgrade (.Net)

DbUp is a tool which can be used to maintain the DB scripts in the project itself. It’s very easy to use but the docs are very up to date. I will raise the PR for docs update, hope they will accept 🙂

So, what we have:

1. Single/Multiple startup projects

What we want to achieve

1. Run newly added scripts automatically to the database which is configured in the appsettings
2. It could be for single project or for multiple startup projects (We may be separating the tables based on schemas)

Steps to follow:

1. Create a new console project.
2. Read the appsetting (You may hardcode the connection string or make it dynamic based on the targeted assembly (startup project))

To select the appsetting from assembly you have to reference it in the console project and use the following snippet the location of appsettings

 Path.GetDirectoryName(typeof(FullNameSpace.Program).Assembly.Location);

Next make sure that DB and Schema and Journal table are created:

var dbSchemaEngineBuilder = DeployChanges.To
    .SqlDatabase(connectionString)
    .JournalTo(new NullJournal());

dbSchemaEngineBuilder.WithScripts(new JournalScriptProvider());

var schemaEngine = dbSchemaEngineBuilder.Build();
schemaEngine.PerformUpgrade();

Script provider (Does not contain the script to create DB. You can that):

    public IEnumerable<SqlScript> GetScripts(IConnectionManager connectionManager)
    {
        var scripts = new List<SqlScript>();

        var createDbUpSchema = @"IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'DbUp')
                                    BEGIN
                                    EXEC('CREATE SCHEMA [DbUp] AUTHORIZATION [dbo];')
                                    END";
        scripts.Add(new SqlScript("Create DbUp Schema",
                                    createDbUpSchema,
                                    new SqlScriptOptions() { RunGroupOrder = 1 }));

        var createMyTableSchemaSchema = @"IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MyTableSchema')
                                    BEGIN
                                    EXEC('CREATE SCHEMA [MyTableSchema] AUTHORIZATION [dbo];')
                                    END";
        scripts.Add(new SqlScript("Create MyTableSchema Schema",
                                    createMyTableSchemaSchema,
                                    new SqlScriptOptions() { RunGroupOrder = 2 }));

        var createJournalTable = @$"
                                    IF OBJECT_ID(N'DbUp.MyUpgradeJournal', N'U') IS NULL 
                                        BEGIN CREATE TABLE [DbUp].[MyUpgradeJournal] (Id int not null identity(1,1), ScriptName nvarchar(200) not null, Applied DateTime Not Null, Constraint [PK_DbUp_MyUpgradeJournal] Primary Key ([Id] ASC)); END;
                                    ";
        Console.WriteLine(createJournalTable);
        scripts.Add(new SqlScript("Create DBUp My Upgrade Journal Table",
                                    createJournalTable,
                                    new SqlScriptOptions() { RunGroupOrder = 3 }));

        return scripts;

    }
}

Now setup the engine which will run the scripts:

 var dbUpgradeEngineBuilder = DeployChanges.To
     .SqlDatabase(connectionString)
     .WithScriptsEmbeddedInAssembly(typeof(Program).Assembly, (s) =>
     {
         return s.Contains("Script_", StringComparison.OrdinalIgnoreCase);
     })
     .JournalToSqlTable("DbUp", "MyUpgradeJournal")
     .WithTransactionPerScript()
     .LogToConsole();
 var dbUpgradeEngine = dbUpgradeEngineBuilder.Build();

 Console.WriteLine("Discovered scripts, {0}", dbUpgradeEngine.GetDiscoveredScripts().Count);

 if (dbUpgradeEngine.IsUpgradeRequired())
 {
     Console.WriteLine("Upgrades have been detected. Upgrading database now...");
     var op = dbUpgradeEngine.PerformUpgrade();
     if (op.Successful)
     {
         Console.WriteLine("Upgrade completed successfully");
     }
     else
     {
         Console.WriteLine("Error happened in the upgrade. {0}", op.Error.Message);

         throw new Exception("Error upgrading database");
     }

 }

Done.

Cheers and Peace out!!!