Using postgreSQL on Ubuntu (with node.js)

In this post I'll detail how to connect to a postgres database (like Amazon Redshift) with Node.js on Ubuntu and hopefully help you avoid some of the common pitfalls I fell into a few times.

I'll go into Redshift and why we're using it another day but this guide applies even if you're using a different postgreSQL database.

To start off we are going to assume you have nodejs and npm already installed on your server.

You're going to want to start off with:

sudo apt-get install postgresql-client

This will install the postgres client on your server and allow you to connect to your target postgres database. If you want to host a postgres database directly on this server then go ahead and use:

sudo apt-get install postgresql

Now most guides will skip over this next step, assuming that you already have it, but if you do you may find that you get an error stating:

pg_config: not found

So in order to avoid this we'll run:

sudo apt-get install libpq-dev

Now you should be ready to install a postgres module for node.js to use. I personally went with node-postgres since it seemed to be the most mature and fully featured.

We'll install it via npm of course.

npm install pg

Now from inside your node.js app we'll require pg via

var pg = require('pg');

Now we'll add a connection string so that pg knows what to connect to:

var conString = "postgresql://username:password@localhost/postgres";

I've used a local host connection parameter here, if you are using Redshift you'll use the url that Amazon provides you with.

Now lets get to the good stuff, querying the database:

pg.connect(conString, function(err, client, done) {
	if(err) {    
		console.error('error fetching client from pool', err);
	} else {
		client.query(text, function(err, result) {
			done();
			if(err) {
				console.error('error running query', err);
			}
			console.log(result.rows);
		});
	}  
});

result.rows will give you an array of the values retreived

text should be replaced with a query string such as SELECT 1+1 as example (this isn't a sql tutorial)

done(); this releases our current connection back to the connection pool (wouldn't want to run out)

Using our example, result.rows[0] should return something like {example: 2}

And thats it. You should have at least a good starting point to begin playing around with connecting to postgreSQL databases from Node.js

If you run into any problems or have suggestions for something I missed here let me know in the comments.