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.
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.
The main advantages of using the command line clients for the database source control are:
Even though everything sounds great so far, there are several disadvantages to this approach:
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:
Disadvantages of using the native drivers’ approach are:
The following is a quick comparison chart between Native drivers vs. Command-line actions for database source control:
Feature | Native drivers | Command-line |
Yes | Yes | |
Yes | Yes | |
Yes | Yes | |
Yes | Yes | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X | |
Yes | X |
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.
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.
No matter where the Git repository is hosted, online, remote or local, the workflow is the same.
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:
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:
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:
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:
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:
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.
© ALL RIGHTS RESERVED. Feedback Conditions d’utilisation Confidentialité Cookie Preference Center