Microsoft Docs | Azure Storage

Stored Procedures, Triggers, and User-Defined Functions (UDFs) in Azure Cosmos DB

Azure Cosmos DB is a globally distributed, multi-model database service that enables you to rapidly develop and scale high-performance applications. Stored procedures, triggers, and user-defined functions (UDFs) are powerful features that allow you to encapsulate logic directly within your Cosmos DB database, improving performance and simplifying application development.

What are Stored Procedures, Triggers, and UDFs?

Stored Procedures

Stored procedures are blocks of code written in JavaScript that are executed server-side within Azure Cosmos DB. They allow you to perform complex operations, enforce business logic, and perform atomic transactions on your data. This reduces network latency and simplifies client application code.

Key benefits:

• Atomicity: Operations within a stored procedure are executed as a single transaction.
• Performance: Reduced network round trips for complex operations.
• Encapsulation: Business logic is kept close to the data.

Triggers

Triggers are also written in JavaScript and can be executed automatically before or after an operation (create, update, delete) on a container. They are useful for enforcing data integrity, performing side effects, or auditing changes.

Types of Triggers:

• Pre-triggers: Execute before a data modification operation.
• Post-triggers: Execute after a data modification operation.

User-Defined Functions (UDFs)

UDFs are JavaScript functions that allow you to define custom logic that can be invoked within your SQL queries. They enable you to perform custom data transformations, calculations, or filtering directly in your queries, further enhancing the expressiveness of the SQL API.

Key benefits:

• Custom Logic: Extend the capabilities of the SQL query language.
• Reusability: Define logic once and use it across multiple queries.

Getting Started

Creating and Managing

You can create and manage stored procedures, triggers, and UDFs using various tools:

• Azure Portal: The web-based portal provides an interface for managing these database objects.
• Azure Cosmos DB SDKs: Use the official SDKs for your preferred language (e.g., .NET, Java, Node.js, Python) to programmatically manage these resources.
• Azure Cosmos DB Data Explorer: A built-in tool within the Azure portal for interacting with your data and database objects.

Example: A Simple Stored Procedure

This example shows a stored procedure that increments a counter field in a document. You would typically use a SDK or the Data Explorer to register this procedure.

function incrementCounter(id) {
    var collection = getContext().getCollection();
    var response = getContext().getResponse();
    var documentLink = collection.getSelfLink() + '/docs/' + id;

    collection.read(documentLink, function (err, doc) {
        if (err) throw err;

        if (!doc) {
            throw new Error("Document not found: " + id);
        }

        doc.counter = (doc.counter || 0) + 1;

        var isAccepted = collection.replace(doc._self, doc, function (err, replaced) {
            if (err) throw err;
            response.setBody(replaced);
        });

        if (!isAccepted) {
            throw new Error('The stored procedure could not be completed due to an issue with the request. Please try again.');
        }
    });
}

Example: A Pre-Trigger for Validation

This trigger ensures that a 'name' field is present before creating a document.

function validateName() {
    var context = getContext();
    var request = context.getRequest();
    var item = request.getBody();

    if (!item.name) {
        var response = context.getResponse();
        response.setBody({ "error": "The 'name' field is required." });
        throw new Error("Validation failed: 'name' field is missing.");
    }

    // Proceed with the operation
    request.execute();
}

Example: A UDF for String Manipulation

This UDF capitalizes the first letter of a string.

function capitalizeFirstLetter(str) {
    if (str && str.length > 0) {
        return str.charAt(0).toUpperCase() + str.slice(1);
    }
    return str;
}

You could then use this UDF in a query like:

SELECT udf.capitalizeFirstLetter(c.name) AS capitalizedName FROM c