Tag Archives: NoSQL

CRUD with CouchDB in Node.js

I started working with database management systems with FoxPro 2.6 which seemed back in the days extremely powerful to me, until 2000 when I learned MySQL, which was a true relational database management system. Then 1-year on Oracle, and then for the rest of my life from 2005 I worked on SQL Server.

When I was approaching MEAN stack, obviously MongoDB was a natural choice, but due to coming from a RDBMS background, I was into more RDBMS sort of production deployment style of a NoSQL database. Ensuring security of MongoDB on production seemed to me a nightmare, or perhaps I was simply too lazy to find a more convenient way out. I just wanted to implement old-school user/pass authentication way of securing a public database. Apache CouchDB does just that. Another selling point to me was that CouchDB exposes a true REST endpoint, which feels even more natural to me especially since I was approaching the *EAN stack.

Last, but not least, tell me Windows has spoiled me over the years, but I still rely more on a dashboard/admin control panel of a database than command-line, not that you can do everything from the dashboard though and still a lot of the things you need to do executing cURLs/code, etc. Therefore, I embraced CouchDB.

Installing on Desktop

Installing Apache CouchDB is fairly simple. Just go ahead and download a Windows installer (.exe). When it’s finished install it. That’s it. It is important to have it installed in your dev machine in order to make it convenient for you to write your code against.

Installing on Azure

There are several ways you can approach this. You can download and install it on your own Windows/Linux VM and configure the ports. Here are the ports configuration: public ports (5984, 6984) and Local ports (5984, 6984).

Perhaps if you’re lazy like me, you would choose a pre-configured Azure VM at the VM Depot here. For this particular post, I am going to choose CouchDB on Ubuntu. You can easily follow along the instruction and get started with deploying to your Azure account.

1464_1_030AF84E

Here are the steps:

  1. Download Publish settings of your Azure account, by visiting this. If asked provide with your login information of the account that’s associated with Microsoft Azure.
  2. Visit the CouchDB on Ubuntu VM at VM Depot here.
  3. Click on Create Virtual Machine, and if it asks you to login, do so using your Microsoft Account associated with Azure. Provide with basic information and also keep the user and password handy.
  4. It will then ask you to drag and drop the Publish settings file that you have downloaded in Step 1. Do that. It will take nearly 30 minutes to complete configuring the VM to be used.

Configuring CouchDB

Now that the CouchDB is installed, rest of the configuration setups are all the same. You will be given a dashboard to setup your database. Depending on your installation you may access it via the following links:

Lets go ahead and quickly check the CouchDB installation by navigating to http://localhost:5984/, and you will likely to receive a JSON response similar to the following:

{"couchdb":"Welcome","uuid":"cad5a00c59c76086cb65d7bf6391f3b7","version":"1.6.1","vendor":{"version":"1.6.1","name":"The Apache Software Foundation"}}

Connecting to the database from Command Prompt via HTTP

There’s a nice little tool called cURL, which allows you to connect internet via HTTP and other protocols, and it’s a free and open software. I am going to show how we can use this tool to connect to our database from Command Prompt. I have used this particular installer. curl command will be available by elevating Command Prompt, with Administrator Privileges. Let us see an example:

curl tanzimsaqib.com

This command will open up my website and print out the HTML on the console. As you can guess, the next command we will try is the following:

curl http://localhost:5984

As you can imagine, that will print out the JSON response we have seen earlier. Now let’s securely connect to Couch with any of the users created above, eg. either admin or the regular testuser:

curl http://testuser:testpassword@localhost:5984/

Securing CouchDB

By default, CouchDB is completely open and exposed to http://your-dns-name-here.cloudapp.net:5984/ or for local computer http://localhost:5984/ as stated above. Anyone can go ahead, use the dashboard and delete all databases without asking for any credential whatsoever. Therefore, you must create an admin to secure the database because once your database is up on the public cloud, it’s accessible to everybody.For example: in order to create a new database, you can always go to Databases tab and click Add New Database. Really anyone can go ahead and check out the rows without needing to login at all. Go to http://your-dns-name-here.cloudapp.net:5984/_utils/fauxton/#createAdmin and create an admin immediately, and of course store the user/password pair.

1680_2_780388A7

Now that we have secured the basic administration privileges of CouchDB, yet your databases are still exposed. For example: in order to create a new database, you can always go to Databases tab and click Add New Database. Really anyone can go ahead and check out the rows without needing to login at all.

3806_4_2CEFCC3E

Creating a database user

I will switch back to the traditional dashboard now, so you get to see how similar both the dashboards are. Go to _users table by navigating to http://your-dns-name-here.cloudapp.net:5984/_utils/database.html?_users, so that you may click on “New Document.” Complete a document like so and “Save Document” it:

4621_CouchDBnewuser_100E597C

Do not worry about your password – it won’t be saved in clear text. Once you have saved, you will be able to see the following screen which indicates that your password was encrypted before it was saved – voila! You’ve a secure database now:

3404_CouchDBnewuser2_3A137906

Creating the first database

Lets go ahead and create our first database. Like I have mentioned above CouchDB exposes a true REST endpoint, we can create a database from Command Prompt:

curl -X PUT http://localhost:5984/testdb

You will get the following response, which means you need to authenticate with a proper user to create a database:

{"error":"unauthorized","reason":"You are not a server admin."}

Here is how you can create a new database named testdb:

curl -X PUT http://youradminuser:youradminpassword@localhost:5984/testdb

Here’s a response of a successful CouchDB operation:

{"ok":true}

Once you would like to specify a HTTP Method, you have to use –X in the curl command. Here, because we were creating a new database we have specified PUT method. By the way, you can always create and manage database from the dashboard.

Securing a database

General principle of CouchDB is that databases are public unless you specify an user and its permission level to the database. Here’s how you can do it. Open your database and click “Security…” which will bring up the following screen. Go ahead and input the newly created username so that this user can get access to that database:

4331_7_4924F915

 

When you are going to create and update design documents later on, you would need an user to authenticate against and execute the operation, hence often times, it’s better to put Admins and Members with the same Names and Roles. eg. same values in the text fields.

Accessing the Linux VM

Remember, at the beginning you have completed a pair of user/password for your Linux VM which we haven’t used yet. We can connect to the VM using a terminal environment and for that I use PuTTY. It’s a nice little tool and it gets the job done. Put your DNS name there and Open:

5707_5_353FA33C

It will then launch a terminal window where you can put that user/password pair and logon to your Linux VM:

5076_6_603F4354

Accessing from Node.js

This is the point from where you will start to pull out your hairs. There are couple popular middlewares namely nano and cradle that enable your applications to talk to CouchDB from Node.js, but there are tradeoffs, personal opinions, API design choices and in some cases inadequate documentation influence your decision, but there’s no reason why I have chosen nano. These libraries essentially makes the REST API calls to CouchDB under the hood, giving us syntactic sugar so that we can be more productive. On the other hand, ideally you should be using one of the nice frameworks such as express/sails as well, but I am showing in a barebones Node.js program how you can access CouchDB database.

Writing the first Node.js application

Here’s a hello world Node.js application which if you point browser to http://localhost:8000 it will say Hello World. Fair enough.

 
var http = require('http'); 
var server = http.createServer(function (request, response) { 
	response.writeHead(200, { "Content-Type": "text/plain" }); 
	response.end("Hello World\n"); 
}); 

server.listen(8000); 
console.log("Server running at http://127.0.0.1:8000/"); 

Here’s how you can run a Node.js application. If you have saved the file as app.js, you can execute node app.js.

Installing & Setting up nano

Execute the following command to install nano into your application:

npm i --save nano

Lets create a database from code:

 
var nano = require("nano")("http://localhost:5984"); 
var http = require("http"); var server = http.createServer(function (request, response) { 
	nano.db.create("mylibrary", function (err, body, header) { 
		if (err) { 
			response.writeHead(500, { "Content-Type": "text/plain" }); 
			response.end("Database creation failed. " + err + "\n"); 
		} else { 
			response.writeHead(200, { "Content-Type": "text/plain" }); 
			response.end("Database created. Response: " + JSON.stringify(body) + "\n"); 
		} 
	}); 
}); 

server.listen(8000); 
console.log("Server running at http://127.0.0.1:8000/");

As you can see here we are referring to the nano library and initializing with the connection string to the CouchDB server. Later on we have attempted to create a mylibrary database and if we point to the browser it will show as below:

Database creation failed. Error: You are not a server admin.

What would we need to make it a success? Yes, you’ve guessed it right. We need a way to authenticate first in order to execute such operation. Just go ahead and change the top line and it would work:

 
var nano = require('nano')('http://youradminuser:youradminpassword@localhost:5984'); 

Now run again, and you will find the following output in the browser:

Database created. Response: {"ok":true}

Inserting a new object

Consider CouchDB databases a dictionary where you can put a value against a key, popularly known as key-value pair (KVP). Databases are also sometimes referred to document store, because they store JSON documents. All our documents are fully JavaScript qualified JSON objects. The following code creates a book object, and stores it with ISBN as its key, so that next time we query for the book with the ISBN, it will be able to identify and retrieve the object (in this case book) for us.

 
var nano = require("nano")("http://youradminuser:youradminpassword@localhost:5984"); 
var http = require("http"); 
var server = http.createServer(function (request, response) { 
	var book = { 
		Title: "A Brief History of Time", 
		Author: "Stephen Hawking", 
		Type: "Paperback – Unabridged, September 1, 1998", 
		ISBN: "978-0553380163" 
	}; 
	
	nano.use("mylibrary").insert(book, book.ISBN, function(err, body, header) { 
		if(err) { 
			response.writeHead(500, { "Content-Type": "text/plain" }); 
			response.end("Inserting book failed. " + err + "\n"); 
		} else { 
			response.writeHead(200, { "Content-Type": "text/plain" }); 
			response.end("Book inserted. Response: " + JSON.stringify(body) + "\n"); 
		} 
	}); 
}); 

server.listen(8000); 
console.log("Server running at http://127.0.0.1:8000/"); 

Now if you take a look at the dashboard, and drill down to the database you will be able to see the object that you’ve just inserted:

Untitled

Note that an extra _rev field is there which keeps track of the revisions of the documents. Every time you will update this document, it will increment the _rev field.

Querying for an object

Welcome to the concept of design documents. Design documents are special documents that contain application code. There’s no direct command/operation for querying an object in CouchDB. We must write a design document which consists of Map and Reduce (optional) functions in order to retrieve our desired documents, and store that design document into the CouchDB database, so that the query may run inside the CouchDB engine. Such design documents are called views. MapReduce has been quite a popular application model for processing fairly large datasets. You specify a map function that processes all KVPs to generate an intermediate KVPs, and reduce function merges all intermediate values with the same intermediate keys. You will find excellent resources online (here’s one), hence I am not going to spend much time on this here. However, for this post, I will focus on only map functions.

Uploading a design document

Lets go ahead and create the following file called mylibrary.json:

 
{ 
	"_id": "_design/mylibrary", 
	"language": "javascript", 
	"views": { 
		"books_by_isbn": { 
			"map": "function (doc) { if(doc.ISBN) { emit (doc.ISBN, doc); } }" 
		} 
	} 
} 

And now execute the following command to upload the view:

curl -X PUT http://youradminuser:youradminpassword@localhost:5984/mylibrary/_design/mylibrary -d @mylibrary.json

If you take a look at the books_by_isbn view that we have written, CouchDB will execute this function for each document and will try to match whether the document has ISBN field. For those documents which are satisfying this criteria will be returned to the application code, in this case, in ISBN as keys and documents as values via in-built emit method.

Updating an existing design document

It’s mandatory to download the latest design document first in order to make changes and upload again, otherwise _rev will mismatch, and there will be a conflict situation. In order to download the latest design document, you may want to execute the following:

curl http://youradminuser:youradminpassword@localhost:5984/mylibrary/_design/my
library > mylibrary.json

You can maintain design documents from the dashboard as well.

Querying a view

Now that we have uploaded the code for books_by_isbn view, lets take a look how we can call use the view from Node.js:

 
var mylib = require("nano")("http://localhost:5984").use("mylibrary") 
var http = require("http"); var server = http.createServer(function (request, response) { 
	var isbn = "978-0553380163"; mylib.view("mylibrary", "books_by_isbn", function (err, body, header) { 
		if (err) { 
			response.writeHead(500, { "Content-Type": "text/plain" }); 
			response.end("Querying books failed. " + err + "\n"); } 
		else { 
			response.writeHead(200, { "Content-Type": "text/plain" }); 
			response.end("Books queried. Response: " + JSON.stringify(body) + "\n"); 
		} 
	}); 
}); 

server.listen(8000); 
console.log("Server running at http://127.0.0.1:8000/"); 

This will return all the books with ISBN property defined.

Books queried. Response: {"total_rows":1,"offset":0,"rows":[{"id":"978-0553380163","key":"978-0553380163","value":{"_id":"978-0553380163","_rev":"1-31ff552cb5824faf270e35ba8d6c6c02","Title":"A Brief History of Time","Author":"Stephen Hawking","Type":"Paperback  – Unabridged, September 1, 1998","ISBN":"978-0553380163"}}]}

If you look at the generated JSON, you can clearly see that the body.row actually holds the collection of the books. If you would like to iterate through them, you can. For example, if you’d like to access title of any book, you may use: body.rows[i].Title.

Querying for an object by ID

The example below in the updating an object demonstrates how to get an object by its ID.

Updating an object

Lets remove all the error handling code and simplify the code above a bit. In this example, I have obtained the book object first by the ISBN property, and then just made a plain insert after I have changed the author from Stephen Hawking to Tanzim Saqib. This will make a revision to the object and next time we will do ‘get’ again, we will get the latest revision:

 
var isbn = "978-0553380163"; 
mylib.view("mylibrary", "books_by_isbn", function (err, body, header) { 
	mylib.get(isbn, function (error, existing) { 
		if (!error) { 
			existing.Author = "Tanzim Saqib"; 
			mylib.insert(existing, isbn, function (err, body, header) { 
				if (!err) { 
					response.writeHead(200, { "Content-Type": "text/plain" }); 
					response.end("Book updated. Response: " + JSON.stringify(body) + "\n"); 
				} 
			}) 
		} 
	}); 
});

CouchDB revisions are beyond the scope of this post. Perhaps someday I will address that at length.

Deleting an object

Deleting an object is rather straight forward. The only change in the code above is a new method called ‘destroy’. All previously destroyed revisions remain in the Couch, unless you do a “Compact & Cleanup” operation on the dashboard.

var isbn = "978-0553380163";
mylib.view("mylibrary", "books_by_isbn", function (err, body, header) {
	mylib.get(isbn, function (error, existing) {
		if (!error) {
			mylib.destroy(isbn, existing._rev, function (err, body, header) {
				if (!error) {
					response.writeHead(200, { "Content-Type": "text/plain" });
					response.end("Book deleted. Response: " + JSON.stringify(body) + "\n");
				}
			})
		}
	});
});

Conclusion

In one post, I have attempted to pinpoint every single possible detail to get you started with CouchDB on Azure environment while working in Node.js.