Postgresql Heroku

[upq]

Any body got postgresql working on heroku, I have followed the heroku documentation for heroku postgresql but it doesnt seem to work , I noticed in the documentation they are connecting using process.env.DATABASE_URL which doesnt exist in the knexfile.

https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js

PS: Im deploying a freshly generated instance of the megaboilerplate only changed the .env to my local db.

[sahat]

module.exports = {
  client: 'postgresql',
  connection: {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME
  }
};

should be the same as

module.exports = {
  client: 'postgresql',
  connection: process.env.DATABASE_URL
};

See knex.js documentation for additional information: http://knexjs.org/#Installation-client.

[sahat]

@upq Let me know if that works and I will update Mega Boilerplate generator to automatically use this single-line connection string when Heroku is selected.

[upq]

Nop , tried everything, using the process.env.DATABASE_URL didnt work and also breaks it locally, I have freshely downloaded a new instance and did nothing but following on the documentation on heroku and using npm run deploy also didnt work,

However I also noticed something in the logs

2016-07-06T22:08:53.263990+00:00 app[web.1]: POST /signup - - ms - -
2016-07-06T22:08:54.814141+00:00 app[web.1]: GET /__webpack_hmr 200 7.292
ms - -
2016-07-06T22:08:53.266404+00:00 heroku[router]: at=error code=H12 desc="R
equest timeout" method=POST path="/signup" host=myappname.herokuapp.com reques
t_id=25d3e327-3162-49a3-a203-4446bea8062f fwd="79.134.157.6" dyno=web.1 co
nnect=0ms service=30000ms status=503 bytes=0

Is it normal to get __webpack_hmr in production?

[plainspace]

Having a similar issue. If anyone knows how to get Mega Boilerplate working on Heroku, sharing instructions would be super helpful.

[sahat]

@plainspace @upq Looks like it's a SSL issue. From Heroku Docs:

SSL

Connecting to a Heroku Postgres database from outside of the Heroku network requires SSL. Your client or application must support and enable SSL to connect to a Heroku Postgres database. Most clients will connect over SSL by default, but on occasion it is necessary to set the sslmode=require parameter on a Postgres connection. Note: it is important to add this paramater in code rather than editing the config var directly. Various automated events such as failover can change the config var, and edits there would be lost.

After I have added ?ssl=true to the database connection string and that seemed to solve the issue.

https://github.com/tgriesser/knex/issues/239 has some interesting solutions. For example:

I ended up here struggling with wierd connection errors on Heroku. My issue was that the connection string given by Heroku doesn't have "?ssl=true" in the end even though Heroku requires it. This can be solved by "heroku config:set PGSSLMODE=require" which will tell the pg adapter to use SSL if the config doesn't say anything about it. It could be the case that pg.Client and pg.connect doesn't behave completely the same.

[sahat]

I have been trying to figure out why POST /signup is stuck on pending. Yes SSL flag is required, but it was another reason. I didn't set any of the environment variables from .env in my Heroku app. This error in particular is about the TOKEN_SECRET variable which can be added in the Heroku dashboard or via heroku config:set TOKEN_SECRET=some_random_string.

TypeError: secret must be a string or buffer
2016-07-07T08:47:26.426291+00:00 app[web.1]:     at typeError (/app/node_modules/jwa/index.js:15:10)
2016-07-07T08:47:26.426292+00:00 app[web.1]:     at Object.sign (/app/node_modules/jwa/index.js:31:13)
2016-07-07T08:47:26.426293+00:00 app[web.1]:     at Object.jwsSign [as sign] (/app/node_modules/jws/lib/sign-stream.js:23:24)
2016-07-07T08:47:26.426294+00:00 app[web.1]:     at Object.module.exports [as sign] (/app/node_modules/jsonwebtoken/sign.js:138:16)
2016-07-07T08:47:26.426295+00:00 app[web.1]:     at generateToken (user.js:17:14)
2016-07-07T08:47:26.426296+00:00 app[web.1]:     at .<anonymous> (user.js:86:23)
2016-07-07T08:47:26.426297+00:00 app[web.1]:     at tryCatcher (/app/node_modules/bluebird/js/main/util.js:26:23)
2016-07-07T08:47:26.426297+00:00 app[web.1]:     at Promise._settlePromiseFromHandler (/app/node_modules/bluebird/js/main/promise.js:507:31)
2016-07-07T08:47:26.426298+00:00 app[web.1]:     at Promise._settlePromiseAt (/app/node_modules/bluebird/js/main/promise.js:581:18)
2016-07-07T08:47:26.426299+00:00 app[web.1]:     at Async._drainQueue (/app/node_modules/bluebird/js/main/async.js:128:12)
2016-07-07T08:47:26.426300+00:00 app[web.1]:     at Async._drainQueues (/app/node_modules/bluebird/js/main/async.js:133:10)
2016-07-07T08:47:26.426300+00:00 app[web.1]:     at Immediate.Async.drainQueues [as _onImmediate] (/app/node_modules/bluebird/js/main/async.js:15:14)
2016-07-07T08:47:26.426301+00:00 app[web.1]:     at tryOnImmediate (timers.js:543:15)
2016-07-07T08:47:26.426302+00:00 app[web.1]:     at processImmediate [as _immediateCallback] (timers.js:523:5)

Also it would hang because not all errors are handled inside .catch. Right now, during signup, it's only catching duplicate entry, i.e. trying to signup with email that already exists. But even then, it wasn't a correct code for PostgreSQL. This should do the trick:

if (err.code === 'ER_DUP_ENTRY' || err.code === '23505') {

}}

[sahat]

So as long as you set PGSSLMODE=require, knexfile could be simplified like so, where first part will be used in production, and connection object will be used during development using .env values:

module.exports = {
  client: 'pg',
  connection: process.env.DATABASE_URL || {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME
  }
};

[plainspace]

@sahat thanks for troubleshooting this.

where did you add the following?

if (err.code === 'ER_DUP_ENTRY' || err.code === '23505') {

}}

I'm still getting the following error after adding the PGSSLMODE and TOKEN_SECRET to Heroku.

POST https://trykimono-production.herokuapp.com/auth/google 500 (Internal Server Error) (bundle.js:509)

[sahat]

@plainspace I have added that condition to POST /signup handler.

Looking at the google auth controller, the only time error 500 is thrown, it's here:

 // Step 2. Retrieve user's profile information.
    request.get({ url: peopleApiUrl, headers: headers, json: true }, function(err, response, profile) {
      if (profile.error) {
        return res.status(500).send({ message: profile.error.message });
      }

So, print out the value of profile and profile.error objects and you will know exactly what is happening. If it happens only in production, you can push the changes to Heroku ,then do heroku logs -t from the app's directory to see logs in real-time.

[plainspace]

am I missing something? it looks like the 500 is being thrown at return fetch(config.url, { here:

function exchangeCodeForToken({ oauthData, config, window, interval, dispatch }) {
  return new Promise((resolve, reject) => {
    const data = Object.assign({}, oauthData, config);

    return fetch(config.url, {
      method: 'post',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(data)
    }).then((response) => {
      if (response.ok) {
        return response.json().then((json) => {
          resolve({ token: json.token, user: json.user, window: window, interval: interval, dispatch: dispatch });
        });
      } else {
        return response.json().then((json) => {
          dispatch({
            type: 'OAUTH_FAILURE',
            messages: Array.isArray(json) ? json : [json]
          });
          closePopup({ window: window, interval: interval });
        });
      }
    });
  });
}

[damianfrizzi]

I had to add ssl: true to the knexfile to make it working locally and on heroku:

module.exports = {
  client: 'pg',
  connection: process.env.DATABASE_URL || {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: true
  }
};

[olso]

https://github.com/knex/knex/issues/239#issuecomment-641616327