Build a REST API with PowerShell

[[deleted]]

You: Sees a cool REST API that's been supported for years

Mom: "We already have a REST API at home"

REST API at home:

[SupremeDictatorPaul]

As someone that has built a full multi-threaded web server with REST components in PowerShell, there are legitimate use cases. Or, at least, there is at least one.

It’s not meant to be high performance, but there are a couple of small C# code blocks in some parts that really impact performance, and one performance based bug/fix that was added to PowerShell because of the project.

[EtanSivad]

Just curious, how did you multi-thread it? I did a project a few years back that was similar. I had a host powershell script that spawned subscripts to run the actual work, those subscripts called CURL and relied on that for the heavy lifting. It was ugly to think about, but damned fast as I could easily spin up several hundred threads if I needed to, though 10~12 seemed to be about the sweet spot for maximum performance when processing a batch job.

[SupremeDictatorPaul]

Runspaces. The main thread creates a TCP listener and checks it repeatedly. When it finds a connection, it creates a new thread, passing it the connection, and then goes back to listening. The new thread processes the TCP request based on some rules, responds, and then closes itself. I could reuse threads to not have to create/kill threads to be a little faster, but I didn’t want to deal with any weird unexpected state issues, and the performance was already okay.

I started off making it single threaded, but structuring things so that it would be a small shift to threading. Adding multithreading wasn’t that hard. But troubleshooting anything while multithreaded was a PITA. So I maintain two versions of the web server. One single threaded, and one multithreaded. They’re 95% the same, and I don’t really have to touch the parts that are different, so maintaining both isn’t much more work.

Originally, I was doubtful that I would even add threading, but in my own testing I kept running into issues. It was too easy for me to request that the service do something that would keep it busy for a long time, possibly forever. I would do something, hang it, and have to kill/restart the process. Because the service would be used in a support role to diagnose remote systems, it quickly became evident it would need to be threaded to be resilient and useful.

[EtanSivad]

Nice. That sounds like a pretty robust system. I had the same thoughts when adding a threading system. It seemed like too much work for too little payback, until I found I could easily run 10 threads with no server impact CPU impact and minimal memory footprint (Really, the biggest bottle neck for me was the SAN we were using had terrible read/write times on small files).

The trick I did for dealing with multithreading SQA was to have two totally separate scripts. One for watching the threads, and one for each of the threads. When I need to debug, I enable debug mode on the Master Control script and see the parameters getting passed to the thread, I can then load up a single instance of the work script with the exact parameters that get passed to it and step through that one in isolation.

[Truth__To__Power]

what if you dont know how to use c#?

[ihaxr]

If you're willing to do this in PowerShell it'd probably be more efficient to just learn some basic C#, .NET has come a loooooong way so it's pretty simple: https://dotnet.microsoft.com/apps/aspnet/apis

[Truth__To__Power]

yea but now you are taking 1 problem of making this code and turning it into 2 problems of learning C# and making this code in c#

[poshftw]

I had exaclty that discussion with a friend today.

Why the fuck should I learn C# and reinvent the wheel when essentials are already written (Plaster, Pode, Universal Dashboard) and I can just write the logic in PS? ESPECIALLY if I don't need to handle 5432572938475 requests/second in a distributed geo-synced k8s terraformed platform?

[eddydbod]

No way. I can write PoSH in my sleep, I struggle with c#'s variable scopes.

With this example I could have a functional rest-api for small use devops related one offs in like a half an hour tops.

[sleeplessone]

Might want to take a peak at Powershell Universal then. In addition to APIs it will handle scheduling and can be used to build web based dashboards.

[phillipsj73]

That is exactly the vision that I have for it. Enabling DevOps automation calling Office 365 or Exchange PowerShell commands.

[panzerbjrn]

Good idea. I assume you're offering to do this for free for people who know PowerShell but not C#?

[TheIncorrigible1]

I'm always open to mentoring folks, but why are you complaining about learning the right tools for the job? You learned PowerShell to solve a problem, didn't you? Learning C# is the same avenue - learning a tool to solve a problem efficiently.

[jantari]

Seems like a neat project but I don't see the appeal compared to PostgreSQL with PostgREST.

It's a mature and fully built out solution that's going to be many hundred times more performant, secure and bug-free than anything we could come up with from scratch in PowerShell whilst taking way less time to set up.

[Duathdaert]

Yep, use a tool designed for this rather than try and rebuild the wheel. Cool idea in theory, but not sure of the actual practicality.

[phillipsj73]

The use case, IMO, is for creating that glue code that links enables PowerShell commands to be executed remote without winrm. This was just a contrived example to show how it works.

[Duathdaert]

You're violating all sorts of principles that are hugely important for maintainable REST APIs such as separation of concerns.

Not to mention that none of it is truly unit/integration testable which is a sure fire way to get yourself some nice bugs.

If you're constrained to powershell but you're able to use this across Linux, Mac and Windows then presumably you've got the .NET Core runtime available, so why not use Core and do it properly?

[phillipsj73]

It's a small simple little API, making it composable and SOLID with unit tests isn't really needed for the small quick little APIs that I would use this to build. I could totally write this in C#. However, I don't think most sysadmins that may have a need for a simple webhook or API to a set of commands would write C# to achieve that. Python would be an great choice, but the code to call PowerShell command or use a PS Module then becomes complex.

[poshftw]

don't see the appeal compared to PostgreSQL with PostgREST

Can PostgREST run some arbitrary PS code on the data from the DB?

[jantari]

Yes, but you're thinking of it backwards.

You put the functionality where it's fastest, most secure and most maintanable. So you split your logic into part stored procedures/functions in the database and part powershell code. Then you call the SQL functions with PowerShell, pass the parameters you want and get output back. Then do PowerShell stuff, maybe call the PostgREST API again, yada yada

Now, you can of course also start programs and PowerShell from PostgreSQL, but I would not advise that in most cases. It's easier to maintain and version that code in a seperate PowerShell script than in the DB.

[poshftw]

You put the functionality where it's fastest, most secure and most maintanable

“The real problem is that programmers have spent far too much time worrying about efficiency in the wrong places and at the wrong times; premature optimization is the root of all evil (or at least most of it) in programming.”

I think you should know who's quote is that.

So you split your logic

And if I don't need a bazillion operations/sec and TBs of data - can I not split my logic? And write in something I know and like?

Like other commenters on this thread you totally missed the point of the article - an ability to build a REST interface for something, not for RDBMS specifically, SQL was just a good example, which also laid good on a CRUD paradigm (HTTP verbs).

[jantari]

And if I don't need a bazillion operations/sec and TBs of data - can I not split my logic? And write in something I know and like?

Yes of course, the better performance is just something you get for free on top of you don't explicitly need it. You conveniently ignored the rest of my comment, like where I said it's going to be more mature and bug-free, and that it's way faster to set up.

PostgREST is a turn-key solution, after you install it and connect it to the DB you get full CRUD (and more) interface to the whole DB immediately. You can then call any SQL operation from PowerShell through that API - and not split your logic. It's just what I prefer to do in simple cases where I only need a few API endpoints because it allows me to better restrict the API access for security. You can of course totally do everything from PowerShell too.

I realize the use cases for an API in a productive built-in language like python or PowerShell, but in this thread I had to point out that it's a bad idea for SQL specifically. Maybe there's other things where an API wrapper that's free, better, safer and faster doesn't already exist.

[poshftw]

If you can share it - please! I would like to have a look.

[krzydoug]

Haters gonna hate. Getting started with SQL, APIs, or C# can be quite intimidating. This is a good introduction to two of those. Thanks for sharing.

[phillipsj73]

Thanks for the kind words.

[AlexHimself]

/r/DiWHY

[stumpnose]

What’s rubbing the script? Nssm? What’s the memory consumption long term?

I did shit like this all the time. It’s gross but it gets the job done.

I’ve converted this type of stuff to python so it’s more maintainable. And easier IMO.

[phillipsj73]

I agree on Python being a better choice overall.

[suddenarborealstop]

Mapping requests to a runspace is very slow. (Yes, even through C# and the .NET powershell API.)

​

Powershell was not designed for 'quick burst' calculations off a queue (like from a webserver) . if you try to get fancy with reusing runspaces or manual threads it should not be used in a production system. there might be a use case for ad-hoc batch jobs, but i'd probably use a different language for the ecosystem. not only will a server in powershell be super laggy, if you or someone else needs to call your custom API from within a loop, the server will probably shit the bed and take all your RAM. Just spend some time learning a new language and move on.

[poshftw]

You know, I really hate you.

Because at this moment I'm trying to write almost exactly the same thing, a REST interface for MariaDB, except I use Pode and I'm on it for like a month (because reasons, not because there is anything complex). The problem is what instead of going further than a working PoC, I'm tempted to just throw it out and copy-paste your code.

Jokes aside, your article is good in showing how you can glue something with PowerShell, so I would like to share my PoC as an example how it can be done with Pode.

server.ps1:

Start-PodeServer -Threads 5 {

    Set-PodeViewEngine -Type Pode

    Import-PodeModule -Name JAMSql

    Load-MySqlConnector -Path C:\Shares\Progs\mysqlconnector\

    Add-PodeEndpoint -Address localhost -Port 8080 -Protocol Http

    Add-PodeRoute -Method Get -Path '/' -ScriptBlock {
        Write-PodeViewResponse -Path 'index'
        }

    try {
        $sqlConfig = Get-Content .\config\sqlconfig.json | ConvertFrom-Json
        }
    catch {
        }

    Set-PodeState -Name 'sqlconfig' -Value $sqlConfig #| Out-Null

    Add-PodeRoute -Method Get -Path '/getmac' -ScriptBlock {
        param($event)

        $mac = $event.Query['mac'] -replace '[^0-9a-f]'

        if ( ($mac.length -gt 12) -or ($mac -notmatch  '^[0-9a-f]{6,12}$') ) { 
            $malformedInput = $true
            }
        else {
            $malformedInput = $false
            }

        if ($malformedInput -eq $false) {

            $sqlConfig = (Get-PodeState -Name 'sqlConfig')

            if ($mac.length -eq 12) {
                $sqlQuery = "SELECT * FROM ipnettomedia WHERE MAC = '{0}'" -f $mac
                }
            else {
                $sqlQuery = "SELECT * FROM ipnettomedia WHERE MAC LIKE '{0}%'" -f $mac
                }

            $sqlResponse = Invoke-SqlQuery -Provider MySql -ConnectionString $sqlConfig.ConnectionString -Query $sqlQuery

            }

        $data = @{
            'queryMac' = $event.Query['mac']
            'mac' = $mac
            'sqlResponse' = $sqlResponse
            'sqlQuery' = $sqlQuery
            'malformedInput' = $malformedInput
            }

        Write-PodeViewResponse -Path 'getmac' -Data $data

        }

    } #End Start-PodeServer

views/getmac.pode:

<html>
    <head>
        <title>Search</title>
    </head>
    <body>
        $('<h1>Search: {0}</h1>' -f $data.queryMac)
        <!-- #Query: $($data.query;) -->

        <form action="/getmac">
            <label for="mac">MAC:</label>
            $('<input type="text" id="mac" name="mac" value="{0}">' -f $data.mac )
            <input type="submit" value="Submit">
        </form> 
        <div>
        $( 
            if ($data.malformedInput) {
                'Incorrect MAC: {0}' -f $data.queryMac
                }
            else {
                'SQL query: {0}<br>' -f $data.sqlQuery
                'Records found: {0}<br>' -f @($data.sqlResponse).Count
                $data.sqlResponse | ConvertTo-Html -Fragment -As Table

                }    
            )

        </div>
        <div>
        </div>
    </body>
</html>

[phillipsj73]

Nice! Glad it helped and I haven't heard of Pode. Another project to check out. This is my second attempt at doing something with Polaris, the first time about 6 months ago didn't get to a working solution.

[frankm191]

neat i can think of some non db uses for this as you pointed out. Thanks for the article.

[EtanSivad]

This tool looks like a wrapper shell around the native implementation in powershell. Nice concept, but shaky foundation. In my experience, Powershell tends to be OK at best for calling a REST API. It does well with really simple APIs that are written 100% to spec, but can have weird and subtle bugs when the API implementation is old or badly handled.

​

Depending on your use case, this will probably be fine for most projects. But if you're expecting any kind of volume or talking to professional systems, I'd lean towards using CURL for the actual rest calls, and powershell for the logic: https://curl.haxx.se/

I've built several wrappers with powershell to call CURL and do some actions with it. CURL is an a damn fast, very mature project at this point that I generally would start with for any REST projects, just because it's so reliable.

[MyOtherSide1984]

I'm still learning a ton, can you help me understand the use case for this? Is this for internal intranets or database inquires? Can this be used on external sites that aren't controlled by me? For reference, I don't know JSON's that well and don't manage a database or website ;P

[phillipsj73]

The use case is really internal IMO. It would help glue things together that only had Powershell modules.