How to organize a simple CI process
This article describes an example of a simple CI process organization from scratch, which you can later download and run on your machine for training. It will be shown and explained how to create the necessary test files and invoke cmdlets.
Create Database Migration Scripts Folder
To start with, you need to create test migration scripts for a database. A test database will be deployed from these scripts later.
For this purpose, create an empty folder as shown below.
Next, you need to create a ScriptsFolder subfolder in the Demo folder as shown below.
Then, in the ScriptsFolder folder, create the CreateDatabase subfolder. This folder will contain a single file with a script for creating an empty database without any objects.
In the CreateDatabase folder, create a file 0001_DropAndCreateDatabase.sql.
Now, edit the file with a script for creating a TestDB database.
USE [master]
GO
DECLARE @db_name NVARCHAR(255);
SET @db_name = N'TestDB';
IF EXISTS (SELECT
1
FROM sys.databases d
WHERE d.name = @db_name)
BEGIN
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = @db_name;
EXEC (N'ALTER DATABASE ' + @db_name + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC (N'DROP DATABASE ' + @db_name);
END;
EXEC (N'CREATE DATABASE ' + @db_name);
GO
Next, in the ScriptsFolder folder, create several files with scripts for creating and modifying database objects as shown below:
The contents of 0002_Create-Customer-Table.sql are the following:
CREATE TABLE Customer (
id INT PRIMARY KEY
,last_name VARCHAR(50) NOT NULL
,first_name VARCHAR(50) NOT NULL
,city VARCHAR(50) NOT NULL
,email VARCHAR(50) NOT NULL
)
GO
The contents 0003_Create-Customer-Views.sql are the following:
CREATE VIEW vCustomers
AS
SELECT
*
FROM Customer
GO
The contents of the 0004_Drop-Columns.sql file are the following:
ALTER TABLE customer
DROP COLUMN city
GO
The contents of 0005_Create-Vendor-Table.sql file are the following:
CREATE TABLE Vendor (
id INT PRIMARY KEY
,name VARCHAR(50) NOT NULL
)
GO
Now your folder with migration scripts is ready. Next, you can write a PowerShell script for invoking cmdlets that recreate the database on the server from the migration scripts.
PowerShell Script for Creating a Database on a Server From the Migration Scripts
First, write the following script:
# variables
$serverName = "(LocalDb)\MSSQLLocalDB"
$masterDatabase = "master"
$testDatabaseName = "TestDB"
$createDatabaseScriptsFolder = "D:\Demo\ScriptsFolder\CreateDatabase\"
$migrationScriptsFolder = "D:\Demo\ScriptsFolder\"
Function check {
param($result)
if ($result) {
Write-Host "Success" -ForegroundColor Green; Write-host ""
}
else {
Write-Host "Failed" -ForegroundColor Red;
Exit(1)
}
}
#region Create TestDB
# Create master database connection
Write-Host "Creating master database connection...`r`n"
$masterConnection = New-DevartSqlDatabaseConnection -Server $serverName -Database $masterDatabase -WindowsAuthentication $true
# Test master database connection
Write-Host "Testing database connection...`r`n"
$result = Test-DevartDatabaseConnection -Connection $masterConnection;
check($result)
# Recreating test database
Write-Host "Recreating database..."
$result = Invoke-DevartExecuteScript -Input $createDatabaseScriptsFolder -Connection $masterConnection
check($result)
#endregion
#region Run the migration scripts against the TestDB database
# Create TestDB database connection
Write-Host "Creating TestDB database connection...`r`n"
$testDBConnection = New-DevartSqlDatabaseConnection -Server $serverName -Database $testDatabaseName -WindowsAuthentication $true
# Test TestDB database connection
Write-Host "Testing TestDB database connection...`r`n"
$result = Test-DevartDatabaseConnection -Connection $testDBConnection;
check($result)
# Run the migration scripts against TestDB database
Write-Host "Run migration scripts..."
$result = Invoke-DevartExecuteScript -Input $migrationScriptsFolder -Connection $testDBConnection
check($result)
#endregion
Let's consider the execution of the script given above. First, you need to connect to the master database in order to recreate the TestDB database. Then you can run the migration scripts on the newly created TestDB database.
There is also a check function that you will use after invoking the cmdlets. If any cmdlet fails, then the execution stops, exit code 1 is returned (unsuccessful execution), and exit occurs.
Create Unit Tests Files
To begin with, create a new UnitTests subfolder in the Demo folder as shown below.
In the UnitTests subfolder, you need to create a file 000_DemoUnitTestClass.sql.
Now, open it for editing and write the following script:
CREATE SCHEMA [DemoUnitTestClass] AUTHORIZATION [dbo]
GO
EXEC sys.sp_addextendedproperty N'tSQLt.TestClass', 1, 'SCHEMA', N'DemoUnitTestClass'
GO
The script above creates a test class named DemoUnitTestClass. As you can see, it comprises a database schema with Extended Property. The presence of this property is crucial. Now that you have a class DemoUnitTestClass, you can create test cases for it.
Create the following three files with scripts:
Next, open them for editing and fill with test scripts.
Edits for 001_DemoUnitTestClass.test MyUnitTest_1.sql are the following:
CREATE PROCEDURE [DemoUnitTestClass].[test MyUnitTest_1]
AS
BEGIN
EXEC tSQLt.AssertEquals @Expected = 1, @Actual = 1
END;
GO
Edits for 002_DemoUnitTestClass.test MyUnitTest_2.sql are the following:
CREATE PROCEDURE [DemoUnitTestClass].[test MyUnitTest_2]
AS
BEGIN
EXEC tSQLt.AssertEquals @Expected = 2, @Actual = 2
END;
GO
Edits for 003_DemoUnitTestClass.test MyUnitTest_3.sql are the following:
CREATE PROCEDURE [DemoUnitTestClass].[test MyUnitTest_3]
AS
BEGIN
EXEC tSQLt.AssertEquals @Expected = 3, @Actual = 3
END;
GO
PowerShell Script for Creating the Unit Test for the Database
First, you need to add a new unitTestsFolder variable. It shows where your unit tests are located.
Your next step is to add a script that will create tests for the database.
Test Data
Filling the database with test data can be an important step before testing the database. To do this, create a TestData folder with your test data as shown below:
Your database contains two tables, Customer and Vendor. For the test case, we will fill the Customer table using the Data Generator tool (generation of meaningful data on the go) and the Vendor table with the help of the Data Pump tool (import from a file).
To create a Data Generator project, right-click the database in Object Explorer, select New Data Generation from the Data Generation menu.
Next, click Open in the wizard that appears.
In the document that opened, select only the Customer table for data generation. The Vendor table will be filled from a file with the help of DataPump.
Next, save the project to a TestData folder as shown below.
Now, create a CSV subfolder. We need it to fill the Vendor table.
Next, put two files, sample_data.csv and template.dit, in this folder.
sample_data.csv is a file with the test data for the Vendor table.
template.dit is an import template file that was created with the help of the Data Pump tool and is aligned to import data from the sample_data.csv file to the Vendor table.
The sample_data.csv data looks as follows:
Now, the CSV folder looks as follows:
The test data is ready.
Export Zip Package With Database
The final stage in the CI pipeline is receiving a database artifact file. In fact, it is a zip package file with our migration scripts.
First, you need to add two new variables, publishFileName and $outputFolder. Where publishFileName is an output file name and $outputFolder is a folder to save it.
Now, you need to write a script to publish your database.
# region Export zip package
Write-Host "Exporting a database project..."
$project= New-DevartDatabaseProject $migrationScriptsFolder
Set-DevartPackageInfo -Project $project -Id $publishFileName -Version 3.0.0
$result = Export-DevartDatabaseProject -Project $project -Format Zip -OutputDirectory $outputFolder -Overwrite
check($result)
#endregion
Final Powershell Script
The final script looks like as follows:
clear;
# variables
$serverName = "(LocalDb)\MSSQLLocalDB"
$masterDatabase = "master"
$testDatabaseName = "TestDB"
$createDatabaseScriptsFolder = "D:\Demo\ScriptsFolder\CreateDatabase\"
$migrationScriptsFolder = "D:\Demo\ScriptsFolder\"
$unitTestsFolder = "D:\Demo\UnitTests\"
$importDataFileName = "D:\Demo\TestData\CSV\sample_data.csv"
$importTemplateFileName = "D:\Demo\TestData\CSV\template.dit"
$generatorFile = "D:\Demo\TestData\TestDB(MSSQLLocalDB).dgen"
$testReportOutputFileName = "D:\Demo\TestReport\report.xml"
$publishFileName = "Test.DevOpsAutomation.Database"
$outputFolder = "D:\Demo\Project\"
Function check {
param($result)
if ($result) {
Write-Host "Success" -ForegroundColor Green; Write-host ""
}
else {
Write-Host "Failed" -ForegroundColor Red;
Exit(1)
}
}
#region Create TestDB
# Create master database connection
Write-Host "Creating master database connection...`r`n"
$masterConnection = New-DevartSqlDatabaseConnection -Server $serverName -Database $masterDatabase -WindowsAuthentication $true
# Test master database connection
Write-Host "Testing database connection...`r`n"
$result = Test-DevartDatabaseConnection -Connection $masterConnection;
check($result)
# Recreating test database
Write-Host "Recreating database..."
$result = Invoke-DevartExecuteScript -Input $createDatabaseScriptsFolder -Connection $masterConnection
check($result)
#endregion
#region Run the migration scripts against the TestDB database
# Create TestDB database connection
Write-Host "Creating TestDB database connection...`r`n"
$testDBConnection = New-DevartSqlDatabaseConnection -Server $serverName -Database $testDatabaseName -WindowsAuthentication $true
# Test TestDB database connection
Write-Host "Testing TestDB database connection...`r`n"
$result = Test-DevartDatabaseConnection -Connection $testDBConnection;
check($result)
# Run the migration scripts against TestDB database
Write-Host "Run migration scripts..."
$result = Invoke-DevartExecuteScript -Input $migrationScriptsFolder -Connection $testDBConnection
check($result)
#endregion
# Restore unit tests on the database
Write-Host "Restoring unit tests..."
$result = Invoke-DevartExecuteScript -Input $unitTestsFolder -Connection $testDBConnection
check($result)
#endregion
#region Import sample data from a file to certain table
Write-Host "Importing sample data from a file...`r`n"
$result = Invoke-DevartDataImport -InputFile $importDataFileName -TemplateFile $importTemplateFileName
check($result)
#endregion
#region Fill database with sample data and run unit tests
Write-Host "Filling database with sample data and running unit tests..."
$result = Invoke-DevartDatabaseTests -InputObject $testDBConnection -InstalltSQLtFramework -IncludeTestData -DataGeneratorProject $generatorFile -OutReportFileName $testReportOutputFileName -ReportFormat JUnit -RewriteReport
check($result)
#endregion
# region Export zip package
Write-Host "Exporting a database project..."
$project= New-DevartDatabaseProject $migrationScriptsFolder
Set-DevartPackageInfo -Project $project -Id $publishFileName -Version 3.0.0
$result = Export-DevartDatabaseProject -Project $project -Format Zip -OutputDirectory $outputFolder -Overwrite
check($result)
#endregion
If you want to download a complete example, click here.
Want to Find out More?
Overview
Request a demo
