ASP.NET Core CRUD with NoSQL: Part 5

This ASP.NET Core CRUD series is coming to end. We’ve covered setup (part 1), reading with SQL++ (part 2), reading with key-value (part 3), and creating/updating (part 4). In this last post, we’ll look at the D in CRUD: deleting.

Deleting with SQL++ or Key-Value

Hopefully you’re noticing a pattern. Just as with reading, creating, and updating, you’ve got multiple paths for deleting. You can use a SQL++ DELETE statement:

DELETE FROM demo._default.wishlist w
WHERE META(w).id = "1c3de2e7-70ea-4ee2-803b-425bbf6251cb"

1 2	DELETE FROM demo._default.wishlist w WHERE META(w).id = "1c3de2e7-70ea-4ee2-803b-425bbf6251cb"

Or you can use a key-value delete operation (aka “Remove“):

await collection.RemoveAsync("1c3de2e7-70ea-4ee2-803b-425bbf6251cb");

1	await collection.RemoveAsync("1c3de2e7-70ea-4ee2-803b-425bbf6251cb");

And as before, here are the very similar guidelines to help you decide which one to use:

Use case	Key-value?	Why or why not?
Delete a user with key “73892”	Yes	Direct access
Delete a group of users with keys “73892”, “47212”, and “90491”	Yes	This may require multiple key-value operations, but this still may be faster than using a SQL DELETE … WHERE … IN query.
Delete every user from “Ohio”	No	User’s state is likely a “secondary” attribute, not a key (multiple users can be from Ohio). This is a good use case for a SQL++ DELETE

A Delete endpoint for ASP.NET Core

With that in mind, create a delete endpoint like so:

[HttpDelete]
[Route("api/delete")]
public async Task<IActionResult> Delete(Guid id)
{
    var bucket = await _bucketProvider.GetBucketAsync("demo");
    var collection = await bucket.CollectionAsync("wishlist");

    await collection.RemoveAsync(id.ToString());

    return Ok(new { success = true });
}

[HttpDelete]

[Route("api/delete")]

public async Task<IActionResult> Delete(Guid id)

{

var bucket = await _bucketProvider.GetBucketAsync("demo");

var collection = await bucket.CollectionAsync("wishlist");

await collection.RemoveAsync(id.ToString());

return Ok(new { success = true });

}

Try this endpoint with OpenAPI / Swagger, and it will behave how you’d expect.

Should I really delete?

In many use cases, you may not want to actually delete data. You may want to do something called a “soft” delete. This involves moving or marking the data in such a way that it still exists in the database, but it is no longer shown to the end user. This has the benefit of being discoverable, recoverable, and reportable.

It’s also a great opportunity to show the flexibility of a JSON NoSQL database.

Soft-deleting

Let’s introduce a “deleted” field to wishlist items. This field will contain a date/time that the item was deleted. If this field exists, the rest of the endpoint should consider this item to be deleted. However, if we need to, we can still query it, report on it, and recover it.

In a relational database, this would likely require an ALTER. Depending on the size of your data, it could require some downtime, or possibly a lot of NULL values. In a JSON database, we don’t need to tell the database anything about a new field.

Let’s use the subdocument API to add a “deleted” field to the document. Sub-document means that we will only operate on a portion of the JSON, and leave the rest alone.

[HttpDelete]
[Route("api/softDelete")]
public async Task<IActionResult> SoftDelete(Guid id)
{
    var bucket = await _bucketProvider.GetBucketAsync("demo");
    var collection = await bucket.CollectionAsync("wishlist");

    await collection.MutateInAsync(id.ToString(),
        options => options.Upsert("deleted", DateTime.Now));

    return Ok(new { success = true });
}

[HttpDelete]

[Route("api/softDelete")]

public async Task<IActionResult> SoftDelete(Guid id)

{

var bucket = await _bucketProvider.GetBucketAsync("demo");

var collection = await bucket.CollectionAsync("wishlist");

await collection.MutateInAsync(id.ToString(),

options => options.Upsert("deleted", DateTime.Now));

return Ok(new { success = true });

}

(Make sure using Couchbase.KeyValue; is at the top of your GiftsController file.)

This code sends a command to Couchbase: for the document with such-and-such ID, upsert a field called “deleted” and give it the current date/time as a value.

Notice that with sub-document, we didn’t have to first load the existing document, and we didn’t have to send the entire modified document back over the wire.

Soft-deleted data

The end result will be a document that looks like this:

{
  "name": "Digital Photo Frame",
  "deleted": "2022-04-21T11:05:26.1766248-04:00"
}

{

"name": "Digital Photo Frame",

"deleted": "2022-04-21T11:05:26.1766248-04:00"

}

The other documents in my wishlist do not have a deleted field. They still look like:

{
  "name": "Skyline Chili 2XL T-Shirt"
}

{

"name": "Skyline Chili 2XL T-Shirt"

}

and

{
  "name": "Joey Votto jersey"
}

{

"name": "Joey Votto jersey"

}

Note that they don’t have a “deleted”: null field; they don’t have a “deleted“ field at all.

Soft-deleted SELECT

The data is marked as deleted, but it’s still in the database. We need to modify the GetAll endpoint (see part 2 for more about GetAll) to take this into account:

SELECT META(w).id, w.*
FROM demo._default.wishlist w
WHERE w.deleted IS MISSING

SELECT META(w).id, w.*

FROM demo._default.wishlist w

WHERE w.deleted IS MISSING

I’ve introduced some more SQL++ syntax here: MISSING. This is a concept that doesn’t exist in relational databases. In relational, any column specified in the query must be defined and must have a value (even if it’s null). With a JSON NoSQL document database, there is no such constraint.

Improving the index

One last point to discuss is indexing. Back in part 2, we created a primary index just to get started. However, that index will rarely be the most efficient. Creating and tuning indexes is a deep topic, just as it is in the relational database world.

Fortunately, Couchbase Capella has a built-in Advise tool that can recommend better indexes. Just click Advise in the Query Workbench (or you can use ADVISE syntax).

In this case, it gives the following recommendation:

"recommended_indexes": {
          "indexes": [
            {
              "index_statement": "CREATE INDEX adv_deletedISMISSING ON `default`:`demo`.`_default`.`wishlist`(`deleted` IS MISSING) WHERE `deleted` IS MISSING",
              "index_statement_relative": "CREATE INDEX adv_deletedISMISSING ON `wishlist`(`deleted` IS MISSING) WHERE `deleted` IS MISSING",
              "keyspace_alias": "wishlist_w",
              "query_context": "default:demo._default",
              "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing."
            }
          ]
        }

"recommended_indexes": {

"indexes": [

{

"index_statement": "CREATE INDEX adv_deletedISMISSING ON `default`:`demo`.`_default`.`wishlist`(`deleted` IS MISSING) WHERE `deleted` IS MISSING",

"index_statement_relative": "CREATE INDEX adv_deletedISMISSING ON `wishlist`(`deleted` IS MISSING) WHERE `deleted` IS MISSING",

"keyspace_alias": "wishlist_w",

"query_context": "default:demo._default",

"recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing."

}

]

}

In the case of our very small amount of wishlist data, this index is probably not worth creating. However, if we were managing the wishlists of an entire eCommerce site (for example), this index would be a good starting point.