node-postgres, Connection terminated unexpectedly

Question

I'm trying to connect to a remote database using node-postgres.

I can connect using the psql client, but I get the error Connection terminated unexpectedly while trying to run this (with same connection string as in psql client):

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

I've also been trying to connect with Sequelize ORM, but got the same error.

@EDIT

Using native mode fixed problem for client query using pg, and sequelize

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

Answer 1

I started having the same problem, but only with long time queries, i found a possible solution by setting idleTimeoutMillis in the Pool constructor, for example to 20000 (the default value is 10000)

See https://node-postgres.com/api/pool#new-pool-config-object-

Answer 2

Working with processes that could take hours, I found the solution using Pool but setting idleTimeoutMillis and connectionTimeoutMillis both with 0. Example:

const { Pool } = require('pg')

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

Answer 3

All the above solution didnt worked for me. I search over the internet but didn't find any suitable solution. In the end, I found out I was entering the wrong port number in the pg client.

This is where I found my actual port number: Server > Database > Properties

And updated it in my code. And problem was solved

Answer 4

using 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());

Sequelize:

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 5

I got this error the first time that worked with Postgres. I didn't know that the default port for Postgres is 5432. Changing the port to 5432 in my DB node config resolved the issue.

const db = knex({
    client: 'postgres',
        connection: {
            host: 'localhost',
            user: 'postgres',
            password: 'admin4321',
            database: 'postgres',
            port: 5432,
        }
    })

Answer 6

I did a lot of research to solve this problem. These pg configurations solved my problem

  acquireConnectionTimeout: 5000,
  pool: {
    min: 0,
    max: 10,
    createTimeoutMillis: 8000,
    acquireTimeoutMillis: 8000,
    idleTimeoutMillis: 8000,
    reapIntervalMillis: 1000,
    createRetryIntervalMillis: 100,
  },

Answer 7

Try this:

var pg = require('pg');
const client = new pg.Client(
{
    user: 'username',
    host: 'host',
    database: 'myDb',
    password: 'secretPswd',
    port: portnum,
});
client.connect(function (err){
    if(err)
        console.log(err);
    else
        console.log("Connected!");
});