A simple approach to versioning MS SQL Server databases

A simple approach to versioning MS SQL Server databases

Preface.

Very often in application programs it is necessary to use modern databases that provide a developer with a lot of functionality, based not only on the data level, but also creating their own API to provide access to this data through stored procedures, triggers, functions. Obviously, in this whole structure, depending on the circumstances, some changes may be needed. And in the most innocuous case, when a developer deals with one client and one changing database (suppose at a small enterprise), the update process looks simple - we make the necessary changes in the structure, compare with the help of special utilities, such as SQL Examiner, old and new version and roll the generated sql script on the existing database. As you can see in the described case, the data is migrated every time the database structure is updated. But unfortunately, the described situation is extremely rare, more often - the clients and the corresponding databases for any product among developers are in the hundreds, if not more. Thus, for a normal database life cycle, a versioning system is needed (not to be confused with source versioning systems of the Subversion type).

An approach

To better understand the purpose of this system, consider the following example: there is some desktop application - a list of office staff, upon request of the customer, this program should store the following data: "Last Name", "First Name", "Middle Name", job title and this program will be installed in each of the offices of the company, which are scattered throughout the city. The last fact suggests that in the future there may be situations when updating software versions will not happen synchronously, for example, it is possible that the program will release version 10 already, and our first version will still be installed in any office, but when upgrading immediately to 10th, the administrator performing this update will not need to install all 10 updates, but he will need to provide one 10 updates at once, which will independently update the entire structure and migrate the data for the new application. Thus, we formulate the main requirements for our database versioning system:

1. Data must be kept safe during migration.
2. Data must be migrated regardless of their version.
3. Migration tool should be as simple as possible.

The easiest version of the task of the migration commands is to develop a migration script in any language; in this example, standard windows bat files will be used.
Now you need to create rules for saving entities.
')

1. A script with DML definitions that creates a database or modifies an existing structure must be in a single file, the name of which will be alter.sql.
2. Each stored procedure must have its own file, both for creation (for the first version) and for modification (for subsequent ones).
3. For each entity in the database, it is as possible to use the following rule: create four procedures each, for selecting, adding, updating, deleting an entity, which should be named according to the rule [Action Entity]. For example, if we have an entity book - Book, then to access it we create four procedures - SelectBook, InsertBook, UpdateBook, DeleteBook. The introduction of new rules is allowed, but they should be as unified as possible.
4. Procedures for working with the same entity should be grouped in the same folder bearing the name of the entity.
5. For each version, folders with the number of the version being implemented should be created, and they should contain the folders from step 4, as well as the initialization script or changes to the database structure from paragraph 1.

Fulfilling all of the above rules, you can achieve a hard cataloging, with which we will come to the required version.
Let's return to our example. In the first version, we will have one Person table, with three fields: FirstName, MiddleName, LastName. First, create a Version folder - It will contain versions, now it is version 1, and below it is the Person folder and the initialization script, which is presented below:

CREATE TABLE Person
(
FirstName varchar (50),
MiddleName varchar (50),
LastName varchar (50)
)

* This source code was highlighted with Source Code Highlighter .

Next, in the persons folder, we create four stored procedures, according to the rule from paragraph (3), call them SelectPerson, InsertPerson, UpdatePerson, DeletePerson respectively and put them inside the Person folder, we will not give the code due to elementary, but let's see turn out:

Creating a base deployment system

Now that all the files and directories have been assembled into a certain structure, we can start creating a mechanism that will deploy our database. For MS SQL server there is an access utility from the command line - sqlcmd.exe is exactly what we will take as the sql command processor. At the time of execution, it is assumed that the path to this utility is registered in the environment variable Path. First, a little information about sqlcmd.exe:

1. This utility can execute files containing SQL commands, this is done using the –i filename key
2. It also can define variables that can be used in the sql code, the variable is defined using the –v param = value key, and in the code they can be accessed using the syntax $ (param), and the executable processor simply replaces these tokens with the value , which was defined in the call with the -v option
3. The utility allows in the sql script to call other sql scripts, this is done using the directive: r filename

These functions will help to create a flexible system for our purposes. First, let's define the database deployment algorithm:

1. Determine whether the database exists, if not, then create a new one.
2. We make backup data.
3. Determine whether the initial configuration of the database is created for versioning, if not, then create it.
4. We define the current version and call the migration script.

In order for the database to store its current version (for step 3), for this it is necessary to create a table with a field where this version will be stored:

CREATE TABLE Settings
(
DbVersion int
)
go
INSERT INTO Settings(DbVersion) VALUES (1)
go

* This source code was highlighted with Source Code Highlighter .

When changing the base version, the corresponding value in the DbVersion field in the created table will change.
Now we define what parameters the deployment script should receive:

1. The database name is the workdbname variable.
2. Instance name for SQL services.
3. The path to the database files is the databasepath variable.
4. The path to the backup file is the backuppath variable.

Preparation stage

Here we will need to write the logic for checking the existence of the base and creating a new one. Immediately it is worth mentioning that the sqlcmd.exe utility when a critical error occurs in the execution of the script, such as division by zero, returns an error code to the environment - 1. This can be used during the execution of the bat-script. For the above check, create the following sql commands in the CheckDbExists.sql file:

use master;
IF NOT EXISTS ( SELECT * FROM sysdatabases WHERE [Name] = $(workdbname)')
BEGIN
SELECT 1/0;
END

* This source code was highlighted with Source Code Highlighter .

Now we will organize the first check in the file deploy.bat:

"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -i CheckDbExists.sql
IF ERRORLEVEL 1 GOTO :CREATENEW

If our base does not exist, then the utility will return the termination code 1 to the execution environment and the logic will proceed to the execution of the physical creation of the base. To do this, we write a sql script with the following content:

use master;
go
CREATE DATABASE $(workdbname) ON PRIMARY
(Name = N '$(workdbname)' , FILENAME = N '$(databasepath)' );
go
use $(workdbname);
go

CREATE TABLE Settings
(
DbVersion int
)
go
INSERT INTO Settings(DbVersion) VALUES (1)
Go

* This source code was highlighted with Source Code Highlighter .

As you can see, the script uses two variables that will need to be passed when you call sqlcmd:

"sqlcmd.exe" -S %2 -v workdbname=%1 -v databasepath=%3 -i InitDatabase.sql

In the step of checking the existence of the database, it could happen that the database already exists, so you need to prepare it for the update, namely, to force the creation of backup data, you can do this by calling the following script:

BACKUP DATABASE $(workdbname) TO DISK = N '$(backuppath)' WITH INIT,
SKIP, NOREWIND, NOUNLOAD

* This source code was highlighted with Source Code Highlighter .

From the generated bat file:
"sqlcmd.exe" -S %2 -v workdbname=%1 -v backuppath=%4 -i BackupDatabase.sql

Now the time has come for the most important thing in our system - checking the current version; this will be done by a simple check for the specified version. Since it is necessary to somehow notify the “outside world” about the version number, here again we apply the divide-by-zero error and file this code in the CheckVersion.sql file:

use $(workdbname);
go
SELECT [DbVersion]/($(checkedVersion) - [DbVersion]) FROM Settings
Go

* This source code was highlighted with Source Code Highlighter .

And we will write a call with checking the first version:
"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -v checkedVersion=1 -i CheckVersion.sql

At the first call, as expected, a division by zero error will occur, and code 1 will return to the execution environment. Thus, we can call a script that should make all the necessary changes to initialize the structure for the first version of the database:
IF ERRORLEVEL 1 "sqlcmd.exe" -S %2 -v workdbname=%1 -i Version\1\alter.sql
The called alter.sql file for each version should work according to the following principle:

1. Must set the context of the database, the name of which was passed to it in the workdbname variable.
2. Call using the directive: r all necessary files containing sql DML code.

For the first version of our example, the script in this file consists of the lines below, plus, at the end, a mandatory version update:

use $(workdbname);
go
:r Version\1\script. sql
go
:r Version\1\Person\DeletePerson. sql
go
:r Version\1\Person\InsertPerson. sql
go
:r Version\1\Person\SelectPerson. sql
go
:r Version\1\Person\UpdatePerson. sql
go
UPDATE Settings SET DbVersion = 2
Go

* This source code was highlighted with Source Code Highlighter .

At this point, the entire setup for working with the first version is completed, and the file structure should take the form shown in the image:

Creating new versions

When the time of the next release comes, all the changes that have accumulated during the work should be collected in a subfolder of the main Version branch. For clarity, we assume that the next version of our database will include changes in the introduction of the new city table and the creation of a connection between the person and the city in which he was born, the structure migration script named script.sql will be as follows:

CREATE TABLE City
(
CityId int identity NOT NULL ,
Title varchar (255) NOT NULL ,
PRIMARY KEY (CityID)
)
go
ALTER TABLE Person ADD CityId int
go
ALTER TABLE Person ADD FOREIGN KEY (CityID) REFERENCES City (CityID)
go

* This source code was highlighted with Source Code Highlighter .

At the same time, you will need to create four procedures for working with the City entity and make changes to the storage for the Person entity. Then write the update script to the alter.sql file:

use $(workdbname);
go
:r Version\2\script. sql
go
:r Version\2\Person\InsertPerson. sql
go
:r Version\2\Person\SelectPerson. sql
go
:r Version\2\Person\UpdatePerson. sql
go
:r Version\2\City\SelectCity. sql
go
:r Version\2\City\InsertCity. sql
go
:r Version\2\City\UpdateCity. sql
go
:r Version\2\City\DeleteCity. sql
go
UPDATE Settings SET DbVersion = 3
Go

* This source code was highlighted with Source Code Highlighter .

Upon completion, we will add the following lines to the version checking section of the main deploy.bat:
"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -v checkedVersion=2 -i CheckVersion.sql
IF ERRORLEVEL 1 "sqlcmd.exe" -S %2 -v workdbname=%1 -i Version\2\alter.sql

For the second version, the structure of files and folders will look like:

Conclusion

The described approach is applicable not only for MS SQL databases, but can be adapted to other DBMS. For example, this method was developed in the process of creating a system for versioning the data schema for a project where the DBMS was Postgres. All source scripts are available here.