Routines and Triggers

+
~

Cosmos DB supports a number of RDBMS-style database objects to perform actions

To call any of these objects, you need to register them.

Stored Procedures

var createDocumentStoredProc = {
    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;
    }
}
Note

The context object provides access to all operations that can be performed in Azure Cosmos DB, and access to the request and response objects.

In the above stored procedure registration code, the actual process of creating a document is asynchronous and contains a callback continuation function to assess the outcome.

Stored procedure inputs are always strings, so arrays must first be deserialised using JSON.parse().

Registration

await client
	.GetContainer("myDatabase", "myContainer")
	.Scripts
	.CreateStoredProcedureAsync(new StoredProcedureProperties
	{
	    Id = "spCreateToDoItems",
	    Body = File.ReadAllText($@"..\js\{storedProcedureId}.js")
	});

Usage

var result = await client
	.GetContainer("database", "container")
	.Scripts
	.ExecuteStoredProcedureAsync<string>(
		"spCreateToDoItem", 
		new PartitionKey("Personal"), 
		new[] { newItems }
	);

Bounded execution

All Cosmos operations must complete within a set amount of time. Stored procedures have a limited amount of time to run on the server. All collection functions return a Boolean which indicates if an operation completed or not.

Transactions

You can implement transactions on items in a container by using a stored procedure. You can return a value from a stored procedure which indicates progress through a long workload and then resume from where you were on the next stored procedure call.

Triggers

Triggers work a little differently to how you are used to. You register a trigger function and then specify the trigger that you want to run when performing a database operation. Triggers are not automatically executed.

Registration

await client
	.GetContainer("database", "container")
	.Scripts
	.CreateTriggerAsync(new TriggerProperties
	{
	    Id = "trgPreValidateToDoItemTimestamp",
	    Body = File.ReadAllText("@..\js\trgPreValidateToDoItemTimestamp.js"),
	    TriggerOperation = TriggerOperation.Create,
	    TriggerType = TriggerType.Pre
	});

Usage

await client
	.GetContainer("database", "container")
	.CreateItemAsync(
		newItem, 
		null, 
		new ItemRequestOptions { 
			PreTriggers = [ "trgPreValidateToDoItemTimestamp" ]
		}
	);

Pretriggers

function validateToDoItemTimestamp() {
    var context = getContext();
    var request = context.getRequest();

    // item to be created in the current operation
    var itemToCreate = request.getBody();

    // validate properties
    if (!("timestamp" in itemToCreate)) {
        var ts = new Date();
        itemToCreate["timestamp"] = ts.getTime();
    }

    // update the item that will be created
    request.setBody(itemToCreate);
}

On trigger registration, you can specify the operations that it supports. The trigger above should support Create operations only, not Replace.

Post-triggers

function updateMetadata() {
	var context = getContext();
	var container = context.getCollection();
	var response = context.getResponse();
	
	// item that was created
	var createdItem = response.getBody();
	
	// query for metadata document
	var filterQuery = 'SELECT * FROM root r WHERE r.id = "_metadata"';
	var accept = container.queryDocuments(
		container.getSelfLink(), 
		filterQuery,
	    updateMetadataCallback
	);
	
	if(!accept) throw "Unable to update metadata, abort";

	function updateMetadataCallback(err, items, responseOptions) {
	    if(err) throw new Error("Error" + err.message);
        if(items.length != 1) throw 'Unable to find metadata document';

        var metadataItem = items[0];

        // update metadata
        metadataItem.createdItems += 1;
        metadataItem.createdNames += " " + createdItem.id;    
        var accept = container.replaceDocument(
	        metadataItem._self,
            metadataItem, 
            function(err, itemReplaced) {
				if(err) throw "Unable to update metadata, abort";
            }
		);
		
        if(!accept) throw "Unable to update metadata, abort";
        return;
    }
}

UDFs

UDFs are named functions registered similarly to triggers which can then be used in SELECT statements

function tax(income) {
	if(income == undefined)
		throw 'no input';

	if (income < 1000)
		return income * 0.1;
	else if (income < 10000)
		return income * 0.2;
	else
		return income * 0.4;
}

Registration

await client
	.GetContainer("database", "container")
	.Scripts
	.CreateUserDefinedFunctionAsync(new UserDefinedFunctionProperties
	{
	    Id = "Tax",
	    Body = File.ReadAllText(@"..\js\Tax.js")
	});

Usage

var iterator = client
	.GetContainer("database", "container")
	.GetItemQueryIterator<dynamic>(
		"SELECT * FROM Incomes t WHERE udf.Tax(t.income) > 20000")
	;

while (iterator.HasMoreResults)
{
    var results = await iterator.ReadNextAsync();
    foreach (var result in results)
    {
        //iterate over results
    }
}