Database Continuous Integration with dbForge Studio

Required tools

  • A source control system, such as Git, Subversion (SVN), or Mercurial
  • Windows PowerShell 3.0 or later
  • dbForge tools:
    • DevOps Automation for SQL Server – Automates database builds and deployments using PowerShell.
    • Schema Compare – Creates and synchronizes databases from scripts.
    • Unit Test – Runs SQL unit tests.

Optional tools

  • Data Generator – Populates tables with test data.
  • Data Export & Data Import – Imports data from files such as .csv or .xml.
  • SQL code formatting – Arranges and formats SQL queries to improve readability, consistency, and adherence to formatting standards.
  • Find Invalid Objects – Identifies database objects, such as views, procedures, or functions, that reference missing or invalid dependencies.
  • Source Control – Integrates database schemas with version control.

Required files

The CI process relies on the following files:

  • PowerShell script (.ps1) that orchestrates the pipeline
  • Migration scripts (.sql) for database creation and updates
  • Unit test scripts (.sql)
  • Test data:
    • Data Generator project (.dgen) or
    • Data files (.csv, .xml)

Store all files in source control and keep them synchronized with the CI pipeline.

CI pipeline with dbForge Studio

The CI database process begins after developers commit changes.

You can configure the CI process to start in one of these ways:

  • On commit – Runs automatically after each change.
  • Scheduled – Runs at defined intervals.
  • Manually – Runs on demand.

You can choose how often you want to run CI, depending on your CI process.

The CI pipeline typically includes the following stages:

  • Required:
    • Build a database
    • Run unit tests
    • Publish packages
  • Optional:
    • Populate with data
    • Format scripts

CI execution flow

1. The latest changes are retrieved from source control:

  • Migration scripts (required)
  • Unit tests (required)
  • Test data or generator projects (optional)

2. The PowerShell script runs and:

  • Creates or recreates the database
  • Executes migration scripts
  • Populates test data (optional)
  • Deploys and runs unit tests
  • Generates a report
  • Publishes the output artifact

Note

You can store all these files in one repository or different ones.

Organize database scripts

Database scripts can be organized using one of the following approaches.

Migration-based approach

Database changes are applied incrementally by executing scripts in sequence:

  • CREATE – Creates objects
  • ALTER – Modifies objects
  • DROP – Removes objects

These scripts may also contain static data.

Scripts are ordered chronologically and executed sequentially.

Migration-based approach

State-based approach

The database is defined as a complete schema using CREATE statements. The target database is synchronized with this state using comparison tools.

CREATE TABLE [dbo].[Member](
    [MemberId] [INT] IDENTITY(1,1) NOT NULL,
    [MemberFirstName] [NVARCHAR](50) NULL,
    [MemberLastName] [NVARCHAR](50) NULL,
    [MemberEmail] [NVARCHAR](50) NULL,
 PRIMARY KEY (MemberId));

In this case, these scripts are synchronized with the target database with the help of Schema Compare.

Hybrid approach

This approach combines migration-based and state-based methods. It requires careful coordination and is used less frequently since a part of the scripts (a more manageable process) comes from migration scripts, and then the database is updated using Schema Compare or vice versa.

Unit testing

Unit tests validate database behavior after changes.

Tests are implemented as SQL scripts that use the tSQLt framework, for example:

  • AssertEquals
  • AssertNotEquals

tSQLt is an open-source unit testing framework for SQL Server. For more information, see the tSQLt documentation.

Note

Install the tSQLt framework in the database before running tests.

When the tSQLt framework is installed on the database, a tSQLt database schema appears that contains tables, procedures, functions, and other database objects.

A tree-view tSQLt database schema

To view a complete list of these objects, run the following script:

SELECT name, type_desc
FROM sys.objects sysobj WHERE schema_id = (
SELECT sch.schema_id from sys.schemas sch WHERE NAME='tSQLt' )
ORDER BY sysobj.name

View a list of objects that exist in the tSQLt database schema

After installing the tSQLt framework, you can create your first SQL unit test or additional tests.

Create a unit test using tSQLt

You can create unit tests in the Add New Test wizard or using SQL scripts.

Create unit tests using SQL scripts

In this scenario, create tests manually using SQL scripts to see how tSQLt tests are arranged.

1. In the SQL Editor, execute the following script to create a test class that will include all test cases.

USE TestDB;
GO
EXEC tSQLt.NewTestClass 'DemoUnitTestClass';

This script creates the DemoUnitTestClass test class.

Note

You can create any number of such classes.

When you create a new test class, tSQLt creates a schema and adds an extended property with the value so that the tSQLt framework can understand that this schema is created for this test class. To verify this, run the following query:

SELECT S.SCHEMA_NAME, E.OBJTYPE, E.NAME, E.VALUE 
FROM INFORMATION_SCHEMA.SCHEMATA s
INNER JOIN fn_listextendedproperty (NULL, 'schema', NULL, NULL, NULL, NULL, NULL) e
ON e.objname = s.SCHEMA_NAME COLLATE Latin1_General_CI_AI AND SCHEMA_NAME = 'DemoUnitTestClass'

Verify that the schema is created for the specified test class

2. To check the value of the schema in the extended properties:

2.1. In Database Explorer, expand Security > Schemas.

2.2. Right-click DemoUnitTestClass, then select Show Details.

2.3. In the Object Viewer document, select Text.

View extended properties of the schema

3. To delete the created test class, execute the following stored procedure, which accepts the test class name as a parameter.

EXEC tSQLt.DropClass 'DemoUnitTestClass'

Note

Running the DropClass procedure deletes the test class (schema) with all the objects associated with this class.

Tip

You can also delete a test in Test List Manager by clicking Delete Test on the toolbar.

Delete a test in Test List Manager

Create a test case

After you’ve created the DemoUnitTestClass class, you can create test cases for it.

To create a new test case:

1. In Database Explorer, right-click the required database and select Unit Test > Add New Test.

2. In the Add New Test dialog, under Test Information, specify the test name and test class.

Specify the test name and test class in the Add New Test dialog

The default test name is MyUnitTest_1.

3. Click Add Test.

A new SQL document opens, displaying the template of the stored procedure, where you can write your verification script.

Template of the test case

Example

Here is a list of methods you can use for test cases:

  • AssertEmptyTable
  • AssertEquals
  • AssertEqualsString
  • AssertEqualsTable
  • AssertEqualsTableSchema
  • AssertLike
  • AssertNotEquals
  • AssertObjectDoesNotExist
  • AssertObjectExists
  • AssertResultSetsHaveSameMetaData
  • Fail

tSQLt.AssertEquals

The tSQLt.AssertEquals method allows you to compare the expected and actual values and accepts three parameters:

  • @expected – Defines the expected value.
  • @actual – Defines the actual value.
  • @message – Defines the message that will be displayed if the unit test fails. This parameter is optional.

In a new SQL document, execute the following script to change the code of the test case:

ALTER PROCEDURE DemoUnitTestClass.[test MyUnitTest_1]
AS
BEGIN

  DECLARE @actualValue INT;

  SELECT @actualValue = 3 + 2;

  EXEC tSQLt.AssertEquals 5, @actualValue;

END;

tSQLt.Run

To run test cases, use the tSQLt.Run method. It accepts the test class name parameter. This method runs all unit tests that are tied to this class.

In a new SQL document, execute the following script:

EXEC tSQLt.Run 'DemoUnitTestClass';

Open the Output pane and view the result:

Result of the tSQLt.Run method

Tip

You can also run the tests using Test List Manager.

This example displays the order of steps required to create test classes and test cases for them. You can use the scripts or Test List Manager to create and modify tests.

Save unit tests

After creating the tests, save them to the central source control repository. This step is required because each time the database is created during the CI process, the tests are also deployed to the database so they can be executed and validated.

Save the test files in alphabetical order. Place the test classes first, followed by the test cases that belong to each class.

For example, if you have the DemoUnitTestClass class and three related test cases, the scripts folder should be organized as follows:

Save unit tests to drive

The content of these files:

  • 000_DemoUnitTestClass.sql contains a script for creating a test class.

  • 001_DemoUnitTestClass.test MyUnitTest_1.sql, 002_DemoUnitTestClass.test MyUnitTest_2.sql, and 003_DemoUnitTestClass.test MyUnitTest_3.sql are the test cases for the DemoUnitTestClass class.

Tip

You can view the contents of these files in Create the folder with unit tests.

Test Data

You can populate the database by using one of the following tools:

  • Data Generator, which can fill all or selected database tables with almost an unlimited amount of realistic data.
  • Data Import, which allows filling in a specific table from the data file (.csv or .xml).

Example of populating test data by using Data Generator

1. In the top menu, select Database > Tasks > New Data Generation.

Open the Data Generator tool

2. In the Data Generator Project Properties dialog, select the server and required database (TestDB), then click Open.

3. In the Tables and views tree, select the table you want to populate with data.

Select the Customer table for data generation

4. In the top menu, select File > Save As and save the data generation project (.dgen) to the required folder.

Save the data generation project of the Customer table

After the project for database population is ready, you can add it to the central repository of Source Control.

Run a PowerShell script

1. Create a new PowerShell file, for example, DemoPipeline.ps1

2. Enter the following script, which invokes cmdlets, by replacing the variables with your actual data.

$serverName = "demo-mssql\SQL2025"
$databaseName = "TestDB"


$migrationScriptsFolder = "D:\Demo\ScriptsFolder\"
$unitTestsFolder = "D:\Demo\UnitTests\"


$generatorFile = "D:\Demo\TestData\TestDB (demo-mssqlSQL2025).dgen"

$publishFileName = "Test.DevOpsAutomation.Database"
$publishOutputFolder = "D:\Demo\NuGet\Repository"



Function check {
    param($result)

    if ($result) {
        Write-Host "Success" -ForegroundColor Green; Write-host ""
    }
    else {
        Write-Host "Failed" -ForegroundColor Red;
        Exit(1)
    }
}



# Create database connection
Write-Host "Creating database connection..."
$connection = New-DevartSqlDatabaseConnection -Server $serverName -Database $databaseName -WindowsAuthentication $true


# Test database connection
Write-Host "Testing database connection..."
$result = Test-DevartDatabaseConnection -Connection $connection;
check($result)


# Recreating database
Write-Host "Recreating database..."
$result = Invoke-DevartExecuteScript -Input $migrationScriptsFolder -Connection $connection
check($result)


# Restore unit tests on the database
Write-Host "Restoring unit tests..."
$result = Invoke-DevartExecuteScript -Input $unitTestsFolder -Connection $connection
check($result)


# Fill database with sample data and run unit tests
Write-Host "Filling database with sample data and running unit tests..."
$result = Invoke-DevartDatabaseTests -InputObject $connection -InstalltSQLtFramework -IncludeTestData -DataGeneratorProject $generatorFile
check($result)


# Publish project
Write-Host "Publishing project..."
$project= New-DevartDatabaseProject $migrationScriptsFolder
Set-DevartPackageInfo -Project $project -Id $publishFileName -Version 3.0.0
Publish-DevartDatabaseProject -Project $project -Repository $publishOutputFolder -AutoIncrementVersion
check($result)

3. Execute the script.

Result of the script execution

The database has been published on the NuGet repository.

Result

After successful execution:

  • The database is created and updated
  • Unit tests are executed
  • A report is generated
  • A package is published to the repository

Video tutorials

Watch these videos to learn how dbForge Tools can be involved in the Continuous Integration process.

  • How to import data to SQL Server database with dbForge Data Pump during the DevOps process
  • Creating database documentation during the Continuous Integration workflow
  • How to automate database schema changes for the CI process during database deployment
  • dbForge Source Control in the DevOps pipeline
  • Unit Testing for SQL Server Database in DevOps process