Structuring a PowerShell module for DBA work
Manifest, Classes/Private/Public folders, loader, SQL templates with placeholders, versioning, and the module / application / tool boundary.
When a DBA team writes PowerShell, scripts start as one-offs and slowly become copies of each other: the same token code, the same retry loop, the same logging call pasted into twenty files. A module is the cure, but only if it is structured well and if you are clear about what belongs in it, in an application, or in a standalone tool. This is how I lay it out.
The layout
Contoso.Dba/
├── Contoso.Dba.psd1 # manifest
├── Contoso.Dba.psm1 # root module: loader only
├── README.md
├── Classes/ # PowerShell classes, loaded first
├── Private/ # internal helpers, never exported
├── Public/ # one file per exported function
├── sql/ # T-SQL templates with {{placeholders}}
├── Tests/ # Pester tests, never loaded at import
└── Examples/
The rule is one function per file, the file named after the function. It makes diffs readable, code review easy, and tests map one to one (Public/Get-PoolUsage.ps1 has Tests/Get-PoolUsage.Tests.ps1).
The manifest
@{
RootModule = 'Contoso.Dba.psm1'
ModuleVersion = '2026.11.1'
GUID = '00000000-0000-0000-0000-000000000000'
Author = 'DBA Team'
PowerShellVersion = '7.0'
RequiredModules = @('SqlServer', 'Az.Accounts')
FunctionsToExport = '*'
CmdletsToExport = @()
AliasesToExport = @()
}
FunctionsToExport = '*' is a trade-off: it is convenient, and the root module does the real filtering with Export-ModuleMember. The cost is that command discovery without importing the module is slower and less precise. A strict explicit list is better for a module published to a gallery; for an internal module I accept the wildcard and let the loader decide.
The loader
The .psm1 contains no business logic, only the load order: classes, private helpers, then public functions, which are exported.
foreach ($folder in 'Classes', 'Private') {
$path = Join-Path $PSScriptRoot $folder
if (Test-Path $path) {
Get-ChildItem $path -Filter *.ps1 | ForEach-Object { . $_.FullName }
}
}
$publicPath = Join-Path $PSScriptRoot 'Public'
if (Test-Path $publicPath) {
Get-ChildItem $publicPath -Filter *.ps1 | ForEach-Object {
. $_.FullName
Export-ModuleMember -Function $_.BaseName
}
}
Classes come first because functions may use them as types. Private comes before Public because Public functions call them. The Tests/ folder is never dot-sourced, which is why tests live next to, not inside, Public/ or Private/.
SQL templates
SQL text does not belong inside PowerShell strings. I keep it in sql/*.sql with {{Name}} placeholders and a small private function that loads the file and replaces them:
function Expand-SqlTemplate {
param([string]$Name, [hashtable]$Values)
$sql = Get-Content (Join-Path $PSScriptRoot "..\sql\$Name.sql") -Raw
foreach ($key in $Values.Keys) { $sql = $sql.Replace("{{$key}}", [string]$Values[$key]) }
if ($sql -match '\{\{\w+\}\}') { throw "Unresolved placeholder in $Name" }
$sql
}
The final check matters: a forgotten placeholder must fail loudly, not reach the server as literal text. Placeholders are for identifiers such as a database name that cannot be a SQL parameter; real values should still go through parameters.
Versioning
I use YYYY.M.N: the year, the month, and a counter that increments within the month. The date tells you how stale a deployed module is at a glance. The tag is per module, so an application can pin the exact module version it was tested with.
Module, application or tool?
| Kind | What it holds | Test |
|---|---|---|
| Module | Reusable functions with no entry point: auth, SQL execution, logging, helpers | Would two different jobs call it? |
| Application | An entry script, its own Functions/, config/ per environment, usually scheduled |
Does it run unattended and need a release? |
| Tool | A standalone script run by hand, reusing the module | Is it occasional, with no deployment of its own? |
flowchart TD
M["Shared module (Public / Private / Classes / sql)"]
A1["Scheduled application 1"]
A2["Scheduled application 2"]
T["Hand-run tool"]
C["config-env.json"]
A1 --> M
A2 --> M
T --> M
C --> A1
C --> A2
Applications and tools depend on the module, never the other way around. A module that reads an application's config file has already gone wrong.
Lessons learned
- A local copy of a helper dot-sourced by an application silently shadows the module version. When a function moves into the module, delete the local copy.
- Keep the module free of environment knowledge: no server names, no config files. Pass everything in as parameters.
- Make one return convention for all public functions (for example a hashtable with a success flag, data and a reason) so callers can handle failure uniformly.
- Keep
Tests/out of the loaded folders, otherwise your tests run at import time. - A tool that grows a schedule and a config file should be promoted to an application; a tool whose job is done should be deleted.