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, INSERIR, DELETE, MERGE, aggregation/GROUP BY, BEGIN/COMMIT/ROLLBACKe muito mais.
On top of that (the “++”), you also get features to deal with JSON data, like MISSING, NEST, ARRAY_* functions, OBJECT_* functionse muito mais.
For this simple CRUD application, we’ll use a SQL++ SELECIONAR query (and index) to return todos 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 |
SELECIONAR * DE demonstração.Padrão.wishlist; |
When you do this, you should get an error message. Something like:
|
1 |
`não índice disponível em keyspace padrão:demonstração.Padrão.wishlist que matches seu consulta. Uso CRIAR PRIMÁRIO ÍNDICE` ... |
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 |
CRIAR PRIMÁRIO ÍNDICE `ix_wishlist` ON `demonstração`.`Padrão`.`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 qualquer 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 SELECIONAR query again, and the results should look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[ { "wishlist": { "nome": "Skyline Chili T-Shirt" } }, { "wishlist": { "nome": "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 |
SELECIONAR w.* DE demonstração.Padrão.wishlist w; |
Which produces the result:
|
1 2 3 4 5 6 7 8 |
[ { "name" (nome): "Skyline Chili T-Shirt" }, { "name" (nome): "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 metadados. SQL++ provides the META() function to query metadata. Use META().id like this:
|
1 |
SELECIONAR META(w).id, w.* DE demonstração.Padrão.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" (nome): "Skyline Chili T-Shirt" }, { "id": "31c9cc33-8dfe-440c-bd1b-bb038939d2e0", "name" (nome): "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.
Em GiftsController, create a endpoint called GetAll:
|
1 2 3 4 5 6 |
[HttpGet] [Rota("api/getall")] público assíncrono Tarefa<IActionResult> GetAll() { } |
To execute SQL++, we need to get an object of type Aglomerado. SQL++ runs at the cluster level (not bucket, or scope, or collection, since it may need to JUNTAR/UNIÃO 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 Aglomerado:
|
1 2 3 4 5 6 7 8 9 |
[HttpGet] [Rota("api/getall")] público assíncrono Tarefa<IActionResult> GetAll() { var balde = aguardar _bucketProvider.GetBucketAsync("demo"); var agrupamento = balde.Aglomerado; // ... snip ... } |
A agrupamento 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> método:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[HttpGet] [Rota("api/getall")] público assíncrono Tarefa<IActionResult> GetAll() { var balde = aguardar _bucketProvider.GetBucketAsync("demo"); var agrupamento = balde.Aglomerado; var resultado = aguardar agrupamento.QueryAsync<WishlistItem>( "SELECT META(w).id, w.* FROM demo._default.wishlist w;" ); retorno Ok(resultado); } |
Make sure you have the following usando statements at the top of your GiftsController.cs file:
|
1 2 3 4 5 |
usando AspNetCoreTutorial.Models; usando Microsoft.AspNetCore.Mvc; usando Couchbase; usando Couchbase.Consulta; usando 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 não 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 e 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 Executar.
You now have the “R” of CRUD in place.
O que vem a seguir?
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
- Confira o Couchbase Playground for .NET examples that you can run right in the browser.
