node-postgres，连接意外终止

Question

我正在尝试使用node-postgres连接到远程数据库。

我可以使用psql客户端进行连接，但在尝试运行此命令时(使用与psql客户端相同的连接字符串)，我得到了错误Connection terminated unexpectedly：

const { Pool, Client } = require('pg')
const connectionString = '...'

const pool = new Pool({
  connectionString: connectionString,
})

pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  pool.end()
})

const client = new Client({
  connectionString: connectionString,
})
client.connect()

client.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  client.end()
})

我也一直在尝试连接Sequelize ORM，但得到了同样的错误。

@编辑

使用原生模式修复了使用pg和sequelize的客户端查询问题

const { Pool, Client } = require('pg').native

Answer 1

我开始遇到同样的问题，但只有在长时间查询的情况下，我找到了一个可能的解决方案，通过在池构造函数中设置idleTimeoutMillis，例如设置为20000 (默认值为10000)。

请参阅https://node-postgres.com/api/pool#new-pool-config-object-

Answer 2

使用pg：

import pg from 'pg';  

const conStringPri = `postgres://${username}:${password}@${host}/postgres`;
  const Client = pg.Client;
  const client = new Client({connectionString: conStringPri});
  client.connect();

  client.query(`CREATE DATABASE ${dataBaseName}`)
    .then(() => client.end());

序列化：

const sequelize = new Sequelize(dbName, username, password, {
  host: host || 'localhost',
  dialect: type || 'postgres',
  operatorsAliases,
  pool: {
    max: 5,
    min: 0,
    idle: 300000,
    acquire: 300000
  },
  port: port || 5432,
  logging: log => console.log('logging:', log)
});

const models = {};
// read all models from same folder
glob.sync(path.join(__dirname, '**/*.js'))
  .forEach(file => {
    const model = sequelize.import(file);
    models[model.name] = model;
  });

Object.keys(models).forEach(model => {
  if (models[model].associate) {
    models[model].associate(models);
  }
});

models.user.create(userObject);
models.user.findAll({where: {name: 'john'}});

Answer 3

在处理可能需要几个小时的进程时，我找到了解决方案，使用Pool，但将idleTimeoutMillis和connectionTimeoutMillis都设置为0。示例：

const { Pool } = require('pg')

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'my_database',
  password: 'XXXX',
  port: 5423,
  idleTimeoutMillis: 0,
  connectionTimeoutMillis: 0,
});