Automate database changes from Kentico Hotfixes when running Kentico Continuous Integration using DbUp and GruntJS

Written by Ilesh Mistry
8th June 2017

20 minute read

Part 1 of this blog focused on Kentico hotfixes and how they work with the different tools out there as well as the process of applying a Kentico hotfix while running the Kentico CI environment. Our next step is to see how we could potentially automate the database changes so that they can be passed around within teams who are running Kentico CI locally on their machines, making sure they have the latest hotfix applied.

Tool for automating the database changes

There are many tools out there for automating database changes and different ways to achieve this possibility. Looking around and speaking to a few of my colleagues at MMT Digital about this, they recommended looking at DbUp.

What is DbUp?

DbUp is a lightweight .Net library, which is installed via a Nuget package, that will allow you to apply SQL changes. Another benefit to using this, is that it remembers which queries have run before, to avoid duplication. Along with this, there is the benefit of being able to run this tool via command line, which I know everybody loves!

Other benefits of using DbUp

  • Really easy to set up
  • It can potentially be run during integration testing
  • It can be used with deployment tools, such as Octopus Deploy using Power Shell script.
  • The script files are in source control and easy to pass between development teams.

Installation process for DbUp

There are some very simple to follow instructions on the DbUp website to get you started.

Add your SQL scripts into a Visual Studio C# project.

Tip - For each script, make sure the 'Embedded Resource' is selected. This is really important so that the tool knows to run each SQL script.

Install the DbUp package using NuGet

Install-Package DbUp 

Then in the Program.cs, add the following code to retrieve your connection details and to perform the steps for this:

static int Main(string[] args) 
{ 
    var connectionString = 
        args.FirstOrDefault() 
        ?? "Server=(local)\\SqlExpress; Database=MyApp; Trusted_connection=true";
    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; 
} 

Customisations with DbUp

The script on the DbUp website is a sample to get you started, you can extend it for your needs and also when you need to retrieve additional information from your project, using alternative methods.

We have a process internally here at MMT Digital and there are also various examples online that show you how to retrieve the database connection string in different ways. Here is an example from Emmanuel Tissera from Get Started.

What's next after installing DbUp?

After a successful installation, you can run your SQL scripts via the command line. You will need to navigate to the following location: SolutionName\ProjectName\bin\Debug\ProjectName.exe.

Here you will you will see DbUp returning the scripts that successfully ran and completed on your projects. Don’t forget, if you rerun this after the SQL scripts have already run once, then they will not run again.

This is awesome right? But how can we take it a step further by automating it within our teams and running the command after a pull command?

Time for task runner GruntJS to step in

If you have read some of my posts in the past, you will notice that I have spoken about GruntJS a lot! Check out my series on automating Kentico CI which talks about what GruntJS is, how it can be used to automate the Kentico CI restore tool using task runner and Git hooks.

GruntJS is a JavaScript task runner, which allows you to install various plugins to perform specific tasks. And the beauty of it is that you can combine these tasks to work together in harmony.

Now how can we utilise GruntJS to help us automate DbUp task?

We can utilise the GruntJS plugins to enable us to call the DbUp tool when we have successfully pulled from a remote branch.

I'm not going to run through the whole process of setting up GruntJS and the plugin for 'grunt exec', so please read my blog series on how to set it up and get that part working.

There are additional steps to take to add another section into the GruntJS file under the 'exec' area for dbup. This is where I placed mine...

exec: {
   ...
      dbup: {
         cwd: './SolutionName/bin/Debug/',
         stdout: true,
         command: 'start cmd /k SolutionName.exe'
     }
},

 

You can use any name for the ‘SolutionName, just make sure you follow the DbUp documentation.

Once you have inserted this into the GruntJS file, when you run 'grunt exec:dbup', it will run a new command prompt and run SQL scripts via the DbUp tool.

This is good, but we actually want it to run after a successful pull from the repository. To do this, you’ll need to follow a blog post I wrote and focus on the git hooks. In particular, the 'post-merge' git hook. Here is what you would need to add in there...

githooks: {
   all: {
      'post-merge': {
         template: './tools/Grunt/githooks-template/node.js.hb',
         dest: './.git/hooks',
         taskNames: 'exec:kentico exec:dbup'
      }
  }
},

I already had most of the git hooks code in the grunt.js file, so then I just included the 'exec:dbup' to call the command we created earlier.

Now you would need to run grunt again to bind this hook, but once you have done this, all successful incoming pulls will not only run the exec:kentico, but also the exec:dbup!

Don't forget post-merge capabilities

Remember that this git hook (post-merge) will only work if there are no merge conflicts from the pull.

If there are conflicts, you will either need to run the tool manually or you can extend what I did to run the Kentico CI restore tool when merge conflicts occur.,/p>

Here I would change this:

grunt.config.set('grunt-commit-message-verify', { minLength : 0,maxLength : 3000, minFirstLineLength : 5, // first line should be both concise and informative maxFirstLineLength : 200,maxLineLength : 80, // this is a good default to prevent overflows in shell console and Github UI regexes : { "check the commit" : {regex : /kenticoci/, explanation : "The commit should contain 'kenticoci' text for the Kentico CI tool to run" } }, shellCommand : "git log --format=%B --no-merges -n 1" // this is the default used if nothing is passed });

To no longer check for the text 'kenticoci' in the last commit message and instead change it to something like 'PostPullTools'

Then within the \node_modules\grunt-commit-message-verify\tasks or the location this is for you, add additional code to run the DbUp command tool. Again this is all in my other post about automating this process.

if (ok) {
   grunt.log.writeln("The commit message was:".yellow);
   grunt.log.writeln(sMsg);
   grunt.log.ok();
   var shellCommandKenticoCI = 'start cmd /K "cd tools/Grunt && grunt exec:kentico"';
   var runKenticoCICommand = exec(shellCommandKenticoCI);
   runKenticoCICommand ;

   var shellCommandDbUp = 'start cmd /K "cd tools/Grunt && grunt exec:dbup"';
   var runDbUpCommand = exec(shellCommandDbUp);
   runDbUpCommand;

That should be it!

Summary

Now you will have a process where you can automatically run the Kentico hotfix SQL queries for each member of your development team working locally to make sure they get the latest hotfix applied to their installation of Kentico. All of this using Kentico CI, DbUp, GruntJS and Git hooks.