Native drivers vs. Command-line database source control – general overview

Applies to

ApexSQL Source Control

Summary

This article will provide the differences (advantages and disadvantages) between two approaches (native and CLI) when talking about database source control. Also, native driver advantages will be shown through the most common database source control features in ApexSQL Source Control.

Description

When starting with the database source control, one of the most important things to decide on, besides the source control system, is which type/approach to use, using the native drivers or command-line (CLI).

Even when talking about the same source control system, the differences between those two approaches can be significant.

How to choose which approach is better for handling the database source control?

Native drivers or CLI – there are no specific criteria on how to choose between those two. The main thing that should be the starting point in the whole process of choosing the database source control approach is the working environment and workflow. Based on that choosing between those two will be easy.

This article will include elementary things (advantages/disadvantages) for both approaches. For this article, the comparison will be done for the Git source control system since it is one of the most used source control systems.

Why use the command-line for database source control?

The main advantages of using the command line clients for the database source control are:

• Need fewer resources – In general, text-based applications need very little machine resources. The database source control via CLI will require fewer resources than doing similar tasks with any GUI application. After the installation process is done, every source control task will be done through the command-line window
• Does not need hosts – The command-line applications do not need the hosts to integrate into in order to work with the database under source control
• High working precision – Using CLI, the destination will be targeted with ease using the specific command, no matter if one or more objects are in question. But this does not mean much if the wrong command is typed
• CLI is a sort of common language – Directions and explanations for every command can be found easily. Once the commands are learned, there will not be additional time spent on learning new features like it can be the case with the GUI application
• Less frequent updates – The command-line applications have updates rarely, changes are less frequent which allows stability in the environment and workflow
• Work automation – Using the CLI scripts, work can be automated in a way to do the same thing at the same time every single day

Even though everything sounds great so far, there are several disadvantages to this approach:

• Less functionality is supported – The command-line cannot be integrated with any of the database source control applications. So, work can only be done through the command-line window. There are no additional features but the basic one (commit, push, pull, delete, revert…)
• Unnecessary messages – Working with database source control over the command-line can raise repetitive unnecessary messages, warnings, etc. Even more when the tasks are repetitive
• Time spent in typing the commands – Unless already prepared command-line script is used for daily work, the amount of time spent on typing one by one command can be time-consuming
• It cannot work with the remote repositories directly – Using the command-line clients for database source control implies that changes made against database objects cannot be committed directly to the remote (online) repository. One additional step must be made. The local folder must be used as a local repository and the workflow will be to commit changes to the local folder then from the local folder push changes to the online repository

Why use the native database source control integration?

One of the most used applications for working with database source control is Visual Studio. But Visual Studio natively supports only two source control systems, Git and Azure DevOps. On the other hand, SQL Server Management Studio (SSMS), even though it does not have any options for source control, can be used as a host for many source control applications that natively support all source control systems (Git, Azure DevOps, Mercurial, Perforce, Subversion).

The advantages of using the native approach when it comes to the database source control are:

• Simpler environment – After integration into SSMS or any other database development tool, all database work (development and track changes) is done from one application
• Works with online repositories directly – Some of the source control applications work directly with the online repositories, so there are no additional steps between object change and commit that change to the online repository
• Easy to use for beginners as for experts – GUI applications do not require to remember a bunch of commands, there is a button (including a tooltip) for every action that needs to be done
• Some source control system features are easier to work with – Some database source control features are easier to work with through GUI than CLI. Through the GUI application will be more information provided in just one click of a button
• The knowledge level is not important – The knowledge level is not a significant factor when it comes to working with GUI applications. It is much easier to learn how something works than using the command line. Working with native driver applications for database source control is recommended for beginners as well for experts
• Visual communication in a multi-developer environment – One of the main benefits of this approach is a visual representation of all the work no matter how many developers are working on the same database at the same time. Database developers do not have to communicate between themselves, the application is doing that for them by showing on which object is currently being worked on, what changes are made and by whom
• Spot on information messages – For every issue, there will be appropriate information/warning messages that will contain all necessary information on how to resolve it
• Additional features – There are always additional features that help in database source control work, features that cannot be found when working with the command line clients

Disadvantages of using the native drivers’ approach are:

• Workflow can rarely be automated as doing it via CLI – Even though some native driver applications support work automation, it is not at the command line level still
• Need more resources – In general, this type of application will need more resources from the machine then when working over the command line
• Frequent updates – These applications have more often updates which can have a negative impact on the environment and additional time spent for installation and settings. In most applications, this is sorted in a way that all settings are preserved (on database and machine) if they need to be re-installed

Comparison

The following is a quick comparison chart between Native drivers vs. Command-line actions for database source control:

Advantages of working with native drivers – ApexSQL Source Control

Native drivers over the command-line advantages will be done for the Git source control system, as one of the most used source control systems.

For this article, an online Git repository hosted on BitBucket.org will be used.

Database source control workflow using the command-line client

In order to start database source control using the command-line client, the online Git repository must be cloned into the local Git folder where the database object scripts will be stored. The scripting part, as well as commit to the local Git folder, must be done object by object. From now on, every changed object must be first scripted, then compared to the latest version in the Git local folder and then committed. After all this, still, all those changed object scripts are not present on the online Git repository. That is a separate step and it must be done through specific command.

Using ApexSQL Source Control, that supports the Git source control system natively, all these actions will be done directly to the online Git repository in just a few clicks.

Database source control workflow using native drivers (ApexSQL Source Control)

No matter where the Git repository is hosted, online, remote or local, the workflow is the same.

Linking a database to a source control system

The first step in tracking changes made against a database is linking it to the source control system.

Using any other similar source control CLI tool for this action requires that remote Git repository is cloned on a local machine. There is no option to link a database directly to a remote Git repository. After the remote repository is cloned to a local folder and database is linked to that local folder, all changes made against a database first must be committed to the local folder and then pushed to the remote (online) repository.

Using ApexSQL Source Control, a database will be linked to the remote Git repository, no matter where it is hosted (BitBucket.org, GitLab, GitHub…) by using:

HTTP(S)

SSH

ApexSQL Source Control supports linking to a local folder, which is a cloned remote (online) Git repository, as well:

After linking a database to a source control system, every change made against the database can be committed directly to the remote (online) repository through the Action center tab with just one click on the Apply button:

Object status

After the database is linked, all linked objects from that database will have an additional icon in the Object Explorer pane. For multi-user environment this is the way to track which object is worked on by any user:

Reverting multiple local changes

Using the command-line client, changes made against a database object can be reverted, but only after the commit to a local folder is done.

With ApexSQL Source Control, after the change is made against a database object, and the Action center is refreshed, all changes will be listed in the Action center tab. If a database is linked in the dedicated development model, the change can be reverted in the Action center tab before it is committed to the repository, with a click on the Undo button:

Note: Change revert can be done for one or more objects at the same time by checking the object for which change should be reverted before the Undo button is clicked.

Additionally, changes can be reverted by changing the action in the Action column of the Action center tab to ‘’commit to database’’ and with a click on the Apply button. This way, the latest version from the repository will be applied against a database and every change made against the specified objects will be reverted to the latest repository state:

If the commit has already been done to the remote (online) repository, revert of a change can be done through the Object/Project history window. From the Object/Project history window any version of a committed object can be reverted from the moment database is linked to source control:

From the Project history window revert of the whole changeset can be done as well. Specify the changeset on the left part of the Project history window, and choose to revert one object change or all the objects committed in that changeset:

Deleting objects from the repository

Using the source control command-line tools, objects can be deleted from the local folder and pushed to the remote (online) repository. This action cannot be reverted since the object is permanently deleted from the remote repository.

The only way the user can delete anything from the repository, using ApexSQL Source Control, is when a specific object does not exist anymore in a linked database. In that case, in the Action center tab, the database column will be empty and applying that change will permanently delete the object from the repository:

ApexSQL Source Control provides users with a feature to exclude any database object from the further change tracking without deleting its last version from the remote repository. This can be done in the Options window under the Local settings tab for the selected database, by unchecking the specific object(s):

Note: For database linked in the shared development model, an object can be excluded globally (stop tracking changes for the object), for all database users in the Global settings tab of the Options window:

Getting the latest version of the repository

When working with the command-line tools, before the synchronization between the local folder repository and the database can be done, the local folder repository must be updated with the latest version of the remote (online) repository.

With ApexSQL Source Control native integration, a database is already linked directly to the remote (online) repository, and when there is a difference between the database and the repository, the Action center tab will inform the user:

Every time the Action center tab is opened/refreshed, comparison between the latest version of the repository and linked database is done, and all differences will be listed:

Working with branches

The active branch should be chosen during the linking process in the Source control setup window:

The active branch can be changed in the Action center tab anytime before the commit is performed:

All additional features, supported by the chosen source control system, for database source control through ApexSQL Source Control is listed in the Object Explorer pane right-click context menu after the database is linked to source control:

Hopefully, this article was informative for you and after reading it, you can see more clearly differences between native and CLI approaches when starting with the database source control.