Gitflow Workflow Hotfix Branching for SQL Database Projects

Problem

As a database developer, Git is a great tool for source control. In this tip we look at how you can utilize Git source control to build and deploy database hotfixes.

Solution

Using the Gitflow Workflow Hotfix Branching model with a SQL Database Project we can meet any database hotfix related requirements. The following digs into how this can be done along with examples.

About Gitflow Workflow Hotfix Branching

The best way to begin is to understand the basic architecture of this branching model from a database development perspective.

Prerequisites

It is assumed that readers of this tip have a registered Azure DevOps account and are fairly knowledgeable about the basics of SQL Database Projects and Git source control from a database development perspective.

If you struggle with the basics of Git source control, please go through the following tips:

Reference Tip

Please find important information about Gitflow Workflow Hotfix branching in the following tip: Branching with Gitflow Workflow for SQL Database Projects.

Definition Review

Let’s review the definition of this workflow in light of the Reference Tip as follows:

"Gitflow Workflow Hotfix Branching model facilitates database hotfix development management in such a way that a hotfix branch is created from the master (where production code lies) and then merged with both master and dev once the work is done."

Life Cycle of Hotfix Branch

A hotfix branch is created when needed and deleted once it serves the purpose. Once the hotfix work is complete and merged with dev and master (production code), it is deallocated to ensure that the source control branching remains efficient and effective.

We can summarize this as follows:

A hotfix branch is created per requirement to design a database hotfix
Once complete, it is merged with dev branch to resolve any (merge) conflicts
It is then merged with master branch to resolve any conflicts
Once merged, it is deleted to in order to be created again (for a different hotfix)

Applying Gitflow Workflow Hotfix Branching

Let’s get some hands-on experience applying Gitflow Workflow hotfix branching model based on a real-time scenario.

Real-Time Scenario: Gitflow Workflow Hotfix Branching

A hotfix needs to be applied to a production database called ToyStoreGFWH which contains the following information (tables):

Sale
Toy

The ToyName column in the Toy table is just 20 characters long and is now causing a problem when the staff is trying to store information about a new toy which has a name exceeding 20 characters.

This seems to be a hotfix case since it is structural change is needed to implement the production database where this table is already in use with the exception of the problem which is reported.

Let’s now setup this scenario. As we are going to be skipping some small details, please refer to the SQL Database Project with Git Feature Branch Workflow tip to see detailed steps similar to the ones we are going to take in this tip.

Create an Azure DevOps Project

Please sign in to the Azure Portal and create a new DevOps project called Gitflow Workflow:

Create a repository

Create a new gitflow-hotfix repository for the project you have just created:

Create local copy of the repo (master branch) in Visual Studio

Please copy the repo to create local (master) branch of the repo in Visual Studio as follows:

Create a local folder for the repo to work locally on the code:

Create a New SQL Database Project under a Solution

Create a new SQL Database Project called ToyStoreGFWH under a solution Gitflow Workflow Hotfix:

Simulating Production Database Issue

We have to simulate a production database issue in order to work on a (database) hotfix using Gitflow Workflow Hotfix branching strategy.

This leads us to simulate a production database which contains the following tables:

Sale
Toy

However, we also have to make sure that the column ToyName has a data type of VARCHAR(20) to replicate the problem.

Per standard practice, we have to assume the following:

Sale and Toy table with ToyName column Varchar(20) was created in the dev branch
The changes were merged with master branch

We can create both tables in the master branch just to simulate the production issue. Please remember that you should always be starting with dev and merging your changes with master in a typical Gitflow Workflow environment.

Simulate Sale Table and Toy Table saved in the master branch

Please add a new table Sale to the SQL Database Project using the following T-SQL script:

CREATE TABLE [dbo].[Sale]
(
   [SaleId] INT NOT NULL IDENTITY ,
   [SaleDate] DATETIME2, 
    CONSTRAINT [PK_Sale] PRIMARY KEY (SaleId)
)

Next, create another table Toy in the database project as follows:

CREATE TABLE [dbo].[Toy]
(
   [ToyId] INT NOT NULL IDENTITY ,
   [NAME] VARCHAR(20), 
    CONSTRAINT [PK_Toy] PRIMARY KEY (ToyId)
)

Build the project and commit the changes to remote repo in the master branch by using the comment "Simulating Sale and Toy table in the production database":

Simulating Sale and Toy table in the production database

Simulate dev branch contained the database code originally

We have to simulate that the dev branch had these tables before they were saved to the master branch.

The quickest way to simulate this is to create dev from master:

Creating dev from master to simulate dev

Go to Sync and click Push link to create remote version of your local dev branch.

Deploy changes to the debug database

Debug the project by pressing F5. This is going to Build followed by deploying changes to the debug database (on successful build):

Build and Deploy Changes to Debug Database

Replicate the Production Issue

Let’s now replicate the issue faced in the Production environment when an end-user tries to insert a new toy name which exceeds 20 characters.

Go to the debug database ToyStoreGFWH, right click on the Toy table, and click on View Data. Try adding NAME "Solar Powered Programmable Robot" to the Toy table:

We see that the long name could not be saved into the table because of the length of the column.

Create a hotfix branch from master

To fix the problem, let’s create a hotfix branch called toy-name-hotfix from master branch.

Creating a new branch toy-name-hotfix from master

Increase Length of the NAME Column

Modify Toy table and change the length of the column NAME from VARCHAR(20) to VARCHAR(60).

Save the changes made to the table and build the project (pressing CTRL+SHIFT+B shortcut key) to see if everything is fine.

Commit Changes to the remote repository

Please save the changes to the hotfix branch of the remote repository with the following comments: "NAME column can have a toy name up to 60 characters now"

Choose Commit All and Push to put the work under Git source control:

Commit and Push changes to the hotfix branch

Merge hotfix changes with dev

Create a pull request to integrate your changes with dev:

Please remember not to delete the branch during the merge process as we have to merge the same changes with master as well:

The successful merge is as follows:

Merge hotfix changes with master

Finally, please merge the hotfix changes with master branch and this time you can delete the hotfix branch on a successful merge.

The results are as follows:

Congratulations! You have successfully implemented Gitflow Workflow Hotfix branching to resolve a production issue related to the database (project) by following the Git standard practice. You can reuse the approach to resolve any other similar sort of issues.

Next Steps

Please deploy changes to the debug database and try adding the long name which failed before the hotfix was applied
Please create another hotfix branch linking-toyid-hotfix to create foreign key column ToyId in the Sales table and join it with the primary key column ToyId in the Toy table
Please follow the SQL Database Project Code Branching in Git tip to continue with new dev (branch) work by adding WatchType table in the ToStoreGFWH database followed by creating a hotfix watchtype-detail-hotfix to increase the column Detail length of the WatchType table from VARCHAR(150) to VARCHAR(200).
Please merge the hotfix changes mentioned in the previous point with dev. Then, merge them with master per standard practice of Gitflow Workflow Hotfix branching

Resources

Community

Subscribe