RCFMARTIN

This guide assumes Powershell 7 (it works on 5.1 too, with one or two adjustments noted) and that you can install NuGet packages locally.

Most "Powershell + SQL" posts hand you Invoke-Sqlcmd and call it a day. That's fine for one-off queries, but the moment you need parameterization, transactions, batched inserts, or you're talking to anything other than SQL Server, you outgrow it fast.

This post walks through the System.Data API directly the same API every .NET ORM is built on for both SQL Server (Microsoft.Data.SqlClient) and PostgreSQL (Npgsql).

Loading the Provider

SQL Server

System.Data.SqlClient is legacy. Use Microsoft.Data.SqlClient:

Install-Package Microsoft.Data.SqlClient -Scope CurrentUser -Force
Add-Type -Path (Get-Package Microsoft.Data.SqlClient).Source

Or, cleaner, with Install-Package into a known folder so you can ship the DLL with your script.

PostgreSQL

Install-Package Npgsql -Scope CurrentUser -Force
Add-Type -Path (Get-Package Npgsql).Source

On Powershell 5.1, both providers work but you may need to explicitly load System.Memory.dll and System.Threading.Tasks.Extensions.dll first. Powershell 7 handles dependencies cleanly.

A Tiny Connection Helper

Every script in this post uses the same helper. Save as Sql.psm1:

function Get-SqlConnection
{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)] [ValidateSet('SqlServer','Postgres')] [string]$Provider,
        [Parameter(Mandatory)] [string]$ConnectionString
    )

    $conn = switch ($Provider) {
        'SqlServer' { [Microsoft.Data.SqlClient.SqlConnection]::new($ConnectionString) }
        'Postgres'  { [Npgsql.NpgsqlConnection]::new($ConnectionString) }
    }
    $conn.Open()
    return $conn
}

Export-ModuleMember -Function Get-SqlConnection

Now every call site looks the same:

$conn = Get-SqlConnection -Provider SqlServer -ConnectionString $cs
try
{
    # ... do work ...
} finally {
    $conn.Dispose()
}

Always wrap the connection in try/finally and call Dispose(). Connection leaks under load are the single most common bug in handwritten data-access code.

Parameterized Queries Stop String-Concatenating

This is the only rule that matters: never build SQL with string interpolation.

# WRONG - SQL injection waiting to happen
$user = "alice'; DROP TABLE Users;--"
$cmd.CommandText = "SELECT * FROM Users WHERE Name = '$user'"

# RIGHT
$cmd = $conn.CreateCommand()
$cmd.CommandText = 'SELECT Id, Name, Email FROM Users WHERE Name = @name'
[void]$cmd.Parameters.AddWithValue('@name', $user)

For PostgreSQL the syntax is identical except parameters use @name too in Npgsql (which translates to its native :name under the hood).

Reading Results

Two patterns. Pick based on result size.

Small Result Set Materialize to Objects

function Invoke-SqlQuery
{
    [CmdletBinding()]
    [OutputType([pscustomobject[]])]
    param(
        [Parameter(Mandatory)] $Connection,
        [Parameter(Mandatory)] [string]$Sql,
        [hashtable]$Parameters = @{}
    )

    $cmd = $Connection.CreateCommand()
    $cmd.CommandText = $Sql
    foreach ($k in $Parameters.Keys)
    {
        [void]$cmd.Parameters.AddWithValue($k, ($Parameters[$k] ?? [DBNull]::Value))
    }

    $reader = $cmd.ExecuteReader()
    try
    {
        while ($reader.Read())
        {
            $row = [ordered]@{}
            for ($i = 0; $i -lt $reader.FieldCount; $i++)
            {
                $val = $reader.GetValue($i)
                $row[$reader.GetName($i)] = if ($val -is [DBNull]) { $null } else { $val }
            }
            [pscustomobject]$row
        }
    } finally {
        $reader.Dispose()
        $cmd.Dispose()
    }
}

Usage:

$queryParams = @{
    Connection = $conn
    Sql        = 'SELECT Id, Name FROM Users WHERE Active = @active'
    Parameters = @{ '@active' = $true }
}
$users = Invoke-SqlQuery @queryParams

Large Result Set Stream

The pattern above already streams (each [pscustomobject]$row flows down the pipeline immediately). Don't be tempted to wrap it in an array:

$cnt = Invoke-SqlQuery $conn 'SELECT * FROM HugeTable' |
       Where-Object Status -eq 'pending' |
       Measure-Object | Select-Object -ExpandProperty Count

This holds at most one row in memory at a time. Wrapping the call in @() would buffer everything.

Single-Value Queries

function Invoke-SqlScalar
{
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
    $cmd = $Connection.CreateCommand()
    $cmd.CommandText = $Sql
    foreach ($k in $Parameters.Keys)
    {
        [void]$cmd.Parameters.AddWithValue($k, ($Parameters[$k] ?? [DBNull]::Value))
    }
    try { $cmd.ExecuteScalar() } finally { $cmd.Dispose() }
}

$count = Invoke-SqlScalar $conn 'SELECT COUNT(*) FROM Users WHERE Active = @a' @{ '@a' = $true }

ExecuteScalar returns the first column of the first row perfect for COUNT, MAX, EXISTS, identity returns.

Non-Query Commands

function Invoke-SqlNonQuery
{
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{}, $Transaction = $null)
    $cmd = $Connection.CreateCommand()
    $cmd.CommandText = $Sql
    if ($Transaction) { $cmd.Transaction = $Transaction }
    foreach ($k in $Parameters.Keys)
    {
        [void]$cmd.Parameters.AddWithValue($k, ($Parameters[$k] ?? [DBNull]::Value))
    }
    try { $cmd.ExecuteNonQuery() } finally { $cmd.Dispose() }
}

Returns the number of rows affected.

Transactions

$tx = $conn.BeginTransaction()
try
{
    $debit = @{
        Connection  = $conn
        Sql         = 'UPDATE Accounts SET Balance = Balance - @a WHERE Id = @from'
        Parameters  = @{ '@a' = 100; '@from' = 1 }
        Transaction = $tx
    }
    Invoke-SqlNonQuery @debit

    $credit = @{
        Connection  = $conn
        Sql         = 'UPDATE Accounts SET Balance = Balance + @a WHERE Id = @to'
        Parameters  = @{ '@a' = 100; '@to'   = 2 }
        Transaction = $tx
    }
    Invoke-SqlNonQuery @credit

    $tx.Commit()
} catch {
    $tx.Rollback()
    throw
} finally {
    $tx.Dispose()
}

Always commit or rollback in finally/catch. A leaked transaction holds locks until the connection dies sometimes for hours.

Bulk Insert (SQL Server)

For anything past a few thousand rows, INSERT row-by-row is hundreds of times slower than SqlBulkCopy.

function Invoke-SqlBulkCopy
{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)] [Microsoft.Data.SqlClient.SqlConnection]$Connection,
        [Parameter(Mandatory)] [string]$DestinationTable,
        [Parameter(Mandatory, ValueFromPipeline)] [pscustomobject[]]$InputObject,
        [int]$BatchSize = 5000
    )
    begin
    {
        $bulk = [Microsoft.Data.SqlClient.SqlBulkCopy]::new($Connection)
        $bulk.DestinationTableName = $DestinationTable
        $bulk.BatchSize            = $BatchSize

        $table = [System.Data.DataTable]::new()
        $first = $true
    }
    process
    {
        foreach ($obj in $InputObject)
        {
            if ($first)
            {
                foreach ($p in $obj.PSObject.Properties)
                {
                    [void]$table.Columns.Add($p.Name)
                }
                $first = $false
            }
            $row = $table.NewRow()
            foreach ($p in $obj.PSObject.Properties)
            {
                $row[$p.Name] = if ($null -eq $p.Value) { [DBNull]::Value } else { $p.Value }
            }
            $table.Rows.Add($row)
        }
    }
    end
    {
        $bulk.WriteToServer($table)
        $bulk.Dispose()
    }
}

Usage:

Import-Csv .\users.csv | Invoke-SqlBulkCopy -Connection $conn -DestinationTable 'dbo.Users'

A million rows that would take ~30 minutes via individual INSERTs lands in seconds.

Bulk Insert (PostgreSQL)

PostgreSQL's equivalent is COPY, exposed by Npgsql as a binary importer:

function Invoke-NpgsqlCopy
{
    param(
        [Npgsql.NpgsqlConnection]$Connection,
        [string]$Table,
        [string[]]$Columns,
        [pscustomobject[]]$Rows
    )
    $colList = ($Columns -join ',')
    $writer  = $Connection.BeginBinaryImport("COPY $Table ($colList) FROM STDIN (FORMAT BINARY)")
    try
    {
        foreach ($r in $Rows)
        {
            $writer.StartRow()
            foreach ($c in $Columns)
            {
                $v = $r.$c
                if ($null -eq $v) { $writer.WriteNull() }
                else              { $writer.Write($v) }
            }
        }
        $writer.Complete()
    } finally {
        $writer.Dispose()
    }
}

Stored Procedures and Output Parameters

$cmd = $conn.CreateCommand()
$cmd.CommandText = 'dbo.usp_CreateUser'
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
[void]$cmd.Parameters.AddWithValue('@Name', 'alice')

$out = $cmd.Parameters.Add('@NewId', [System.Data.SqlDbType]::Int)
$out.Direction = [System.Data.ParameterDirection]::Output

[void]$cmd.ExecuteNonQuery()
$newId = $out.Value

Connection Strings A Few Production Defaults

# SQL Server
Server=tcp:db01.example.com,1433;
Database=AppDb;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=15;
Application Name=MyOpsScript;

# PostgreSQL
Host=db01.example.com;
Port=5432;
Database=appdb;
Username=appuser;
Password=...;
SSL Mode=Require;
Trust Server Certificate=false;
Application Name=MyOpsScript;
Timeout=15;
Command Timeout=60;

Set Application Name always. When the DB starts misbehaving and your DBA asks "what's hammering us?", the connection list grouped by app name is the first thing they'll look at.

Retries and Transient Failures

Both providers throw transient errors that are safe to retry (deadlocks, brief network blips). A small wrapper pays for itself the first time a script survives a failover:

function Invoke-WithRetry
{
    param([scriptblock]$Action, [int]$MaxAttempts = 3, [int]$DelayMs = 500)
    for ($i = 1; $i -le $MaxAttempts; $i++)
    {
        try { return & $Action }
        catch
        {
            $isTransient = $_.Exception -is [Microsoft.Data.SqlClient.SqlException] -and
                           $_.Exception.Number -in 1205, 49918, 49919, 49920, 4060, 40197
            if (-not $isTransient -or $i -eq $MaxAttempts) { throw }
            Start-Sleep -Milliseconds ($DelayMs * $i)
        }
    }
}

Use it for the operations that are safe to repeat (idempotent reads, deadlock victims). Don't blindly wrap everything replaying a non-idempotent write can double-charge a customer.

What to Do Next

The System.Data providers give you everything an ORM does, minus the magic, and you only need a few hundred lines of helper functions to make them feel native to PowerShell. Parameterize your queries, dispose your connections, batch your inserts, and wrap retries narrowly.

Three concrete moves to deploy the pattern this week:

Find the next script that calls Invoke-Sqlcmd with string-interpolated SQL. That's an injection bug today. Replace with the Invoke-SqlQuery helper from above; the call site barely changes and the security posture goes from broken to correct.
Drop a Sql.psm1 with the four helpers (Get-SqlConnection, Invoke-SqlQuery, Invoke-SqlScalar, Invoke-SqlNonQuery) into your team's shared module path. Anyone writing a new SQL-touching script imports the module instead of rewriting the cmd boilerplate.
For the next bulk import, replace the row-by-row INSERT loop with SqlBulkCopy. The runtime drops from minutes to seconds; the script is also shorter.

Pairs naturally with the secret-management post (the connection-string credential is the first thing to move out of script literals) and the error-handling post (database operations have specific exception types worth catching narrowly).

Powershell + SQL Without an ORM