MENU

How to Create Stored Procedures in Azure Cosmos DB

Filed Under: , , , , , on
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 wanted to read my previous articles, then read them using the following links:

In this article, we will learn how to create stored procedures in Azure Cosmos DB. Let’s begin with the basics so everyone can understand, including beginners.

What is a Cosmos DB Stored Procedure?

A Stored Procedures are sets 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?

  • Efficiency
  • Atomicity
  • Speed
  • Security
  • Maintainability
  • Consistency

Efficiency

They 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

They ensure tasks are done correctly and can rollback changes if something goes wrong while processing the database operation.

Speed

They execute quickly as they run close to where the data is stored and pre-compiled.

Security

They 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 organised, 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 lets 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:

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"
}

Step 2 : Open the Cosmos DB Emulator or Explorer

Now that we have all the prerequisites, including an understanding of our Azure Cosmos DB database and collection structure, we can now open the 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 clicked on the create new stored procedure as shown the preceding step two image , The following editor is get opens, follow the following steps

  • Remove the existing default code.
  • Use the code as given in the editor.
  • 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.



getAllEmployees Stored Procedure Code

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.');
    }
}

Please download the stored procedure code from Github for more details.
Summary

I hope that from the preceding examples and explanations, you have learned how to create the stored procedure in the Azure Cosmos database. If you have any feedback or suggestions, please send them via the comment box.

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode