Rajendra Gupta
Edit environment variable

Source Control using Git in Azure Data Studio (ADS)

July 2, 2020 by

Azure Data Studio is a tool for both developers and database administrators with exciting features. You can install ADS on both Windows and Linux platforms. You can go through various Azure Data Studio articles on SQLShack to be comfortable with the tool and its features.

Source version control is a valuable part of IT developments. Suppose you are a developer and working on a database project. As part of your task, you might need to make specific changes in the database objects such as stored procedures, tables, functions. Your colleagues might be working on a separate project that affects the same objects. You should be aware of all these changes.

Similarly, recently someone deployed a stored procedure change in a critical production database. Your manager wants to know the history of changes to this procedure. You need information such as change date, what change was performed in the script, or make a comparison between the current stage and previous change.

Source control allows you to have a centralized location for all your database scripts, their versions, compare object versions, and commit or rollback the changes. You might have heard about the opensource and distributed version control system – GitHub. It is a useful tool for developers to store all modifications in a single place. They can download the current version, make changes, upload the new version for everyone’s visibility.

Azure Data Studio integrates Source control functionality for this purpose. In this article, we will explore the source control Git repository integration with ADS.

Pre-requisite:

You should always use the latest version for Azure Data Studio. I use ADS version 1.19.0 released on 15th June 2020. You can download it from the Microsoft Docs.

Git Repository in Azure Data Studio

Launch ADS and navigate to the highlighted Source Control icon. You can also use shortcut key Ctrl+ Shift + G.

You can see it gives a message to me that a valid git installation is not found on my system. We need to install Git locally before start using source control:

Git Repository in Azure Data Studio

Click on the hyperlink install git. It opens an external website, so ADS requires your permission to open it. It requires an active internet connection:

install git

In the git web page, you can download the latest version for Windows from the link highlighted:

Download Git

Click on the Download 2.27.0 for Windows, and it starts downloading the EXE file of Git.

Save file

Installation of Git source control

Once the Git installation file is downloaded, launch the setup. The first step is to accept the GNU general public license:

GNU general public license

In the next step, specify the directory for the Git installation in your local system. By default, it installs Git in the program files of the root drive. You can modify the directory as per your requirement:

Location

Now, select the components for the Git installation. It installs Git Bash and Git GUI for the Windows Explorer integration. We can use the default components and proceed with the next step:

Select Components

By default, Git uses Vim as the default editor. You can change it to Visual Studio Code, Notepad++, Atom from the drop-down. I use the default Vim editor for this article:

Default editor

You can choose the Git usage from the Git bash only or also from the 3rd party software. In this article, we use Git in Azure Data Studio. You must select the option – Git from the command line and also from the 3rd party software:

Adjust the path environment

In the next step, choose the default option – Use the OpenSSL Library:

Use the OpenSSL Library

It also allows configuring the line ending styles. We can go ahead with the default option here:

Checkout windows style

Now, configure the terminal emulator to use with the Git Bash. Choose the Windows default console – Command Prompt for this purpose:

Windows default console

Next, choose the default behavior of the git pull. We should use the default behavior as fast-forward or merge the branch:

Default behavior of git pull

In the next step, Configure the extra options it enables by default. It enables the following features.

  • Enable file system caching
  • Enable Git credential manager

Enable file system caching

The configuration for the Git is complete, click on Install in the next page:

Click on Install

It starts installing the Git software on your system:

Installation progress

You get the following screen once the setup is complete. We can launch the Git Bash from this page in case we put a tick on the checkbox:

Complete setup

We need to add the Git directory in the Windows environment variables as well. Open the system properties -> Advanced -> Environment Variables -> Path:

Edit environment variable

Now, we can verify the Git installation from the command prompt. Type git in the command prompt and you get output in terms of its syntax, usage:

Run Git commands

We require a profile with an email id and user name for the Git. For this, we use the git config command.

  • >git config –global user.email “rajendra.gupta16@gmail.com”
  • >git config –global user.name “RajendraGupta”

Add profile

Connect Git in the Azure Data Studio

Once we installed the Git in the local system, relaunch the ADS and navigate to the Source Control. Here, you see it shows two options.

  • Open Folder
  • Clone Repository

Connect Git in the Azure Data Studio

First, we need to create a folder that will contain the database scripts for source control. Create a folder in any directory, go to open the folder, and specify the folder path.

You can also open the folder from the Azure Data Studio menu as File -> Open Folder:

Open Folder

It opens the folder, and you can see the folder name in the title bar. I specified the folder name as “GitHub” so it shows it in the title bar as per the following screenshot:

Folder name in the title

Now, go back to source control and click on Initialize Repository:

Initialize Repository

You can see the source control as Git in the following image:

source control as Git

Add a SQL Script in the Git Repository

For the demonstration purpose, expand the AdventureWorks sample database and script the highlighted view using the option Script as Create:

Add a SQL Script in the Git Repository

It generates the script in a new query window of Azure Data Studio:

Generate script

Save this script in the Git repository folder we created earlier. You can Press Ctrl+S to save this script or go to File -> Save As option:

Save script

You can see the saved SQL script in the GitHub folder:

View script in source folder

In the source control menu, you can see this SQL script. It currently shows 1 issue in the existing file along with the U mark that defines the Untracked object:

source control menu

As we have scripted an existing object in the [AdventureWorks] database, it shows the error message. Let’s rename the view name in the generated script and save it.

You can see it removes the error message because it is not a duplicate object now:

Error message

Now, click on the Commit(tick icon) to commit the changes in the source control:

Commit

Click on Yes to automatically stage all changes and commit them directly into the source control:

Click on Yes

It asks for a commit message. It is a custom message that you can put for your reference purpose. By default, it commits on the master branch in the Git:

Specify a commit message

For my reference, I highlighted the message that it modifies the view name:

Specify a commit message and press Enter

Once you commit changes, it removes the corresponding messages from the source control:

Modified changes

Edit an existing file from the source control in Azure Data Studio

In this section, we will modify an existing SQL script in the Git source control. Click on the script in the GitHub folder, and it opens the script in the new query window.

For the demo, we remove the highlighted columns from the view definition and save the script:

Edit an existing file from the source control

Right-click on the script in the open editor window and click on Compare with Saved:

Compare with Saved

It compares the original script and the modified script. You can see the difference in the below screenshot. In the left hand, we have the original script with all columns and right-side, it shows a modified script that removes a few columns from the select statement:

Compare scripts

In the below screenshot, it shows that integrated view script is modified. You can also see the different color of the text highlighting it is a modified script:

Modified script

Commit the changes by specifying a commit message. I specified the message that the modified script removes a few columns from the select statement:

Commit message

It saves the changes in source control:

Saved file in source control

View the timelines of script modifications

In the source control, go down and expand the timeline folder. Here, you see two commits for my SQL script along with the last commit hr.

  • First commit – Modify view name: We committed the SQL script after modified the view name
  • Commit- Remove a few columns from select: We committed SQL script again after removing a few columns from the select

View the timelines of script modifications

Click on the Commit- Remove few columns from select, and you can see the changes we did in the SQL script for this commit:

Commit- Remove few columns from select

Similarly, you can make multiple changes to SQL script and commit the scripts for different versions. You can view previous changes anytime after the modifications as well.

Conclusion

In this article, we explored the useful Source control Git for SQL Scripts in the Azure Data Studio. You can put multiple versions for SQL script, compare the changes at a later date.

I will cover more topics on the Git Source control for ADS in the upcoming articles. We will also link the local repository with the GitHub to have centralized source control.

Table of contents

The SQL Server Assessment Extension for Azure Data Studio
Source Control using Git in Azure Data Studio (ADS)
Integrating Azure Data Studio with Git and GitHub
Working with Git components in Azure Data Studio

Rajendra Gupta
200 Views