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:

Edit source list:

sudo vim /etc/apt/sources.list

and add append this line:

deb precise-pgdg main

Save and exit.
Import repository key and update.

wget --quiet -O - | 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

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 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.static(path.join(__dirname, 'public')));

// development only
if ('development' == app.get('env')) {

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

function disconnectAll() {

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());


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: " +;
                console.log("author: " +;
                console.log("content: " + row.content);


//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);

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:


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).


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.

Next: Part 2

Leave a Reply