current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 12 down vote favorite
1

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.

share|improve this question
up vote 24 down vote accepted

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:

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,
    dialectOptions: {
        ssl: true
    }
});
share|improve this answer
1  
By the way, I ended up creating my own node module to manage Heroku addons database connection with Sequelize, both locally and from Heroku servers, here it is: npmjs.com/package/sequelize-heroku – Iwazaru Dec 31 '14 at 17:20
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 1 '15 at 5: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 6 '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. – Iwazaru Jul 6 '15 at 20:38
    
Solid! You should accept your answer as well. – Tonithy Dec 17 '15 at 23:25
up vote 3 down vote

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

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

share|improve this answer
    
Thanks for the tip, but I'm not sure how this answers the question. – Iwazaru Jan 4 '16 at 14:39
    
You already answered your own question (dialectOptions.ssl: true), I just built upon it to provide a simpler solution – Giovanni P. Jan 4 '16 at 15:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.