Best Practices for using N1QL in Couchbase Cluster

[atom_yang]

I want my application which is wrote by NodeJS to use N1QL to query data from Couchbase cluster,
Following is two options for me:

1. Use Couchbase NodeJS SDK to query data from couchbase cluster;
   PRO:
   NodeJS SDK will manage couchbase cluster,application only focus on businuss；
   CON:
   N1QL will be inclueded in application,I want to separate data handle from application；
   All N1QL will be executed with process of parsing and compiling execution plans;

2. Use REST API to query data by PREPARE/EXECUTE statement;
   PRO:
   All query logic can package in REST API;
   N1QL will be optimized execution without process of parsing and compiling execution plans;
   CON:
   application should manage couchbase cluster(query service node)；

My choice is 2. I want to deploy a nginx server to loadbalnace and fail over the query service, there are two questions here:

1. How to configrue nginx server to loadbalnace and fail over the query service?
   As keshav_m suggest here,can some one give me some more detail information,such as health check of REST API endpoint/result explain/even nginx configuration
2. How to auto reload PREPARE statement after one node restart(fail over/restart/rebalnace etc)?
   prasad suggest here,but how to add it into couchbase startup script?

[geraldss]

Hi @atom_yang,

A few thoughts. You can use NodeJS or any SDK to implement your REST APIs, and you will still get application separation. You can also use prepared statements with SDKs, and you will get cluster management.

For option 2, prepared statements contain the query plan. They can be fully self-contained if you use both the name and the encoded plan. There is no need to handle restart / failover / rebalance / etc.

[prasad]

Hi Atom_yang,

1. Node SDK also uses or REST API internally to communicate with query service.
2. irrespective of API/SDK,

• process of parsing and compiling execution plans is needed for all queries[quote=“atom_yang, post:1, topic:9503”]
  NodeJS SDK will manage couchbase cluster,application only focus on businuss；
  [/quote]

SDK has API to manage cluster, but your app will need to manage the cluster (or using web-console)

@atom_yang

N1QL will be inclueded in application,I want to separate data handle from application；

Its more to do with modularity/design of your app. You can have data-access layer that deals with N1QL/data access.

@atom_yang

All N1QL will be executed with process of parsing and compiling execution plans;

You can use prepare statements with SDK as well. Irrespective of SDK/REST, query execution will be optimized when using prepared statements.[quote=“atom_yang, post:1, topic:9503”]
application should manage couchbase cluster(query service node)；
[/quote]
This is also common for both approaches. Either application manages the cluster, or admin can manage it with web-console.

@atom_yang

1. How to configrue nginx server to loadbalnace and fail over the query service?
   give me some more detail information,such as health check of REST API endpoint/result explain/even nginx configuration

REST api provides cluster/nodes info and stats.

Couchbase SDKs

You can implement custom health check using the API. Or, use/configure auto-failover feature built-in couchbase.

@atom_yang

prasad suggest here,but how to add it into couchbase startup script?

I am referring to any shell script you may have to start cbq-engine. Or, you can save all prepared-queries in a file, and run them with cbq after starting the query service. See cbq documentation at

Couchbase SDKs

[geraldss]

@prasad

I am referring to any shell script you may have to start cbq-engine. Or, you can save all prepared-queries in a file, and run them with cbq after starting the query service. See cbq documentation at Couchbase SDKs

This is only needed if your REAST API layer or application completely shuts down. In that case, your code should re-prepare the queries anyway. If you add a new query node, there is no need to save prepared queries. Just keep them in memory within your REST API layer.

[atom_yang]

@geraldss

prepared statements contain the query plan. They can be fully self-contained if you use both the name and the encoded plan. There is no need to handle restart / failover / rebalance / etc.

yes, I have tested this way by query data with encoded plan,after I restart couchbase server,it really work.
but there are some insufficients:

1. I can not get the avgElapsedTime and uses by query system:prepareds;
2. encoded plan equals to N1QL in application, I am not get absolute query logic separation.

but this way is a option, Thank you very much.