SQL Database Project with Git Feature Branch Workflow

By: Haroon Ashraf   |   Updated: 2020-10-30   |   Comments   |   Related: > Database Design

Problem

As a SQL Server database developer, how can I utilize a source control system when working on different features requested by customers?

Solution

The solution is to build and manage the database using a SQL Database Project along with using Git source control that allows branching workflow to be able to work on new features.

SQL Database Project and Git Feature Branch Workflow

A SQL Database Project as part of SQL Server Data Tools (SSDT) has built-in support for declarative database development to build, test, refactor, deploy and maintain databases which comes with many out-of-the-box database support tools including complete support for Database Lifecycle Management.

Git is one of the best source control tools to protect, save, share and publish your code to a central repository which can be easily linked or trigger the delivery of the project.

To learn more about SQL Server Data Tools (SSDT) and SQL Database Project please go through the Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT) tip.

Git Feature Branch Workflow Overview

Git is a feature-rich source control which supports multiple workflows including Feature Branch workflow. A workflow is a standard pattern that you follow to achieve your (database) coding objectives and goals by architecting your solution towards the most optimum path standardized after several revisions.

Git Feature Branch Workflow is specifically available to address those problems where features need to be added to the existing or proposed (database) system in a very robust and controlled manner.

We can say that it is a feature focused workflow for those database projects where features are integral part of the solution or in other words Git Feature Branch workflow is best suited for features-driven Database Projects.

A Feature from this workflow context can be anything from a single database object (which serves the purpose) to a full-fledge project feature consisting of multiple database objects, such as tables, views, procedures and their integrated working.

Git Feature Branch Workflow Strategy

In a typical and most common Git source control scenario you have to have at least the following two branches of the database code:

1. master branch
2. dev branch

Please go through the tip SQL Database Project Code Branching in Git to see above code branching in action with the help of a fully implementable walkthrough.

As opposed to the typical Git branching scenario, we have to come up with dynamic feature branching where each feature is managed by a separate branch of the code and can be removed once considered complete.

So, Git Feature Branch Workflow consists of the following branches of the database code:

1. master branch
2. feature-1 branch, feature-2 branch, … ,feature-n branch

The completion of a feature (Feature Branch) is to merge the changes of the feature branch with master (branch).

The journey begins with branching off from master to start with a new feature branch and then merging the changes back with the master branch once the feature is complete and so on.

Git Feature Branch Workflow Walkthrough

Let us understand Git Feature Branch Workflow for database development and management with the help of a walkthrough considering a simple scenario.

Assumptions

This tip assumes that the readers who are interested to implement the walkthrough are familiar with the following tools and technologies:

1. SQL Server Data Tools (SSDT)
2. Azure DevOps
3. Git source control

This tip also assumes that the Azure DevOps online account registration and the installation of SQL Server Data Tools (SSDT) for Visual Studio has also been done.

Please register for an Azure DevOps account if you have not registered yet but interested to implement the walkthrough.

Creating Azure DevOps Project to work locally

One of the first things to do is to create a new Azure DevOps project to start working locally on your database code.

Please go to the Azure DevOps portal and sign in to your account followed by creating a new Azure DevOps Project called SQL Database Project with Git Feature Branch Workflow:

Please go to the Repos section of the project in the left navigation bar in Azure DevOps portal and click the current repo and then select Manage Repositories option to rename the repo:

Rename the repository as sql-feature- workflow. The repository once renamed looks as follows:

Please switch back to the Repos section and click Switch to default sql-feature-workflow repository button:

Create local copy of the repo as follows:

You are now ready to work on the database solution while your work remains connected with the Git source control via master branch (at the moment).

Please switch to the Team Explorer window in Visual Studio:

Creating SQL Database Project

Considering the same Team Explorer window shown above please click New… to create a new SQL Database Project called WatchesFW under a solution called Feature Workflow:

Go to the Solution Explorer to see the blank database project created on the master branch of the code repository:

Saving blank database project on the master branch of the repo

This is the beginning of your database work and it is fine to push the blank database project directly to the master branch to represent the first version of the pseudo (serving as a placeholder) production code.

Before you push your changes to the master branch of the remote repo it is a good practice to compile the project by pressing F5.

If the project is successfully compiled please proceed further with saving your changes by switching to Team Explorer window and adding the comment "Initial commit" followed by selecting Commit All and Push option as shown below:

Navigate to the Repos section of the Azure DevOps portal online to see your saved changes on the master branch:

Analyzing new Feature request

Let us assume that you have received a new feature request as follows:

"The business user should be able to add new customers."

Now this feature seems looks like an object bundle in which at least you have to add the following objects:

1. A table named Customer to store the customers data
2. A stored procedure named AddCustomer to add new customers

Creating a Feature branch to add new customers

Please click on the master branch and then click + New Branch as shown in the figure:

Create a new remote branch named add-customer-feature:

Creating Local Feature branch and switching it on

We need to create local copy of the feature branch from the remote version to start working on the desired feature.

In the Visual Studio Team Explorer window right click on the master branch under remotes/origin folder and click Fetch to see the newly created remote feature branch:

Next right click on add-customer-feature branch under remotes/origin folder and click on New Local Branch From…and then click Create Branch button as follows:

The status bar at the bottom of the visual studio should be showing add-customer-feature as an active branch now:

Add Customer table and AddCustomer Procedure to the Database Project

Now that we are working on the feature branch of the repo we need to add the desired feature by adding the Customer table using the default settings:

Add a new stored procedure called AddCustomer to the SQL Database Project using the following code:

CREATE PROCEDURE [dbo].[AddCustomer]
   @CustomerId int = 0
AS
   INSERT INTO dbo.Customer
   (Id)
   Values
   (@CustomerId)
RETURN 0

Save your Feature (commit your database changes to the remote feature branch)

Compile the project by pressing F5 and once the new feature is tested successfully (we are skipping this step) it must be committed to the remote branch of the repository just like the way we pushed changes to the master branch in the beginning.

Add the comments "Feature complete" and commit the changes:

Merge your changes with master

Once you or the developer who is tasked to work on this feature is good to go, the next step is to push the feature to the production branch which is master branch and this can be done by creating a pull request to merge your changes.

Please go back to the Repos section of Azure DevOps portal and click "Create a pull request":

You can also remove the feature branch during the pull request since the changes are going to be merged with the master branch and the feature is complete.

Please choose the following options during the pull request (unless you have a legitimate reason not to delete the feature branch):

The merge request is complete and the feature branch has been removed from the remote repository:

Please remember you have to manually remove the local feature branch from the Visual Studio Team Explorer (and in some cases the remote feature branch as it may not have not refreshed automatically) in order to perform some clean up activity after a feature is complete.

Now if you view Repos > Files you can see the updated master branch showing the latest feature added:

Congratulations you have successfully implemented Git Feature Branch Workflow with SQL Database Project which is an excellent workflow to work with the business requirements which often come in the form of feature requests.

Next Steps

• Please refer to the tip to add a new table WatchType by creating a new feature branch watch-type-feature using Git Feature Branch Workflow following finally merging the changes with mater branch
• Please implement Git Feature Branch Workflow for the sample database referenced in Free Database Unit-Testing Framework for SQL Server tip by sub-diving the setup script into two different features where one feature deals with adding the tables and the other feature handles the reference data for the tables.
• Please follow the tip to add more features to your project by bringing in Watch and WatchOrder tables along with their reference data to the project using Git Feature Branch Workflow.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips