Stored Procedures

  • Article

Azure Cosmos DB is a globally distributed multi-model database that supports the document, graph, and key-value data models. The content in this section is for creating, querying, and managing document resources using the SQL API via REST.

A stored procedure is a piece of application logic written in JavaScript that is registered and executed against a collection as a single transaction. In Azure Cosmos DB, JavaScript is hosted in the same memory space as the database. Hence, requests made within stored procedures execute in the same scope of a database session. This process enables Azure Cosmos DB to guarantee ACID for all operations that are part of a single stored procedure.

The stored procedure resource is represented by sprocs in the Azure Cosmos DB resource model.

The stored procedure resource has a fixed schema. The body property contains the application logic. The following example illustrates the JSON construct of a stored procedure.

{    
   "id":"SimpleStoredProc",  
   "body":"function (docToCreate, addedPropertyName, addedPropertyValue {getContext().getResponse().setBody('Hello World');}",  
   "_rid":"hLEEAI1YjgcBAAAAAAAAgA==",  
   "_ts":1408058682,  
   "_self":"dbs\/hLEEAA==\/colls\/hLEEAI1Yjgc=\/sprocs\/hLEEAI1YjgcBAAAAAAAAgA==\/",  
   "_etag":"00004100-0000-0000-0000-53ed453a0000"  
}

Merely having All access mode for a particular stored procedure does not allow the user to execute the stored procedure. Instead, the user has to have All access mode at the collection level in order to execute a stored procedure.

Property Description
id Required. It is a user settable property. It is the unique name used to identify the stored procedure. The id must not exceed 255 characters.
body Required. It is a user settable property. It is the body of the stored procedure.
_rid It is a system-generated property. The resource ID (_rid) is a unique identifier that is also hierarchical per the resource stack on the resource model. It is used internally for placement and navigation of the stored procedure resource.
_ts It is a system-generated property. It specifies the last updated timestamp of the resource. The value is a timestamp.
_self It is a system-generated property. It is the unique addressable URI for the resource.
_etag It is a system-generated property that specifies the resource etag required for optimistic concurrency control.

Stored procedures can use the Cosmos DB JavaScript Server-side SDK to make database operations like create, read, update, delete and query documents, as well as read from the request body and write to the response body of the stored procedure. For more information, see the Cosmos DB server side programming tutorial.

For example, here’s a stored procedure for “Hello World”:

var helloWorldStoredProc = {  
    id: "helloWorld",  
    body: function () {  
        var context = getContext();  
        var response = context.getResponse();  
  
        response.setBody("Hello, World");  
    }  
}

The following example creates a document inside the stored procedure:

{  
    id: "createMyDocument",  
    body: function createMyDocument(documentToCreate) {  
        var context = getContext();  
        var collection = context.getCollection();  
  
        var accepted = collection.createDocument(collection.getSelfLink(),  
              documentToCreate,  
            function (err, documentCreated) {  
                if (err) throw new Error('Error' + err.message);  
                context.getResponse().setBody(documentCreated.id)  
            });  
        if (!accepted) return;  
    }  
}

The following example swaps two items inside a stored procedure:

// JavaScript source code  
var exchangeItemsSproc = {  
    name: "exchangeItems",  
    body: function (playerId1, playerId2) {  
        var context = getContext();  
        var collection = context.getCollection();  
        var response = context.getResponse();  
  
        var player1Document, player2Document;  
  
        // query for players  
        var filterQuery = 'SELECT * FROM Players p where p.id  = "' + playerId1 + '"';  
        var accept = collection.queryDocuments(collection.getSelfLink(), filterQuery, {},  
            function (err, documents, responseOptions) {  
                if (err) throw new Error("Error" + err.message);  
  
                if (documents.length != 1) throw "Unable to find both names";  
                player1Document = documents[0];  
  
                var filterQuery2 = 'SELECT * FROM Players p where p.id = "' + playerId2 + '"';  
                var accept2 = collection.queryDocuments(collection.getSelfLink(), filterQuery2, {},  
                    function (err2, documents2, responseOptions2) {  
                        if (err2) throw new Error("Error" + err2.message);  
                        if (documents2.length != 1) throw "Unable to find both names";  
                        player2Document = documents2[0];  
                        swapItems(player1Document, player2Document);  
                        return;  
                    });  
                if (!accept2) throw "Unable to read player details, abort ";  
            });  
  
        if (!accept) throw "Unable to read player details, abort ";  
  
        // swap the two players’ items  
        function swapItems(player1, player2) {  
            var player1ItemSave = player1.item;  
            player1.item = player2.item;  
            player2.item = player1ItemSave;  
  
            var accept = collection.replaceDocument(player1._self, player1,  
                function (err, docReplaced) {  
                    if (err) throw "Unable to update player 1, abort ";  
  
                    var accept2 = collection.replaceDocument(player2._self, player2,  
                        function (err2, docReplaced2) {  
                            if (err) throw "Unable to update player 2, abort"  
                        });  
  
                    if (!accept2) throw "Unable to update player 2, abort";  
                });  
  
            if (!accept) throw "Unable to update player 1, abort";  
        }  
    }  
}

Tasks

You can do the following operations with stored procedures:

For information on how stored procedures work, including execution of a stored procedure, see Azure Cosmos DB programming: Stored procedures, triggers, and UDFs.

See Also