Brewing a potion with Bluemix, PostgreSQL, Node.js in the cloud


Here is a heady potion made with the key ingredients of IBM’s Bluemix, PostgreSQL & Node.js. In this post I instantiate an app ‘mypgdb01’ on IBM’s Bluemix which uses the services of PostgreSQL in the cloud. This is shown in the picture below.

8

The mypgdb01 is a Webserver running on Node.js in Bluemix and uses the services of PostgreSQL DB. The app mypgdb01 performs basic CRUD (Create, Remove, Update & Delete) on the PostgreSQL.

The code for this can be forked from Devops  from my link mypgdb

The code can also be cloned from GitHub at mypgdb

1) This app uses the Node.js Webstarter kit and the PostgreSQL service.  The Node.js Webserver is created by parsing the VCAP services variable as usual

if (process.env.VCAP_SERVICES) {
var env = JSON.parse(process.env.VCAP_SERVICES);
var credentials = env['postgresql-9.1'][0]['credentials'];
} else {
var credentials = {"uri":"postgre://user:secret1@localhost:5433/db"}
}
var port = (process.env.VCAP_APP_PORT || 1337);
var host = (process.env.VCAP_APP_HOST || '0.0.0.0');
http.createServer(function(req, res) {
console.log("Inside Webserver");
....
....
}).listen(port, host);

The details of VCAP_SERVICES can be see by clicking Runtime for you application and is shown below

9

Note: From above it can be seen that the PostgreSQL DB’s host & port is

"host": "192.155.243.14",
"hostname": "192.155.243.14",
"port": 5433

This different from the host & port of the Node.js Webserver which  can be see in Logs & Files tab (included below) and is

Webserver: host: 0.0.0.0
port:62733

2) Once the Webserver is started the server waits for REST calls of GET,PUT, UPDATE & DELETE as shown below.

// Perform CRUD operations through REST APIs
if(req.method == 'POST') {
insert_records(req,res);
}
else if(req.method == 'GET') {
list_records(req,res);
}
else if(req.method == 'PUT') {
update_record(req,res);
}
else if(req.method == 'DELETE') {
delete_record(req,res);
}

3)  The REST API call  are implements as follows

a) POST:

var insert_records = function(req, res) {
// Connect to DB
var client = new pg.Client(credentials.uri);
client.connect(function(err) {
if (err) {
res.end("Could not connect to postgre: " + err);
}

//Drop table if it exists
client.query("DROP TABLE IF EXISTS emps");
// Creat table and insert 2 records into it
client.query("CREATE TABLE IF NOT EXISTS emps(firstname varchar(64), lastname varchar(64))");
client.query("INSERT INTO emps(firstname, lastname) values($1, $2)", ['Tinniam', 'Ganesh']);
client.query("INSERT INTO emps(firstname, lastname) values($1, $2)", ['Anand', 'Karthik']);

b) GET:

// Select all rows in the table<
var query = client.query("SELECT firstname, lastname FROM emps ORDER BY lastname, firstname");
query.on("row", function (row, result) {
result.addRow(row);
});
query.on("end", function (result) {
// On end JSONify and write the results to console and to HTML output
console.log(JSON.stringify(result.rows, null, "    "));
res.writeHead(200, {'Content-Type': 'text/plain'});
res.write(JSON.stringify(result.rows) + "\n");
res.end();
});

c) UPDATE:

query = client.query("UPDATE emps set firstname = 'Kumar' WHERE firstname='Anand' AND lastname='Karthik'");
res.writeHead(200, {'Content-Type': 'text/plain'});
res.write("Updated record  - Set record with firstname Anand to Kumar\n");

d)DELETE

// Delete the record where the lastname is Karthik
client.query("DELETE FROM  emps WHERE lastname = 'Karthik'");
res.writeHead(200, {'Content-Type': 'text/plain'});
res.write("Deleted record where lastname was Karthik\n");

4) Once the changes are made you can  push the changes to Bluemix using ‘cf’ commands.

The commands are

cf login -a https://api.ng.bluemix.net
cf push mypgdb01-p . -m 512M
cf create-service postgresql 100 pgdb01
cf bind-service mypgdb01 pgdb01

5) In the Bluemix dashboard the app ‘mypgdb01’ should be up and running.

6) To invoke the different database operations we need to make REST API calls to the app.

7) To make the REST API calls you can install Sure Utils -> REST API Chrome extension.  I installed the SureUtils-.REST API Client. This is a Chrome extension and can be installed from Chrome Web Store (search for REST API client). You could choose any REST API client of your choice for the browser you intend to use (Chrome, Firefox)

8) Now we can test the Nodejs-PostgreSQL app with the Sure Utils – Chrome extension

8) The following REST API calls can be made to test the PostgreSQL operations on the database

POST- insert

1

GET – select

2

UPDATE + GET – update + select

The PUT API updates Anand Karthik to Kumar Karthik. This is shown in the GET API call

3

4

.DELETE + GET – delete  + select

5

6

 

Here the DELETE API call deletes the Kumar Karthik record. The GET API call now displays only 1 record.

The console.log output in Bluemix can be see in Files and logs -> stdout.log as shown below

7

The above post shows some basic operations done on a cloud based application that is composed of a Webserver with a PostgreSQL as a backend. The code can be enhanced by adding  front end using Node express.

As mentioned above the code for this can be forked from Devops at mypgdb. The code can also be cloned from GitHub at mypgdb

Disclaimer: This article represents the author’s viewpoint only and doesn’t necessarily represent IBM’s positions, strategies or opinions

You may also like
1. A Bluemix recipe with MongoDB and Node.js
2. Spicing up IBM Bluemix with MongoDB and NodeExpress
3. A Cloud Medley with IBM’s Bluemix, Cloudant and Node.js
4. Rock N’ Roll with Bluemix, Cloudant & NodeExpress


Find me on Google+

Advertisements

16 thoughts on “Brewing a potion with Bluemix, PostgreSQL, Node.js in the cloud

  1. Pingback: Working with Node.js and PostgreSQL | Giga thoughts …

  2. Pingback: A Cloud medley with IBM Bluemix, Cloudant DB and Node.js | Giga thoughts …

  3. Pingback: Rock N’ Roll with Bluemix, Cloudant & NodeExpress | Giga thoughts …

  4. Pingback: Spicing up a IBM Bluemix cloud app with MongoDB and NodeExpress | Giga thoughts …

  5. Pingback: Elements of CRUD with NodeExpress and MongoDB using Enide Studio | Giga thoughts …

  6. Pingback: A Bluemix recipe with MongoDB and Node.js | Giga thoughts …

  7. Pingback: What’s up Watson? Using IBM Watson’s QAAPI with Bluemix, NodeExpress – Part 1 | Giga thoughts …

  8. Pingback: Powershell GUI – Adding bells and whistles | Giga thoughts …

  9. Pingback: Adventures in LogParser, HTA and charts | Giga thoughts …

  10. Pingback: Tips for building a decent HTA/HTML application | Giga thoughts …

  11. Pingback: Stir fry a VBA with Excel application quickly | Giga thoughts …

  12. Pingback: Building a respectable VBA with Excel Application | Giga thoughts …

  13. Pingback: Get your feet wet with Powershell GUI | Giga thoughts …

  14. Pingback: Bend it like Bluemix, MongoDB using Auto-scale – Part 1! | Giga thoughts …

  15. Pingback: Bend it like Bluemix, MongoDB using Auto-scaling – Part 2! | Giga thoughts …

  16. Pingback: Bend it like Bluemix, MongoDB with auto-scaling – Part 3 | Giga thoughts …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s