使用 node.js postgresql 模块的正确方法是什么? [英] What is the proper way to use the node.js postgresql module?

问题描述

我正在 Heroku 上编写一个 node.js 应用程序并使用 pg 模块.我无法找出为我需要查询数据库的每个请求获取客户端对象的正确"方法.

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.

文档使用如下代码:

pg.connect(conString, function(err, client) {
  // Use the client to do things here
});

但你肯定不需要在每个使用数据库的函数中调用 pg.connect 吧?我见过 其他代码 这样做:

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

我倾向于第二种选择，因为我相信 Heroku 的免费数据库实例无论如何都仅限于一个连接，但是这样做有什么缺点吗?每次使用前是否需要检查我的客户端对象是否仍然连接?

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?

推荐答案

我是 node-postgres 的作者.首先，我很抱歉文档未能明确说明正确的选项:这是我的错.我会努力改进它.我刚刚写了一个要点来解释这个，因为对话对于 Twitter 来说太长了.

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.

在网络环境中使用 pg.connect 是可行的方法.

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

PostgreSQL 服务器每次连接只能处理 1 个查询.这意味着如果您有 1 个全局 new pg.Client() 连接到您的根据 postgres 的速度，后端您的整个应用程序是瓶颈可以回复查询.从字面上看，它会将所有东西排成一行，排队每个查询.是的，它是异步的，所以没关系......但你不会吗而是将您的吞吐量乘以 10 倍?使用 pg.connect 设置pg.defaults.poolSize 到一些理智的东西(我们做 25-100，不确定正确的数字).

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 适合您知道自己在做什么.当你需要由于某种原因或需要非常小心的单一长期客户控制生命周期.一个很好的例子是当使用聆听/通知.倾听的客户需要在身边已连接但未共享，因此它可以正确处理 NOTIFY 消息.另一个例子是当打开一个 1-off 客户端杀死一些挂起的东西或在命令行脚本中.

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.

一件非常有用的事情是将应用程序中对数据库的所有访问集中到一个文件中.不要在整个过程中乱丢 pg.connect 调用或新客户端.有一个像 db.js 这样的文件，看起来像这样:

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

通过这种方式，您可以将您的实现从 pg.connect 更改为自定义客户端池或其他任何内容，并且只需在一个地方进行更改.

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.

查看执行此操作的 node-pg-query 模块.

这篇关于使用 node.js postgresql 模块的正确方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！