How to link a SQL Server database to a Git repository

Applies to

ApexSQL Source Control

Summary

This article describes how to link a database to the Git repository on various offerings of Git including Git on a local file system, online Git repositories as well as Git hosted on Azure DevOps Server/Services. Also, the Git repository can be accessed using the SSH protocol, which is also supported and will be covered.

Description

To link a database, follow the steps described in How to link a database to source control article since these are common steps that should be performed no matter which source control system is used.

In this article, the focus will be on a specific configuration that is required for linking a database to the Git repository.

As the first step when linking a database, the Git source control system must be selected under the Connection type tab, in the Source control setup window:

Choosing Git in the Connection tab

Quick tip icon

Quick tip:

When linking a database to a Git repository, no matter where a Git repository is hosted, under the Connection tab in the Source control setup window the Git source control system must be chosen

If the Git source control system is chosen in the first tab, the Connection type tab, the System login tab (the final step of the Source control setup window) is as follows:

The System login tab when the Git source control system is chosen

Git with file protocol (local Git repository)

It is a common practice for users to have a local repository on a machine that is either a blank repository initialized using any Git client or a cloned version of the existing remote repository

Quick tip icon

Quick tip:

No matter if a repository on a local file system is initialized in a blank folder, or it represents a clone of the existing remote repository, linking a database goes in the same way

For this article, the GitBash will be used as the Git command-line client, but any other Git client could be used, as well.

Initialized Git repository

To initialize the repository, an empty folder called GitLocal will be created, GitBash will be started, and make the path to the GitLocal folder as current. Once this is set, the following command is executed in GitBash:

$ git init --bare

Quick tip icon

Quick tip:

Initializing a bare repository is mandatory when linking a database to the local Git repository

Now, the initialized repository can be used in the Source control setup window, specifically in the System login tab, just by pasting the path of the previously created folder in the Repository field under the File tab. The username and password by default are not mandatory, but if for any reason, a folder initialized as the Git repository requires authentication such as a network drive that is password-protected, or a Windows Basic authentication is set (that requires the user to provide username and password), the username and password must be specified:

The System login tab when linking a database to the Git repository using the File protocol

To specify the Repository path, copy the location from Windows Explorer:

The Repository path to empty bare Git local repository

Cloning existing repository

Since ApexSQL Source Control supports only bare Git repositories, to clone the existing remote repository, the following command should be used, assuming the remote repository exists and it is accessible:

$ git clone --bare https://Mara@bitbucket.org/Mara/gitlocaltest.git

This will clone the existing gitlocaltest repository to the current folder path, so it can be specified in the Repository field:

The Repository path to cloned bare remote Git repository

When specifying the local repository, separate projects can be created for each linked database.

Quick tip icon

Quick tip:

Creating and deleting new projects directly from the Source control setup window is available regardless of the used protocol (file, HTTP(S) or SSH) unless such restriction is not introduced on the Git Server directly

To manage projects inside the Git repository (no matter if it is empty or it contains existing projects), use the Browse for folder button to review the current repository structure:

The Choosing project window

The New and Delete buttons can be used to create new projects or delete existing ones.

Once the project is set, clicking the Finish button results in loading the Action center tab and showing scripts for all previously selected objects to be initially committed.

Quick tip icon

Quick tip:

When a local repository is used changes will be committed to a local repository only and not on a remote one, no matter if the repository is cloned or initialized

Git with HTTP(S) protocol

This is probably the most used option when working with the Git source control system. There are a variety of cloud-based services that offer free or paid Git repository hosting and that is easy to register and initially set. In this article, Bitbucket.org is used as the Git hosting provider. When a new repository is created (in this case, the repository called gitlocaltest is created), the URL given will be as follows:

https://Mara@bitbucket.org/Mara/gitlocaltest.git

When using the cloud-based Git repository, credentials are, in most cases, the same as the one used to login to the cloud-based portal and access the User profile information and manage Git projects. To link a database to a Git repository, a username instead of an email address could be used.

Information in the System login tab will be as follows:

The System login tab in the Source contro lsetup window

In the same way, as explained for the local Git repository, projects inside the remote repository can be managed (create, delete).

Git with SSH protocol

In order to link a database using the SSH protocol, a necessary pre-requisite is to:

  • Generate SSH key pair (private and public keys) on a local machine, using any of the SSH clients
  • Add public key to a Git Server-side

Since GitBash can be used for generating the SSH key pair, it will be used in the following example.

Generating SSH key pair

To generate the SSH key pair, execute the following command in Command Prompt:

ssh-keygen

Or this one in GitBash:

ssh-keygen -m PEM

The output is as follows

Generating public/private rsa key pair.
Enter file in which to save the key (C:\Users\<username>/.ssh/id_rsa):

Hit the Enter keyboard key to verify the creation of the default SSH key and path for storing the SSH keys. In the next step, a passphrase is required:

Enter passphrase (empty for no passphrase):

Once the passphrase is specified, the same one needs to be confirmed, as the next step is:

Enter same passphrase again:

After confirming the passphrase, the result is as follows:

Your identification has been saved in C:\Users\<username>/.ssh/id_rsa.
Your public key has been saved in C:\Users\<username>/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Bs6nRUN4SJVJT9UGhWFbvPc

Quick tip icon

Quick tip:

Make sure that the passphrase meets the requirements (it is strong enough), so it could not be used by other people

Copying public key

As noted above, a public key has been saved on a local machine and it needs to be added to the Git Server in order for the Server (in this case Bitbucket.org) to establish a connection with a local machine. To copy the public key, navigate to the default location where it is created previously, and open a file in any text editor:

Editing the public SSH key

Quick tip icon

Quick tip:

In case you cannot find the .ssh folder, make sure that hidden folders are shown, as it may be hidden in some situations

Once opened, copy the entire content of the file to clipboard:

Editing the SSH key in Notepad ++

Quick tip icon

Quick tip:

Be careful when copying the public key. A recommendation is to use CTRL + A keyboard shortcut, to make sure the public key is properly selected/copied

To copy public key using the command line, one of the following methods (commands) can be used:

  • For Windows Command Line, type the following:

    %userprofile%\.ssh\id_rsa.pub | clip
  • For GitBash on Windows/Windows PowerShell, type the following:

    cat ~/.ssh/id_rsa.pub | clip

Using any of the given methods, the public key will be copied and stored in the clipboard.

To add the SSH public key to the Git Server side (in this case to Bitbucket.org), navigate to the BitBucket settings page, choose the SSH keys tab and click the Add key button:

Adding the public SSH key to BitBucket.org

Paste the public key in the appropriate field and click the Add key button:

The Add SSH key window on BitBucket.org

For more information about how to add a public key to the Git Server that is not cloud-based, check the Git on the Server – Setting Up the Server article.

After the public key is added to the Git Server side, a database can be linked. To link a database, switch to the SSH tab in the System login tab of the Source control setup window:

The System login tab when linking a database to Git repository using the SSH protocol

In the Passphrase field, specify the passphrase that was used when the SSH key pair was generated.

Make sure that the Key pair path field contains a default location to the .ssh folder, where the private key is stored.

As an argument for the Repository field, use the SSH URL provided by the cloud service:

The SSH URL on BitBucket.org

Quick tip icon

Quick tip:

The format of the HTTP repository URL is different compared to the SSH repository URL, so make sure that the correct one is specified in the Repository field

Once the requested information is provided in the System login tab, a database can be linked with a click on the Finish button.

Git hosted on Team Foundation Server (Azure DevOps Server/Services)

When the Git repository is hosted on Team Foundation Server (Azure DevOps Server/Services), it can be used in the same way as explained above for HTTP(S) and SSH. Once the Git repository is created, for HTTP(S) use it with the account credentials combination (username and Token) for the Azure DevOps account, and in case of SSH connection, add the public key in the same way as for any other Git cloud-based service and use the passphrase in the System login tab of the Source control setup window.

FAQs

Q: How can I use a new password to link a database that is already linked using the old password?

A: If the password that is used to access the repository and linking a database is changed, just re-link a database, and in the System login tab, provide a new password or a passphrase for SSH connection.

Q: What if the passphrase is not specified when the SSH key pair is generated?

A: The add-in reports an error that the passphrase is not valid. To fix this, generate a new key pair with the passphrase.

Q: Can I use other clients to generate the SSH key pair?

A: No, the official BitBucket.org policy is to created SSH keys only using Command Prompt or GitBash. Additional information can be found in the Set up an SSH key article.

Q: How can I start using SSH in a repository where I am currently using HTTPS?

A: Unlink a database from the HTTP(S) repository, make sure that the public key is added for the SSH repository, and link a database using the given passphrase, along with the SSH repository URL.