CRUD Stored Procedures in Azure Cosmos DB

Introduction

In my previous articles, we have learned how to create Azure Cosmos DB as well as how to implement the Azure Cosmos DB CRUD operation programmatically with ASP.NET Core. If you want to read my previous articles, then read them using the following links:

•  CRUD Operations In Azure Cosmos DB Using ASP.NET Core Web API
•  ASP.NET Core Blazor Server CRUD Operations with Azure Cosmos DB and C#

The CRUD operation we have implemented in the above articles is with plain, inline queries, which is not the recommended way for several reasons. In this article, we’ll focus on creating stored procedures in Azure Cosmos DB to perform insert, read, delete, and update operations. Let’s begin with the basics so everyone can understand, including beginners.

What is a Cosmos DB Stored Procedure?

A Stored Procedure is a set of pre-compiled instructions in Azure Cosmos DB, written in JavaScript, allowing us to perform operations like reading, adding, updating, and deleting data.

Azure Cosmos DB can understand and work with many conditions and queries. For instance, if you are using the SQL API, you can use SQL queries to ask questions about your data. So, you could ask, "Can you show me all the employees who live in Latur?

However, there’s a difference when it comes to stored procedures. Unlike SQL databases, where you might write stored procedures using SQL, in Azure Cosmos DB, you write stored procedures in JavaScript.

Why use stored procedures?

The stored procedure has various advantages over the inline queries for the database operation. The following are the key advantages of using the stored procedure:

• Efficiency: Stored procedure can perform multiple operations in a single call, which reduces the amount of work and time needed to execute certain database operations in a single call instead of sending multiple requests to the database server.
• Atomicity: The stored procedure ensures tasks are done correctly and can roll back changes if something goes wrong while processing the database operation.
• Speed: The stored procedure executes quickly as it runs close to where the data is stored and pre-compiled.
• Security: Stored procedures help keep the data safe by limiting direct access to the data in the database, reducing the risk of unwanted access.
• Maintainability: Encapsulating logic within stored procedures makes the codebase more organized, maintainable, and reusable, allowing for hassle-free updates and modifications.
• Consistency: By providing a consistent interface to the database and hiding its complexities, stored procedures allow for seamless interactions even when there are changes in the underlying database schema, ensuring uninterrupted and smooth application functionality.

Now let's start implementing the stored procedures for the Azure Cosmos DB SQL API step by step.

Step 1. Set up Prerequisites

Before we write our stored procedure, we need to create a Cosmos DB account, a database, and a container to hold our employee collection. These steps have already been covered in my previous article. If you have missed them, then please refer to them using the following link:

•  CRUD Operations In Azure Cosmos DB Using ASP.NET Core Web API
•  ASP.NET Core Blazor Server CRUD Operations with Azure Cosmos DB and C#

As explained in our previous articles, we will use the Azure Cosmos DB emulator for ease of understanding. The following is our Azure Cosmos DB container structure.

 

In our EmployeeManagementDB database, we have an Employee collection with the following structure.

{
    "id": "1",
    "Name": "Sarika S",
    "Country": "India",
    "City": "Phaltan",
    "Department": "IT",
    "Designation": "Senior DataBase Architect"
}

Now that we have all the prerequisites, including an understanding of our Azure Cosmos DB database and collection structure to perform the create, read, update, and delete operation using the stored procedure.

Step 2. Open the Cosmos DB Emulator or Explorer

Open the Cosmos DB editor, depending on whether it is the local Cosmos DB emulator or the Azure Cosmos DB Explorer, to create the stored procedure. Follow the following steps to create the stored procedure.

• Navigate to the SQL API section under Explorer.
• Expand the Employee section.
• Access the Stored Procedures section.
• Click on New Stored Procedure to create a new procedure.

Refer to the following image for the visual representation:

Step 3. Create the Stored Procedure to Get All Employees

After clicking on the create a new stored procedure as shown in the preceding step two image, the following editor opens, and then Follow the following steps:

• Remove the existing default code and replace it with the code as given in the code snippet.
• Provide the unique ID for the stored procedure for unique identification.
• Click on Save, and then the stored procedure will be shown under the stored procedure blend.

Refer to the following image for the visual representation of creating and saving the stored procedure in Azure Cosmos DB.

 

Cosmos DB Stored Procedure Code for Reading All Employees

getAllEmployees.js

function getAllEmployees() {
    var context = getContext();
    var response = context.getResponse();
    var collection = context.getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM Employees',
        function (err, feed, options) {
            if (err) {
                throw new Error("Error while querying for documents: " + err.message);
            }
            response.setBody(feed);
        }
    );

    if (!isAccepted) {
        throw new Error('The query was not accepted by the server.');
    }
}

Code Explanation:

The preceding stored procedure is created to fetch all the employees from our Cosmos DB collection. Let’s break down what each part of the stored procedure does:

• Initialization of Context: Every stored procedure runs within a specific context. The getContext() function retrieves the current execution context of the stored procedure, allowing us to access its features.
• Preparing to Respond: We plan to return a result (all the employee records). To do so, we get a reference to the response object using the context. getResponse(), which will let us customize what we send back at the end of the procedure.
• Getting Collection Reference: Our stored procedure interacts with our database collection. The context. getCollection() gives us a handle on the collection our stored procedure resides in
• Executing the query: We are initiating a query against our collection using the queryDocuments function is asynchronous and does three main things:
  • It uses collection.getSelfLink() to target the current collection.
  • It runs the SQL query 'SELECT * FROM Employees' to fetch all employee records.
  • It waits for the query's completion and then runs a callback function to handle the results.
• Handling Query Results: The following callback function is triggered after the query's completion. If an error (err) occurs during the query, it gets reported. If the query is successful, the fetched employee records (feed) are set as the response body.

  function (err, feed, options) {
      if (err) {
          throw new Error("Error while querying for documents: " + err.message);
      }
      response.setBody(feed);
  }
  

• Checking Server's Response: In the following function, we check if the server has accepted our query request. If not, we throw an error indicating the rejection.

  if (!isAccepted) {
      throw new Error('The query was not accepted by the server.');
  }
  

Executing the Stored Procedure

Navigate to Collection

• In the Data Explorer, expand the database where your stored procedure resides.
• Then, select the collection where the stored procedure is stored.

Navigate to Stored Procedure

• In the Data Explorer tree on the left, expand the database and then the collection where your stored procedure is located.
• Under the collection, click on the "Stored Procedures" node. You should now see a list of stored procedures associated with that collection.

Execute the stored Procedure

• Find the stored procedure you wish to execute from the list.
• Hover over the stored procedure name and click on the ellipsis (three dots) icon that appears next to it.
• Click on the "Execute" option.

If the stored procedure requires input parameters

• A pane will appear on the right side of the screen, prompting you for the required parameters.
• Fill in the necessary parameters.
• Click the "Execute" button.

Our current getAllEmployees stored procedure only requires the partition key as an input parameter. Refer to the following image for a visual representation of the above-explained steps.

Review the Results

• Once executed, the results (or any error messages) will be displayed in the output pane at the bottom of the Data Explorer.
• You can review the returned data or diagnose any issues based on the displayed output.

Complete the Execution

After reviewing the results, you can close the Data Explorer or proceed with other tasks in the Azure Portal or the local emulator. Remember that executing stored procedures may have associated RU (Request Unit) costs, so always be aware of the impact on your Cosmos DB account's throughput.

Step 4. Create the Stored Procedure to Add Employees Employee Details

Now, let's create the stored procedure to add or insert the employee details in Cosmos DB.

• Refer to Steps 2 and 3 to open the stored procedure blend and Create the stored procedure.
• Click on the Create Stored procedure.
• Remove the existing default code.
• Use the code as given in the editor.
• Provide the unique ID as createEmployee or for the stored procedure for unique identification.
• Click on Save, and then the stored procedure will be shown under the stored procedure blend.

following image for a visual representation of creating and saving the stored procedure for inserting employee details in Azure Cosmos DB.

Stored Procedure Code for Inserting the Employee Details

createEmployee.js

function createEmployee(id, name, country, city, department, designation) {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();

    // Constructing the employee data from individual parameters
    var employeeData = {
        "id": id,
        "Name": name,
        "Country": country,
        "City": city,
        "Department": department,
        "Designation": designation
    };

    // Insert the constructed document into the Employees container
    var accepted = container.createDocument(container.getSelfLink(),
        employeeData,
        function (err, doc) {
            if (err) throw new Error("Error while creating the document: " + err.message);
            response.setBody(doc.id);
        });

    // If the createDocument is not queued up, return an error message
    if (!accepted) throw new Error("The query was not accepted by the server.");
}

//created by vithal Wadje

Explanation

The purpose of the preceding stored procedure is to create an employee document in Azure Cosmos DB by accepting individual parameters for each field.

• Function Definition: function createEmployee(id, name, country, city, department, designation) { ... }. The function is named createEmployee and takes in six parameters: id, name, country, city, department, and designation.
• Initialise Context and Variables: var context = getContext();: This gets the current operational context within which the stored procedure is running. var container = context.getCollection();: Retrieves a reference to the current collection (in this case, our Employees container). var response = context.getResponse();: This provides a handle to the response that will be returned from this stored procedure.
• Constructing Employee Data: We then construct a JavaScript object named employeeData using the parameters we've received to form the document we intend to insert into our database.
• Insert a document into Database:  container.createDocument(...) responsible for the following operation
  • It tries to create a new document in our Employees container.
  • container.getSelfLink() retrieves the current container's link, which essentially points to where we want to add our document.
  • employeeData is the data we wish to insert.
  • An inline function is defined next to handle success (where we send the document's ID in response) or errors (throw an error message).
• Error Handling: The createDocument method attempts to queue up our document creation. If, for some reason, it can't be queued, it returns false. Our stored procedure checks for this with the if (!accepted) ... line and throws an error if it wasn't queued.

Execute Stored Procedure

Find the stored procedure that we have recently created for inserting employee details, then hover over the stored procedure name and click on the ellipsis (three dots) icon that appears next to it. It will open the emulator to execute the stored procedure as follows:

To execute this stored procedure in the Azure Cosmos DB emulator, input the following parameters as required for our stored procedure:

Partition key value:

• Key: String (our partition key is defined, which is Department).
• Value: The value of the partition key, whether that is IT, Management, or any other, is under our partition key department.

Enter the input parameters:

• Click Add New Param for each parameter.
• Key: String
• Params for each field:
  1. "1" (or the desired ID unique for each partition key value)
  2. "Mahesh Chand"
  3. "United States of America"
  4. "New York"
  5. "Leadership"
  6. "CEO"

After providing the parameters, you can execute the stored procedure, and it should insert the employee details into the Employees container.

Note. Please note that we should have a unique ID under each partition key value. That is, if HR has ID 1, you can't insert another item under the partition key value with ID 1, but if the partition key is different (IT), then you can use ID 1. This means you should have a unique ID under each partition key value, but if the partition key value is different, then the ID can have the same value.

The following image shows the visual representation of the preceding steps. Please make sure the parameter sequence and the values assigned to it are appropriate.

Now, after clicking on the execute button, the data will be inserted into the Azure Cosmos DB container, which looks as follows:

The preceding JSON document represents an item in an Azure Cosmos DB container, which we have inserted with the help of our created stored procedure. "id": "1" is a user-defined unique identifier for the document, a crucial field for differentiating documents within a container and partition key. Personal and professional details like the individual's name, country, city, department, and designation are represented by custom properties, such as "Name": "Mahesh Chand", "Country": "United States of America", "City": "New York", "Department": "Leadership", and "Designation": "CEO".

Azure Cosmos DB also integrates system-generated properties essential for database management. The "_rid" is a unique identifier for the document created by the system. The "_self" value is a unique URI, effectively the 'address' of the document within the Cosmos DB database. Another system property, the "_etag", serves as an entity tag designed for managing optimistic concurrency during updates to ensure data consistency. The "_attachments" field provides a relative URI path, pointing to any associated media or attachments linked to this specific document. Lastly, the "_ts" is a timestamp, also generated by the system, denoting the last time the document was modified, expressed as seconds since the Unix epoch.

Step 5. Create the Stored Procedure to Update or Replace Existing Records

We have learned so far about getting and inserting data into the Azure Cosmos DB by creating two different stored procedures. Now let's create the stored procedure for updating and modifying the existing items in the Azure Cosmos DB container.

Now, let's create the stored procedure step by step.

• Refer to Steps 2 and 3 to open the stored procedure blend and Create the stored procedure.
• Click on the Create Stored procedure.
• Remove the existing default code and Use the code as given in the following code snippet.
• Provide the unique ID as updateEmployee or as some other name as you wish for the stored procedure for unique identification.
• Click on Save, and then the stored procedure will be shown under the stored procedure blend.

The following is the stored procedure code for modifying or updating the existing Cosmos DB container items.

updateEmployee.js

function updateEmployee(department, id, updatedName, updatedCountry, updatedCity, updatedDesignation) {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();

    if (!id || !department) {
        throw new Error("Please provide both the employee's ID and Department (Partition Key) you wish to update.");
    }

    var filterQuery = {
        'query': 'SELECT * FROM Employees e WHERE e.id = @id AND e.Department = @department',
        'parameters': [
            { 'name': '@id', 'value': id },
            { 'name': '@department', 'value': department }
        ]
    };

    container.queryDocuments(container.getSelfLink(), filterQuery, {}, function(err, items) {
        if (err) {
            throw new Error("Error querying the document: " + err.message);
        } else if (items.length === 0) {
            response.setBody('No employee found with the given ID and Department.');
        } else {
            var employeeDocument = items[0];
            employeeDocument.Name = updatedName || employeeDocument.Name;
            employeeDocument.Country = updatedCountry || employeeDocument.Country;
            employeeDocument.City = updatedCity || employeeDocument.City;
            employeeDocument.Designation = updatedDesignation || employeeDocument.Designation;

            container.replaceDocument(employeeDocument._self, employeeDocument, function(err, result) {
                if (err) {
                    throw new Error("Error updating the document: " + err.message);
                } else {
                    response.setBody('Successfully updated the employee details for ID: ' + id + ' in Department: ' + department);
                }
            });
        }
    });
}

Explanation

The preceding update An employee stored procedure is specifically designed to update the details of an employee in Azure Cosmos DB, given certain conditions. The following is an explanation of the preceding stored procedure.

• Function Parameters: The stored procedure takes in six parameters. These include the department (which is the partition key) and the ID (a unique identifier for the employee). Additionally, four other parameters capture the updated values for the employee's name, country, city, and designation.
• Context and Collection Initialization: The function begins by initializing the context with getContext() and then acquires the reference to the Cosmos DB container using context. getCollection(). The response object, which will be used later to send back a message, is also initialized.
• Input Validation: There's a check to ensure that both ID and department are provided. If they aren't, an error is thrown.
• Query Creation: A filter query is constructed to retrieve the employee's document based on the provided ID and department. This query ensures that the system fetches the correct employee record to be updated.
• Document Querying: The function uses a container.queryDocuments to fetch the document that matches the conditions defined in the filter query.
• No Match Scenario: If no matching employee is found based on the provided ID and department, a response message is sent indicating the same.
• Updating Document: If a matching employee is found, the system updates the respective fields using the provided parameters. If a certain update parameter isn't given, the existing value in the document remains unchanged.
• Replacing Document in Cosmos DB: Once the document's fields are updated, the container.replaceDocument method is invoked to replace the old document with the updated one in Cosmos DB.
• Success & Error Handling: If the document replacement succeeds, a success message is sent. In case of any error during querying or updating, appropriate error messages are thrown, making it easier to debug or inform the user.

Execute the Stored procedure

Find the stored procedure that we have recently created for updating the employee details, then hover over the stored procedure name and click on the ellipsis (three dots) icon that appears next to it. It will open the emulator to execute the stored procedure, as we have seen in our previous stored procedures. To execute this stored procedure in the Azure Cosmos DB to update the employee details, the following parameters are accepted by our created stored procedure to update the item. The ID and department are mandatory parameters, and others are optional.

• department (Required): This parameter represents the partition key for the employee document you want to update, and this requires identifying the item, but you can not change the value of the existing partition key.
• id (Required): This is the unique identifier for the employee. Along with the department, it will help in pinpointing the exact document that needs to be updated.
• updatedName (Optional): If you want to update the employee's name, provide this parameter. If not provided, the existing name remains unchanged.
• updatedCountry (Optional): To update the country attribute, supply this parameter. If omitted, the country remains the same.
• updatedCity (Optional): Similarly, if you want to change the city, provide this parameter. If not, the existing city remains.
• updatedDesignation (Optional): This is for updating the employee's designation. If you skip this, the designation stays unchanged.

Consider the scenario where we want to update the fields Name, County, City, and designation to make some corrections that may be wrongly inserted for the id 4. The following screenshot shows the item to be updated or replaced with the latest values.

Provide all the input parameters that you want to update in the stored procedure emulator execution window, as we did in the previous stored procedure execution steps. The following image shows the visual representation of the preceding steps. Please make sure the parameter sequence and the values assigned to it are appropriate.

Now, after providing the input parameter values as shown in the preceding screenshot, click on the execute button, and then the item with ID 4 will get replaced or updated with the latest values as shown in the following screenshot.

Step 6. Create the Stored Procedure to Delete items

Now we have the stored procedure for inserting, reading, and updating the Azure Cosmos DB container items. Now let's create the stored procedure for deleting the items from the Azure Cosmos DB container. Let's create the stored procedure step by step.

• Refer to Steps 2 and 3 to open the stored procedure blend and create the stored procedure.
• Click on the Create Stored procedure.
• Remove the existing default code and use the code as given in the following code snippet.
• Provide the unique ID as deleteEmployee or as some other name as you wish for the stored procedure for unique identification.
• Click on Save, and then the stored procedure will be shown under the stored procedure blend.

The following is the stored procedure code for deleting the Cosmos DB container items.

deleteEmployee.js

function deleteEmployee(department, id) {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();

    if (!id || !department) {
        throw new Error("Please provide both the employee's ID and Department (Partition Key) you wish to delete.");
    }

    var filterQuery = {
        'query': 'SELECT * FROM Employees e WHERE e.id = @id AND e.Department = @department',
        'parameters': [
            { 'name': '@id', 'value': id },
            { 'name': '@department', 'value': department }
        ]
    };

    container.queryDocuments(container.getSelfLink(), filterQuery, {}, function(err, items) {
        if (err) {
            throw new Error("Error querying the document: " + err.message);
        } else if (items.length === 0) {
            response.setBody('No employee found with the given ID and Department.');
        } else {
            var employeeDocument = items[0];
            container.deleteDocument(employeeDocument._self, { 'partitionKey': department }, function(err, result) {
                if (err) {
                    throw new Error("Error deleting the document: " + err.message);
                } else {
                    response.setBody('Successfully deleted the employee with ID: ' + id + ' from Department: ' + department);
                }
            });
        }
    });
}

Explanation

The deleteEmployee stored procedure is designed to delete an employee record from the Azure Cosmos DB container based on the provided ID and department. The procedure begins by ensuring that both the ID (representing the unique identifier of an employee) and the department (serving as the partition key) are supplied. Failing to provide both parameters results in an error.

To locate the specific employee document, a filtered query is constructed using the given ID and department. The queryDocuments function then executes this query. If the procedure encounters an error during this query operation, an error message is promptly relayed. If the search returns no matching documents, a response is generated indicating the absence of an employee with the specified ID and department.

On successfully identifying the desired document, the procedure proceeds with the deleteDocument function to remove the document from the database. It's important to note that the partition key (department) is crucial and must be provided during the deletion operation to ensure accurate targeting. Upon successful deletion, a confirmation message detailing the deletion of the employee with the given ID and department is returned. However, if the procedure faces any issues during the deletion, an error message highlights the problem encountered.

Execute Delete Stored Procedure

Now let's execute the stored procedure as similar to the previous stored procedure. Consider the following scenario: as shown in the screenshot below, the item ID is 9, and the department is IT, which we wanted to delete from the Azure Cosmos DB container.

Before executing the stored procedure, you need to supply the required input parameters, and our stored procedure accepts the two parameters, which are the partition key and the id of the item, which are unique under each partition key value. Open the stored procedure in execution mode using the Cosmos DB emulator as we have done for our previous stored procedure execution. The following images show the visual representation of the steps that we have explained in our previous and current paragraphs.

After providing the required parameters, click on the execute button, and it will delete the item as expected with ID 9 and partition key value IT. The following animated image shows the item deletion demo using our stored procedure.

After creating the stored procedure to create, read, update, and delete the CosmosDB Explorer, the stored procedure section will look as shown in the following image.

Note

• Please download the attached stored procedure code for more details.
• Do the appropriate changes and validation before production use.

Summary

I hope from the preceding examples and explanations, you have learned how to create the stored procedure in the Azure Cosmos database for insert, read, update, and delete operations in a real-time scenario. If you have any feedback or suggestions, please send them via the comment box.

Related articles

• Get Return Value From Stored Procedure Using Dapper ORM In ASP.NET MVC
• CRUD Operation In ASP.NET MVC Using jQuery JSON With Dapper ORM
• CRUD Operations In WCF REST With JSON Message Format