©2015 - 2022 Chad’s Technoworks. Disclaimer and Terms of Use

Chad’s TechnoWorks My Journal On Technology

Information Technology

COUCHBASE CRUD OPERATIONS USING CBC

TABLE OF CONTENTS


CBC Commands


CRUD Operations

  CBC Create A Bucket Example


  C = Create A Document

     CBC Insert By Example

     N1QL Insert By Example


  R = Read A Document

     CBC Cat By Example

     N1QL Query By Example

         Create Primary Index

         N1QL Select Statement


  U = Update A Document

     CBC Replace By Example

     N1QL Update By Example

        Update To Change Value Of Attribute

        Update To Add A New Attribute To A Document

        Querying Arrays

        Update To Remove An Attribute From The Document

     Upsert To Add A Document If It Doesn't Exist

        CBC Upsert By Example

        N1QL Upsert By Example


 D = Delete A Document

    CBC Delete By Example

    N1QL Delete By Example

       Delete By Using A Key

       Delete By Using Conditions



CBC COMMANDS


For more details, read Couchbase C Client documentation.


[chad@lxnode4 ~]$ cbc help

Usage: cbc <command> [options]

command may be:

   help                Show help

   cat                 Retrieve items from the cluster

   create              Store item to the server

   observe             Obtain persistence and replication status for keys

   observe-seqno       Request information about a particular vBucket UUID

   incr                Increment a counter

   decr                Decrement a counter

   mcflush             Flush a memcached bucket

   hash                Get mapping information for keys

   lock                Lock keys and retrieve them from the cluster

   unlock              Unlock keys

   rm                  Remove items from the cluster

   stats               Retrieve cluster statistics

   version             Display information about libcouchbase

   verbosity           Modify the memcached logging level

   view                Query a view

   n1ql                Execute a N1QL Query

   admin               Invoke an administrative REST API

   bucket-create       Create a bucket

   bucket-delete       Delete a bucket

   bucket-flush        Flush a bucket

   connstr             Parse a connection string and provide info on its components

   write-config        Write the configuration file based on arguments passed

   strerror            Decode library error code

[chad@lxnode4 ~]$


To Create/Update Documents

  cbc create <attributes>

  cbc-create  <attributes>  



To Retrieve A Document

  cbc cat <attributes>

  cbc-cat <attributes>


To Execute A Query

  cbc n1ql <query>

  cbc-n1ql <query>

  

To Remove a Document

  cbc rm <doc>

  cbc-rm <doc>

  

Use the following parameter for connecting to a Couchbase cluster by supplying the hostname of one of its nodes.


-U couchbase://hostname

Or,

-U http://hostname:<port>/<optional: bucket> -P <SASL bucket password>


example:

cbc n1ql <your query> -U couchbase://lxnode2cb-vip


Command symbolic link are resented as cbc-<command>, i.e. cbc-cat.

These symbolic links are useful for piping from standard input.


CBC Query By Example


Query 1


[chad@lxnode4 ~]$ cbc n1ql 'SELECT airportname, city FROM `travel-sample` WHERE type="airport" LIMIT 2' \

> -U couchbase://lxnode1cb-vip.vlabs.net

Encoded query: {"statement":"SELECT airportname, city FROM `travel-sample` WHERE type=\"airport\" LIMIT 2"}


{

            "airportname": "Calais Dunkerque",

            "city": "Calais"

        },

{

            "airportname": "Peronne St Quentin",

            "city": "Peronne"

        },

** N1QL Response finished

{

    "requestID": "9c94731b-ffc5-4076-9186-4b095407f0e6",

    "signature": {

        "airportname": "json",

        "city": "json"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "299.912522ms",

        "executionTime": "298.784563ms",

        "resultCount": 2,

        "resultSize": 177

    }

}


[chad@lxnode4 ~]$




CRUD OPERATIONS USING CBC


NOTE: Couchbase uses JSON format to store its documents. I will not be discussing about the syntax and definition of JSON in this article.

To learn more about JSON document syntax check this site www.w3schools.com/json   


CRUD is an acronym for a series of data manipulation operations of a NoSQL database.

C = Create

R = Read

U = Update

D = Delete


Before we get started with our CRUD exercise, we need to create a bucket to house our documents. In some documentations, this is

sometimes referred to as namespace.


Create A Bucket

Using cbc, let's create a bucket called CRUD_Lab.


cbc bucket-create --bucket-type=couchbase --ram-quota=100 \

-u Administrator -P - -U http://lxnode5.vlabs.net CRUD_Lab


Since the above command requires an administrator privilege, you have to indicate an admin user ("-u") and password ("-P").

The parameter "-P -" will prompt a password to be entered when the command is executed, otherwise you have to supply the password

along with the parameter to avoid being prompted which is useful on scripted implementations.

If you want the bucket to have a password, use "--password <your password>".


Sample output:


[couchbas@lxnode4 ~]$ cbc bucket-create --bucket-type=couchbase --ram-quota=100 \

> -u Administrator -P - -U http://lxnode5.vlabs.net CRUD_Lab

Requesting /pools/default/buckets

Bucket password:

202

  Cache-Control: no-cache

  Content-Length: 0

  Date: Mon, 11 Jan 2016 17:09:01 GMT

  Location: /pools/default/buckets/CRUD_Lab

  Pragma: no-cache

  Server: Couchbase Server

[couchbas@lxnode4 ~]$




C = CREATE A DOCUMENT


Here is a JSON Doc sample of a record found in Oracle database Scott schema.


{

"empno": 7369,

"ename": "SMITH",

"job": "CLERK",

"mgr": 7902,

"hiredate": "1980/12/17",

"sal": 800,

"comm": 0,

"dept": { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }

}


For demonstration purposes, I have stored the above entries in smith.json file.

The JSON doc structure shown above is derived from 2 tables - EMP and DEPT.

Since JSON doc is primarily built in first normal form, the JSON doc above has the department details embedded within the employee details.

Thus, the resulting format is a nested JSON doc.



CBC Insert By Example


The CBC is flexible to accept pipe commands from standard input.


Example:

cbc create --mode insert -U http://lxnode5.vlabs.net/CRUD_Lab emp_7369 < smith.json


Note the URI provided includes the target bucket (CRUD_Lab) of where the data is to be created.


[couchbas@lxnode4 Documents]$ cbc create --mode insert -U http://lxnode5.vlabs.net/CRUD_Lab emp_7369 < smith.json

emp_7369            Stored. CAS=0x4277ef7a2814

[couchbas@lxnode4 Documents]$


N1QL Insert By Example


For Insert syntax reference, read Couchbase N1QL Insert Statement article.


Here’s an example to insert a new record for employee named “Count” using N1QL.


cbc n1ql -U http://lxnode5.vlabs.net \

'INSERT INTO CRUD_Lab (key,value) VALUES ("emp_1234",{"empno": 1234,"ename": "COUNT","job": "MUPPET","mgr": 7902,"hiredate": "1989/10/15","sal": 900,"comm": 0,"dept": { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }}) RETURNING *'


[couchbas@lxnode4 Documents]$ cbc n1ql -U http://lxnode5.vlabs.net \

> 'INSERT INTO CRUD_Lab (key,value) VALUES ("emp_1234",{"empno": 1234,"ename": "COUNT","job": "MUPPET","mgr": 7902,"hiredate": "1989/10/15","sal": 900,"comm": 0,"dept": { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }}) RETURNING *'

Encoded query: {"statement":"INSERT INTO CRUD_Lab (key,value) VALUES (\"emp_1234\",{\"empno\": 1234,\"ename\": \"COUNT\",\"job\": \"MUPPET\",\"mgr\": 7902,\"hiredate\": \"1989/10/15\",\"sal\": 900,\"comm\": 0,\"dept\": { \"deptno\": 20, \"dname\": \"RESEARCH\", \"loc\": \"DALLAS\" }}) RETURNING *"}


{

            "CRUD_Lab": {

                "comm": 0,

                "dept": {

                    "deptno": 20,

                    "dname": "RESEARCH",

                    "loc": "DALLAS"

                },

                "empno": 1234,

                "ename": "COUNT",

                "hiredate": "1989/10/15",

                "job": "MUPPET",

                "mgr": 7902,

                "sal": 900

            }

        },

** N1QL Response finished

{

    "requestID": "f9056bde-6bfb-4ebb-ac2e-03c8c84d62a7",

    "signature": {

        "*": "*"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "10.914613ms",

        "executionTime": "10.803684ms",

        "resultCount": 1,

        "resultSize": 430,

        "mutationCount": 1

    }

}


[couchbas@lxnode4 Documents]$




R = READ A DOCUMENT


CBC Query By Example



[couchbas@lxnode4 Documents]$ cbc cat emp_7369 -U http://lxnode5.vlabs.net/CRUD_Lab

emp_7369             CAS=0x4277ef7a2814, Flags=0x0. Size=180

{

"empno": 7369,

"ename": "SMITH",

"job": "CLERK",

"mgr": 7902,

"hiredate": "1980/12/17",

"sal": 800,

"comm": 0,

"dept": { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }

}


[couchbas@lxnode4 Documents]$



N1QL Query By Example


In order for N1QL query to work, an index has to exist. If an index doesn’t exist to satisfy the “where” clause, it defaults to use the primary index.

In our case since there’s no index yet for our newly created documents, let’s create at least the primary keys.


Create Primary Index


[couchbas@lxnode4 Documents]$ cbc n1ql 'CREATE PRIMARY INDEX ON `CRUD_Lab`' -U http://lxnode5cb-vip.vlabs.net

Encoded query: {"statement":"CREATE PRIMARY INDEX ON `CRUD_Lab`"}


** N1QL Response finished

{

    "requestID": "c31fe699-f975-4dc0-b2f7-dcf732d5a783",

    "signature": null,

    "results": [

    ],

    "status": "success",

    "metrics": {

        "elapsedTime": "2.904555634s",

        "executionTime": "2.903092679s",

        "resultCount": 0,

        "resultSize": 0

    }

}


[couchbas@lxnode4 Documents]$



N1QL Select Statement


For N1QL Select syntax reference, refer to Couchbase N1QL Select Statement article. See also N1QL query language structure.


Example to query specific fields and document:


cbc n1ql 'SELECT empno,ename,job FROM CRUD_Lab WHERE empno = 7369' -U http://lxnode5.vlabs.net


[couchbas@lxnode4 Documents]$ cbc n1ql 'SELECT empno,ename,job from CRUD_Lab where empno = 7369' -U http://lxnode5.vlabs.net

Encoded query: {"statement":"SELECT empno,ename,job from CRUD_Lab where empno = 7369"}


{

            "empno": 7369,

            "ename": "SMITH",

            "job": "CLERK"

        },

** N1QL Response finished

{

    "requestID": "4f02db16-f28a-4907-a13d-036926256805",

    "signature": {

        "empno": "json",

        "ename": "json",

        "job": "json"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "29.13874ms",

        "executionTime": "29.058544ms",

        "resultCount": 1,

        "resultSize": 95

    }

}


[couchbas@lxnode4 Documents]$



Example to list all documents:


[couchbas@lxnode4 ~]$ cbc n1ql 'select * from CRUD_Lab'  -U http://lxnode5.vlabs.net

Encoded query: {"statement":"select * from CRUD_Lab"}


{

            "CRUD_Lab": {

                "comm": 0,

                "dept": {

                    "deptno": 20,

                    "dname": "RESEARCH",

                    "loc": "DALLAS"

                },

                "empno": 1234,

                "ename": "COUNT",

                "hiredate": "1989/10/15",

                "job": "MUPPET",

                "mgr": 7902,

                "sal": 900

            }

        },

{

            "CRUD_Lab": {

                "comm": 0,

                "dept": {

                    "deptno": 20,

                    "dname": "RESEARCH",

                    "loc": "DALLAS"

                },

                "empno": 7369,

                "ename": "SMITH",

                "hiredate": "1980/12/17",

                "job": "CLERK",

                "mgr": 7902,

                "sal": 800

            }

        },

** N1QL Response finished

{

    "requestID": "ea2e05f6-512e-44a2-902a-6e7ee096f58b",

    "signature": {

        "*": "*"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "152.868041ms",

        "executionTime": "151.592336ms",

        "resultCount": 2,

        "resultSize": 859

    }

}


[couchbas@lxnode4 ~]$



More Query examples:


Querying Arrays Using N1QL


 1 2 3 > Next