I had to access a PostgreSQL database from within Node.js today and, being a total noob in this field, I decided to write up my experience, hoping it would help others.

Install PostgreSQL

The adventurous types would probably go test this on the production server right away, but not me. I'm a boring guy and prefer to work things out on my developer machine or in a VM before going for the real thing. That's why we install PostgreSQL first. Ubuntu already has a PostgreSQL package, but that is the older version: 9.1. While it is a perfectly fine and stable version, I decided I would go for the latest and greatest release: 9.2. We don't have to compile it from source ourselves - there is a PPA maintained by the PostgreSQL guys that has the latest packages. To add it to your system, follow the procedure outlined in the Ubuntu install docs and execute:

sudo add-apt-repository ppa:pitti/postgresql

Then we need to update our apt cache and install the postgresql-9.2 package:

sudo apt-get update
sudo apt-get install postgresql-9.2

Configure PostgreSQL

So far, so good -- we have a working PostgreSQL server. We can administer it by running commands from the newly-created postgres user. Start a login session by running sudo -i -u postgres. You need to enter all admin commands there.

Let us now create a test database owned by a separate user. We wouldn't want to connect to our server with admin credentials, right? First, we create the user using the createuser executable and setting up a password for it by passing the -P option:

postgres@desktop:~$ createuser -P nodetest
Enter password for new role: 
Enter it again:

Now, let's create a new "nodetest" db and set our "nodetest" user as the owner:

postgres@desktop:~$ createdb -O nodetest nodetest

The last thing we need to do is allow MD5 password logins for all local users. You do this by editing the /etc/postgresql/9.2/main/pg_hba.conf file. Find this line:

local   all             all                                     peer

and insert this one above it:

local   all             all                                     md5

Save the file, and restart your DB server: sudo service postgresql restart.

Now let's test that our newly-created user can create to its database:

postgres@desktop:~$ psql -U nodetest -W nodetest
Password for user nodetest: 
psql (9.2.1)
Type "help" for help.

nodetest=>

As you can see, it does. If you get an error about a failed peer authentication, then get back to fixing your pg_hba.conf config file.

Install Node.js Libraries.

We will access our PostgreSQL database using the "pg" Node.js module. It calls out the libpq library and you need to install the libpq-dev package in order to build the C extension:

sudo apt-get install libpq-dev

Then configure "pg" as a dependency in your package.json file:

{
  "name": "node-postgresql",
  "description": "Node.js calling PostgreSQL sample app",
  "version": "0.0.1",
  "private": true,
  "dependencies": {
    "pg": "*"
  }
}

... and run npm install to fetch and install it.

Connecting to the Database

pg has a relatively simple async API. You give a callback to the pg.connect function and it calls you passing a client instance that you can then use to query the database. Let us use that to fetch a single datum from the db -- the current time:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
//Use the native libpq bindings
var pg = require('pg').native;

var dbUrl = "tcp://nodetest:1234@localhost/nodetest";

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

There are four interesting moments in the code above:

  1. We select the native libpq backend after requiring the "pg" package. It is supposed to be faster than the pure-JavaScript version.
  2. We define the database address in an URL-like connection string. Me leaving my test DB password in there is not too secure, but that will do for my test script. Make sure you store that in a secure location for production apps!
  3. We get the data as a callback parameter and access values as row attributes. As you can see, we get a column named when which gets transformed as a JavaScript Date object bound to the when row property.
  4. We finally call the onDone callback. I've added that to be able to easily chain DB commands.

Let's call our function:

1
testDate(disconnectAll)

I have defined a simple disconnectAll helper function that closes my connection, so that my test script doesn't hang forever and properly ends its execution. Here it is:

1
2
3
function disconnectAll() {
    pg.end();
}

You will not need that in a long-running web app as pg.connect will pool and manage connections for you. I have added it just for my console test script.

Now, let's do something real: create a temporary "reviews" table, insert two reviews and read them back.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
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();
        });
    });
}

And call both our test functions by chaining callbacks:

1
2
3
testDate((function() {
    testTable(disconnectAll)
}));

Here is the output:

~/w/javascript/node-postgresql node test-connection.js
Row count: 1
Current year: 2012
Row count: 2
id: 1
author: mad_reviewer
content: I'd buy this any day of the week!!11
id: 2
author: calm_reviewer
content: Yes, that was a pretty good product.

Summary

Working with PostgreSQL from within Node.js is very easy once you properly set up your environment. What is left now is to query PostgreSQL from a web app -- I'll leave that for another blog post. Oh, and all my sample project's source code is on GitHub.