24

I'm trying to connect to a Heroku postgresql database from a local nodejs app with Sequelize. I followed this two guides an everything is working perfectly fine on the heroky server side, but my node app won't connect to heroku when I run it locally on my Mac.

Here is how I start the local app:

DATABASE_URL=$(heroku config:get DATABASE_URL) nodemon

Gets me:

Sequelize: Unable to connect to the database:

But I get the correct URL by doing this:

echo $(heroku config:get DATABASE_URL)

And those commands are working fine:

heroku pg:psql
psql $(heroku config:get DATABASE_URL)

Here is my nodejs code :

var match = process.env.DATABASE_URL.match(/postgres:\/\/([^:]+):([^@]+)@([^:]+):(\d+)\/(.+)/)
sequelize = new Sequelize(match[5], match[1], match[2], {
    dialect:  'postgres',
    protocol: 'postgres',
    port:     match[4],
    host:     match[3],
    logging: false
})

sequelize
.authenticate()
.complete(function(err) {
    if (!!err) {
        log('Sequelize: Unable to connect to the database:', err);
    } else {
        http.listen(process.env.PORT || config.server.port, function(){
            log('Web server listening on port '+process.env.PORT || config.server.port);
        });
    }
});

I tried to add native: true to the sequelize options, but then I get:

    /Users/clement/Projets/XMM/node_modules/sequelize/lib/sequelize.js:188
      throw new Error('The dialect ' + this.getDialect() + ' is not supported.
            ^
Error: The dialect postgres is not supported. (Error: Please install postgres package manually)
    at new module.exports.Sequelize (/Users/clement/Projets/XMM/node_modules/sequelize/lib/sequelize.js:188:13)
    at Object.<anonymous> (/Users/clement/Projets/XMM/server.js:17:14)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Function.Module.runMain (module.js:497:10)
    at startup (node.js:119:16)
    at node.js:929:3

Even after doing:

npm install pg
npm install -g pg
brew install postgresql

This is working by the way:

var pg = require('pg');
pg.connect(process.env.DATABASE_URL+'?ssl=true', function(err, client, done) {
    if (err) return console.log(err);
    client.query('SELECT * FROM pg_catalog.pg_tables', function(err, result) {
        done();
        if(err) return console.error(err);
        console.log(result.rows);
    });
});

But i'd rather use Sequelize.

clemlatz
  • 7,543
  • 4
  • 37
  • 51
  • They now have some official Node.js but non-sequelize specific documentation at: https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js which might also be of interest. – Ciro Santilli OurBigBook.com Mar 20 '21 at 16:11

5 Answers5

63

OK, found the answer by browsing sequelize source code : https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/connection-manager.js#L39

To activate SSL for PG connections you don't need native: true or ssl: true but dialectOptions.ssl: true so the following did finally work:

sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect: 'postgres',
    protocol: 'postgres',
    dialectOptions: {
        ssl: true
    }
});

To work around the self signed certificate bug on node-postgres version 8 mentioned at SequelizeConnectionError: self signed certificate you can use instead:

sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect: 'postgres',
    protocol: 'postgres',
    dialectOptions: {
        ssl: {
            require: true,
            rejectUnauthorized: false
        }
    }
});
Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
clemlatz
  • 7,543
  • 4
  • 37
  • 51
  • 1
    Thanks! This took me forever to figure out. There are bad instructions elsewhere online (was also trying to use native). It's important to note that sequelize's error messages are bad (the actual error is not at the top) -- so if you get a dialect unsupported message look a bit lower ... in my case, pg-hstore was missing and needed to be installed. – phillipwei May 01 '15 at 05:19
  • Thanks for this. Maybe this a super noob question - I've got the `ssl` option set as `true` on the server, and I've added this on the client. The connection is working. Is that it? Do I need to do anything else to enable SSL? Super thanks – Chris Houghton Jul 06 '15 at 16:59
  • Hi Chris, I can't answer your question, and as it is not directly related to the topic discussed here, I think you should create a brand new question on stackoverflow in order to get help. – clemlatz Jul 06 '15 at 20:38
  • Holy moly, I spend 3 hours on this error. Thanks a lot! – spectre10 Mar 04 '17 at 19:45
  • In my case, this got me a little farther, but I still can't connect. Now I'm getting `ERROR: self signed certificate`. – Noah May 18 '20 at 18:45
  • 5
    To fix the `self signed certificate` error, refer to this post: https://stackoverflow.com/questions/58965011/sequelizeconnectionerror-self-signed-certificate – Noah May 18 '20 at 18:48
  • The second option worked for me like a charm after a whole day of debugging and searching. – Basil Dec 03 '21 at 21:50
17

You no longer need to parse the DATABASE_URL env variable, there is a Sequelize constructor which accepts the connection URL:

sequelize = new Sequelize(process.env.DATABASE_URL, {
    dialect: 'postgres',
    protocol: 'postgres',
    dialectOptions: {
        ssl: true
    }
});
Giovanni P.
  • 1,017
  • 15
  • 23
  • 4
    You already answered your own question (dialectOptions.ssl: true), I just built upon it to provide a simpler solution – Giovanni P. Jan 04 '16 at 15:29
11

One needs to add dialectOptions under ssl

 "development": {
    "username": process.env.DB_USERNAME,
    "password": process.env.DB_PASSWORD,
    "database": process.env.DB_NAME,
    "host": process.env.DB_HOST,
    "dialect": process.env.DB_DIALECT,
    "dialectOptions": {
        ssl: {
            require: true,
            rejectUnauthorized: false
        }
    }
},

Source is as per official sequelize github

embarker
  • 194
  • 1
  • 6
  • 1
    This worked for me while accepted answer didn't. Must be a recent update. – atultw Mar 13 '21 at 19:34
  • `rejectUnauthorized: false` also mentioned at: https://stackoverflow.com/questions/58965011/sequelizeconnectionerror-self-signed-certificate The only env var Heroku now defines seems to be `process.env.DATABASE_URL` though, but sequelize parses all the fields out of that correctly via https://stackoverflow.com/a/27688357/895245 – Ciro Santilli OurBigBook.com Mar 20 '21 at 16:25
2

You need just these 2 things

  1. Append ?sslmode=require to your POSTGRES DATABASE URI
  2. Make sure you have rejectUnauthorized: false in your dialectOptions
const sequelize = new Sequelize(`${process.env.DATABASE_URI}?sslmode=require`, {
  url: process.env.DATABASE_URI,
  dialect: 'postgres',
  logging: false,
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: false, // very important
    }
  }
}

For More Information, here is an Article on Heroku DevCenter about it
https://devcenter.heroku.com/articles/heroku-postgresql#heroku-postgres-ssl

Tunji Oyeniran
  • 3,948
  • 1
  • 18
  • 16
1

I had the same problem and for these cases you can consider the following documentation example on how to connect the heroku database:

https://sequelize.readthedocs.io/en/1.7.0/articles/heroku/

At the end i did implement a code like :

const sequelize = new Sequelize(
    process.env.DATABASE_NAME_DB_CONFIG,
    process.env.USER_NAME_DB_CONFIG,
    process.env.USER_PASSWORD_DB_CONFIG,
    {
        host: process.env.HOST_DB_CONFIG,
        dialect: process.env.DIALECT_DB_CONFIG,
        protocol: process.env.PROTOCOL_DB_CONFIG,
        logging:  true,
        dialectOptions: {
            ssl: true
        },
        pool: {
            max: 5,
            min: 0,
            idle: 10000
        }
    }
);

where you need to take account the dialectOptions with ssl: true.

That's all you need to know.

Ismael Terreno
  • 1,021
  • 8
  • 5