ClearDB connecting with 'npm run start' but not with Heroku deploy; ERR_CONNECTION_REFUSED

Question

I deployed a node app on Heroku and set up ClearDB with MySQL Workbench. When I use npm run start it works great, data shows up. When I open in Heroku; heroku local web and heroku open the app is deployed but no data is displayed. The error message is resource: net::ERR_CONNECTION_REFUSED.

My database is tiny, so the size limit is not an issue. My Procfile is set to

web: npm run start

Why isn't my clearDB connecting through Heroku?

Some other answers I've read involve an .env file, which I didn't have. I tried creating one with the DATABASE_URL but no change. I also tried adding port: process.env.PORT || 3036 to my database config file.

I can connect to the Heroku / ClearDB database through MySQL Workbench and all the tables are there.

My database config file:

import mysql from 'mysql'

let pool = mysql.createPool({
connectionLimit: 10,
user: '******',
password: '*******',
host: '*******',
database: '*******',
port: process.env.PORT || 3036
});

let chirprdb = {};

chirprdb.all = () => {
return new Promise((resolve, reject) => {
    pool.query('SELECT * FROM chirps', (err, results) => {
        if (err) {
            return reject(err);
        }
        return resolve(results);
    })
})
}

export default chirprdb;

Expected result: see the app and all its data on the deployed Heroku URL

Actual result: seeing the app but no data is loading, error is resource: net::ERR_CONNECTION_REFUSED

Edit I changed the database (so as to not publish my credentials here) and created a new connection with MySQL Workbench. Clicking 'test connection' gave me this warning .

router that imports chirperdb

import { Router } from 'express';
import db from '../db'

let router = Router();

router.get('/:id?', async (req, res) => {
let id = req.params.id;
if (id) {
    try {
        let results = await db.one(id);
        res.json(results);
    } catch (e) {
        console.log(e);
        res.sendStatus(500);
    }
} else {
    try {
        let results = await db.all();
        res.json(results);
    } catch (e) {
        console.log(e);
        res.sendStatus(500);
    }
}
});

I would remove your credentials from the question. With that being said, ERR_CONNECTION_REFUSED usually points to an issue with connecting to your desired resource, in this case your database. This could be due to a wrong IP address, an incorrect port, the server is listening on the wrong port, or a credentials issue. Can you compare your connection string from your workbench to the one your code resolves? Also, where are you using chirprdb? Maybe post that section of code too — Nathan, Feb 12 '19 at 02:25
@NathanWright what are the correct ports? I had the app listening on process.env.PORT || 3000. — cDub, Feb 12 '19 at 21:32

ChrisGPT was on strike · Answer 1 · 2019-02-12T15:23:58.277

First of all, before you do anything else, change your ClearDB credentials. You've posted them here and you can't trust them anymore. Editing the question to remove them isn't enough.

Some other answers I've read involve an .env file, which I didn't have. I tried creating one with the DATABASE_URL but no change.

Don't use an .env file on Heroku. That file is just a roundabout way of populating environment variables, but Heroku supports environment variables natively. If you need to set environment variables, do it with heroku config:set or the web interface.

I also tried adding port: process.env.PORT || 3036 to my database config file.

The PORT environment variable is the port your web server needs to listen on, not the port you should use to connect to your database. There should be a CLEARDB_DATABASE_URL environment variable already set by the ClearDB addon. Use that (or individual CLEARDB_ variables, if you must) to connect to MySQL.

You can use the parse-database-url library to parse the single CLEARDB_DATABASE_URL value and connect to your database.

ClearDB connecting with 'npm run start' but not with Heroku deploy; ERR_CONNECTION_REFUSED

1 Answers1