How to clear a Cosmos DB database or delete all items using Azure portal

Question

If go to https://portal.azure.com, open our Azure Cosmos DB account (1) --> Data Explorer (2) --> Click on users (3) --> Click on New SQL Query:

Azure will open a text box to enter a Query:

I've found that Cosmos DB does not allow the usage of DELETE instead of SELECT: https://stackoverflow.com/a/48339202/1198404, so I should do something like:

SELECT * FROM c DELETE c
SELECT * FROM c DELETE *

But none of my attempts worked.

Answer 1

One option is to set a TTL of 0 on that particular Container, depending on the number of records though it could take a bit of time.

Alternatively, and this is probably a more viable option, is to simply just to delete & recreate the Container.

Answer 2

A Cosmos DB database can contain zero, one, or more Containers. Containers store items. The hierarchy is described here. I am assuming that you want to clear a Container of all items.

Since your connection string is scoped to the database level, the way I quickly clear a Container of all of its items is to just delete and recreate the Container within the database.

To delete a Container in the Azure Portal, do the following:

1. In the left menu within the portal, choose All resources -> then choose your Cosmos DB resource to bring up the Cosmos DB management blade.
2. Choose Data Explorer. You'll see your databases and each Container listed beneath its database.
3. Choose the container you want to delete. Once you highlight the menu item for the Container, click the ... to the right of the Container name. This will have a popup menu where you can choose to delete the container.

For example, if the Container name is users:

Answer 3

You can only bulk delete with BulkExecutor and not from the portal, you can only delete one item at a time from the Portal.

I would handle environment setup differently. I suggest you create separate resource group for each environment or at least create another collection for production. Regarding resource group solution to keep cost down, just tear down the test environment when not in use.

Answer 4

You can add a delete stored procedure to execute delete completely.

function bulkDeleteSproc(query) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var response = getContext().getResponse();
    var responseBody = {
        deleted: 0,
        continuation: true       
    };
    query='SELECT * FROM root r';

    // Validate input.
    if (!query) throw new Error("The query is undefined or null.");

    tryQueryAndDelete();


    function tryQueryAndDelete(continuation) {
        
        var requestOptions = {continuation: continuation};
        console.log(requestOptions);
        var isAccepted = collection.queryDocuments(collectionLink, query, requestOptions, function (err, retrievedDocs, responseOptions) {
            if (err) throw err;
              
            if (retrievedDocs.length > 0) {
                // Begin deleting documents as soon as documents are returned form the query results.
                // tryDelete() resumes querying after deleting; no need to page through continuation tokens.
                //  - this is to prioritize writes over reads given timeout constraints.
                tryDelete(retrievedDocs);
            } else if (responseOptions.continuation) {
                // Else if the query came back empty, but with a continuation token; repeat the query w/ the token.
                tryQueryAndDelete(responseOptions.continuation);
            } else {
                // Else if there are no more documents and no continuation token - we are finished deleting documents.
                responseBody.continuation = false;
                response.setBody(responseBody);
            }
        });

        // If we hit execution bounds - return continuation: true.
        if (!isAccepted) {
            console.log("tryquerydelete not accepted");
            response.setBody(responseBody);
        }
    }

    // Recursively deletes documents passed in as an array argument.
    // Attempts to query for more on empty array.
    function tryDelete(documents) {
        if (documents.length > 0) {
            // Delete the first document in the array.
            var isAccepted = collection.deleteDocument(documents[0]._self, {}, function (err, responseOptions) {
                if (err) throw err;

                responseBody.deleted++;
               console.log("hi");
                documents.shift();
               
                // Delete the next document in the array.
                tryDelete(documents);
                console.log(isAccepted);
            });

            // If we hit execution bounds - return continuation: true.
            if (!isAccepted) {
                console.log("trydelete not accepted");
                response.setBody(responseBody);
            }
        } else {
            // If the document array is empty, query for more documents.
            tryQueryAndDelete();
        }
    }
}

Answer 5

To delete all the items using Azure portal it is best to just delete the container which contains the items.

Click on the 3 dots

and delete the container