Heroku Postgres add-on connection string for Nodejs app

Question

I have a problem deploying a Nodejs app with a Postgresql database. The database comes from Heroku itself (Heroku Postgres add-on, hobby-dev). My app refused to connect to the database.

I found where the problem came from but I can't find a clean solution. And I think I could have misunderstood something (I'm new to Node and Heroku).

Heroku automatically gives me an environment variable DATABASE_CONFIG that includes the port:

postgres://username:password@hostname:port/databasename

Then, to connect with pg in my app, I use process.env.DATABASE_CONFIG as a connection string. I do something like:

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

This fails to connect.

But if instead of using this environment variable, I cheat and change it, removing the port number from this connection string, it works.

I don't know why but the problem is that Heroku gives you this DATABASE_URL with the port included and you can't change it.

Did I do something wrong? Is there a clean solution to avoid that?

(because what I did is ugly as I hard-coded the DATABASE_CONFIG without the port directly in my code)

Thanks for your help!

Mando75 · Accepted Answer · 2017-11-15T05:47:21.143

First off, I would avoid using new Client() as this can lead to your connection being bottlenecked. Instead, use connection pooling. You can read a more indepth answer into why you want to do that here.

As for you direct issue, personally I have had trouble connecting to heroku postgres databases in the past, but here is a (basic) typical setup that works for me 99% of the time:

let pg = require('pg');
if (process.env.DATABASE_URL) {
  pg.defaults.ssl = true;
}

// include an OR statement if you switch between a local dev db and 
// a remote heroku environment

let connString = process.env.DATABASE_URL || 'postgresql://postgres:password@localhost:localpostgresport/yourlocaldbname';
const { Pool } = require('pg');

const pool = new Pool({
  connectionString : connString
});

My first guess would be that it may have to do with ssl not being enabled. I know that has cause me problems in the past. Secondly, you want to make sure that you uses the process.env.DATABASE_URL, as environment variable should be set as the postgres connection string by Heroku.

Hi, I've figured out what the problem is. It's the last part of the answer. I was using DATABASE_CONFIG instead of DATABASE_URL (which does not contain the port). I can't believe this was that simple and I could not see it. Thanks for your help! — macadam, Nov 16 '17 at 21:45
Of course! Getting set up with Heroku can be tricky sometimes. I'm happy you were able to figure it out :) — Mando75, Nov 18 '17 at 01:47

Heroku Postgres add-on connection string for Nodejs app

1 Answers1