Git Stash SQL Server Database Code Changes using Visual Studio 2019


By:   |   Updated: 2019-12-18   |   Comments   |   Related: More > Database Design

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:

  1. 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.
  2. 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:

  1. T-SQL scripting
  2. Azure Devops Project
  3. SQL Server Data Tools (SSDT)
  4. SQL Database Project
  5. 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:

Create a new Azure DevOps Project

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.

Rename Repo 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.

Connect to DevOps Project through Visual Studio

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:

Create Watch and WatchOrder Tables

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:

Add Project to the Git Source Control

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:

Connect to Azure DevOps Project

Click Publish Repository to push all the changes directly to the master branch:

Publishing Repo to master branch

Go to Azure DevOps on the web to see the repository Git Stash Database:

Git Stash Database Repo

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:

  1. Assumer we first created dev branch out of master branch
  2. Then tables were created in the dev branch
  3. Dev branch was pushed to the source control
  4. Then dev changes were merged with the master branch in the form of creating a pull request
  5. 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:

Add a SQL View to the master branch

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:

Newly Created SQL View

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:

Opening Code Changes in Team Explorer

Add comments and Click Stash All under Stash as shown below:

Stashing Database Code Changes

All the changes previously associated with master branch are gone now and have been stashed:

Code Changes Stashed Successfully

Create a new dev branch

Create a new dev branch from master as shown below:

Create a new dev branch

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.

Pop Stashed Changes to dev branch

Next, add comments and push the restored stashed changes to the dev branch:

Pushing 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:

Check master branch

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


Last Updated: 2019-12-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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools