100

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?

Philip
  • 4,128
  • 5
  • 31
  • 49

7 Answers7

166

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.

Using pg.connect is the way to go in a web environment.

PostgreSQL server can only handle 1 query at a time per connection. That means if you have 1 global new pg.Client() connected to your backend your entire app is bottleknecked based on how fast postgres can respond to queries. It literally will line everything up, queuing each query. Yeah, it's async and so that's alright...but wouldn't you rather multiply your throughput by 10x? Use pg.connect set the pg.defaults.poolSize to something sane (we do 25-100, not sure the right number yet).

new pg.Client is for when you know what you're doing. When you need a single long lived client for some reason or need to very carefully control the life-cycle. A good example of this is when using LISTEN/NOTIFY. The listening client needs to be around and connected and not shared so it can properly handle NOTIFY messages. Other example would be when opening up a 1-off client to kill some hung stuff or in command line scripts.

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 like db.js that looks something like this:

module.exports = {
   query: function(text, values, cb) {
      pg.connect(function(err, client, done) {
        client.query(text, values, function(err, result) {
          done();
          cb(err, result);
        })
      });
   }
}

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.

ndequeker
  • 7,932
  • 7
  • 61
  • 93
brianc
  • 2,924
  • 1
  • 20
  • 7
  • 2
    Sorry, I'm fairly new to DBMS and I still have a problem understanding this, but why don't we want to "litter pg.connect" calls? Is it for simplicity or due to performance reason? For example, I call pg.connect once in each of the routes I have in my basic app (all with the same conString). Is this okay? Intuitively, it feels like it's making a new connection to the same db whenever I call it (which I don't want), but does it use the pooled connections internally? Thanks. – user1164937 Oct 12 '13 at 03:31
  • Awesome. Why are you employing one connection per query instead of one per request? I've been looking for an appropriate way to share a connection across multiple queries within a request and had been considering res.locals prior to finding your answer here. – Joe Lapp Jul 18 '14 at 02:08
  • 2
    Oh wait. It looks like your solution here won't support transactions. – Joe Lapp Jul 18 '14 at 02:23
  • This should be permalinked to the github. – Ryan Willis Feb 26 '16 at 17:14
  • @brianc I'm also new to RDBMS. As reading pg's documentation seems like Client Pooling is the way to go for big websites (or not?). `pg-query` uses client pooling? If not, do you have an example how to write a `db.js` when you put your request inside `pool.connect((err, client, done) => {});`? The `max number of clients in the pool` can be very high number, like 10000? And what does it mean exactly? At the exact same moment the database can't handle more visitors than this number? – Lanti Sep 26 '16 at 15:40
  • Do I have to call client.end() at the end of the callback? – mishap Mar 10 '17 at 16:29
  • awesome library but this confuses me as well. Perhaps a code example with setInterval(console.log... ) with pooling and without to show exactly what's going on. – Rune Jeppesen Nov 14 '17 at 20:01
  • What if i have to call cb back in case of error in pg.connect. Something like this `module.exports = { query: function(text, values, cb) { pg.connect(function(err, client, done) { if (err) { cb(null, values); } client.query(text, values, function(err, result) { done(); cb(err, result); }) }); } }` by calling cb(null, values) from error block, sockets are increased in unix server. I am checking sockets by this command **ls -ltr /proc/cat /path of pid/fd/ | grep socket | wc -l** – Vardan Feb 06 '18 at 09:03
  • 1
    How to close pg connection? – Vardan Feb 06 '18 at 09:06
  • 1
    Note that pg.connect was removed post v7 of node-postgres aka pg. See https://stackoverflow.com/questions/45174120/pg-connect-not-a-function – Colin D Jul 21 '18 at 19:46
26

I am the author of pg-promise, which simplifies the use of node-postgres via promises.

It addresses the issues about the right way of connecting to and disconnecting from the database, using the connection pool implemented by node-postgres, among other things, like automated transactions.

An individual request in pg-promise boils down to just what's relevant to your business logic:

db.any('SELECT * FROM users WHERE status = $1', ['active'])
    .then(data => {
        console.log('DATA:', data);
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

i.e. you do not need to deal with connection logic when executing queries, because you set up the connection only once, globally, like this:

const pgp = require('pg-promise')(/*options*/);

const cn = {
    host: 'localhost', // server name or IP address;
    port: 5432,
    database: 'myDatabase',
    user: 'myUser',
    password: 'myPassword'
};
// alternative:
// const cn = 'postgres://username:password@host:port/database';

const db = pgp(cn); // database instance;

You can find many more examples in Learn by Example tutorial, or on the project's home page.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • Hi, Heroku only accepts SSL connections. In `pg` this is specified by `pg.defaults.ssl = true;`. How do you do this in `pg-promise`? – ocram Jul 23 '16 at 17:19
  • @ocram https://github.com/vitaly-t/pg-promise/wiki/FAQ#how-to-access-the-instance-of-node-postgres-thats-used, or you can specify SSL within the connection parameters: https://github.com/vitaly-t/pg-promise/wiki/Connection-Syntax – vitaly-t Jul 23 '16 at 17:53
  • I'm new to most of this: javascript, promises, postgres, etc. and this is exactly what I needed. Thank you!! – Ryan Rodemoyer Oct 09 '16 at 22:59
  • 1
    @ocram I just got this working by doing `pgp.pg.defaults.ssl = true;` – CharlieC Jan 18 '17 at 06:26
  • will this create multiple connections to improve the postgres throughput automatically when we give multiple query request to postgres? – sundar Jan 29 '18 at 07:45
  • @sundar Yes, it will. It has each [Database](http://vitaly-t.github.io/pg-promise/Database.html) object contain property [$pool](http://vitaly-t.github.io/pg-promise/Database.html#$pool) set to the dynamic connections pool, same as you would use with the low-level driver ;) – vitaly-t Jan 29 '18 at 10:56
10

Pool is the way to go now.Some thing like this

const { Pool } = require('pg');

    const pool = new Pool({
      connectionString: DATABASE_URL,
      ssl: false,
      max: 20,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    });
    module.exports = {
        query: (text, params) => pool.query(text, params)
      }

it can be used as db.query('<BEGIN,COMMIT,ROLLBACK,your query,anything')

amar
  • 4,285
  • 8
  • 40
  • 52
1

It is better to create a pg pool globally and each time you need to do a db operation use the client and then release it back to the pool. Once all db operations are done end the pool using pool.end()

Sample code -

let pool = new pg.Pool(dbConfig);
pool.connect(function(err, client, done) {

if (err) {
    console.error('Error connecting to pg server' + err.stack);
    callback(err);
} else {
    console.log('Connection established with pg db server');

    client.query("select * from employee", (err, res) => {

            if (err) {
                console.error('Error executing query on pg db' + err.stack);
                callback(err);
            } else {
                console.log('Got query results : ' + res.rows.length);


               async.each(res.rows, function(empRecord) {   
                        console.log(empRecord.name);
                });
            }
            client.release();

        });
}

});  

For more details, you can refer to my blog post -Source

Aniket Thakur
  • 66,731
  • 38
  • 279
  • 289
0

As you can see from the documentation both options are valid, so choose whichever you prefer. As you, I would go with the second choice.

alessioalex
  • 62,577
  • 16
  • 155
  • 122
  • What about reconnecting when the connection drops? Is that done automatically? The wiki page on error handling is... empty https://github.com/brianc/node-postgres/wiki/Error-handling – alltom Mar 25 '13 at 15:40
  • I've asked it separately: http://stackoverflow.com/questions/15619456/how-do-i-use-node-postgres-in-a-server – alltom Mar 25 '13 at 16:10
-1

I was interested in a very simple handler for this so I made my own without making it over complicated. I'm under no illusions that it's super basic but it could help some people get started. Basically, it connects, runs queries and handles errors for you.

function runQuery(queryString, callback) {
  // connect to postgres database
  pg.connect(postgresDatabase.url,function(err,client,done) {
    // if error, stop here
    if (err) {console.error(err); done(); callback(); return;}
    // execute queryString
    client.query(queryString,function(err,result) {
      // if error, stop here
      if (err) {console.error(err+'\nQuery: '+queryString); done(); callback(); return;}
      // callback to close connection
      done();
      // callback with results
      callback(result.rows);
    });
  });
}

Then you would use by calling it this way:

runQuery("SELECT * FROM table", function(result) {
  // Whatever you need to do with 'result'
}
JM-AGMS
  • 1,670
  • 1
  • 15
  • 33
  • This doesn't even release the connection back to the pool. It will deplete the pool real fast. The basic example on `node-postgres` page does better than this. – vitaly-t Aug 14 '16 at 13:55
-2

Here's how I do it, sort of an "all of the above approach"

Promise = require 'bluebird'
pg = module.exports = require 'pg'

Promise.promisifyAll pg.Client.prototype
Promise.promisifyAll pg.Client
Promise.promisifyAll pg.Connection.prototype
Promise.promisifyAll pg.Connection
Promise.promisifyAll pg.Query.prototype
Promise.promisifyAll pg.Query
Promise.promisifyAll pg

connectionString = process.env.DATABASE_URL

module.exports.queryAsync = (sql, values) ->
  pg.connectAsync connectionString
  .spread (connection, release) ->
    connection.queryAsync sql, values
    .then (result) ->
      console.log result.rows[0]
    .finally ->
      release()
Duane Fields
  • 1,331
  • 12
  • 20