Blog / 2011 / January / 10

Node.js and Postgres Phone Book

January 10, 2011

I wanted to write a simple application that would use some of the functionality of node.js, yet be more than a trivial Hello World example. To this end I elected to write a phone book. The idea is that you can pass a partial phone number or partial name in a query string and the app will show the matching records from a postgres database.

Installing Node and V8

I installed directly from git:

$ git clone git://github.com/ry/node.git
$ ./configure
$ make
$ sudo make install

Install Node Package Manager and Postgres Module

We need to install the node package manager NPM so that we can easily install modules.

Go to http://npmjs.org/ and download the source code. Unpack it then run

$ sudo make

See https://github.com/isaacs/npm#readme for options for getting npm installed if you have node installed as root. I opted to install npm as root, and then ran 

/usr/local/lib$ sudo chown -R tjs:tjs node

to make sure npm can write files where it needs to. YMMV.

Run the following command to install the postgres driver

$ npm install pg
npm info it worked if it ends with ok
npm info using npm@0.2.14-3
npm info using node@v0.3.5-pre
npm info fetch http://registry.npmjs.org/pg/-/pg-0.2.2.tgz
npm info calculating sha1 /tmp/npm-1294662920350/1294662920991-0.1517097947653383/tmp.tgz
npm info shasum db540b51b9635ca6020517d3636406aa5786e050
npm info calculating sha1 /usr/local/lib/node/.npm/.cache/pg/0.2.2/package.tgz
npm info shasum e743fec77705d472cdec1c1c24210efa9ddf0f5d
npm info preinstall pg@0.2.2
npm info install pg@0.2.2
npm info postinstall pg@0.2.2
npm info preactivate pg@0.2.2
npm info activate pg@0.2.2
npm info postactivate pg@0.2.2
npm info build Success: pg@0.2.2
npm ok

Print Out Phone Number List

As a first stage, we're just going to print out the list of phone numbers in the database in a HTML table.

Here's the code to connect to postgres, select all rows, then print out the name and number:

var http = require('http');
var pg = require('pg');

var connectionString = "pg://phone:book@server:5432/phone";
pg.connect(connectionString, function(err, client) {
  if(err) {
    console.log(err);
  }
  else {
    http.createServer(function(request, response){
      response.writeHead(200, {'Content-Type': 'text/html'});
      response.write('<h1>Phone Book</h1>');

      client.query('SELECT name, number FROM phonebook', function(err, result) {
        if(err) {
         console.log(err);
        }
        else {
          response.write('<table>');
          for (var i=0; i<result.rows.length; i++){
            response.write('<tr><td>' + result.rows[i].name + '</td><td>' + result.rows[i].number + '</td></tr>');
          }
          response.write('</table>');
          response.end();
        }
      });
    }).listen(8080, "localhost");
    console.log('Server running at http://127.0.0.1:8080/');
  }
});

Benchmark

We can run the apache benchmark on this to see what sort of performance we get. I have three rows in the database, and the database itself is on a remote machine on the network. Each request will do the query to the remote machine.

$ ab -n 1000 -c 100 'http://127.0.0.1:8080/'

This runs 1000 requests, and results are 959.44 requests per second. The longest request was 117ms. Not bad.

Query Processing

Here's the modifications that take a single parameter q on the URL and print out any records that match this string either name or number.

var http = require('http');
var pg = require('pg');
var url = require('url');

var connectionString = "pg://phone:book@server:5432/phone";
pg.connect(connectionString, function(err, client) {
  if(err) {
    console.log(err);
  }
  else {
    http.createServer(function(request, response){
      var lVars = url.parse(request.url, true);
      var lQuery = lVars.query.q;

      response.writeHead(200, {'Content-Type': 'text/html'});
      response.write('<h1>Phone Book</h1>');

      var lSql = 'SELECT name, number FROM phonebook WHERE UPPER(name) LIKE UPPER($1) OR UPPER(number) LIKE UPPER($1)';
      client.query(lSql, ['%'+lQuery+'%'], function(err, result) {
        if(err) {
        console.log(err);
        }
        else {
          response.write('<table>');
          for (var i=0; i<result.rows.length; i++){
            response.write('<tr><td>' + result.rows[i].name + '</td><td>' + result.rows[i].number + '</td></tr>');
          }
          response.write('</table>');
          response.end();
        }
      });
    }).listen(8080, "localhost");
    console.log('Server running at http://127.0.0.1:8080/');
  }
});

Bear in mind that UPPERing like this in the query will not use any indexes on the table.

Troubleshooting

If you run a node.js application from a browser (in my case I was just doing a hello world) there was a request being done whenever I switched into the tab with that app in. This happened in Firefox but not in Chrome. This turned out to be a request for /favicon.ico.

Reference

Tags: nodejs postgres