Deploying Azure Cosmos DB stored procedures with Powershell

How can you deploy stored procedures to an Azure Cosmos database using Azure DevOps Pipelines?

That was the question I was trying to solve. I needed to be able to deploy stored procedures if they didn't already exist and be able to update them if they already existed. I wanted to be able to check in an individual file to create a new stored procedure or update an existing file to update an existing stored procedure. This would allow for maximum reusability in the future no matter how many stored procedures were needed or how often they needed to change.

Start with the Powershell Commandlets fromhttps://github.com/PlagueHO/CosmosDB. This gave me the ability to create a new stored procedure or update an existing one with the New-CosmosDbStoredProcedure and Set-CosmosDbStoredProcedure commands respectively.

Using the New and Set commands I created my own command that will first create a new stored procedure and then update that stored procedure with the contents of a file. If the stored procedure already existed, it would error, so we catch that error and proceed to update knowing that it there in all cases.

function CreateOrUpdate-Sproc
{
    param([string] $sprocName, [string] $sprocFilePath)
    echo ("Loading sproc from file: "   $sprocFilePath)
    $sprocValue = Get-Content -Path $sprocFilePath -Raw
    try{
        New-CosmosDbStoredProcedure -Context $cosmosDbContext
      -Database "dbName"
      -CollectionId "collectionName"
      -Id $sprocName
      -StoredProcedureBody "function overwriteMe() {}"
      -ErrorAction Stop
    } catch {
        ## Ignore error here
    }
    Set-CosmosDbStoredProcedure -Context $cosmosDbContext
     -Database "dbName"
     -CollectionId "collectionName"
     -Id $sprocName
     -StoredProcedureBody $sprocValue
     -ErrorAction Stop
}

Now I need to loop over the files within a folder and call this method for each file I find.

# Loop through all sproc.js files in sprocs folder to create or update sprocs
$sprocsFolder = Join-Path (Get-ScriptDirectory) "sprocs"
Get-ChildItem $sprocsFolder -Filter *.sproc.js |
Foreach-Object {
    $fullPath = $_.FullName
    $sprocName = $_.BaseName.Split(".")[0]
    CreateOrUpdate-Sproc $sprocName $fullPath
}

Here I am using only looking for files with the extension of sproc.js. I use the rest of the filename as the stored procedure name and pass that name and file path to the CreateOrUpdate-Sproc command.

The only thing remaining for this to work is to make sure that those sproc.js files are packaged with my solution’s build. I need to make sure that these files are marked as Content and Copy Always.

Right-Click your sproc.js file and choose properties, then verify these fields are set to match.

There are some aspects of this that I do not love. I wish I could find a way around catching an exception to determine if the stored procedure already exists, or better yet have a single command that would create if needed or update if needed. I believe it is likely these concerns will get solved in the future, but for the time being this solves my problem and hopefully yours as well.