Updating Documents in Cosmos DB: A Step-by-Step Guide on Creating and Executing a Stored Procedure

Cosmos DB is a powerful, fully-managed, globally distributed, and multi-model database service provided by Microsoft Azure. One of its key features is the ability to create and execute stored procedures, which are JavaScript functions that can be used to perform operations on the data in your container. In this post, we’ll take a look at how to create and execute a stored procedure in Cosmos DB that updates a document in a container.

Creating the Stored Procedure

To create a stored procedure, we need to provide a JavaScript function that performs the desired operation. In this case, we want to update a document in a container, so we’ll create a function that takes in the id of the document to be updated and the updated document itself as parameters. The function will then retrieve the document from the container using the provided id and replace the old document with the updated one.

Here’s an example of the JavaScript function that we can use to update a document in a container:

function updateDocument(id, updatedDoc) {
    var container = getContext().getCollection();
    var query = "SELECT * FROM c WHERE c.id = '" + id + "'";
    var accept = container.queryDocuments(container.getSelfLink(), query, function (err, documents, options) {
        if (err) throw new Error("Error: " + err.message);
        if (documents.length != 1) throw new Error("Document not found");
        var doc = documents[0];
        doc = updatedDoc;
        container.replaceDocument(doc._self, doc, function (err, updatedDoc) {
            if (err) throw new Error("Error: " + err.message);
            getContext().getResponse().setBody(updatedDoc);
        });
    });
    if (!accept) throw new Error("Unable to read document");
}

It’s important to note that you should replace the container link with your container link in the above code.

Executing the Stored Procedure

Once the stored procedure is created, we can execute it in the Azure portal by:

1. Go to your Cosmos DB account in the Azure portal.
2. Select the database that contains the container where your document is located.
3. Select the “Stored Procedures” tab.
4. Click on the “Execute” button.
5. In the “Execute Stored Procedure” blade, enter the id of the document you wish to update, and the updated document in the input fields provided.
6. Click on the “Execute” button to run the stored procedure.

Alternatively, you can invoke the stored procedure via the SDK by creating a client, passing the stored procedure id and parameters.

var client = new CosmosClient(connectionString);
var storedProcedure = client.getStoredProcedure("your container link", "updateDocument");
var response = await storedProcedure.execute({id: "your document id", updatedDoc: {your updated document}});

To sum up, Cosmos DB’s stored procedures offer a robust and adaptable approach to managing your data in a highly available and scalable way. By following the steps outlined in this guide, you can now create and execute a stored procedure that updates a document in a container, making it easy to keep your data updated and organized. With the tools provided in this post, you can begin to utilize stored procedures in your Cosmos DB projects. Remember to test your code and monitor the performance and scalability of your solution as you continue to develop and deploy your application.

References:

Here are some reference links that you can use to further explore the topic of creating and executing stored procedures in Cosmos DB:

1. Azure Cosmos DB documentation on stored procedures: https://docs.microsoft.com/en-us/azure/cosmos-db/programming-stored-procedures
2. Azure Cosmos DB REST API reference for stored procedures: https://docs.microsoft.com/en-us/rest/api/cosmos-db/sprocs
3. Azure Cosmos DB .NET SDK documentation on stored procedures: https://docs.microsoft.com/en-us/dotnet/api/microsoft.azure.documents.client.storedprocedure
4. Azure Cosmos DB Node.js SDK documentation on stored procedures: https://azure.github.io/azure-cosmos-js-server/StoredProcedure.html
5. Azure Cosmos DB Java SDK documentation on stored procedures: https://azure.github.io/azure-cosmos-java-sdk/stored_procedures.html
6. Microsoft’s sample on Stored procedure, triggers and user defined functions in Cosmos DB: https://github.com/Azure-Samples/azure-cosmos-db-serverless-stored-procedures-triggers-udfs
7. Microsoft’s sample on implementing stored procedure to implement a simple voting system: https://github.com/Azure-Samples/azure-cosmos-db-stored-procedure-voting-app

These links provide in-depth information and examples on how to create and execute stored procedures in Cosmos DB, as well as how to use the SDKs provided by Microsoft to interact with the service.