Hello Azure Sproc

Hello Azure Sproc

Overview

CosmosDB Stored Procedures

Why stored procedures?

Stored Procedures (sprocs) are functions that run on the server side of a database. In particular we will be talking about sprocs in CosmosDB.

The main reasons to use CosmosDB Sprocs are for atomic transactions and performance. All database operations completed in a single sproc call will be atomic. Since the sproc runs on the server the network latency for loading and saving documents will be considerably less than if you ran the same operations from a remote server or laptop.

To complete this example I assume that you already have a cosmosDB account and the write token.

The Code

For this example we will create a simple "Hello World" sproc that returns "Hello World" when called.

Create the Sproc

To define a sproc in code, create an object that has an 'id' and a body that is the function itself. The 'id' will be the name of the sproc and will be used for execution calls.

Once a sproc is in production, I recommend versioning it, by naming the next one something like id: "helloWorld_2", if you are concerned with breaking exsiting applications.

1const helloWorldStoredProc = {
2    id: "helloWorld",
3    body: function () {
4        console.log("logging message from the sproc")
5        var context = getContext();
6        var response = context.getResponse();
7        response.setBody("Hello, World");
8    }
9}

This is a very simple sproc, it just gets the response object from the context and sets the body. When called we expect to see 'Hello, World" as the return.

Every stored procedure is a javascript object with an ID and a body, where the body is the function to exectue.

Setup hello_world_sproc.js

1# if you prefix the export command with a space
2# it usually will not save the secret key to your history file
3 export cosmos_key="<<INSERT KEY HERE>>"
4npm i -s @azure/cosmos

Load Cosmos client

1const { CosmosClient } = require('@azure/cosmos')
2
3const endpoint = 'https://******.documents.azure.com:443/'
4const key = process.env.cosmos_key
5const client = new CosmosClient({ endpoint, key })
6
7//get a reference to the container we want to work with
8const container = client.database('cart').container('coupons')

Create Sproc in the container

write a function to push the sproc up

1async function createSproc(sproc) {
2    return container.scripts.storedProcedures.create(sproc)
3}

In order to work with sprocs, you need to access the Scripts class from the container. This class gets you access to both sprocs as well as User Defined Functions. We will use the create method of the storedProcedure object for now.

Execute the sproc

 1async function runSproc(sprocname, partition_id, args) {
 2    const result = await container
 3            .scripts
 4            .storedProcedure(sprocname)
 5            .execute(partition_id, args, 
 6                { enableScriptLogging: true }
 7                )
 8
 9    console.log("Sproc Log: ", decodeURIComponent(result.headers['x-ms-documentdb-script-log-results']))
10    console.log("Sproc RU cost: ", result.headers['x-ms-request-charge'])
11    return result.resource
12}

Again use the scripts object to access the sprocs. container.scripts.storedProcedure(sprocname) will give you access to the stored Procedure object, which you can then use to access the execute method.

execute takes a few positional arguments.

1function execute<T>(partitionKey: any, params?: any[], options?: RequestOptions)

The first being a partition key (string works for simple keys), the second are the arguments to pass into the sproc, and the third is a RequestOptions configuration object that we will use to capture logs. If you do not specifically request logs, then you will not see them returned.

Get it working

Add a small async main function and get this code working!

1async function runHello() {
2  console.log("\n*****\n* Starting simple case\n")
3  const sproc = helloWorldStoredProc
4  await createSproc(sproc)
5  const res = await runSproc(sproc.id, "")
6  console.log(res)
7}
8
9runHello()

Create or Update??

Once this works, it should fail the second time, because you cannot create a sproc that already exists. You must update it.

Lets modify createSproc to be a createOrModify function. Since we will more likey be updating than creating, let's optimize it that way.

 1async function createOrUpdateSproc(sproc) {
 2  try {
 3    await container.scripts.storedProcedure(sproc.id).replace(sproc)
 4  } catch (e) {
 5    if (e.code === 404) { //same error code for 'not found'
 6      console.log('REPLACE failed, try to add ', sproc.id)
 7      await container.scripts.storedProcedures.create(sproc)
 8    } else {
 9      throw(e) // don't swallow unexpected errors!
10    }
11  }
12}

update your code and now you should have a simple little test harness to quickly test how to write basic stored procedures.

For a general introduction to interacting with CosmosDB using nodejs see: https://docs.microsoft.com/en-us/learn/modules/build-node-cosmos-app-vscode/

Completed Script

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
const assert = require('assert')
const { CosmosClient } = require('@azure/cosmos')

const endpoint = 'https://********.documents.azure.com:443/'
const key = "process.env.cosmos_key"
const client = new CosmosClient({ endpoint, key });

const container = client.database('cart').container('coupons')

const helloWorldStoredProc = {
    id: "helloWorld2",
    body: function () {
        var context = getContext();
        var response = context.getResponse();
        console.log("logging message from the sproc")
        response.setBody("Hello, World");
    }
}

async function createOrUpdateSproc(sproc) {
  try {
    await container.scripts.storedProcedure(sproc.id).replace(sproc)
  } catch (e) {
    if (e.code === 404) {
      console.log('REPLACE failed, try to add ', sproc.id)
      await container.scripts.storedProcedures.create(sproc)
    } else {
      throw(e)
    }
  }
}

async function runSproc(sprocname, partition_id, args) {
    const result = await container
                            .scripts
                            .storedProcedure(sprocname)
                            .execute(partition_id, args, { enableScriptLogging: true })
    console.log("Sproc Log: ", decodeURIComponent(result.headers['x-ms-documentdb-script-log-results']))
    console.log("Sproc RU cost: ", result.headers['x-ms-request-charge'])
    return result.resource
}

async function runHello() {
  console.log("\n*****\n* Starting simple case\n")
  const sproc = helloWorldStoredProc
  await createOrUpdateSproc(sproc)
  const res = await runSproc(sproc.id, "")
  assert.strictEqual(res, "Hello, World")
  console.log("\n*Assertions passed")
  return res
}

runHello().then(console.log).catch(console.error)