What is the proper way to use the node.js postgresql module?

by

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.

More Related Contents:

  1. What is this JavaScript “require”?
  2. Multi-row insert with pg-promise
  3. Discord bot on Heroku throws an error: agent ??= new https.Agent
  4. What does “./bin/www” do in Express 4.x?
  5. Puppeteer unable to run on Heroku
  6. How do I properly insert multiple rows into PG with node-postgres?
  7. Heroku: “No default language could be detected for this app” error thrown for node app
  8. When is .then(success, fail) considered an antipattern for promises?
  9. How do I fix CLIENT_MISSING_INTENTS error?
  10. Render basic HTML view?
  11. ECMAScript 6 features available in Node.js 0.12
  12. Can you force Vue.js to reload/re-render?
  13. How do I remove documents using Node.js Mongoose?
  14. TypeError: Router.use() requires middleware function but got a Object
  15. How can I get city name from a latitude and longitude point?
  16. Passing a variable from node.js to html
  17. Modify file in place (same dest) using Gulp.js and a globbing pattern
  18. How to pass a function in Puppeteers .evaluate() method?
  19. Next.js ISR ( Incremental Static Regeneration ), how to rebuild or update a specific page manually or dynamically before the interval/ISR time start?
  20. Discord API valid intents must be provided for the client [duplicate]
  21. node.js /socket.io/socket.io.js not found
  22. A plain JavaScript way to decode HTML entities, works on both browsers and Node
  23. How do I add an action to watson conversation response?
  24. Running Node.js in apache?
  25. how to use jQuery ajax calls with node.js
  26. MissingSchemaError: Schema hasn’t been registered for model “User”
  27. Where argument of first next() call goes? [duplicate]
  28. Conflict: Multiple assets emit to the same filename
  29. NodeJS – setTimeout(fn,0) vs setImmediate(fn)
  30. In Node.js / Express, how do I “download” a page and gets its HTML?

Leave a Comment