I am writing a node.js app on Heroku and using the pg module. I can't figure out the "right" way to get a client object for each request that I need to query the database.
The documentation uses code like this:
pg.connect(conString, function(err, client) {
// Use the client to do things here
});
But surely you don't need to call pg.connect
inside every function that uses the database right? I've seen other code that does this:
var conString = process.env.DATABASE_URL || "tcp://postgres:1234@localhost/postgres";
var client = new pg.Client(conString);
client.connect();
// client is a global so you can use it anywhere now
I am leaning toward the second option since I believe the free database instance for Heroku is limited to one connection anyway, but are there any drawbacks to doing it this way? Do I need to check if my client object is still connected every time before I use it?
Best Answer
I'm the author of node-postgres. First, I apologize the documentation has failed to make the right option clear: that's my fault. I'll try to improve it. I wrote a Gist just now to explain this because the conversation grew too long for Twitter.
One very helpful thing is to centralize all access to your database in your app to one file. Don't litter
pg.connect
calls or new clients throughout. Have a file likedb.js
that looks something like this:This way you can change out your implementation from
pg.connect
to a custom pool of clients or whatever and only have to change things in one place.Have a look at the node-pg-query module that does just this.