How To Build Server Side Objects Like UDF, Stored Procedure, & Trigger In Cosmos DB

Introduction

Welcome back to the third article in an overview of CosmosDB – start from scratch article series. In the previous article, we discussed the document structure of the Cosmos DB Database. Also, we discussed the partitioning concept in Cosmos DB and also can perform different types of Query on Cosmos DB. Now, in this article, we will discuss the server-side programming concept in Cosmos DB. In this article, we will discuss how to define a user-defined function, stored procedure, and trigger in Cosmos DB. If you want to read the previous articles of this series, then please click on the below links:

This article introduces the concept that is fundamental to understanding server-side programming in Cosmos DB and describes the server-side programming API. In this article, we also cover how to work with server-side, user-defined functions. So, at the end of this article, the reader will be able to get an idea of the below points:

Explain why we might user server-side operations in Cosmos DB.
Overview of Stored Procedure, user-defined functions, and triggers in Cosmos DB.
About JavaScript Query API
Create a User-defined function
Create a Stored Procedure
About different types of Trigger in Cosmos DB
Create a Trigger

Overview of user-defined functions, stored procedures, and triggers

We can perform three different types of Server-side operations in Cosmos DB i.e. user-defined functions, stored procedures, and triggers. As we are familiar with user-defined functions, stored procedures, and triggers in any RDBMS systems like Microsoft SQL server – we will find that their purposes in Cosmos DB are nearly similar. In Cosmos DB, we can define server-side operations as functions in JavaScript (ECMA-262). Unlike an RDBMS, you don’t create and modify server-side operators using data-definition language (DDL) operations in the SQL language; instead, you create them by calls to the Cosmos DB SQL API. All server-side operations are implemented as members of a collection; you cannot define a user-defined function, stored procedure, or trigger that is shared across multiple collections.

User-Defined Functions

User-defined functions provide “compute-only” processing of information within a single document in Cosmos DB without any side-effects.
It might take zero or more parameters and always return a JavaScript object.
We can use a user-defined function in a SQL query just like other built-in SQL functions.
Every user-defined function must reside within the UDF namespace.

Stored Procedures

We can use stored procedures to manipulate one or more documents within a collection in Cosmos DB.
Within a stored procedure, we can perform create, modify, delete, and query data that covers one or multiple documents in a single collection.
Stored procedures accept zero or more parameters and optionally return a result set to the calling client in the body of an HTTP response message.
We can execute a stored procedure with a SQL API method such as ExecuteStoredProcedureAsync.

Triggers

We can use triggers to validate and/or modify data when a document is added, modified, or deleted within a collection.
We can configure a trigger to run before the DML operation start – called a Pre-Trigger – or after the DML operation has completed – called a Post-Trigger.
The trigger does not accept any parameters or does not return any result set.
Just like RDBMS, triggers are not automatically executed in the Cosmos DB. We need to specify the trigger(s) to run and whether they are executed as a pre-trigger or post-trigger, in the options passed to a SQL API method such as CreateDocumentAsync.

Concept of Server-side Operations in Cosmos DB

As per the previous section, Cosmos DB server-side operations are implemented as JavaScript functions which are executed by a JavaScript engine running inside the database. With the help of the JavaScript engine, developers working with Cosmos DB can implement complex operation related to the data include other benefits like:

Procedural Logic
Due to the JavaScript languages, Cosmos DB server-side operations provide us a powerful and familiar programming environment to implement procedural business logic.
Encapsulation
With the help of stored procedures, we can implement our business logic once and store it in one place. In this way, we do not need to implement the same business login in several client applications, because we already implement that on the server-side. The client applications always interact with the database through the stored procedures and do not require any direct access to the document in a collection.
Atomic transactions
All the database operations carried out in the body of a single stored procedure or trigger are atomic, meaning that all the operations are treated as a single logical unit of work, also referred to as a transaction. You use these transactions to protect your data from inconsistencies caused by batches that fail partway through processing; all the operations in a stored procedure or trigger must succeed, or none of them succeed.
Performance
Since Cosmos DB uses JSON as its basic data storage format, JavaScript manipulates Cosmos DB data directly without any type-mapping—because JSON is derived from a subset of the JavaScript type system. This builds in several performance optimizations, such as making documents available on-demand, to running code through the lazy materialization of documents to the buffer pool. Also, Cosmos DB makes compilation cost savings by precompiling server-side operator code to byte code once, when the object is created—so avoiding the cost of compilation for each execution of the operator.

All server-side operators run in an environment that’s isolated and sandboxed from other operations - code running in one stored procedure, user-defined function or trigger cannot inadvertently affect other databases, collections, or server-side operations. When a piece of server-side logic finishes execution, its complete contextual environment is removed from its assigned memory space and can’t be read by (or otherwise affect) code that is subsequently executed in the same memory address space.

How are transactions performed in server-side operations?

As you learned earlier in this lesson, the code inside Cosmos DB stored procedures and triggers is treated as a single logical transaction. Cosmos DB transactions are guaranteed to meet the ACID properties:

Atomicity
The transaction will succeed only if all the statements inside the transaction succeed. If any statement raises an exception, the transaction will fail and the changes carried out inside the transaction will be discarded.
Consistency
The transaction will bring the database from one valid state to another.
Isolation
The transaction will not affect the state of any other concurrently running transaction.
Durability
The changes carried out by the transaction will be persisted if the transaction is committed.

Server-side operations are the only way to implement multistatement transactions in Cosmos DB—there are no explicit BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK TRANSACTION commands available to the SQL API.

Concept of JavaScript Query API

To develop any server-side objects like stored procedures or triggers, we need to use the SQL dialect provided by the SQL API. Alternatively, we need to write queries using the server-side JavaScript query API. We can use the JavaScript query API to compose queries using a fluent JavaScript interface. For that, there is no need for the knowledge of SQL. We need to build queries programmatically, bypassing predicates to function calls, chaining functions with a similar syntax to JavaScript’s Array built-in object. These queries are efficiently parsed by the server-side JavaScript engine.

Stored procedures and triggers interact with the database through a well-defined object model that provides access to the current collection, context, request, and response objects; most important are the getContext and getCollection functions that—as their names suggest—return the context and collection of the current request, through which you access the JavaScript query API. For convenience, you can use “__” (double underscore) as an alias for getContext().getCollection().

getContext().getCollection() Alias

In the context of a server-side stored procedure or trigger, the following snippets are equivalent:

getContext().getCollection().map(…)
__.map(…)

Functions supported by the JavaScript query API include: chain() ... .value([callback] [, options])

This begins a chained call. The end of the chained call must be indicated with the value().

filter(predicateFunction [, options] [, callback])

Applies a predicate function (that returns true or false) to filter input documents into the output set; similar to the WHERE clause in a SQL statement.

map(transformationFunction [, options] [, callback])

Uses the supplied transformation function as a projection that maps each input item to a JavaScript object or value; similar to the SELECT clause in a SQL statement.

sortBy([predicate] [, options] [, callback])
sortByDescending([predicate] [, options] [, callback])

Sort the input documents into a new set in ascending order, based on the supplied predicate; similar to the ORDER BY clause in a SQL statement.

The following example shows an unfiltered query of all the documents in a collection called products that are expressed first as SQL, and then as JavaScript:

// SQL
SELECT * FROM products
// JavaScript
getContext().getCollection.map(function(product) {
return product;
});

The following example shows an unfiltered query of all the documents in a collection called products that returns only the id, weight, and name properties, aliasing weight as product_weight, that’s expressed first as SQL, and then as JavaScript,

// SQL
SELECT products.id, products.weight AS product_weight, products.name FROM products
// JavaScript
getContext().getCollection.map(function(product) {
return {
id: product.id,
product_weight: product.weight,
name: product.name
};
});

The following example shows a filtered query of the documents in a collection called products where id is P001 that’s expressed first as SQL, and then as JavaScript,

// SQL
SELECT * FROM products WHERE products.id = "P001"
// JavaScript
getContext().getCollection.filter(function(product) {
return product.id ==="P001";
});

The following example combines the previous two examples and shows a filtered query of the documents in a collection called products that returns only the id, weight, and name properties, aliasing weight as product_weight where id is P001 that’s expressed first as SQL, and then as JavaScript,

// SQL
SELECT products.id, products.weight AS product_weight, products.name FROM products
WHERE products.id = "P001"
// JavaScript
getContext().getCollection.chain()
.filter(function(product) {
return product.id ==="P001";
})
.map(function(product) {
return {
id: product.id,
product_weight: product.weight,
name: product.name
};
})
.value();

Create a User-Defined Function and Execute

We can create a server-side user-defined function in several ways, including by using the REST API or the Azure SDK, or through the Azure portal with Cosmos DB Data Explorer. To create a user-defined function in an existing Cosmos DB SQL API instance, in an existing collection with Data Explorer, follow these steps,

Log in to the Azure portal.
Click All resources, then in the All resources blade, click the name of your Cosmos DB account.
In the Cosmos DB account blade, click Data Explorer.
In the Data Explorer blade, in the SQL API pane, expand the entry for the database that contains your target collection, then click the name of the collection.
Click the down arrow next to New Stored Procedure, then click New UDF.
In the New UDF 1 pane, in the User Defined Function Id box, type an identifier for your function. In the User-Defined Function Body box, replace function userDefinedFunction(){} with the definition for your function. When you have finished, click Save.

function getPriceInINR(price){
if(typeof price != 'number')
throw new Error("Parameter must be numeric");
if (price < 0)
throw new Error("Parameter must be non-negative");
var rateINR = 70;
var finalPrice = price*rateINR;
return finalPrice;
}

Now, to execute the above user-defined function, run the below query:

SELECT p.name, p.brand, p.price, UDF. getPriceInINR (p.price) as princeInINR FROM products p

Once you execute the above syntax, the output will be as below:

Create a Stored Procedure and Execute

A stored procedure is defined as a JavaScript function, and is typically made up from the following sections:

Input
A stored procedure can accept zero or more inputs in the form of parameters passed to the JavaScript function; these might be objects of any type, including documents or arrays of documents. It’s not necessary to pass Cosmos DB context information to the stored procedure as a parameter; context is accessible through the getContext and getCollection JavaScript query API functions.
Body
Code in the body of the stored procedure carries out one or more actions; the details of these actions will depend on the purpose of the stored procedure. To do useful work, a stored procedure will typically read or write one or more documents using the SQL API or JavaScript query API.
Output
To return output to the client from a stored procedure, you use the getContext().getResponse().setBody() function to write the response. A stored procedure is not required to return a response.

The steps to create a stored procedure using Azure Portal are similar to the creation of a user-defined function. The below example shows a very simple stored procedure that accepts a parameter and returns a string response:

function helloWorld(name) {
var context = getContext();
var response = context.getResponse();
response.setBody(`Hello, ${name}! Greetings from the Cosmos DB SQL API`);
}

Invoking a Stored procedures

We execute a stored procedure in several ways, including with the REST API, with the Azure SDK, or through the Azure portal with Cosmos DB Data Explorer. To invoke a stored procedure with Data Explorer, follow these steps:

Log in to the Azure portal.
Click All resources, then in the All resources blade, click the name of your Cosmos DB account.
In the Cosmos DB account blade, click Data Explorer.
In the Data Explorer blade, in the SQL API pane, expand the entry for the database that contains your target collection, then expand the entry for the collection. Expand Stored Procedures, then click the name of the stored procedure you want to execute.
In the <stored procedure name> pane, click Execute.
In the Input parameters blade, if the stored procedure takes no parameters, click Execute. If the stored procedure takes parameters, use the Add New Param button to add a box for each parameter. Type a value for each parameter into the boxes you have created, then click Execute.
In the Result area of the <stored procedure name> pane, review the response.

Types of Trigger in Cosmos DB

When Cosmos DB runs in SQL API mode (or in MongoDB API mode), you implement data integrity checks with triggers. Cosmos DB supports two types of trigger:

Pre-triggers
Pre-triggers executes before a data change (insert, update, or delete), and have access to the type of change, the current state of the database, and the revised data. With pre-triggers, you implement RI and data quality checks to guarantee that your database contains valid data.
Post-triggers
Post-triggers executes after a data change (insert, update, or delete), and have access to the type of change, the current state of the database, and the response message returned to the client. With post-triggers, you implement actions to preserve the historical integrity and maintain a log or audit trail.

We should think of Cosmos DB triggers as a specialized type of server-side stored procedure; like a stored procedure, actions in a trigger are executed in an atomic transaction with ACID guarantees.

The transaction associated with a pre-trigger extends across the trigger and the associated data modification; if either the pre-trigger or the data modification fails, the actions of the pre-trigger and the data modification are rolled back.
The transaction associated with a post-trigger extends across the associated data modification and the post-trigger; if either the data modification or the post-trigger fails, the data modification and the actions of the post-trigger are rolled back.

The transaction scope (and associated guarantees) does not extend across multiple triggers; if an action in the database fires both a pre-trigger and a post-trigger, each trigger runs in its transaction—a failure in the post-trigger will not roll back the actions of the pre-trigger.

Create a Trigger and Execute

The Cosmos DB SQL API supports a dialect of SQL for querying documents using SELECT statements that will be familiar if you have written SELECT statements in a relational database using an ANSI SQL compliant database engine. The SQL API returns results in the form of JSON documents. All queries are executed in the context of a single collection. A SQL API SELECT Clause is comprised of the following clauses as mentioned in the picture.

A trigger—whether it’s a pre-trigger or a post-trigger - has a structure like the body of a serverside stored procedure. Unlike a stored procedure, a trigger can’t take input parameters and does not return a response body—although a post-trigger might modify the response body. Details of the operation that caused the trigger to be fired are available through the server-side programming API, through getContext().getRequest(),

The getOperationType function of the request object indicates whether the triggering request is an insert, replace, or delete operation.
The getBody function of the request object returns the document being processed.
In a pre-trigger, you use the setBody function of the request object to modify the document; the document you pass to setBody will be persisted in place of the original document.

In a post-trigger, you use the getContext().getResponse() function to modify the response:

The getBody function of the response object returns the response that will be returned to the client.
You use the setBody function of the response object to replace the response body with the value you pass to setBody; the original response will be discarded.
The getValue function of the response object returns the value of a header that will be returned to the client.
You use the setValue function of the response object to replace the value of a header with a value that you pass to setValue. You cannot use setValue to create new headers.

To cause the trigger to return a failure (and the associated transaction to roll back), you raise an error that throws a new Error.

function validateprice(){
var context = getContext();
var request = context.getRequest();
var documentBeingCreated = request.getBody();
// If the operation being performed is not a create or replace, then ignore this trigger
if (request.getOperationType() == "Delete") {
return;
}
var price = documentBeingCreated.price;
if (price<=0)
throw new Error("value in the price field is required");
}

Invoking a Trigger

Unlike user-defined functions and stored procedures, you do not execute triggers directly; triggers are executed as a side effect of a modification to a document (such as create, update, or delete). In an RDBMS, a trigger is associated with a table and one or more data modification actions (create, update, or delete); the trigger is fired automatically every time the table undergoes the modification associated with the trigger. In Cosmos DB, server-side triggers are not fired automatically; you must associate a pre-trigger and/or a post-trigger with a request using the request options object.

Because we can’t directly execute a trigger, we can’t directly test its behavior; we only test it in the context of a data modification request that’s associated with the trigger. Depending on the purpose of the trigger, you might be able to determine whether a trigger is working correctly by examining the response object from a request associated with the trigger, or by examining the state of the collection after a trigger has executed.

The Azure portal’s Data Explorer does not support specifying pre-triggers or post-triggers when working with documents. You use the open-source DocumentDB Studio project—a Windows desktop application like Data Explorer, for working with CosmosDB instances—to execute requests with an associated pre-trigger and/or post-trigger. Alternatively, you write an application to test the behavior of triggers with calls to Cosmos DB over the REST API or Azure SDK. For downloading the DocumentDB Studio project, click on the link https://github.com/mingaliu/DocumentDBStudio/releases

Conclusion

In this article, we discussed the concept of server-side objects along with server-side operations in the Cosmos DB Database. Also, we discussed UDF, stored procedures, and triggers on Cosmos DB. Any suggestions, feedback, or queries related to this article are most welcome.