CODEWITHSUNDEEP
mysqlnode.js
Baruch_Mashasha
Baruch_Mashasha

Reputation: 207

Nodejs with MYSQL problem to query outside the connection method

I make a function that connect once to mysql server and it work fine. now i make other function to just query but i cant do this. i call this function in my app.js

import:

const mysql = require("mysql");
const config = require("../configuration/config");

const connectToDB = () => {
  const connection = mysql.createConnection(config.database);
  connection.connect(err => {
    if (err) return console.log(err);
  });
  console.log("MYSQL DB CONNECTED");
};

then i make this function

const getAllUserReports = userID => {
  return new Promise((resolve, reject) => {
    const reports = mysql.query(
      `SELECT reports.id,report_types.[type],companies.id as companyID,reports.[year]
      FROM reports
      INNER JOIN report_types on reports.reportTypeID=report_types.id
      INNER JOIN companies on reports.companyID=companies.id
      where reports.creatorUserID=${userID} and active_report=1
      `
    );
    resolve(reports);
    reject("Server Error");
  });
};

i call the second function in my routes but "mysql.query" not work. in sql server its work like this.

There is other way to make it ?? I dont want to connect after once query i want one connect when server on.

Upvotes: 0

Views: 411

Answers (1)

O. Jones
O. Jones

Reputation: 108676

When you use the mysql npm package, you can't run a query from the mysql object. You must run it from the connection object instead. You must say

const reports = connection.query(/*whatever*/, function(error, results, fields) {
    /*handle query results*/
})

or the promisified equivalent. Otherwise the mysql package has no idea which connection to use.

And, with respect, your tactic of opening the connection just once and reusing it is doomed to failure if your server keeps running for any amount of time; if the connection drops your server can't proceed.

Use a connection pool. Then grab a connection from the pool for every query you must run. That puts connection management onto a robust and debugged piece of code. And, you'll get concurrent asynchronous querying as well.

Something like this (which I have not debugged) might work.

const mysql = require("mysql");
const config = require("../configuration/config");
const pool mysql.createPool(config.databaseConnectionPool);

...

let resultset;
pool.query (/*whatever*/, function (error, result, fields) {
   if (error) throw error;
   resultset = results;
});

Upvotes: 1

Related Questions