PostgreSQL and Node.js – 100% JavaScript Adventure

This is a log of every step I will take to create a 100%-JavaScript application back to front.

Hardware: Windows 7 64bit Home Edition ASUS laptop with Intel P6100 CPU @2.0GHz, 4GB RAM, 320GB HDD

Oracle VirtualBox 4.2.12
Ubuntu 12.04.2 – Long term support
Downloaded 32bit ISO image – laptop does not support 64bit virtualisation 🙁
Virtual machine: 1 CPU, 1GB RAM, 20GB HDD, with bridged network, boot CD first to use ISO image
Minimal install with OpenSSH server

Update repositories and upgrade base system

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install python-software-properties

To use PostgreSQL Apt repository, follow: http://wiki.postgresql.org/wiki/Apt

Edit source list:

sudo vim /etc/apt/sources.list

and add append this line:

deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main

Save and exit.
Import repository key and update.

wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.2
sudo apt-get install libpq-dev

This will also install GCC and many related packages to compile code.

Install Node.js – refer to http://nodejs.org/

sudo apt-get install g++
sudo apt-get install make
sudo add-apt-repository ppa:chris-lea/node.js
sudo apt-get update
sudo apt-get install nodejs

Install git if you want to work with github or bitbucket etc.

sudo apt-get install git

I think my development environment is ready. Next step is to create an application.

Let’s prepare the database server. Create a database user and a database owned by that user.

sudo su postgres
createuser -P myuser
createdb -O myuser mydb

Make sure local connections are allowed for local users:

sudo vim /etc/postgresql/9.2/main/pg_hba.conf
psql -W --user=myuser --host=localhost mydb

And execute this code to create a table and save couple of records in our sample database.

create table tbl_test (
  id int,
  name text,
  ts timestamptz,
  info json,
  primary key(id)
);
insert into tbl_test values (1, 'murat', now(), '{"a": "11", "b": "21"}');
insert into tbl_test values (2, 'yaman', now(), '{"a": "12", "b": "22"}');

Install Express for Node globally:

sudo npm install -g express

Clone my sample application if you want to.

git clone git@github.com:muratyaman/pgnode.git pgnode-app
cd pgnode-app
vim package.json

One of the dependencies is “pg” which is the PostgreSQL module for Node.js.

{
  "name": "pgnode-app",
  "description": "Sample app for Node.js and PostgreSQL",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "3.2.6",
    "jade": "*",
    "pg": "*"
  }
}

Install any dependencies.

npm install

Sample code: app.js


/**
 * Module dependencies.
 */

var express = require('express')
  , pg      = require('pg').native
  , routes  = require('./routes')
  , user    = require('./routes/user')
  //, test    = require('./routes/test')
  , http    = require('http')
  , path    = require('path')
;

var app = express();

// all environments
app.set('port', process.env.PORT || 3000);
app.set('views', __dirname + '/views');
app.set('view engine', 'jade');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.bodyParser());
app.use(express.methodOverride());
app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));

// development only
if ('development' == app.get('env')) {
  app.use(express.errorHandler());
}


//--
var dbUrl = "tcp://myuser:123456@localhost/mydb";

function disconnectAll() {
    pg.end();
}

function testDate(onDone) {
    pg.connect(dbUrl, function(err, client) {
        client.query("SELECT NOW() as when", function(err, result) {
            console.log("Row count: %d",result.rows.length); // 1
            console.log("Current year: %d", result.rows[0].when.getFullYear());

            onDone();
        });
    });
}

function testTable(onDone) {
    pg.connect(dbUrl, function(err, client) {
        client.query("CREATE TEMP TABLE reviews(id SERIAL, author VARCHAR(50), content TEXT)");
        client.query("INSERT INTO reviews(author, content) VALUES($1, $2)",
            ["mad_reviewer", "I'd buy this any day of the week!!11"]);
        client.query("INSERT INTO reviews(author, content) VALUES($1, $2)",
            ["calm_reviewer", "Yes, that was a pretty good product."]);
        client.query("SELECT * FROM reviews", function(err, result) {
            console.log("Row count: %d",result.rows.length); // 1
            for (var i = 0; i < result.rows.length; i++) {
                var row = result.rows[i];
                console.log("id: " + row.id);
                console.log("author: " + row.author);
                console.log("content: " + row.content);
            }

            onDone();
        });
    });
}

//testDate(function() {
//    testTable(disconnectAll)
//});


function processReqResSql(req, res, sql){
    pg.connect(dbUrl, function(err, client) {
        client.query(sql, function(err, result) {
            var sr = new ServiceResult ('success');
            sr.output = result.rows;
            //for (i = 0; i < result.rows.length; i++) {
            //    sr.output[i] = JSON.parse(result.rows[i].data);
            //}
            res.send(sr);
        });
    });
}

function ServiceResult (status){
    this.ts = new Date();
    this.status = status;
    this.output = new Array();
}
//--

app.get('/', routes.index);
app.get('/users', user.list);
app.get('/test', //test.list
  function(req, res) {
    var sql = 'select * from tbl_test';
    processReqResSql(req, res, sql);
  }
);

http.createServer(app).listen(app.get('port'), function(){
  console.log('Express server listening on port ' + app.get('port'));
});

And run the (server) application.

node app

On your browser, request the test page:

http://yourserver:3000/test

Output should be:

{
  "ts": "2013-06-22T19:54:43.413Z",
  "status": "success",
  "output": [
    {
      "id": 2,
      "name": "yaman",
      "ts": "2013-06-22T18:52:06.136Z",
      "data": {
        "a": "12",
        "b": "22"
      }
    },
    {
      "id": 1,
      "name": "murat",
      "ts": "2013-06-22T18:51:58.254Z",
      "data": {
        "a": "11",
        "b": "21"
      }
    }
  ]
}

You can see the output in JSON including the info field which uses JSON field type (added in PostgreSQL 9.2).

Lovely!

Next step is to create functions in PostgreSQL using PL/V8. This way I want to keep my server very lightweight and fast. I like my database server as the data gate keeper 🙂

Basically, almost every request will execute:

SELECT * FROM a_function_name_based_on_request (inputs_in_json_format);

And each function will output a row or many rows which will be served as part of the response.

https://github.com/muratyaman/pgnode

Next: Part 2

Leave a Reply