Javascript 使用 node.js postgresql 模块的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8484404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the proper way to use the node.js postgresql module?
提问by Philip
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.
我正在 Heroku 上编写一个 node.js 应用程序并使用pg 模块。我无法找出为我需要查询数据库的每个请求获取客户端对象的“正确”方法。
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.connectinside every function that uses the database right? I've seen other codethat does this:
但是您肯定不需要pg.connect在每个使用数据库的函数内部调用吧？我见过这样做的其他代码：
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?
我倾向于第二种选择，因为我相信 Heroku 的免费数据库实例无论如何都仅限于一个连接，但是这样做有什么缺点吗？每次使用之前，我是否需要检查我的客户端对象是否仍然连接？
回答by brianc
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 Gistjust now to explain this because the conversationgrew too long for Twitter.
我是node-postgres的作者。首先，我很抱歉文档未能明确说明正确的选项：这是我的错。我会努力改进它。我刚刚写了一篇 Gist来解释这一点，因为Twitter的谈话时间太长了。
Using
pg.connectis the way to goin 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? Usepg.connectset thepg.defaults.poolSizeto something sane (we do 25-100, not sure the right number yet).
new pg.Clientis 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 usingLISTEN/NOTIFY. The listening client needs to be around and connected and not shared so it can properly handleNOTIFYmessages. Other example would be when opening up a 1-off client to kill some hung stuff or in command line scripts.
使用
pg.connect是在 Web 环境中使用的方法。PostgreSQL 服务器每个连接一次只能处理 1 个查询。这意味着，如果您有 1 个全局
new pg.Client()连接到您的后端，那么您的整个应用程序就会因 postgres 对查询的响应速度而陷入瓶颈。它实际上会将所有内容排列起来，对每个查询进行排队。是的，它是异步的，所以没关系……但是您不想将吞吐量乘以 10 倍吗？使用pg.connect设置pg.defaults.poolSize为理智的 东西（我们做 25-100，还不确定正确的数字）。
new pg.Client是为了当你知道自己在做什么时。当您出于某种原因需要一个长期存在的客户端或需要非常小心地控制生命周期时。一个很好的例子是使用LISTEN/NOTIFY. 监听客户端需要在周围并连接而不是共享，以便它可以正确处理NOTIFY消息。其他示例是打开 1-off 客户端以杀死一些挂起的东西或在命令行脚本中。
One very helpful thing is to centralize all access to your database in your app to one file. Don't litter pg.connectcalls or new clients throughout. Have a file like db.jsthat looks something like this:
一件非常有用的事情是将应用程序中对数据库的所有访问集中到一个文件中。不要pg.connect在整个过程中乱扔电话或新客户。有一个这样的文件db.js，看起来像这样：
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.connectto a custom pool of clients or whatever and only have to change things in one place.
通过这种方式，您可以将您的实现从pg.connect自定义客户端池或其他任何内容更改，并且只需要在一个地方进行更改。
Have a look at the node-pg-query modulethat does just this.
查看执行此操作的node-pg-query 模块。
回答by vitaly-t
I am the author of pg-promise, which simplifies the use of node-postgresvia promises.
我是pg-promise的作者，它通过 promise简化了node-postgres 的使用。
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.
它使用由node-postgres实现的连接池解决了与数据库连接和断开连接的正确方式的问题，其中包括自动事务等。
An individual request in pg-promiseboils down to just what's relevant to your business logic:
pg-promise 中的单个请求归结为与您的业务逻辑相关的内容：
db.any('SELECT * FROM users WHERE status = ', ['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 Exampletutorial, or on the project's home page.
您可以在Learn by Example教程或项目主页 中找到更多示例。
回答by amar
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')
它可以用作 db.query('<BEGIN,COMMIT,ROLLBACK,your query,anything')
回答by Aniket Thakur
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()
最好全局创建一个 pg 池，每次需要执行 db 操作时使用客户端，然后将其释放回池中。完成所有数据库操作后，使用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
更详细的可以参考我的博文 -源码
回答by alessioalex
As you can see from the documentationboth options are valid, so choose whichever you prefer. As you, I would go with the second choice.
正如您从文档中看到的，这两个选项都有效，因此请选择您喜欢的任何一个。和你一样，我会选择第二个。
回答by JM-AGMS
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'
}
回答by Duane Fields
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()
