node red module to connect to azure cosmos db
npm install node-red-contrib-cosmos-db$HOME/.node-red
shell
npm install node-red-contrib-cosmos-db
`
---
Usage
There are a total of three nodes implemented.
Config Node
Before using any nodes you have to configure your Database. For that you have
the option to create config nodes from within the normal nodes.
You only need 2 parameters from your Cosmos Database:
1. Your endpoint, usally something like this:
https://[instance-name].documents.azure.com:443/
2. The primary key, which is found under the Keys Tab in the Azure console.
SQL Node
The SQL Node is used to perform read operations against a specified container.
The query can be set by:
1. The msg.topic parameter
2. The included text editor within the node.
The second option will always override any topic that was specified beforehand.
msg.topic example:
`sql
SELECT *
FROM c
`
The reference for the SQL-Syntax can be found in the [Microsoft SQL
Docs][microsoft-cosmos-sql-docs].
$3
You also have the option to use prepared statements. For that the query has to
be defined in the included editor within the node. This is to prevent
SQL-Injection. Internally [sqlstring][sqlstring-npmjs] is used to properly escape values.
The node uses ?variable in the statement, so the query should look something like this:
`sql
SELECT *
FROM c
WHERE c.id = ?id
AND c.city = ?city
`
To fill the variables you use the msg.params object:
`json
{
"id": 1234,
"city": "Berlin"
}
`
Note that this method can only be used to replace values, not table names and value names. Such a replacement cannot be safely achieved without the risk of SQL-Injection. If you need such a feature, you can always generate the required query in a function node and write it to the msg.topic object.
$3
Within the node you have the option to expose the container object, which is loosley described in the [Node.js documentation][microsoft-cosmos-node-js-docs]. This allows you to use operations like replace or delete. If the option is checked, the API will be exposed on the msg.cosmos property, and can be used like this:
`js
//Get the desired resource
let data = msg.payload.resources[0];
//Change whatever you like
data.city = "Berlin";
//Write the updated data to Cosmos
msg.cosmos
.item(data.id)
.replace(data).then(o => {
//Then part is used to wait for the object to complete, you do not have to do this.
msg.replacedItem = o;
node.send(msg);
});
`
$3
When updating or creating new items it is useful to keep the original payload. I
used to achieve this with a function node in front of the query node. The second
checkbox within the node allows the user to skip this step. When enabled the
original payload gets written to msg.initialPayload.
Authentication
In Addition to SQL queries, the other nodes allow you to acquire resource tokens
from the database, aswell as the creation of permissions on the database.
$3
The node takes a permission object as input. In addition to that, some
parameters can be altered in the node config. Although they will not override any variable from the input object msg.payload.permission. The object is defined as follows:
`ts
export declare interface Permission {
/* The id of the permission /
id: string;
/* The mode of the permission /
permissionMode: "none" | "read" | "all";
/* The id of the container that the permission will be applied to. /
container: string;
/* Partitions Keys for the Permission, optional /
resourcePartitionKey?: string | any[];
}
`
If a permission with the specified id exists, the resulting permission object,
including the resource token will be returned on msg.payload.permission,
overriding the input.
If no permission is found, the msg.payload.permission will be unaltered. In
addition the property msg.permissionError can be set to the following options:
- noUser: there is no user with the user id specified within the node config
- noPermission`: while the user exists, there is no permission with the