Design stored procedures to work with multiple documents transactionally

Designing and implementing native applications using Microsoft Azure Cosmos DB allows developers to build highly scalable and globally distributed solutions. One important aspect of working with Cosmos DB is designing and implementing stored procedures to work with multiple documents transactionally. In this article, we will explore the best practices and techniques for achieving this.

Creating Collections

Before we dive into stored procedures, let’s create the collections in our Cosmos DB account. We can do this using the Azure portal or programmatically using the Cosmos DB SDKs.

Here’s an example of creating the orders and inventory collections:

await client.createContainer({
    id: "orders",
    partitionKey: { paths: ["/customerId"] }
});

await client.createContainer({
    id: "inventory",
    partitionKey: { paths: ["/productId"] }
});

Defining the Stored Procedure

Next, let’s define our stored procedure to process an order. The stored procedure will reduce the inventory count and create an order document in a single transaction.

// Define the stored procedure
function processOrder(order) {
    var collection = getContext().getCollection();
    var response = getContext().getResponse();

    // Retrieve the inventory document
    var inventoryLink = collection.getAltLink() + "/docs/" + order.productId;
    collection.readDocument(inventoryLink, function (err, inventory) {
        if (err) {
            response.setBody("Error reading inventory document: " + err.message);
        } else {
            // Check if there is enough stock
            if (inventory.quantity >= order.quantity) {
                // Update the inventory count
                inventory.quantity -= order.quantity;
                collection.replaceDocument(inventoryLink, inventory, function (err, updatedInventory) {
                   if (err) {
                     response.setBody("Error updating inventory document: " + err.message);
                   } else {
                    // Create the order document
                    order.id = "order-" + Date.now();
                    collection.createDocument(collection.getSelfLink(), order, function (err, createdOrder) {
                      if (err) {
                       response.setBody("Error creating order document: " + err.message);
                      } else {
                       response.setBody("Order processed successfully");
                      }
                    });
            }
            } else {
                response.setBody("Insufficient stock");
            }
        }
    });
}

Invoking the Stored Procedure

Finally, let’s see how we can invoke the stored procedure from our application code. The following code snippet demonstrates how to execute the stored procedure using the JavaScript SDK:

var order = {
    customerId: "customer-123",
    productId: "product-456",
    quantity: 2
};

client.scripts.storedProcedure("processOrder").execute(requestOptions, order, function (err, result) {
    if (err) {
        console.log("Error processing order: " + err.message);
    } else {
        console.log("Order processed successfully");
    }
});

In the code above, we first define an order object with the required properties. We also specify the partition key for the order, which in our case is the customer ID. We then execute the stored procedure using the `execute` method of the `StoredProcedure` object.

By using stored procedures and executing them in a transactional manner, we can ensure the consistency of our data. If any operation within the stored procedure fails, the entire transaction will be rolled back, maintaining the integrity of our data.

In conclusion, designing and implementing stored procedures to work with multiple documents transactionally in Azure Cosmos DB is a powerful feature that allows us to create highly scalable and reliable native applications. By following the best practices and techniques outlined in this article, you can ensure the consistency and integrity of your data in Cosmos DB.