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:
|
1 |
SELECT * FROM demo._default.wishlist; |
When you do this, you should get an error message. Something like:
|
1 |
`no index available on keyspace default:demo._default.wishlist that matches your query. Use CREATE PRIMARY INDEX` ... |
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:
|
1 |
CREATE PRIMARY INDEX `ix_wishlist` ON `demo`.`_default`.`wishlist` |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[ { "wishlist": { "name": "Skyline Chili T-Shirt" } }, { "wishlist": { "name": "Joey Votto jersey" } } ] |
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:
|
1 |
SELECT w.* FROM demo._default.wishlist w; |
Which produces the result:
|
1 2 3 4 5 6 7 8 |
[ { "name": "Skyline Chili T-Shirt" }, { "name": "Joey Votto jersey" } ] |
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:
|
1 |
SELECT META(w).id, w.* FROM demo._default.wishlist w; |
And that finally gives us a result of:
|
1 2 3 4 5 6 7 8 9 10 |
[ { "id": "2dab198b-1836-4409-9bdf-17275a2b2462", "name": "Skyline Chili T-Shirt" }, { "id": "31c9cc33-8dfe-440c-bd1b-bb038939d2e0", "name": "Joey Votto jersey" } ] |
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:
|
1 2 3 4 5 6 |
[HttpGet] [Route("api/getall")] public async Task<IActionResult> 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:
|
1 2 3 4 5 6 7 8 9 |
[HttpGet] [Route("api/getall")] public async Task<IActionResult> GetAll() { var bucket = await _bucketProvider.GetBucketAsync("demo"); var cluster = bucket.Cluster; // ... snip ... } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[HttpGet] [Route("api/getall")] public async Task<IActionResult> GetAll() { var bucket = await _bucketProvider.GetBucketAsync("demo"); var cluster = bucket.Cluster; var result = await cluster.QueryAsync<WishlistItem>( "SELECT META(w).id, w.* FROM demo._default.wishlist w;" ); return Ok(result); } |
Make sure you have the following using statements at the top of your GiftsController.cs file:
|
1 2 3 4 5 |
using AspNetCoreTutorial.Models; using Microsoft.AspNetCore.Mvc; using Couchbase; using Couchbase.Query; using Couchbase.Extensions.DependencyInjection; |
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.
(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.
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:
-
- Sign up for a Capella free-trial
- Check out the Couchbase Playground for .NET examples that you can run right in the browser.
