By: Haroon Ashraf | Comments | Related: > DevOps
Problem
As a SQL Server database developer, I need to understand the concept of stashing database code changes along with its application in real-time scenarios.
Solution
The Git stash code feature can help developers to be more productive by maintaining the standards of database/application coding and complying with source control best practices.
About the Git Stash Code Feature
The Git stash feature in Visual Studio 2019 can revolutionize the way developers (including database developers) work with source code branches (in Visual Studio) particularly in the context of the most widely used Git source control.
It actually allows the database developers to switch from one branch to another to focus on the piece of code they need to without mixing it with another work item.
In order to understand Git stash code changes, let us first review one of the main concepts of working with source control regarding master and dev branch.
Importance of Master Branch
We know that no database changes should be directly committed (saved) to the master branch since master is treated as the production branch so it must remain very stable.
The database developers should always work on branches other than master to create new database objects or modify existing ones to meet business specifications. In either case the new work should not be directly performed on the master branch as per standard source control best practices.
The key is to work on any branch other than master such as dev and then create a pull request, also known as integration request, to merge your changes from the dev branch with the master branch once all looks accurate.
Please refer to the article SQL Database Project Code Branching in Git to see a walkthrough of the whole scenario where a dev branch gets merged with master branch once the new work is ready to be integrated.
Uses of Git Stash Feature
Keeping in mind that no database changes should be performed directly on the master branch, here are two of the most productive uses of the Git Stash feature in Visual Studio 2019:
- You have just added a new stored procedure and a SQL view to the database project in SQL Server Data Tools (SSDT). Suddenly you notice that your active branch is master where you should not be saving changes directly. You can easily address this issue by simply stashing the database code changes and then creating a feature branch and brining all those new changes into it without committing anything to the master branch.
- You are creating a new table in the database (project) in SQL Server Data Tools (SSDT) as per business requirements. You have not finished the work and some more urgent database requirements arrive that you have been asked to work on address first. So, the best way is to stash your code changes and start working on new work in a different branch and then come back to this branch to continue at a later date.
Walkthrough: Git Stash Database Code Changes
Let us now understand how Git stash database code works with the help a walkthrough based on a real-world scenario.
Prerequisites
This tip assumes the readers are familiar with the following:
- T-SQL scripting
- Azure Devops Project
- SQL Server Data Tools (SSDT)
- SQL Database Project
- Git Source Control
Please note that this tip works with Visual Studio 2019.
Create an Azure DevOps Project
Let us begin by creating an Azure DevOps Project and name it Git Stash Database Code as follows:
Rename Repo as git-stash-database
Navigate to the project properties and shorten the name of your project repository (repo) by renaming it as git-stash-database.
Connect to DevOps Project through Visual Studio
Open Visual Studio and create a new SQL database project WatchesV3 under Git Stash Database solution.
Create Watch and WatchOrder Tables
Create a new folder called dbo and then create another folder called Tables.
Create a new table Watch under Tables folder using the following T-SQL script:
CREATE TABLE [dbo].[Watch] ( [WatchId] INT NOT NULL IDENTITY(1,1), [WatchType] VARCHAR(40), [Brand] VARCHAR(40), [Colour] VARCHAR(20), [Price] DECIMAL(10,2) CONSTRAINT [PK_WatchType] PRIMARY KEY (WatchId), )
Create another table called WatchOrder under the same Tables folder as follows:
CREATE TABLE [dbo].[WatchOrder] ( [WatchOrderId] INT NOT NULL IDENTITY(1,1), [WatchId] INT NOT NULL, [Quantity] INT NOT NULL, [OrderDate] DATETIME2 NOT NULL, [Price] DECIMAL(10,2) NOT NULL, CONSTRAINT [PK_WatchOrder] PRIMARY KEY ([WatchOrderId]), CONSTRAINT [FK_WatchOrder_ToTable] FOREIGN KEY (WatchId) REFERENCES [Watch]([WatchId]), )
The project should look as follows now:
Add Project to the Git Source Control
We have to put the project under source control now.
Click Add to Source Control or press CTRL+ALT+F9 and then click Git at the bottom right side of the Visual Studio status bar:
Connect to the Azure DevOps Project we crafted earlier and the related repository by signing in to Azure DevOps account and pointing to the Azure DevOps project under Team Explorer window:
Click Publish Repository to push all the changes directly to the master branch:
Go to Azure DevOps on the web to see the repository Git Stash Database:
Mock up master branch Scenario
No code should be directly saved to the master branch as per standard practices.
The reason we directly pushed changes to the master branch is to quickly mock up the following scenario:
- Assumer we first created dev branch out of master branch
- Then tables were created in the dev branch
- Dev branch was pushed to the source control
- Then dev changes were merged with the master branch in the form of creating a pull request
- Once all the dev changes have been reflected in master then the dev branch was deleted
Please refer to the tip SQL Database Project Code Branching in Git to see the above in action.
Add a SQL View to the master branch
A new requirement is to add a SQL view by the development team.
Now assume that the database developer working on this project got so occupied that he/she forgot to switch to any other branch when meeting the new requirements to create a SQL view.
Create a new SQL View in the current project while staying in the current branch to replicate the scenario which is the master branch as follows:
Please type the following code to create view:
CREATE VIEW [dbo].[WatchOrders] AS SELECT wo.WatchOrderId,w.Brand,wo.Quantity,wo.OrderDate,wo.Price FROM [WatchOrder] wo INNER JOIN [Watch] w on wo.WatchId=w.WatchId
The project is shown below:
Git Stash Changes Done to the master branch
We have now made the changes directly to the master branch as per stand practice so no change should be directly made to the master branch.
So, the solution is to stash the changes to be picked up by another branch preserving the master branch which is production code.
Go to Team Explorer Home section and click Changes:
Add comments and Click Stash All under Stash as shown below:
All the changes previously associated with master branch are gone now and have been stashed:
Create a new dev branch
Create a new dev branch from master as shown below:
Pop Stashed Changes to dev branch
Once the dev from master branch is created you are going to be switched to the dev branch.
Click Pop and then click Pop and Restore Staged in Changes section of Team Explorer to restore the parked changes to the dev branch to be checked in.
Next, add comments and push the restored stashed changes to the dev branch:
The changes are pushed successfully to the dev branch.
Check master branch
Now switch to master branch to check if SQL view is still there or not:
There is no SQL view in the master branch which means we have successfully stashed the database changes and restored them to the dev branch by preventing accidental direct changes to the mater branch.
Congratulations, you have successfully stashed direct database code changes from master branch and restored it to another branch by preserving the reliability of the master branch which serves as the production ready branch.
Next Steps
- Please create a pull request to merge your changes with the master branch
- Try stashing code changes in a dev branch and working on another feature branch and then coming back to the dev branch
- Please try to add reference data folders to the project followed by a post deployment script using the tip Manage Multiple Database Backup Formats in SQL Server Data Tools (SSDT) - Part 2 as a reference
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
