CRUD stands for Create, Read, Update, and Delete. In part 2, we’ll look at R for Read, and build an ASP.NET Core endpoint to read data from Couchbase using SQL.

Make sure to read part 1 of this series, which covers setup and configuration of your ASP.NET Core “wishlist” project.

SQL++ to read

Couchbase is unique among NoSQL databases in that it supports a full SQL implementation (called SQL++, née N1QL) to query JSON data.

SQL++ is not a watered-down “SQL-like” language. With SQL++, you have JOINs, CTEs/WITH, UPDATE, INSERT, DELETE, MERGE, aggregation/GROUP BY, BEGIN/COMMIT/ROLLBACK, and more.

On top of that (the “++”), you also get features to deal with JSON data, like MISSING, NEST, ARRAY_* functions, OBJECT_* functions, and more.

For this simple CRUD application, we’ll use a SQL++ SELECT query (and index) to return all items from my wishlist.

Writing your first SQL++ query

First, let’s try writing a SQL++ query to get all wishlist items right in the Capella Query Workbench.

To start with, try:

When you do this, you should get an error message. Something like:

This is expected behavior. (Most) SQL++ queries in Couchbase will not run unless there is at least one index available for them.

No problem. Create a simple PRIMARY INDEX with a command like this:

Primary indexes are generally not meant to be used in a typical production environment, but they are very helpful for a development environment, since they guarantee that any SQL++ query will run on the indexed collection (though not as efficient as a properly indexed collection). Once you start creating more complex SQL++ queries, you can use the “Advise” button on the Query Workbench to get suggestions of more efficient indexes to create (and you should avoid using SELECT * whenever you can 😆).

After creating the index, retry the above SELECT query again, and the results should look like this:

Almost there. Try imagining this array of objects being serialized to a C# List<WishlistItem>. It wouldn’t quite work, because the objects are nested with the collection name. So, I’ve gotten into a habit of aliasing the collections, like this:

Which produces the result:

Looking good, but there’s still something missing. Where are those GUIDs that we used for the document keys? Couchbase doesn’t store them as data; it stores them as metadata. SQL++ provides the META() function to query metadata. Use META().id like this:

And that finally gives us a result of:

This will serialize nicely into WishlistItem objects, using the class created in part 1.

Using SQL++ in ASP.NET Core

Let’s get that SQL++ query we just wrote into an ASP.NET Core endpoint.

In GiftsController, create a endpoint called GetAll:

To execute SQL++, we need to get an object of type Cluster. SQL++ runs at the cluster level (not bucket, or scope, or collection, since it may need to JOIN/UNION between them). We could go back and add ClusterProvider as a constructor parameter. If this endpoint was going to only work with SQL++, that would be a good idea. However, let’s stick with what we created in part 1. We have an object of type BucketProvider. From that object, you can get a object of type Cluster:

A cluster object is how ASP.NET Core will interact with a Couchbase cluster in a variety of ways. For now, we’re interested in its QueryAsync<T> method:

Make sure you have the following using statements at the top of your GiftsController.cs file:

One more thing to note. When executing SQL++, there are a number of (scan) consistency options. The default is ScanConsistency.NotBounded. This setting means that the query engine will not wait on indexes to finish updating before returning results. This is the most performant option. However, in some situations, you will want stronger index consistency. Couchbase provides RequestPlus and AtPlus.

Try out the ASP.NET Core Endpoint

From Visual Studio, Ctrl+F5 will start the app. You should see an OpenAPI / Swagger page in your browser.

ASP.NET app reading from Couchbase

(Ignore WeatherForecast, that just came with the Visual Studio template).

Click on the endpoint to try it out. There are no parameters to specify, so just click Execute.

Testing an ASP.NET endpoint and Couchbase

You now have the “R” of CRUD in place.

What’s next?

The ASP.NET Core project is connected to Couchbase Capella, and it is reading data via SQL++.

In the next blog post, we’ll create another “read” endpoint. Instead of SQL++, we’ll look at another, faster way that data can be accessed and read.

In the meantime, you should:

Author

Author

Posted by Matthew Groves

Matthew D. Groves is a guy who loves to code. It doesn't matter if it's C#, jQuery, or PHP: he'll submit pull requests for anything. He has been coding professionally ever since he wrote a QuickBASIC point-of-sale app for his parent's pizza shop back in the 90s. He currently works as a Product Marketing Manager for Couchbase. His free time is spent with his family, watching the Reds, and getting involved in the developer community. He is the author of AOP in .NET (published by Manning), a Pluralsight author, and a Microsoft MVP.

Leave a reply