How can I link a database to my Pode website

[seekerquest]

Question

I know Pode has a config file, Is this a good start where we can define initialize DB and start using from .pode files ?

[Badgerati]

Hi @eldjoko,

The config file would be a good place to store the likes of connection details, ie: URL, Port, Username, etc. (though I'd store any credentials in a more secure format / secret manager).

The connection itself can then be done within Pode, and stored using Set-PodeState and retrieved using Get-PodeState. This lets you have one connection, rather than re-connecting every time.

Hope that helps!

[seekerquest]

Many thanks! I will explore the use case and if everything work well, I can share the process of linking a database in a step-by-step tutorial.

[ericxin1982]

@eldjoko Any luck to link db inside Pode?
I am getting stuck here also.

[phdavis]

We store non-sensitive variables as you usually would in PowerShell (such as the server/database name) in the primary server script block, then reference them throughout Pode scriptblocks if needed with $using.

The SQL credentials aren't stored since we're running as a service under a service account that already has the proper SQL access. You could use something like Azure Key Vault though and retrieve credentials then store them in PodeState as mentioned.

You should be able to then import and use a module in Pode like Sqlserver or SimplySQL to run queries inside scripts, routes, pages, etc.

# Assuming you've imported your SQL module at some point
Add-PodeRoute -Method Get -Path '/Users' -ScriptBlock {
   $Query = "SELECT TOP (5) username FROM users ORDER by username DESC"
   $Result = Invoke-Sqlcmd -ServerInstance $($using:SQLServer) -Database $($using:SQLDatabase) -Query $Query
   Write-PodeJsonResponse -Value $Result -StatusCode 200
}

[ili101]

I was trying to use $using or Set-PodeState to connect once then use the same connection, it works for some simple module I made that stores a connection (a PS class) in the module in a $Script:Connection.
But when I try to do the same thing with SimplySql this usually hang. The procedure works with SimplySql in the same runspace but it doesn't like it when it's exported in one runspace and imported in another.
Why is that?

# Install-Module SimplySql
# Install-Module Pode

Import-Module SimplySql
Import-Module Pode

# Open-SQLiteConnection -ConnectionString "Data Source=:memory:"
Open-SQLiteConnection -DataSource ".\temp.db"

function Get-SimplySqlConnection {
    [CmdletBinding()]
    param ()
    # . (Get-Module SimplySql) | Format-List { $Script:Connections }
    . (Get-Command Invoke-SqlQuery).Module { $Script:Connections }
}
function Set-SimplySqlConnection {
    [CmdletBinding()]
    param (
        [parameter(ValueFromPipeline)]
        $Connection
    )
    . (Get-Command Invoke-SqlQuery).Module { $Script:Connections = $args[0] } $Connection
}
$SimplySqlConnection = Get-SimplySqlConnection


# Test that it works in the current scope.
Import-Module SimplySql -Force # Reset the module.
Invoke-SqlScalar -Query "SELECT 1" # As expected: WARNING: There is no active SQL Connection.
$SimplySqlConnection | Set-SimplySqlConnection # Reload the connection.
Invoke-SqlScalar -Query "SELECT 1" # Works, returns 1.


# Now in the runspace it usually hangs, but sometimes it run once.
Start-PodeServer {
    Add-PodeEndpoint -Address localhost -Port 8093 -Protocol Http
    New-PodeLoggingMethod -File -Name 'Errors' | Enable-PodeErrorLogging
    New-PodeLoggingMethod -File -Name 'Requests' | Enable-PodeRequestLogging

    Add-PodeRoute -Method Get -Path '/test' -ScriptBlock {
        $using:SimplySqlConnection | Set-SimplySqlConnection
        Write-Host 'ok'
        $Data = Invoke-SqlScalar -Query "SELECT 1"
        Write-Host 'Did not get here'
        Write-PodeTextResponse -Value ('String:', ($Data) | Out-String)
    }
}

[ericxin1982]

Hi @Badgerati \

How you did the connection first and keep them in set-podestate, just learn how to use this in a proper way.

Thanks
Eric Xin

[Badgerati]

Hi @ericxin1982 / @ili101,

So I'm not sure about SimplySql as I've not used it before; it does look like it uses powershell classes so that could be an issue with the way classes work in powershell and runspaces. If you use Open-SQLiteConnection in the Route (or Middleware) and run it only when $Script:Connection is empty - so effectively open a connection once per runspace, do that stop the hanging?

As for how I've done connections in the past, I've only really use SQLConnection in .NET. I just create and open the connection, and cache it in Pode's State:

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$sqlConnection.Open()

Start-PodeServer {
    Set-PodeState -Name 'Connection' -Value $sqlConnection | Out-Null

    Add-PodeRoute -Method Get -Path '/' -ScriptBlock {
        $conn = Get-PodeState -Name 'Connection'
        # Create SqlCmd and run query here using above connection
    }
}

[ili101]

I was planning to get back to it soon as I'm finessing the backend part of the tool now.
First Microsoft is so annoying by naming their SQL implementation "SQL" like you can't know if someone refers to the standard or to MSSQL.
Anyway I didn't got into it yet, but from what I can see all SQLs (the standard) under .NET are using "ADO.NET", and in it's documentation it actually state that you should just create a new connection when needed and not share it:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling?redirectedfrom=MSDN#pool-creation-and-assignment

So for example SQLite is thread safe, but Microsoft.Data.Sqlite is not as it's implementing ADO.NET like all the SQLs in .NET.

In contrast to that for example Azure SDK state that you basically should connect once and share this connection and also you don't need to worry about looking.

[RobinBeismann]

Chiming in as I deal alot with Pode and MSSQL. Check out: https://github.com/dataplat/Invoke-SqlCmd2
This function is my goto cmdlet for every occassion where I have to interface with MSSQL on Powershell. With Pode you can even utilize KCD to impersonate the user against the SQL backend.

[ericxin1982]

Hi @Badgerati

Thanks so much!

Thanks
Eric Xin

[ili101]

So I did some tests using SimplySql and Postgres.
As mentiond in the Microsoft ADO.NET documentation disposing the connection in the thread doesn't actually close it but mark it as free for reuse for the next connection that need to query the same server. that seems to work nicely as expected.

# docker run --name TestPostgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres

$Server = 'localhost'
$Cred = [System.Management.Automation.PSCredential]::new('postgres', (ConvertTo-SecureString 'mysecretpassword' -AsPlainText -Force))
$SimplySqlPath = 'SimplySql'

Import-Module $SimplySqlPath
Open-PostGreConnection -Server $Server -Credential $Cred -ConnectionName MyDb
Close-SqlConnection -ConnectionName MyDb

Measure-Command {
    1..100 | ForEach-Object -ThrottleLimit 10 -Parallel {
        Import-Module $using:SimplySqlPath
        $ConName = 'MyDb' #+ (Get-Random)
        Open-PostGreConnection -Server $using:Server -Credential $using:Cred -ConnectionName $ConName
        $Sleep = -1
        $Data = Invoke-SqlQuery -Query "SELECT concat(pg_sleep($Sleep), 1+1) AS ""Num""" -ConnectionName $ConName
        Close-SqlConnection -ConnectionName $ConName
    }
} | Select-Object -ExpandProperty TotalSeconds
(Get-NetTCPConnection -OwningProcess $PID -RemotePort 5432).Count

This actually may change in .NET 7.0 as I see they added DbDataSource which is like a connection factory, but I can't find match information on it as it looks like only Postgres implemented it so far (and even them less then a month ago), from what I see disposing the data source actually disconnect the pool, so you do need to not recreate it every time like with the connection.
We will see if the other providers like MSSQL and MYSQL will also add this for the .NET 7.0 versions

Add-Type -Path Npgsql.dll
$connectionString = 'Application Name="PowerShell (MyDb)";Max Auto Prepare=25;Host=localhost;Database=postgres;Port=5432;Username=postgres;Password=mysecretpassword;SSL Mode=Prefer'
$dataSource = [Npgsql.NpgsqlDataSource]::Create($connectionString)

1..100 | ForEach-Object -ThrottleLimit 10 -Parallel {
    $Sleep = 0.1
    $dataSource = $using:dataSource
    $cmd = $dataSource.CreateCommand("SELECT concat(pg_sleep($Sleep), 1+1) AS ""Num""")
    $reader = $cmd.ExecuteReader()
    while ($reader.Read()) {
        $null =  $reader.GetString(0)
    }
    $reader.Dispose()
    $cmd.Dispose()
}
(Get-NetTCPConnection -OwningProcess $PID -RemotePort 5432).Count
$dataSource.Dispose()

[ili101]

This is the pattern I came up with and it looks like it working reliably so far.
Maybe I will cleanup, generalize and share it at some point.

Modules/Connections

• Thread safe modules I load in PodeServer.ps1.
• If those modules have data I initialize, export and in runspace import it once.
• if not thread safe I import and initialize it once in runspace.
• SimplySql connect in runspace as needed as recommended by Microsoft, import in PodeServer.ps1 and also connect and disconnect once (not sure why but without it it can sometime error at first).

A few additional gotchas I encountered:

1. Be careful when using Start-PodeServer -FilePath. If you "&" your "PodeServer.ps1", the parameters will not be in "PodeMain.ps1" if not global.
2. Pode imports modules fail if modules use RequiredModules dependency (Pode.Web) #1046.
3. Be careful using $using:Foo use Set-PodeState as it's not available everywhere.
4. Add-Type is not thread safe, this is why I added the Lock-PodeObject Add-Type fails sporadically with "An item with the same key has already been added" PowerShell/PowerShell#19470
5. PowerShell classes are not thread safe, but starting from PS 7.4 preview 1 they can be with this new attribute 😀:

[NoRunspaceAffinity()]
class TestLock { }

6. I use a function (in this example Invoke-PsFPode) to wrap all my scriptblocks. at the beginning I tried to use Add-PodeMiddleware and Add-PodeEndware but opted not to as I have more control on what urls it attache to, with PodeMiddleware I can't control if it run before or after login and trapping is simpler like this.

Reduced example

PodeServer.ps1

param (
    [string]$Environment = $env:PSF_ENVIRONMENT ?? 'Test',
    [string]$Address = 'localhost',
    [nullable[int]]$Port = 8444,
    [nullable[int]]$PortExternal,
    [nullable[int]]$PortRedirect = 8081
)
# This is to move params from here to PodeMain.ps1 in case the script was executed with "&" instead of ".".
$global:Address = $Address
$global:Port = $Port
$global:PortExternal = $PortExternal ?? $Port
$global:PortRedirect = $PortRedirect

# Put your setting somewhere, I put it here for simplicity.
$ModulesFolder = 'Foo'
$Server = 'localhost'
$Cred = Get-Credential

Import-Module (Join-Path $ModulesFolder 'SimplySql')
# WORKAROUND: When not connecting here can get: "Object reference not set to an instance of an object." (if the first 2 runspaces run in the same time).
Open-PostGreConnection -Server $Server -Credential $Cred -ConnectionName MyDb
Show-SqlConnection -All | Close-SqlConnection
# Thread safe module without $script:Foo data. Just import it once here.
Import-Module (Join-Path $ModulesFolder 'ThreadSafeModuleWithoutData')
# Thread safe module with $script:Foo data (export, import later steps).
Import-Module (Join-Path $ModulesFolder 'ThreadSafeModuleWithData')
Initialize-ThreadSafeModuleWithData -Foo 'Bar'

# WORKAROUND: https://github.com/Badgerati/Pode/issues/1046 "The required module 'Pode' is not loaded."
$env:PSModulePath = $env:PSModulePath + ($IsWindows ? ';' : ':') + $ModulesFolder

Import-Module -Name (Join-Path $ModulesFolder 'Pode')
Import-Module -Name (Join-Path $ModulesFolder 'Pode.Web')
Import-Module -Name (Join-Path $ModulesFolder 'Pode.Kestrel')

Start-PodeServer -Threads 3 -FilePath (Join-Path $PSScriptRoot 'PodeMain.ps1')

PodeMain.ps1

{
    # Set-PodeState as we are using a function and $using will not work in this case.
    Set-PodeState -Name 'ThreadSafeModuleWithDataConnection' -Value (Get-ThreadSafeModuleWithData) | Out-Null

    # Add a simple endpoint
    $Certificate = Get-PfxCertificate -FilePath 'cert.pfx'
    Add-PodeEndpoint -Address $global:Address -Port $global:Port -Protocol Https -X509Certificate $Certificate

    # Redirect http to https.
    Add-PodeEndpoint -Address $global:Address -Port $global:PortRedirect -Protocol Http -Name 'Redirect'
    Add-PodeRoute -Method * -Path * -EndpointName 'Redirect' -ScriptBlock { Move-PodeResponseUrl -Protocol Https -Port $args[0] } -ArgumentList $global:PortExternal

    New-PodeLoggingMethod -File -Name 'Errors' -Path './Logs' | Enable-PodeErrorLogging
    New-PodeLoggingMethod -File -Name 'Requests' -Path './Logs' | Enable-PodeRequestLogging
    New-PodeLoggingMethod -File -Name 'Custom' -Path './Logs' | Add-PodeLogger -Name 'Main' -ScriptBlock { param($Message) return "$Message" }
    Enable-PodeSessionMiddleware -Secret 'Foo' -Duration (60 * 60 * 24) -Extend
    # Set the use of templates, and set a login page
    Use-PodeWebTemplates -Title 'Tool'

    function Invoke-PsFPode {
        [CmdletBinding()]
        param (
            [Parameter(Mandatory)]
            [scriptblock]$Script,
            [string]$ValidationParam,
            [scriptblock]$Finely
        )
        Set-StrictMode -Version 1.0
        try {
            if (!(Test-Variable -Name PodeInitialized -Scope global)) {
                $global:PodeInitialized = $true
                $global:ErrorActionPreference = 'Stop'
                # WORKAROUND: https://github.com/PowerShell/PowerShell/issues/19470 "An item with the same key has already been added."
                Lock-PodeObject -ScriptBlock {
                    # Load the connection/data to a thread safe module that support exporting and importing:
                    Set-ThreadSafeModuleWithData -Connection (Get-PodeState -Name 'ThreadSafeModuleWithDataConnection')
                    # Import and initialize a module that is not thread safe:
                    Import-Module -Name 'PartnerCenterCommunity'
                    Connect-PartnerCenter -Credential $Credential -RefreshToken $RefreshToken
                }
            }
            Open-PostGreConnection -Server $Server -Credential $Cred -ConnectionName MyDb

            . $Script
            # No error cleanup
        }
        catch {
            # Your error handling
            $Errors = $_
            if ($ValidationParam) {
                Out-PodeWebValidation -Name $ValidationParam -Message $Errors
            }
            else {
                $Errors | ForEach-Object { Show-PodeWebToast -Message $_ -Duration (10 * 1000) }
            }
        }
        finally {
            # Cleanup
            Show-SqlConnection -All | Close-SqlConnection
            if ($Finely) {
                . $Finely
            }
        }
    }
    . .\Page1.ps1
    . .\Page2.ps1
}

Page1.ps1

$WizardSection = New-PodeWebAccordion -Bellows @(
    New-PodeWebBellow -Name 'wizard' -Content @(
        New-PodeWebSteps -Name 'Steps' -Steps @(
            New-PodeWebStep -Name 'Subscription' -Icon 'cog-outline' -Content @(
                New-PodeWebTextbox -Name 'Subscription ID'
            ) -ScriptBlock {
                Invoke-PsFPode {
                    # Do stuff:
                    $Data = Invoke-SqlQuery -Query "SELECT ..." -ConnectionName MyDb
                } -ValidationParam 'Subscription ID'
            }
        ) -ScriptBlock {
            Invoke-PsFPode {
                # Do stuff
            } -Finely { Set-Foo }
        }
    )
)
Add-PodeWebPage -Name 'Page1' -Icon 'cart-minus' -Layouts $WizardSection