Node and Postgres

Or, two great tastes that work together. PostgreSQL Conference West was last week, and we will be looking at East 2011 in a few short months. I was fortunate enough to present a paper on Postgres.js, the driver I've been working on for Node.js for the past several months. I had a lot of great feedback in my talk, a lot of great questions, and even some immediate bug reports.
But the feedback I received most often? "What is Node.js?"

What is Node?

Node.js is a fast, server-side, event-driven JavaScript programming environment. For starters, this means we are talking about server side programming. Not, "web" programming. There are no cross browser problems, or fighting with IE. Node.js also uses V8, the very same JavaScript engine that Google Chrome uses.

Why do you need to care?

Node.JS, and JavaScript in particular is huge. Today, right now, it owns the frontend. There is more code being written in JavaScript, for all the Web2.0 magic we take for granted. Not only that, JavaScript is getting bigger every day. JavaScript is also the focus of a lot of language research and optimization. Google. Apple. Mozilla. Opera. Everyone cares about making JS better, faster. Check out Mozilla's benchmark tracking page. JavaScript runs on the server, quickly, with Node. JS already dominates the frontend. PL/V8 exists, and allows us to write stored procedures in JavaScript. Soon, you'll be able to develop entire infrastructures without learning a new language. People today who do not consider themselves programmers, know JavaScript, can build complex systems in JavaScript. Tomorrow, they'll be able to build new systems, leverage tighter coupling of browser and server and database.

Postgres and Node

So Node is an important step into tomorrow, but we still have our database systems, and tomorrow we will still be building new database systems. Further, Node's design and positioning as a network-first environment makes it a natural fit for working with Postgres. Data is simple, and communication is easy. Here's how:
var sys = require("sys");
var pg = require("./lib/postgres-pure");

var db = new pg.connect("pgsql://test:12345@localhost:5432/insert_test");
db.query("SELECT 1::int;", function (error, rs, tx) {
    if (error) {
        console.log(error); // Will print the error statement
        console.log(error.code); 
    }
    else {
        console.log(sys.inspect(rs));
    }
});
which returns:
[ { int4: 1 } ]
Or, to show off error handling:
db.query("SELECT 1::errortest;", function (error, rs, tx) {
    if (error) {
        console.log(error);
    }
    else {
        console.log(sys.inspect(rs));
    }
});
which returns:
Error!
{ severity: 'ERROR'
, code: '42704'
, message: 'type "errortest" does not exist'
, toString: [Function]
}

Prepared Statements and Parameterized Queries

The key part of good support for Postgres is in the Parameterized Queries. This feature allows Postgres itself to handle correct data transformations and replacements, preventing the possibility of most SQL injection attacks. Here's how to use prepared statements in postgres.js:
db.prepare("SELECT ?::int AS preparetest", function (sth, tx) {
    var logit = function (err, rs) {
        console.log(sys.inspect(rs));
    }
    sth.execute(1, logit);
    sth.execute(2, logit);
});
which will return:
[ { preparetest: 1 } ]
[ { preparetest: 2 } ]
The sth object passed by performing a prepare easily allows for the same query to be reused, saving time re-parsing the query on the server, as well as grouping all the execute statements into a single logical block.

The Future

The future of Postgres and Node is bright. We're looking to support LISTEN/NOTIFY transparently, allowing for very fast and very easy monitoring software to be written in Node.
var sys = require("sys");
var pg = require("./lib/postgres-pure");

var db = new pg.connect("pgsql://test:12345@localhost:5432/insert_test");
db.on("YOUR_EVENT", function (payload) {
    // notify handler here
});
And that's it; nothing more or less complex than a single function to handle your event.

The Caveats

Sadly, it's not entirely sunshine and roses on postgres.js. We have to post the disclaimer that this is very much alpha software, and shouldn't be used in a production context. But, we are working hard at stabilizing the driver, and Command Prompt is pleased to provide full support, and we welcome hearing from anyone using it.

How to Get It

postgres.js can be downloaded from one of two locations: http://github.com/aurynn/postgres-js for the bleeding-edge, most unstable variant. And: http://github.com/commandprompt/postgres-js for the stable, when-it-happens release fork.