Microsoft Docs

Azure Cosmos DB Stored Procedures

Stored procedures in Azure Cosmos DB allow you to encapsulate data manipulation logic within the database. They are written in JavaScript and executed server-side, offering benefits such as atomicity, consistency, and improved performance.

This document provides a comprehensive guide to understanding, creating, and using stored procedures in Azure Cosmos DB.

Benefits of Stored Procedures

• Atomicity: Stored procedures ensure that a set of operations is performed as a single unit. If any operation fails, the entire transaction is rolled back, maintaining data integrity.
• Consistency: By executing logic server-side, you ensure that data operations are consistent and adhere to predefined business rules.
• Performance: Reduces network latency by executing complex logic directly on the database server. Batch operations can be significantly faster.
• Encapsulation: Hides complex database logic from client applications, simplifying development and maintenance.
• Security: Can enforce access control and business logic at the database level.

JavaScript SDK

Azure Cosmos DB stored procedures are written using JavaScript. The SDK provides access to the database context and allows you to perform CRUD (Create, Read, Update, Delete) operations on documents.

You can interact with documents using the following methods:

• getContext().getRequest().getBody(): To get the request body.
• getContext().getResponse().setBody(): To set the response body.
• collection.readDocument(), collection.createDocument(), collection.upsertDocument(), collection.replaceDocument(), collection.deleteDocument(): For document manipulation.
• collection.queryDocuments(): To query documents.

Creating Stored Procedures

Stored procedures are created as part of a collection in Azure Cosmos DB. You can create them using the Azure portal, Azure CLI, or SDKs.

Here's an example of a simple stored procedure that creates a new document:

function createDocumentProcedure(newItem) {
    var collection = getContext().getCollection();
    var response = getContext().getResponse();

    var accepted = collection.createDocument(
        collection.getSelfLink(),
        newItem,
        function (err, document, options) {
            if (err) throw err;
            response.setBody(document);
        }
    );

    if (!accepted) {
        // Handle potential continuation if the operation was not immediately accepted
        // For simplicity, we'll just throw an error here. In real applications,
        // you might want to implement more robust continuation handling.
        throw new Error("The create operation was not accepted. Consider continuation.");
    }
}
                    

In this example:

• The `createDocumentProcedure` function takes `newItem` as an argument.
• It retrieves the current collection and prepares the response.
• collection.createDocument is called to insert the new item.
• A callback function handles the result, throwing an error if one occurs or setting the newly created document as the response body.

Executing Stored Procedures

Stored procedures are executed via HTTP POST requests to the stored procedure's link. You can execute them using various SDKs or REST API calls.

Example using Node.js SDK:

const cosmosClient = require('@azure/cosmos').DocumentClient;

const endpoint = process.env.COSMOS_ENDPOINT;
const key = process.env.COSMOS_KEY;
const client = new cosmosClient(endpoint, { masterKey: key });

const databaseId = 'myDatabase';
const collectionId = 'myCollection';

const newItem = { id: 'uniqueId123', category: 'electronics', name: 'Laptop', price: 1200 };

client.executeStoredProcedure(
    `dbs/${databaseId}/colls/${collectionId}/sprocs/createDocumentProcedure`,
    [newItem] // Arguments for the stored procedure
)
.then(function (result) {
    console.log("Stored procedure executed successfully. Result:", result.result);
})
.catch(function (error) {
    console.error("Error executing stored procedure:", error);
});
                    

Best Practices

• Keep stored procedures focused on a single logical operation.
• Handle errors gracefully and provide meaningful feedback.
• Consider the Request Units (RUs) consumed by your stored procedures. Complex operations can be RU-intensive.
• Use stored procedures for transactional operations where atomicity is critical.
• Avoid long-running stored procedures. If a procedure takes too long, it might time out or consume excessive RUs. Consider breaking down complex logic into smaller procedures or using alternative approaches.
• Test your stored procedures thoroughly under various conditions.