What is causing this Heroku mysql connection error? (Trying to connect frontend to backend POST requ-CodePudding

{Solved}. New Bug

Error from Heroku logs:

Error: connect ECONNREFUSED 127.0.0.1:3306
2021-09-23T18:24:12.236657 00:00 app[web.1]: at TCPConnectWrap.afterConnect.          [as        oncomplete] (node:net:1146:16) {
2021-09-23T18:24:12.236658 00:00 app[web.1]: errno: -111,
2021-09-23T18:24:12.236658 00:00 app[web.1]: code: 'ECONNREFUSED',
2021-09-23T18:24:12.236658 00:00 app[web.1]: syscall: 'connect',
2021-09-23T18:24:12.236659 00:00 app[web.1]: address: '127.0.0.1',
2021-09-23T18:24:12.236659 00:00 app[web.1]: port: 3306,
2021-09-23T18:24:12.236659 00:00 app[web.1]: fatal: true

Information / Background:

React js front end (hosted now on Netlifty) Javascript Node backend using Express and MYSQL2 (Hosted on Heroku)

Goal: To connect Neflifty Frontend POST request with Heroku Backend fetching POST payload data and inserting it into MYSQL table.

Update: 09/24/2021

I have done all that was suggested. I have created a new database with clearDB. Added it and tested the connection in mysql Workbench. Created the table needed. Updated the backend code for creating the connection to the new database. Checked heroko variables and made sure they reflected correctly to the new database. Now there is an authorization issue. {solved}

New Backend Code with corrections:

const express = require('express');

const app = express();

const port = process.env.Port || 8000

app.listen(port);
console.log(`server is listing on ${port}`);

Time Out

Question: If this connects locally on workbench why wouldn't Heroku connect if they are added as variables? {Answered}

New question: why is it trying to use port 8000 when it should be using the environment? Why is it timing out?

Any help on this would be greatly appreciated.

CodePudding user response：

You can't connect to the database because you don't have any database instance running at Heroku. Once you pushed your code using Heroku CLI, Heroku sets up a NodeJS application for you, but it does not mean that it will set up a database for you aswell.

You can add a database to your app through Heroku's interface or CLI. From CLI (we are going to setup ClearDB but any MySQL database addon may work):

heroku addons:create cleardb:ignite

After done that, you wanna have your new database URL (which won't be localhost) with:

heroku config | grep CLEARDB_DATABASE_URL

The last command's output will be something like:

mysql://<user>:<password>@<host>/<database>?reconnect=true

Now, with this in hand, you shall modify your code a little bit with that new information. You don't want to expose your database credentials in your versioning control, so you may use environment variables to get it done:

const db =  mysql.createConnection({
    connectionaLimit: 50,
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    password: process.env.DB_PASSWORD,
    database: process.env.DATABASE,
    port: 3306
});

Also, you need to set the environment variables for your running Heroku's app:

heroku config:set DB_USER=<user>
heroku config:set DB_PASSWORD=<password>
heroku config:set DB_HOST=<host>
heroku config:set DATABASE=<database>

Now, you have a database instance running at Heroku and a NodeJS app instance running that can connect to this database.

For further reading, you may wanna take a look at those links:

https://lo-victoria.com/build-a-mysql-nodejs-crud-app-4-deploying-to-heroku-finale

https://www.bezkoder.com/deploy-node-js-app-heroku-cleardb-mysql/

https://raddy.co.uk/blog/how-to-deploy-node-js-express-ejs-mysql-website-on-heroku-cleardb/ (this one uses Heroku's interface)

CodePudding user response：

@ferandes these answered worked. Thanks for all the help.

" You can't connect to the database because you don't have any database instance running at Heroku. Once you pushed your code using Heroku CLI, Heroku sets up a NodeJS application for you, but it does not mean that it will set up a database for you aswell.

You can add a database to your app through Heroku's interface or CLI. From CLI (we are going to setup ClearDB but any MySQL database addon may work): "

"remove your ?reconnect=true from your DATABASE env var."

"environment variables are case sensitive. Just change to uppercase: process.env.PORT"