Revision history of an object change in a SQL database using Git

The essence of each source control system is the ability to easily review the history of committed revisions. In addition, to comparing revisions, a user needs to get a specific revision and apply it against a database. Each revision should have a unique timestamp and should carry the information about the user who did the commit. Such system ensures that it is easy to determine who committed what and when, and in some way provide a complete auditing trail of committed revisions.

In this article, the following will be covered: revision history review, compare between revisions and get specific SQL database object revision. In addition, the same processes will be presented using ApexSQL Source Control, a SQL Server Management Studio add-in that serves for revision control of SQL database objects.

For the purpose of the article, the exact same changes are performed, in each case, in order to show how the revision history can be reviewed using Git command line client and ApexSQL Source Control. There will be no explanation about how to initialize the repository, commit changes, etc. As a starting point, it is assumed that a remote repository in each case is created, linked to a working folder on a local machine where the SQL database object scripts are stored and the following list of changes are committed one after another:

• Initial commit of all database objects

• Added discount column to the Products table with the 0 as a default value using the following script:

  ALTER TABLE [Production].[Products] ADD discount MONEY DEFAULT 0 NOT NULL
  

• Added a new table Sales.Currency to a database using the following script:

  CREATE TABLE [Sales].[Currency] (
  	[CurrencyCode] [nchar](3) NOT NULL
  	,[Name] NVARCHAR(20) NOT NULL
  	,[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Currency_ModifiedDate] DEFAULT(getdate())
  	);
  
  

• Renamed a column from Name to CurrencyName in the Sales.Currency table using the following SQL script:

  EXEC [sys].[sp_rename] N'Sales.Currency.Name'
  	,'CurrencyName'
  

Git

Prerequisites

GitBash (command line tool) is a free Git client tool is used to show how this can be achieved from the command line. It comes with the Git installation for Windows OS.

Review history

To review the history using GitBash, make sure that the current directory is local Git repository, where previously listed changes are already committed, and type the following command:

$git log

The following output will be shown:

$ git log
commit 41adfd269a515bb88ab0272d9026d6963542fde0
Author: ApexSQLTest <>
Date: Wed Apr 13 15:09:14 2016 +0200

  Initialization

By default, a list of commits will be shown in reverse chronological order, which means that the most recent commits are shown first. Each commit contains unique identifier, the information about the author (in this case ApexSQLTest), timestamp, and the commit message.

Improving this a bit more, the exact change introduced within a single commit can be shown by adding the -p argument after the log command. To show that for the most recent commit in this case, the following command is used:

$git log -p -1

The outcome, showing the column rename from Name to CurrencyName inside the Sales, is as follows (-1 in the above command means that only the most recent change will be shown):

diff --git a/Tables/Sales.Currency.sql b/Tables/Sales.Currency.sql
index 70f9683..bbd6114 100644
--- a/Tables/Sales.Currency.sql
+++ b/Tables/Sales.Currency.sql
@@ -4,7 +4,7 @@ SET ANSI_PADDING ON
 GO
 CREATE TABLE [Sales].[Currency] (
    [CurrencyCode]  [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
-    [Name]   [nvarchar](20 NOT NULL,
+    [CurrencyName]  [nvarchar](20)NOT NULL,^M
    [ModifiedDate]  [datetime] NOT NULL
) ON [PRIMARY]
GO

Some additional arguments for the log command are shown in the following table (source: Viewing the commit history):

All above mentioned commands will be applied against the full history, which can contain a large number of committed changes. To prevent listing all changes, there are arguments that are used to limit the outcome of the log command. One of them is used above ($git log -p -1) to limit the preview to the most recent committed change. In general -x can be used as an argument, where x is the number of commits shown starting from the most recent one.

The most common argument used to limit the scope of the outcome for the log command are shown in the following table:

As an example, to find the exact commit where the column Name is renamed to CurrencyName, the following command can be used:

$ git log -SCurrencyName

Adding the -S argument along with the renamed column, the result is a commit where the actual column rename was made:

commit 41adfd269a515bb88ab0272d9026d6963542fde0
Author: ApexSQLTest <>
Date: Wed Apr 13 15:09:14 2016 +0200

  Renamed column: "Name" to "CurrencyName"

Compare between revisions

When a database object is modified and the appropriate SQL script is saved in the working directory, running the git status command gives a result where each changed file is shows as modified. To show how this works, another change is made in the script for the Sales.Currency table. After saving the script, and running the command:

$ git status

The result is as follows:

On branch master
Your branch is up-to-date with 'origin/master'.
Changes not staged for commit:
 (use "git add <file>..." to update what will be committed)
 (use "git checkout -- <file>..." to discard changes in working directory)

   modified: Tables/Sales.Currency.sql

no changes added to commit (use "git add" and/or "git commit -a")

As shown above, Git identified that the Sales.Currency table is changed. However, such a command gives only a list of changed files (only one file in this case) inside the working directory and not the actual change. To see the exact change inside the modified SQL script, the following command is used:

$ git diff

The result is as follows:

diff --git a/Tables/Sales.Currency.sql b/Tables/Sales.Currency.sql
index bbd6114..15c7a6f 100644
--- a/Tables/Sales.Currency.sql
+++ b/Tables/Sales.Currency.sql
@@ -5,7 +5,8 @@ GO
 CREATE TABLE [Sales].[Currency] (
    [CurrencyCode]  [nchar](3,
    [CurrencyName]  [nvarchar](20),
-    [ModifiedDate]  [datetime] NOT NULL
+    [ModifiedDate]  [datetime] NOT NULL,^M
+    [test]       [int] NOT NULL^M
) ON [PRIMARY]
GO
ALTER TABLE [Sales].[Currency]

The green colored line with the plus sign means that the test column has been added. Changes shown for the ModifiedDate column represent adding the comma after the column definition, in order to avoid a syntax error. If this change is committed using the git add command, executing the git diff command won’t give any results/differences. Instead, to see the difference for staged files (files that are prepared to be committed), the following command should be executed:

$ git diff --staged

As a result, the same outcome will be shown as in the previous case.

Differences can be reviewed in the user interface, if the appropriate diff tool (supported by Git) is installed. In that case the command will be as follows:

git difftool --staged

As a result, the appropriate tool will run, showing the differences:

There are variety of options for the git diff command in order to scope the difference checking between commits, branches, tags, etc. For a detailed information about how to use the diff command, refer to the official documentation page.

Get specific revision

There are three situations where changes can be undone. Undoing the last commit, undoing locally committed changes, and undoing already committed changes.

Undoing the most recent commit

This can be achieved using the git commit –amend command. In order to show how this works, the very last commit message will be improved by using the following command:

git commit --amend -m "Renamed column in the Sales.Currency table from "Name" to "CurrencyName"”

Showing the most recent commit from the history will be as follows:

commit 41adfd269a515bb88ab0272d9026d6963542fde0
Author: ApexSQLTest <>
Date: Wed Apr 13 15:09:14 2016 +0200

   Renamed column in the Sales.Currency table from "Name" to "CurrencyName

If additional changes should be added to the most recent commit, this can be done by simply staging them using the git add command, and committing using the –amend argument as shown above

Undoing local changes that are not yet committed

Editing a commit, as shown above, is available for the very last commit only. In case changes are made locally but not yet committed, they can be reverted by using the git checkout command. For instance, if any changes are made to the Sales.Currency table, but not yet committed, to get back to the state when the table was committed last time, run the following command:

git checkout -- Sales.Currency.sql

Such a command will discard all changes and revert the object to the state of the last commit. This option should be used carefully, since undoing changes like this will discard all changes since the last commit.

Undoing locally committed changes

This is often needed when changes are committed but for any reason they cannot be properly applied. In this case, an object should be reverted to a “working” state from the previous commit. Specifically, the column rename we have committed will cause the data loss when applied by other developers who are working on the same database. To get back to the proper revision of the Sales.Currency table the following command is used:

git reset acc171e

The git reset command is followed by the hash of the commit which about to be reset. In addition, another command

Undoing changes pushed to the remote repository

If one step more is passed with the inappropriate commit, and it is being pushed to the remote repository, it can be undone using the git revert command. This will create a change (new commit) that is opposite to the one pushed by mistake. Anything added in the incorrect commit will be deleted, and anything deleted will be added. When committing such change it will undo the wrong commit.

More about how to undo changes in Git can be found on this link.

Note that when working with SQL database objects (that will be most likely edited from within SQL Server Management Studio) it is necessary to switch between GitBash and SQL Server Management Studio each time you need to do anything with a change you made.

ApexSQL Source Control

The same operations can be achieved using ApexSQL Source Control, the add-in that enables SQL database version control, directly from SQL Server Management Studio.

It is assumed that a database is linked to a Git repository and that changes are committed through the Action center tab, in the exact same order as it is done in the above example. To review the revision history of changes mentioned at the beginning of the article, perform the steps below:

1. Right-click a database from within the Object Explorer pane and from the ApexSQL Source Control menu, select the Project history option:

   

   This will initiate the Project history form, showing a complete history of each committed revision:

   

2. A list of committed changesets will be shown in the upper left part of the form. Highlighting any particular changeset will give a list of all objects included in a selected changeset:

   

3. By selecting any specific object in the changeset, its script will be presented in the differences pane on the right side. Particularly, the Production.Products table is selected from a changeset where the discount column is added:

   

4. Selected object revision can be compared with any other revision of the same object, or with the current state in the database. To select the revision to compare with, pick it from the Version drop down list as shown above.

5. To get back to any specific version of any object, simply click the Get button in the down right corner. Specifically, a column rename operation in the Sales.Currency table will be reverted:

   

   This will revert back performed column rename, with the script generated and shown under the Script tab:

   

6. Clicking the Apply button will execute the script and apply selected change.

In this way, each change can be reviewed, compared, and reverted from within a single form, inside SQL Server Management Studio.