Connect MySQL database with node JavaScript application

Question

I make a node JavaScript app and deploy it on cPanel using SSH. App is working fine without database but when I connect the app with database on cPanel (GoDaddy) it takes times and shows the message "Error establishing a database connection". My connection code

const mysql = require('mysql');
const express = require('express');
const app = express();

var pool = mysql.createConnection({
    host: 'localhost',
    user: '<MY_USER_NAME>',
    password: '<MY_PASSWORD>',
    database: '<DB_NAME>'
});

pool.connect(function(err) {
  if (err) throw err;
  else{
  console.log("Connected!");
  }
});



module.exports = pool;

route where DB interact,but lost the connection.

app.post('/loginn', (req, res) => {
var id = req.body.id
  console.log("user_id= "+id);
  var sql = "select * from users where id  NOT IN ('" + id + "') ";
    pool.query(sql, function (err, rows) {
        if (err) throw err;
        else {
          res.render('allusers', {
            users: rows,
            user_id:id
          })
        }
    });
  });

Are you sure you have the credentials for the DB correct? Can you connect to the DB at all, or just not in your application? — djs
– djs, Commented Mar 29, 2020 at 4:24
I check it in the terminal first connection is build but when I request to any route than connection destroy — Hammad Ali
– Hammad Ali, Commented Mar 29, 2020 at 5:30
So Connected! is printed in the terminal, but when you run a request to a route-handler, the connection is lost, is that correct? Can you post the code where the route-handlers interact with the db? — djs
– djs, Commented Mar 29, 2020 at 17:40
Can you post where the pool is brought in and defined, and any code from this module that closes a db connection? I assume the first example is the relevant connection code for the entire app, but where is that code being required in your app? — djs
– djs, Commented Mar 29, 2020 at 18:50

djs · Accepted Answer · 2020-03-29 19:04:11Z

0

This answer is going to take the form of a debugging journey, because that's the only way I can see to get to the bottom of your issue.

Let's do a dead-simple representation of your app to make sure that you can send a query to MySQL and receive a response from a route-handler in Express. Setup your app like this:

const mysql = require('mysql');
const express = require('express');
const app = express();
const PORT = // define the PORT for your host

var connection = mysql.createConnection({
    host: 'localhost',
    user: '<MY_USER_NAME>',
    password: '<MY_PASSWORD>',
    database: '<DB_NAME>'
});

connection.connect(function(err) {
  if (err) console.error(err);
  console.log("Connected!");
});

app.get('/db-test', (req, res, next) => {

  var id = // fill in a user_id that you know exists
  var sql = `SELECT * FROM users WHERE id NOT IN ('${id}') `;

  console.log(sql); // confirm you are sending the sql request you believe you should be sending

  connection.query(sql, function (err, results, fields) {
      if (err) console.error(err);
      console.log(`results: ${results}\nfields: ${fields}`);
  });

});

app.listen(PORT);

And then hit the route /db-test from your app, and see what happens. If this works, then we will have at least proved that you CAN make requests between Express and MySQL. Right now, I'm not sure you can, so I'm not sure what to debug.

answered Mar 29, 2020 at 19:04

djs

4,0753 gold badges18 silver badges28 bronze badges

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

1 Comment

djs Over a year ago

So, the Connected! string still prints, but the request fails?

Collectives™ on Stack Overflow

Connect MySQL database with node JavaScript application

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related