Today’s the day your Node.js app learns “go fetch.”

By following this basic tutorial, you’ll learn how to build a REST API for your Node.js app that sends N1QL / SQL queries to retrieve data from Couchbase.

This post continues my introductory series on using Node.js with Couchbase, including async functions and building a REST API with Node.js and Express.

Today’s post picks up where the previous ones left off after building a REST API for direct document retrieval.

Setting Up Your Couchbase Database

This post assumes you have installed the travel-sample Bucket that comes with all Couchbase installations. It’s important you have this dataset installed because we’ll use indexes that are automatically created and specific document criteria will be used that are entirely repeatable.

The basic connectivity and REST API foundation is already laid out in the script we created together last week. I’ll include it in today’s code sample, but you’ll need to read all of the previous posts (linked above) for a full explanation.

Also, for the sake of simplicity in today’s example, we’ll use the default Scopes and Collections. As usual, I’ll also assume you have basic familiarity with JavaScript, Node.js and NoSQL document databases.

Understanding N1QL Queries

The N1QL query language for Couchbase is almost exactly like SQL, with just a few exceptions.

When a N1QL query requests a specific column from a Bucket, it returns a list of all matching JSON documents in the Bucket. If there is no data in the document for the column then you get a NULL value, unless it is filtered out with a WHERE clause.

At a minimum, a primary index must be available on the documents in order for those documents to be queried. However, query performance is improved when there is a secondary index defined for a given column.

Both of these indexes can be created with a query itself and can be done through a script or through the Couchbase Web Console. In fact, you can learn about these indexes and see the query used to create them by exploring them in the web console.

Couchbase web console showing query index being created

In the above example, there is an index created on the column called city within any documents that are in the travel-sample Bucket.

A Sample Query

Before diving into code, you can run queries directly in the web console to ensure they return what you expect. Switch to the Query tab and enter the following query, then press Execute.

The results show in the console that 323 documents matched. Note that a mix of documents is returned – hotels, airports, etc. as shown in the type column. (Now, I’m saying “columns” but of course these are actually JSON objects/elements that could be embedded inside other objects.)

Tweak your query to return just a few columns, like in the example below.

Couchbase N1QL Query of Travel-sample database

Building a Query Function

Before diving into the rest of the code, we’ll start by looking at the basics of passing queries using the Node.js SDK.

Rather than pass a fully-formed query string, you’ll want to use the built-in placeholders to make it reusable code. In this case, use named parameters: Your query will have a placeholder variable in it, and you’ll pass a variable with the values to be used.

First create the query variable:

Then create the input parameters variable:

Then pass both of these along to the cluster query function to initiate and return results:

The full function you create is now possible:

REST-ifying Your N1QL Query

The next step is to add this query function to our previous REST API code example so you can take an input city name and return HTTP results back to the browser.

First, make the function pass in a variable for the city name, like below:

Then, create a query endpoint (instead of the get route we used in an earlier post) and move the logic for the REST response into that function.

This returns the RAW JSON back from the REST call, as shown in the image below in the web browser or in the Postman REST API tool.

couchbase N1QL query response from a REST API with node.js

Full Code Example

Here is the full sample code result. Note that some previous code from other articles is retained for your reference.

Conclusion

There are a lot of new directions you can take this project. For starters, you might want to include proper error capturing, output log information to the console, or using the results.rows object to create tabular results.

I’d encourage you to take a deeper dive into the Couchbase documentation on using N1QL queries from the Node.js SDK.

Ready to roll up your sleeves and do it yourself?

Test out Couchbase 7 today

 
 
 

Author

Author

Posted by Tyler Mitchell

A guest blogger, advisor, consultant, and writer on database topics with a focus on product marketing ideas and a specialty in geospatial topics. Tyler worked as a Couchbase Product Manager (SDK, Full-Text Search) and as a Product Marketing Manager. See LinkedIn for more details on books he's written and other roles in the database ecosystem.

Leave a reply