Server-Side Programming

Code inside the database

Stored procedures

Writing a stored procedure

Stored procedures are JavaScript functions that execute within a single logical partition with ACID guarantees:

// Stored procedure: create a project and its first task atomically
function createProjectWithTask(project, task) {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();

    // Create the project
    var projectAccepted = container.createDocument(
        container.getSelfLink(),
        project,
        function (err, createdProject) {
            if (err) throw new Error("Failed to create project: " + err.message);

            // Create the task (only if project succeeded)
            var taskAccepted = container.createDocument(
                container.getSelfLink(),
                task,
                function (err, createdTask) {
                    if (err) throw new Error("Failed to create task: " + err.message);
                    response.setBody({
                        project: createdProject,
                        task: createdTask
                    });
                }
            );

            if (!taskAccepted) throw new Error("Task creation not accepted — timeout?");
        }
    );

    if (!projectAccepted) throw new Error("Project creation not accepted — timeout?");
}

Key characteristics:

• Written in JavaScript (not C# or Java)
• Scoped to a single logical partition — all reads and writes must target the same partition key value
• Provide ACID transactions — if any operation fails, everything rolls back
• Use callback-based async — not Promises, not async/await
• 5-second timeout — if the procedure doesn’t complete within 5 seconds, it’s aborted

Calling from C#

var project = new { id = "proj-003", tenantId = "tenant-abc", type = "project", name = "API Rewrite" };
var task = new { id = "task-201", tenantId = "tenant-abc", type = "task", projectId = "proj-003", title = "Design endpoints" };

Scripts scripts = container.Scripts;

StoredProcedureExecuteResponse<dynamic> response = await scripts.ExecuteStoredProcedureAsync<dynamic>(
    storedProcedureId: "createProjectWithTask",
    partitionKey: new PartitionKey("tenant-abc"),
    parameters: new dynamic[] { project, task });

Console.WriteLine($"Result: {response.Resource}");
Console.WriteLine($"RU charge: {response.RequestCharge}");

💡 Exam tip: 5-second timeout

Stored procedures have a hard 5-second execution limit. If your procedure processes a large number of items, it may time out. The pattern is to process items in batches, return a continuation token when time runs low (check !container.createDocument(...) returning false), and have the client re-invoke the procedure to continue. Know this pattern for the exam.

User-Defined Functions (UDFs)

UDFs are read-only JavaScript functions used inside SQL queries:

// UDF: calculate task priority score
function priorityScore(urgency, impact) {
    if (urgency === "critical" && impact === "high") return 100;
    if (urgency === "critical" || impact === "high") return 75;
    if (urgency === "medium") return 50;
    return 25;
}

Use in a SQL query:

SELECT c.title, udf.priorityScore(c.urgency, c.impact) AS score
FROM c
WHERE c.tenantId = 'tenant-abc'
  AND c.type = 'task'
ORDER BY udf.priorityScore(c.urgency, c.impact) DESC

UDF rules:

• Read-only — they can compute and return values, but cannot create, update, or delete items
• Called with the udf. prefix in SQL queries
• Add RU overhead — they run for every matching document
• Cannot be used in stored procedures or triggers

Pre-triggers and post-triggers

Pre-triggers (before a write)

Run before an insert, replace, or delete to validate or set defaults:

// Pre-trigger: auto-set createdAt and validate required fields
function validateAndStamp() {
    var context = getContext();
    var request = context.getRequest();
    var doc = request.getBody();

    // Validate
    if (!doc.title || doc.title.length === 0) {
        throw new Error("Title is required");
    }

    // Set defaults
    if (!doc.createdAt) {
        doc.createdAt = new Date().toISOString();
    }

    doc.updatedAt = new Date().toISOString();

    // Write the modified document back
    request.setBody(doc);
}

Post-triggers (after a write)

Run after a successful write to update metadata or log changes:

// Post-trigger: update a metadata document with the latest task count
function updateTaskCount() {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();
    var createdDoc = response.getBody();

    if (createdDoc.type !== "task") return;

    // Query for the metadata document
    var query = 'SELECT * FROM c WHERE c.type = "metadata" AND c.id = "task-count"';
    var accepted = container.queryDocuments(container.getSelfLink(), query,
        function (err, docs) {
            if (err) throw err;
            if (docs.length === 0) return;

            var meta = docs[0];
            meta.count += 1;
            meta.lastUpdated = new Date().toISOString();

            container.replaceDocument(meta._self, meta, function (err) {
                if (err) throw err;
            });
        }
    );

    if (!accepted) throw new Error("Query not accepted");
}

Triggers are NOT automatic

This is critical: Triggers do NOT fire automatically. You must explicitly specify them in each request:

// Specify pre-trigger and post-trigger in the request
var options = new ItemRequestOptions
{
    PreTriggers = new List<string> { "validateAndStamp" },
    PostTriggers = new List<string> { "updateTaskCount" }
};

await container.CreateItemAsync(task, new PartitionKey("tenant-abc"), options);

If you don’t specify the trigger in ItemRequestOptions, it simply doesn’t run — no error, no warning.

Comparing stored procedures, UDFs, and triggers

Feature	Stored procedures	UDFs	Triggers
Language	JavaScript	JavaScript	JavaScript
Can read data	✅ Yes	✅ Yes (in query context)	✅ Yes
Can write data	✅ Yes (create, update, delete)	❌ No — read-only	✅ Yes (pre: modify request body; post: write to container)
ACID transaction	✅ Yes — entire procedure is atomic	N/A — runs inside a query	✅ Part of the triggering operation's transaction
Partition scope	Single logical partition only	Runs within the query's scope	Same partition as the triggering operation
Invocation	Called explicitly from SDK	Called with udf. prefix in SQL	Specified in ItemRequestOptions — NOT automatic
Timeout	5 seconds	Part of query timeout	Part of the triggering operation's timeout
Best for	Multi-item transactions, complex business logic	Custom calculations in queries	Validation (pre), audit logging (post)

Ravi’s mistake: Ravi registered a pre-trigger to validate all task documents but forgot to specify it in ItemRequestOptions. Tasks without titles were created successfully. Priya pointed out that triggers are opt-in per request, not automatic database-level constraints.

💡 Exam tip: triggers must be explicitly specified

The exam loves this: “Ravi created a pre-trigger to enforce validation, but invalid documents are still being created. Why?” The answer is always that triggers are NOT automatic. They must be specified in PreTriggers or PostTriggers in each SDK request. There’s no “enable for all operations” setting.

🎬 Video walkthrough

🎬 Video coming soon

Server-Side Programming — DP-420 Module 10

Server-Side Programming — DP-420 Module 10

~16 min

Flashcards

Question

What language are Cosmos DB stored procedures, UDFs, and triggers written in?

Click or press Enter to reveal answer

Answer

JavaScript. Not C#, not Java, not Python — only JavaScript. Stored procedures use callback-based async (not Promises or async/await).

Click to flip back

💡 Show hint

Question

Are Cosmos DB triggers automatic?

Click or press Enter to reveal answer

Answer

No. Triggers must be explicitly specified in each SDK request via PreTriggers or PostTriggers in ItemRequestOptions. If you don't specify them, they don't fire — no error, no warning.

Click to flip back

💡 Show hint

Question

What is the execution timeout for a stored procedure?

Click or press Enter to reveal answer

Answer

5 seconds. If the procedure doesn't complete within 5 seconds, it's aborted and all changes are rolled back. For large batch operations, use a continuation pattern — process a chunk, return a token, and have the client re-invoke.

Click to flip back

💡 Show hint

Question

Can UDFs write data to the container?

Click or press Enter to reveal answer

Answer

No — UDFs are read-only. They can only compute and return values within a SQL query. They're called with the udf. prefix: SELECT udf.myFunc(c.field) FROM c.

Click to flip back

💡 Show hint

Question

What scope do stored procedures execute in?

Click or press Enter to reveal answer

Answer

A single logical partition. All reads and writes within a stored procedure must target the same partition key value. This gives ACID guarantees but means you can't do cross-partition operations.

Click to flip back

💡 Show hint

Knowledge check

Knowledge Check

Ravi creates a pre-trigger to validate that every task has a title. He creates a task without a title using the SDK. What happens?

AThe trigger rejects the task — validation failsBThe task is created successfully — Ravi forgot to specify the trigger in ItemRequestOptionsCThe trigger logs a warning but allows the creationDThe SDK automatically detects and applies all registered triggers

Check Answer

Knowledge Check

Priya's stored procedure processes 10,000 items in a logical partition. What risk does she face?

ANo risk — stored procedures have no limitsBThe procedure may time out after 5 seconds and roll back all changesCThe procedure will automatically split into batchesDOnly 100 items can be processed per stored procedure call

Check Answer

Knowledge Check

Sophie wants to calculate a 'risk score' for each task in a SQL query. Which server-side feature should she use?

AA stored procedure — it can compute valuesBA UDF — read-only function callable from SQL with udf. prefixCA post-trigger — it can process documents after they're writtenDA pre-trigger — it runs before the query executes

Check Answer

---

Next up: Transactions in Practice — real-world patterns for order processing with TransactionalBatch, inventory reservation with stored procedures, and cross-partition sagas.