如何通过 Node.js 连接到 Postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9205496/
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
How to make connection to Postgres via Node.js
提问by Doboy
I find myself trying to create a postgres database, so I installed postgres and started a server with initdb /usr/local/pgsql/data, then I started that instance with postgres -D /usr/local/pgsql/datanow how can I interact with this through node? For example, what would the connectionstringbe, or how am I able to find out what it is.
我发现自己正在尝试创建一个 postgres 数据库，所以我安装了 postgres 并使用 启动了一个服务器initdb /usr/local/pgsql/data，然后我启动了该实例，postgres -D /usr/local/pgsql/data现在如何通过节点与它进行交互？例如，这会connectionstring是什么，或者我如何能够找出它是什么。
回答by Kuberchaun
Here is an example I used to connect node.js to my Postgres database.
这是我用来将 node.js 连接到我的 Postgres 数据库的示例。
The interface in node.js that I used can be found here https://github.com/brianc/node-postgres
我使用的 node.js 中的接口可以在这里找到https://github.com/brianc/node-postgres
var pg = require('pg');
var conString = "postgres://YourUserName:YourPassword@localhost:5432/YourDatabase";
var client = new pg.Client(conString);
client.connect();
//queries are queued and executed one after another once the connection becomes available
var x = 1000;
while (x > 0) {
client.query("INSERT INTO junk(name, a_number) values('Ted',12)");
client.query("INSERT INTO junk(name, a_number) values(, )", ['John', x]);
x = x - 1;
}
var query = client.query("SELECT * FROM junk");
//fired after last row is emitted
query.on('row', function(row) {
console.log(row);
});
query.on('end', function() {
client.end();
});
//queries can be executed either via text/parameter values passed as individual arguments
//or by passing an options object containing text, (optional) parameter values, and (optional) query name
client.query({
name: 'insert beatle',
text: "INSERT INTO beatles(name, height, birthday) values(, , )",
values: ['George', 70, new Date(1946, 02, 14)]
});
//subsequent queries with the same name will be executed without re-parsing the query plan by postgres
client.query({
name: 'insert beatle',
values: ['Paul', 63, new Date(1945, 04, 03)]
});
var query = client.query("SELECT * FROM beatles WHERE name = ", ['john']);
//can stream row results back 1 at a time
query.on('row', function(row) {
console.log(row);
console.log("Beatle name: %s", row.name); //Beatle name: John
console.log("Beatle birth year: %d", row.birthday.getYear()); //dates are returned as javascript dates
console.log("Beatle height: %d' %d\"", Math.floor(row.height / 12), row.height % 12); //integers are returned as javascript ints
});
//fired after last row is emitted
query.on('end', function() {
client.end();
});
UPDATE:- THE query.onfunction is now deprecated and hence the above code will not work as intended. As a solution for this look at:- query.on is not a function
更新：- 该query.on函数现已弃用，因此上述代码将无法按预期工作。作为此外观的解决方案：- query.on 不是函数
回答by vitaly-t
A modern and simple approach: pg-promise:
一种现代而简单的方法：pg-promise：
const pgp = require('pg-promise')(/* initialization options */);
const cn = {
host: 'localhost', // server name or IP address;
port: 5432,
database: 'myDatabase',
user: 'myUser',
password: 'myPassword'
};
// alternative:
// var cn = 'postgres://username:password@host:port/database';
const db = pgp(cn); // database instance;
// select and return a single user name from id:
db.one('SELECT name FROM users WHERE id = ', [123])
.then(user => {
console.log(user.name); // print user name;
})
.catch(error => {
console.log(error); // print the error;
});
// alternative - new ES7 syntax with 'await':
// await db.one('SELECT name FROM users WHERE id = ', [123]);
See also: How to correctly declare your database module.
另请参阅：如何正确声明您的数据库模块。
回答by mlaccetti
Just to add a different option - I use Node-DBIto connect to PG, but also due to the ability to talk to MySQL and sqlite. Node-DBI also includes functionality to build a select statement, which is handy for doing dynamic stuff on the fly.
只是添加一个不同的选项 - 我使用Node-DBI连接到 PG，但也是由于能够与 MySQL 和 sqlite 对话。Node-DBI 还包括构建 select 语句的功能，这对于动态处理动态内容非常方便。
Quick sample (using config information stored in another file):
快速示例（使用存储在另一个文件中的配置信息）：
var DBWrapper = require('node-dbi').DBWrapper;
var config = require('./config');
var dbConnectionConfig = { host:config.db.host, user:config.db.username, password:config.db.password, database:config.db.database };
var dbWrapper = new DBWrapper('pg', dbConnectionConfig);
dbWrapper.connect();
dbWrapper.fetchAll(sql_query, null, function (err, result) {
if (!err) {
console.log("Data came back from the DB.");
} else {
console.log("DB returned an error: %s", err);
}
dbWrapper.close(function (close_err) {
if (close_err) {
console.log("Error while disconnecting: %s", close_err);
}
});
});
config.js:
配置文件：
var config = {
db:{
host:"plop",
database:"musicbrainz",
username:"musicbrainz",
password:"musicbrainz"
},
}
module.exports = config;
回答by OmG
One solution can be using poolof clients like the following:
一种解决方案可以使用pool如下客户端：
const { Pool } = require('pg');
var config = {
user: 'foo',
database: 'my_db',
password: 'secret',
host: 'localhost',
port: 5432,
max: 10, // max number of clients in the pool
idleTimeoutMillis: 30000
};
const pool = new Pool(config);
pool.on('error', function (err, client) {
console.error('idle client error', err.message, err.stack);
});
pool.query('SELECT ::int AS number', ['2'], function(err, res) {
if(err) {
return console.error('error running query', err);
}
console.log('number:', res.rows[0].number);
});
You can see more details on this resource.
您可以查看有关此资源的更多详细信息。
回答by Gajus
Slonikis an alternative to answers proposed by Kuberchaun and Vitaly.
Slonik是 Kuberchaun 和 Vitaly 提出的答案的替代方案。
Slonik implements safe connection handling; you create a connection pool and connection opening/handling is handled for you.
Slonik 实现安全连接处理；您创建一个连接池，并为您处理连接打开/处理。
import {
createPool,
sql
} from 'slonik';
const pool = createPool('postgres://user:password@host:port/database');
return pool.connect((connection) => {
// You are now connected to the database.
return connection.query(sql`SELECT foo()`);
})
.then(() => {
// You are no longer connected to the database.
});
postgres://user:password@host:port/databaseis your connection string (or more canonically a connection URI or DSN).
postgres://user:password@host:port/database是您的连接字符串（或更规范的连接 URI 或 DSN）。
The benefit of this approach is that your script ensures that you never accidentally leave hanging connections.
这种方法的好处是您的脚本确保您永远不会意外地离开挂起的连接。
Other benefits for using Slonik include:
使用 Slonik 的其他好处包括：
回答by Naveen Karnam
We can also use postgresql-easy. It is built on node-postgresand sqlutil. Note:pg_connection.js& your_handler.jsare in the same folder. db.jsis in the config folder placed.
我们也可以使用postgresql-easy。它建立在node-postgres和sqlutil 之上。 注意：pg_connection.js和your_handler.js在同一个文件夹中。db.js位于 config 文件夹中。
pg_connection.js
pg_connection.js
const PgConnection = require('postgresql-easy');
const dbConfig = require('./config/db');
const pg = new PgConnection(dbConfig);
module.exports = pg;
./config/db.js
./config/db.js
module.exports = {
database: 'your db',
host: 'your host',
port: 'your port',
user: 'your user',
password: 'your pwd',
}
your_handler.js
your_handler.js
const pg_conctn = require('./pg_connection');
pg_conctn.getAll('your table')
.then(res => {
doResponseHandlingstuff();
})
.catch(e => {
doErrorHandlingStuff()
})
