Can't connect to heroku postgresql database from local node app with sequelize

Question

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.

They now have some official Node.js but non-sequelize specific documentation at: devcenter.heroku.com/articles/… which might also be of interest. — Ciro Santilli OurBigBook.com
– Ciro Santilli OurBigBook.com, Commented Mar 20, 2021 at 16:11

Ciro Santilli OurBigBook.com · Accepted Answer · 2021-03-21 22:51:58Z

65

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

edited Mar 21, 2021 at 22:51

Ciro Santilli OurBigBook.com

392k120 gold badges1.3k silver badges1.1k bronze badges

answered Dec 29, 2014 at 11:54

clemlatz

8,0955 gold badges41 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

phillipwei Over a year ago

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.

Chris Houghton Over a year ago

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

clemlatz Over a year ago

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.

spectre10 Over a year ago

Holy moly, I spend 3 hours on this error. Thanks a lot!

Noah Over a year ago

To fix the self signed certificate error, refer to this post: stackoverflow.com/questions/58965011/…

|

Giovanni P. · Accepted Answer · 2018-01-04 12:35:37Z

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

edited Jan 4, 2018 at 12:35

answered Jan 4, 2016 at 12:14

Giovanni P.

1,05616 silver badges24 bronze badges

1 Comment

Giovanni P. Over a year ago

You already answered your own question (dialectOptions.ssl: true), I just built upon it to provide a simpler solution

embarker · Accepted Answer · 2021-01-12 11:12:46Z

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

answered Jan 12, 2021 at 11:12

embarker

1941 silver badge6 bronze badges

2 Comments

atultw Over a year ago

This worked for me while accepted answer didn't. Must be a recent update.

Ciro Santilli OurBigBook.com Over a year ago

rejectUnauthorized: false also mentioned at: stackoverflow.com/questions/58965011/… 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 stackoverflow.com/a/27688357/895245

Tunji Oyeniran · Accepted Answer · 2021-02-12 11:41:10Z

2

You need just these 2 things

Append ?sslmode=require to your POSTGRES DATABASE URI
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

answered Feb 12, 2021 at 11:41

Tunji Oyeniran

4,4541 gold badge20 silver badges16 bronze badges

Comments

Ismael Terreno · Accepted Answer · 2019-11-17 15:00:42Z

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.

Collectives™ on Stack Overflow

Can't connect to heroku postgresql database from local node app with sequelize

5 Answers 5

8 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related