Testing SQL-facing PowerShell code with Pester, without a database
Mocking the data access layer, testing SQL templates and config files, and running the tests in CI without a database.
PowerShell that talks to SQL Server is the code people are most afraid to test: it needs a server, credentials, data in a known state, and a failed test may be a failed network. My approach is to stop testing the database and test everything around it. In practice that covers about 90% of the bugs I ever ship.
Where the bugs actually are
Look at the defects in a DBA tooling module: a wrong return value on the failure path, a placeholder never replaced, a config key missing in one environment, a destructive call made when it should have been skipped, a retry loop that never stops. None of these need a real database. They need the code to run with a fake data access layer.
Pester 5 only
Windows PowerShell ships with Pester 3.4, which has a different syntax and scoping. Install Pester 5 explicitly and import it with a minimum version before running:
Install-Module Pester -MinimumVersion 5.5 -Scope CurrentUser -SkipPublisherCheck
Import-Module Pester -MinimumVersion 5.5
Invoke-Pester -Path .\Tests\ -Output Detailed
Tests live in a Tests/ folder at the root of the module or application, never inside folders that are dot-sourced at start-up.
Mock the data access layer
The key design decision is to have one thin function that is the only place where SQL is actually executed. Every other function calls it. In tests, that function is mocked and the test asserts on what it was asked to do.
BeforeAll {
Import-Module "$PSScriptRoot\..\Contoso.Dba.psd1" -Force
}
Describe 'Get-PoolUsage' {
Context 'success' {
BeforeAll {
Mock Invoke-SqlQuery -ModuleName Contoso.Dba {
[pscustomobject]@{ PoolName = 'pool-elp-01'; CpuPercent = 42 }
}
}
It 'returns the rows with IsValid true' {
$r = Get-PoolUsage -ServerName 'sql-contoso-prd' -LogFile (Join-Path $TestDrive 't.log')
$r.IsValid | Should -BeTrue
$r.Data[0].CpuPercent | Should -Be 42
}
}
Context 'failure' {
BeforeAll { Mock Invoke-SqlQuery -ModuleName Contoso.Dba { throw 'login timeout' } }
It 'returns IsValid false with a reason' {
$r = Get-PoolUsage -ServerName 'sql-contoso-prd' -LogFile (Join-Path $TestDrive 't.log')
$r.IsValid | Should -BeFalse
$r.Reason | Should -Match 'login timeout'
}
}
}
Three habits matter here:
-ModuleNameonMockis required when the call happens inside the module, otherwise the mock is invisible to it.- Assert both paths of the return contract: success data and failure reason.
- Logs go to
$TestDrive, which Pester cleans up, never to the real log folder.
Destructive operations get the opposite assertion. For a function that must not drop anything in dry-run mode:
Should -Invoke Remove-Database -Times 0 -ModuleName Contoso.Dba
Testing SQL templates
If the SQL lives in template files with placeholders, test the template expansion rather than the result of the query:
It 'replaces every placeholder' {
$sql = Expand-SqlTemplate -Name 'PoolUsage' -Values @{ DatabaseName = 'AppDb' }
$sql | Should -Match 'AppDb'
$sql | Should -Not -Match '\{\{'
}
It 'fails on a missing value' {
{ Expand-SqlTemplate -Name 'PoolUsage' -Values @{} } | Should -Throw
}
Cheap extras worth adding: every .sql file has no unreplaced placeholder left after expansion with the full value set, and every placeholder used in a template is documented. If you need syntax validation without a server, a T-SQL parser library can parse the expanded text and report errors; that is optional and worth it only for critical scripts.
Testing config files
Config files are code in disguise: a missing key in one environment fails at 3 a.m. Test them in discovery:
BeforeDiscovery {
$files = Get-ChildItem "$PSScriptRoot\..\config\config-*.json" |
Where-Object Name -ne 'config-common.json'
}
Describe 'config <_.Name>' -ForEach $files {
It 'is valid JSON' { { Get-Content $_.FullName -Raw | ConvertFrom-Json } | Should -Not -Throw }
It 'has the required keys' {
$c = Get-Content $_.FullName -Raw | ConvertFrom-Json
$c.sqlServer | Should -Not -BeNullOrEmpty
}
}
BeforeDiscovery matters in Pester 5: data used to generate tests must exist at discovery time, not in BeforeAll.
Running in CI without a database
Because nothing real is touched, the same tests run on a hosted build agent with no network access to SQL and no Azure login:
steps:
- pwsh: |
Install-Module Pester -MinimumVersion 5.5 -Scope CurrentUser -Force -SkipPublisherCheck
$cfg = New-PesterConfiguration
$cfg.Run.Path = './Tests'
$cfg.Run.Exit = $true
$cfg.TestResult.Enabled = $true
Invoke-Pester -Configuration $cfg
displayName: Pester
Publish the result file so failures show in the pipeline UI. A test that needs a real server is an integration test: keep it in a separate folder, tag it, and run it on demand.
Lessons learned
- Mock everything with side effects: SQL, Azure cmdlets, token acquisition, secret retrieval, mail sending. A unit test that reaches the network is a bug.
- When a production defect is fixed, write the regression test first, with the real error text as input.
- Static analysis applies to test files too; Pester scoping triggers a known false positive on assigned-but-unused variables that you can suppress in that one file.
- Do not back-fill tests for the whole code base. Add them whenever you touch a function; coverage grows where the changes are.