Organize a simple Continuous Integration process using tSQLt Unit Tests

#region the variables

$serverName = "(LocalDb)\MSSQLLocalDB"
$databaseName = "TestDB"

# A folder where the database sql files(in alphabetical order) are located, for example:
# ...\0001_Drop-Create-Database.sql
# ...\0002_Create-Customer-Table.sql
# ...\0003_Create-Customer-Views.sql
# ...\0004_Drop-Columns.sql
# ...\0005_Create-Vendor-Table.sql
$migrationScriptsFolder = "C:\Test\Scripts"

# A folder where the sql unit-tests are located, for example:
# ...\Test1.sql
# ...\Test2.sql
# ...\Test4.sql
$unitTestsFolder = "C:\Test\UnitTests"

# A filepath to a datagenerator file
# Note: .dgen file can be generated using dbForge Data Generator for SQL Server
$generatorFile = "C:\Test\TestData\data.dgen"

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

#endregion

#region the check function

Function check {
    param($result)

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

#endregion

#region CI pipeline

# 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
$result = Publish-DevartDatabaseProject -Project $project -Repository $publishOutputFolder -AutoIncrementVersion
check($result)

#endregion