Gitflow Workflow Hotfix Branching for SQL Database Projects


By:   |   Updated: 2021-01-18   |   Comments (2)   |   Related: More > DevOps


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)
Gitflow Workflow Hotifix Branching

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:

Azure DevOps Project

Create a repository

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

New gitflow hotfix repository

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:

local copy of the repo

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

Creating repo folder locally

Create a New SQL Database Project under a Solution

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

New SQL Database Project

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.

dev branch from master

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:

Long Toy Name cannot be saved

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).

Increase Length of the NAME Column

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:

Merge hotfix 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:

Completing the pull request

The successful merge is as follows:

Successful Merge

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.

Merge hotfix changes with master

The results are as follows:

Merge Successful

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


Last Updated: 2021-01-18


get scripts

next tip button



About the author
MSSQLTips author Haroon Ashraf

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

View all my tips




Comments For This Article




Wednesday, February 10, 2021 - 1:06:30 PM - Haroon Ashraf Back To Top (88212)
Thanks for your comments.

You have to use Visual Studio or any other alternative tool but I donít think so you can use Visual Studio Code to build SQL Database Projects.

I did a quick check for you but (at the time of writing this comment) did not find any promising add-on for Visual Studio Code to be used for developing SQL Database Projects.

Wednesday, February 10, 2021 - 10:22:45 AM - Salam Back To Top (88209)
Hi, very interesting article. I don't have VS, I use Visual studio code, is it possible to create a sql project with VSC? Thanks


download





Recommended Reading

Creating a Visual Studio Database Project for an existing SQL Server Database

Basic Database Continuous Integration and Delivery (CI/CD) using Visual Studio Team Services (VSTS)

Getting Started with GitHub for SQL Server Developers

Continuous database deployments with Azure DevOps

Using Azure DevOps CI CD to Deploy Azure Data Factory Environments














get free sql tips
agree to terms