LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon