SQL Database Project Code Branching in Git

By:   |   Comments (6)   |   Related: > DevOps


As a SQL Server database developer, I need to understand and implement the concept of database code branching particularly in the case of state based database development using SQL Server Data Tools (SSDT).


The solution is to implement proper database code branching in a source control like Git after getting familiar with branching concepts and their implementation specifically from a SQL Database project point of view.

Moreover, implementing proper database code branching in Git or a similar source control can help you speed up database development, testing, refactoring and deployment there by creating a smooth and effective Database Lifecycle Management (DLM) process.

About Database Project Code Branching

It is worth reviewing SQL Database Project code branching and related concepts for beginners before we jump into implementing the solution.

SQL Database Project

A SQL Database Project as the name indicates is a special kind of project created and managed in SQL Server Data Tools (SSDT) which helps to quickly design, build, test, refactor and deploy database and its related objects as per requirement to the target environments.

State Based Database Development

In a state based development SQL Database Project which contains the database structure and its reference data (in some cases), is considered a single source of truth rather than the deployed database.

The SQL Database Project initiates the database development process and then it gets published to the target database through a number of ways. The database developer behind the SQL Database Project simply adds new objects or modifies existing objects there by changing their previous state to a new one and then publishes those changes to the target database environments without any need to write alter scripts.

Database Code Branching in Git

The importance of database code branching when using a source control like Git is almost same as maintaining your database structure through SQL Database Project in a state based development.

In a professional working environment, database development via SQL Database Project or any other database development approach is backed by source control systems such as Git which allows you to work on multiple versions of the code known as branching.

For example, when you start developing a database project backed by Git source control you are given a default branch of the repository to work under which is called the master branch.

However, the master branch is considered to be deployment ready code so basically you work in another branch such as dev and then integrate your changes with the master branch.

Finally, it is through the master branch you deploy new database changes to Dev, Test and Production environments in a traditional database development scenario with connected source control.

This is illustrated as follows:

SQL Database Project Code Branching in Git Source Control

Azure DevOps

In the previous section I talked about creating a SQL Database Project in SQL Server Data Tools (SSDT) and then adding it to the source control, but what if you start with out of the box source control support right from the beginning of the creation of your SQL Database Project.

Azure DevOps is set of services including Azure Git Repos (repositories) which offers new projects with built in source control support and a lot more features for teams and individual developers to build, test, deploy and manage their web/database solutions.

Implementing Database Project Code Branching

The database project source code branching is very important in day to day commercial database developments.

Create an Azure DevOps Project

Please go to the Azure DevOps web page and then sign in to your account.

In case you have do not have an Azure DevOps account then please sign up for one if you would like to implement the walkthrough in this tip.

Create an Azure DevOps project called database-project-code-branching:

Creating an Azure DevOps project database-project-code-branching

Import DevOps Project Repository Locally

Click Repos from the left navigation bar in Azure DevOps to go to the project repository and import it locally on to your dev machine by choosing the second option to import the project as shown below:

Import DevOps Project Repository Locally

Check the Azure DevOps Project

Go to the Team Explorer in your Visual Studio and click Home button from the tool bar or press CTRL + 0 and H shortcut key to view Home section where you should see your imported Azure DevOps project (repository):

Checking locally imported Azure DevOps project repository.

Create a New SQL Database Project

Click New… under Solutions in Team Explorer and create a new SQL Database Project called Watches under Watches Database Solution as shown below: 

 Create a New SQL Database Project.

View the newly created SQL Database Project in Solution Explorer:

 Blank SQL Database Project

Understanding Git Repo and Branching

Please remember that as soon as we create an Azure DevOps project a Git repository is created with it and this is where we are going to save our database project code as the work continues.

Now the repository can have one or more branches where the master branch is considered the most stable version of your work.

It is the master branch which contains the changes to be deployed to Dev, Test and Production environments.

However, you should not directly work on master since this is considered to be the most stable or deployment-ready version of your code which finally gets published to the Production server.

Take a closer look on the status bar at lower right which tells you the following things about your SQL Database Project:

  • master - This means the currently selected Git branch is master
  • database-project-code-branching - This is the name of your Git repository
  • Four (4) - This means there are currently four changes in the (database) code which need to be put under Git source control.
Current Status of Git Source Control in the context of SQL Database Project.

Let's ignore the fourth icon showing 0 on the status bar for the time being to keep things simple.

Understanding Git Code Changes

As we can see there are four code changes to be committed to the Git source control, but we know we have not yet even created a single database object then what are those four changes?

The four pending code changes are as follows:

  1. We created a SQL Database Project which needs to be saved (even blank)
  2. We created the SQL Database Project in a Solution which also needs to be saved
  3. Git ignore file (a file which contains information about those files extensions which Git is not required to save)
  4. Git attributes file (a file which is required by Git to understand certain settings)

I am deliberately leaving the details of some of the Git things in this tip to keep us focused on the objectives of this tip and keeping things simple for the readers.

Click the pen icon to see the code changes:

Reviewing code changes.

Commit Default Changes to master

At this point there are no changes other than what came out of the box as we created a blank SQL Database Project in a solution.

So there is no harm saving these changes directly to the master branch as an exception to the rule I discussed earlier.

Since these changes are not our actual changes to the SQL Database Project, they are considered to be in the best stable form.

Please go to Team Explorer if you are not already there and type in comments Initial commit and click Commit All drop down and select and click Commit All and Push.

The changes are going to be saved instantly into the master branch.

Initial Commit

You can skip this step and wait for all these default changes to be saved with your database changes to the dev branch since we said no code should be directly saved to master branch.

However, both ways are fine as long as your first code push to the master branch does not include any change other than what you got by default when the project was created.

Requirement to add WatchType Table

You receive a requirement to add WatchType table to the database project for business use.

As soon as you receive a business requirement to add new table WatchType (or any other database object) then you should not add it directly to the master branch rather this is time to work on a different branch such as dev.

Create dev branch from master

Click master branch on the status bar (lower right corner) and then click New Branch:

 Create dev branch from master

Type in the name of the new branch dev and click the Create Branch button as shown below:

 Create dev branch from master

Please note that not only dev branch is created, but the Checkout branch check box if checked is going to switch you to the dev branch.

 dev branch created from master branch

Add WatchType Table

Add a new table called WatchType to the SQL Database Project using the following T-SQL:

CREATE TABLE [dbo].[WatchType]
   [WatchTypeId] INT NOT NULL IDENTITY(1,1),
   [Name] VARCHAR(50) NOT NULL,
   [Detail] VARCHAR(150) NOT NULL, 
   CONSTRAINT [PK_WatchType] PRIMARY KEY ([WatchTypeId])

The newly created table can be seen as follows:

Adding WatchType Table

Commit Changes to dev

Click the pen icon or CRLT+Alt+F7 shortcut key to commit changes to the dev branch.

Type in comments as "WatchType table added" and then click Commit and Push:

 Commit Changes to dev

Create a Pull Request

This step is very important when you see an option to create a pull request as soon as you push your changes into the dev branch.

Basically, a pull request means the request to integrate with the master branch and you must create a pull request when you are ready to push your changes to the final stage which is the master branch.

Click Create a pull request as follows:

 Create a pull request

This will take you to the Azure web portal where you are going to first create a pull request by clicking the button followed by clicking complete pull request and finalizing it by clicking Complete merge:

Complete Pull Request

Once the pull request is complete, you will be given an option to delete the source branch (dev) or not. I suggest keeping it (dev) so that you can carry on doing more changes and merging them with master by using pull requests. However, the branching does get deeper when you create a branch for each new feature.

Please see the below screenshot of a successful pull request:

 Successful Pull Request

Switch, Sync and Check master branch

Let us now check the master branch in Team Explorer which should have the latest changes.

Once you switch to the master branch in Visual Studio please remember to sync it with your remote master branch by clicking Sync in Team Explorer - Home:

 Sync local master with remote master

Go to the Solution Explorer to view the SQL Database Project to see the latest pushed into the master branch by the pull request and merge operation:

master Branch Updated

Congratulations! You have successfully implemented database code branching in Git.

You master branch is now ready to deploy the SQL Database Project to Dev, Test and Production environments.

Please remember as said earlier, the master branch was never updated directly rather we worked on the dev branch and then merged the changes with master.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

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

View all my tips

Comments For This Article

Thursday, December 31, 2020 - 2:26:11 PM - Haroon Ashraf Back To Top (87990)
Dear Salam,

Yes, it is fine to sync local master with remote master if you are concerned about a new developer using your machine/environment may be mislead by looking at the outdated code.

However, please remember that any new developer must begin his work by either creating a new branch from the remote master which is actually considered as a single source of truth or he still has to sync the local master branch first (what if some else has updated the master further after you pushed your work to remote master and synched your local master) if he wants to create a branch from local master.

You mentioned TFVC which is a centralised version control software where one branch logic works beautifully well and the same can be achieved through Git but the purpose and scope of Git is far more bigger than that.

Please go through the following tip to understand how the TFVC style can be achieved by Git:

Also go through the following tip to understand the real potential of Git as a distributed source control:

Kind Regards,
Haroon Ashraf

Thursday, December 31, 2020 - 4:47:55 AM - Salam Back To Top (87987)
Thanks, understand what you say which means that local master will be lagging behind all branches and might mislead a new developer one day or another if he uses my environment. I am new to git, I used heavily TFVC which was always develop locally on 1 branch (unless using shelves), update remotely to 1 rep

Wednesday, December 30, 2020 - 2:36:01 PM - Haroon Ashraf Back To Top (87984)
Dear Salam,
Please think of a pull request as an integration request to merge (remote) dev changes with (remote) master.

So, if you were to drive a car would you be worrying about all the traffic signals that you see ahead of you?
Of course not, you should be watchful of that traffic signal which is right in front of you.

If you remember and follow the sequence below it serves the purpose:
1. Write the database code on the local dev branch
2. Push your dev changes to the remote dev branch
3. Integrate your dev changes with master branch (which is a pull request)

That's it you are done because your master branch (production ready) has got the latest changes from the dev branch and you can carry on working locally on the dev to push new changes followed by a new pull request against the (remote) master while the local master branch is no where in this scenario.
Kind Regards,
Haroon Ashraf

Wednesday, December 30, 2020 - 9:19:32 AM - Salam Back To Top (87980)
Hi, please confirm my understanding, so by doing "Create a Pull Request", then merge you update the master first on AzureDevops, then back to VS and update local master from remote master? If yes, shouldn't be the other way round? I mean updating local master and generating pull request from local master then merge it with remote master !

Thursday, September 10, 2020 - 6:27:34 AM - Haroon Ashraf Back To Top (86450)
Hi Koen,

Excellent question!

The short answer is no, so neither the changes nor the checked in objects by both developers be lost as long as they work in a standard way.

You sync changes when you work so as soon as you sync the changes your SSDT will have all the objects created by other developers.

The bigger problem is when two developers knowingly or unknowingly work on exactly same database object (such as table, view, stored procedure etc.) then a conflict surely occurs which must be resolved manually.

Please stay in touch with MSSQLTips.com as your question is interesting enough for me to consider writing a tip which I also thought at one point as this could be very useful for other community members which may have the same question in mind but finding it hard to replicate or understand this scenario.

Kind Regards,
Haroon Ashraf

Wednesday, September 9, 2020 - 7:33:30 AM - Koen Verbeeck Back To Top (86441)
Hi Haroon,
I was wondering about the following scenario.
Suppose there are two stories that need to be implemented. In both stories, a new table must be created. Developer A finishes the first story and creates table A in the SSDT project in his own branch. Developer B creates a new branch, creates table B and deploys his/her branch to the server (by merging his branch with the dev/main branch). This means only table B is added to the database on the server.
Now, developer A checks in his branch and merges it with the dev/main branch, meaning table A gets deployed. However, in the SSDT project table B doesn't exist, so it is deleted. How do we prevent this?


get free sql tips
agree to terms