Gitflow Workflow Feature Branching for SQL Database Projects


By:   |   Updated: 2020-12-30   |   Comments   |   Related: More > Database Design


Problem

As a SQL Server database developer, I would like to understand how to use Gitflow workflow to meet the growing demand of new database related features requested by our customers.

Solution

The solution is to use Gitflow workflow Feature branching model with a SQL Database project.

Prerequisites

This tip assumes the following:

  • Understanding of Gitflow workflow concepts and implementation
  • Understanding of Gitflow Feature Branching
  • Azure DevOps account to create Projects
  • SQL Server Data Tools (SSDT) to create and manage SQL database project

Please read the following tips to get an introduction of Gitflow workflow if you are not already familiar with it

You can create an Azure DevOps account by clicking this link: Free Azure DevOps account.

Applying Gitflow Feature Branching

If you have neither read the tips mentioned above nor are you familiar with Gitflow feature branching model, it is highly recommended to go through the reference tips above first.  However, it is not difficult to understand this style of database code branching if you have used Git source control before.

Recap: Gitflow Feature Branching

This is the kind of database code branching model which is used to build features requested by customers from time to time. Once you are done, a feature (database code related to that feature) is sent to the dev branch (merged with dev code).

A Gitflow workflow in a feature branch is straight away created from the dev branch and then merged back with dev branch once the work is done. It will then be removed as it is no longer required.

Git Feature Branch Workflow vs Gitflow Workflow Feature Branch

It is important not to confuse Git Feature Branch Workflow with Gitflow Workflow Feature Branch as they both work differently.

For example, Git Feature Branch Workflow is well suited for you to receive requirements for new features, but you have to think of other things such as a bug fix as a feature. This can be a bit tricky to understand, but it works well in that context and more importantly you can branch off from the master to begin your work and then merge your feature back with the master branch.

On the other side, Gitflow Workflow Feature Branch has an advantage as it offers separate branching for other pieces of code such as bugs and releases. Plus, it is well integrated into Gitflow workflow for long term benefits.

Comparing Git Workflows

Real World Scenario: Search Customer Feature

Let's now apply the Gitflow Workflow Feature branching model considering a real world scenario in which you have been asked to add a new Search Customer Feature to the database system which holds the information about the products and customers.

We are assuming that this database was created long ago and already has a Customer and Product table that is all managed through a SQL Database Project and the database project code is managed through an Azure DevOps project repository using Git source control.

Please go through this tip to find more detailed information about the project setup steps mentioned in this tip.

Creating a DevOps Project

Please sign into your Azure DevOps web portal and create a project called Gitflow Workflow:

Creating a DevOps Project

Creating a Repository

Please create a new repository or rename the default repository as gitflow-feature:

Creating a Repo

Creating a dev branch

We are first going to create a dev branch to replicate the scenario where we have both dev and master.

Click on the master branch of the repo and then click on new branch:

Creating a new branch dev from master

Create a new branch called dev:

Creating a new branch dev from master

Connect to the dev branch of the Azure DevOps Project locally

Once the dev branch is created in the Azure portal please copy the repository in Visual Studio to work locally:

Copying the repo locally

Next, create a local dev branch from the remote dev branch in the Team Explorer window of Visual Studio as shown below:

Creating a local dev branch

A dev branch is created and the current code branch is going to be switched from master to dev.

Create SQL Database Project under a Solution

Create a new SQL Database Project ToyStoreGWFW in a solution you name as Gitflow Workflow Feature:

Create SQL Database Project under a Solution

Switch to the Solution Explorer to view the blank project:

Blank SQL Database Project

Add Product and Customer Table

Since we are currently switched to the dev branch of the database code, let's assume that it contains some work in the form of two tables which we can imitate by adding two tables to the database project.

Please add product table to the SQL Database Project using the following code:

CREATE TABLE [dbo].[Product]
(
   [ProductId] INT NOT NULL IDENTITY, 
    [Name] VARCHAR(50) NOT NULL, 
    CONSTRAINT [PK_Product] PRIMARY KEY (ProductId) 
 
)

Similarly create another table called Customer with the help of the following T-SQL script:

CREATE TABLE [dbo].[Customer]
(
   [CustomerId] INT NOT NULL IDENTITY, 
    [Name] VARCHAR(50) NOT NULL, 
    CONSTRAINT [PK_Customer] PRIMARY KEY (CustomerId) 
 
)

Save (local) dev branch changes

Let's commit these changes to the remote dev branch by adding the comment "Customer Product tables added" followed by selecting Commit All and Push option.

Saving dev branch changes to remote repo

The project after the changes is shown below:

Project saved with two tables

Debug the Project to deploy changes

Press F5 to build and deploy changes to the debug database. The debug database is seen as follows:

Debug Database

Understanding new Search Customer Feature Request

Now we are going to demonstrate how a new feature request fits into the whole scenario which has the following situation from a database code perspective:

  • master branch is empty (no production ready code yet)
  • dev branch has two tables Customer and Product
  • A new feature request has just arrived

Create a new feature branch (search-customer-feature)

We are going to sync the local dev with remote dev (although we already know they are in synch because we have just pushed the local changes to the remote repository) since this is good practice before creating a new branch from another branch.

Create a feature branch named search-customer-feature from the dev branch:

Creating a new feature branch from dev

Based on the choices above, as soon as a local feature branch is created, it is ready to be used for your feature development work.

Create a SearchCustomer stored procedure in the feature branch

The newly requested feature can be introduced in the form of a stored procedure which serves the purpose.

Right click on the SQL Database Project and click Add > Stored Procedure.

Create a stored procedure named SearchCustomer using the following code:

CREATE PROCEDURE [dbo].[SearchCustomer]
   @Name VARCHAR(50)=''
AS
   SELECT C.CustomerId,C.Name FROM dbo.Customer C
   WHERE C.Name LIKE '%@Name%'
RETURN 0

This is illustrated as follows:

Creating SearchCustomer stored procedure in the search-customer-customer feature branch

Commit changes to the feature branch (remote repository)

Let's save these changes to the remote repository in the (feature branch).

Press the pen icon at the bottom of the Visual Studio (status bar) or use the shortcut key CTRL+ALT+F7 to save the newly built stored procedure and add the comments "Added SearchCustomer stored procedure", followed by clicking Commit All and Push:

Commit changes to the feature branch (remote repository)

Merge feature branch changes with dev branch

We have just finished the feature and it is time for the hand-over session. We need to integrate our changes in the feature branch with the dev branch.

This requires a pull request. Please go to Team Explorer – Home click on Pull Requests:

Creating a new pull request to integrate feature changes with dev

Next, click on the New Pull Request link which will take you to the Azure Web portal:

New Pull Request Link

Create a new pull request, after making sure you are merging search-customer-feature branch with dev followed, by clicking the Create button:

Creating a new pull request

Once you initiate the process, all merge conflicts (if any) are shown to you. This is not something we are expecting since we know that we have worked on different things in different branches. We are good to go.

You can also choose (this is recommended) to delete the feature branch once the merge is complete as highlighted below:

Delete feature branch after merging

After some time, you are going to see the following results:

Merge complete

View the database code finally saved to the dev branch:

Database code saved to the dev branch

Congratulations! You have successfully implemented Gitflow Workflow Feature Branching starting from a fresh feature branch from dev followed by pushing your changes back to dev.

Next Steps
  • Please check SQL Database Project with Git Feature Branch Workflow tip and compare it with this tip to see if you prefer one over the other and feel free to add comments at the bottom of this tip
  • Please go through this tip and see if you can replace it with Gitflow Workflow Feature Branch model
  • Keeping this tip in mind, try to merge your dev branch code mentioned in the previous point with the master branch
  • Try adding Search Product Feature by creating search-product-feature branch from dev using the sample database in this tip
  • Please go through Basics of Gitflow Workflow for SQL Database Projects Part 1 and Part 2  to improve your Git skills further
  • Please refer to this tip to see detailed steps of how to use dev branching with SQL Database project and try adding the work done in the reference tip to the current project


Last Updated: 2020-12-30


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





download





Recommended Reading

Find and Remove Duplicate Rows from a SQL Server Table

Working with SQL Server Extended Properties

Create a Star Schema Data Model in SQL Server using the Microsoft Toolset

What is a GUID in SQL Server

SQL Server Database Diagram Tool in Management Studio














get free sql tips
agree to terms