I am working with making a website using Nodejs, express and MySQL.
The problem I am facing is that with the way I am making my connection and queries to the database right now the connection is always connected so it times out in the end. I tried using Pools but I have a problem where when I want the connections to be called through a function and then return the results of the query so that I won't need to repeat the same code for each time I want to query that specific query.
Here is how I have it now where I create a connection and then whenever a function is called it would query the code and return the results of the query.
const mysql = require('promise-mysql');
let db;
(async function (err)
{
db = await mysql.createConnection({
host: dotenv.parsed.DB_HOST,
user: dotenv.parsed.DB_LOGIN,
password: dotenv.parsed.DB_PASSWORD,
database: dotenv.parsed.DB_NAME,
charset: dotenv.parsed.DB_CHAR,
multipleStatements: dotenv.parsed.DB_MULTI
});
if (err){console.log(err);};
process.on('exit', () => {db.end()});
})();
/**
* @description gets the user's personnumber and password
* @param {*} personnummer is the personal number issued by the Swedish government for the person in question
*/
async function getPat(personnummer)
{
let sql = "SELECT * FROM patients where personnummer=?";
let res = await db.query(sql, [personnummer]);
return res;
}
So how would I go about doing that in pools? because when I try to do that in a pool
const connection = mysql.createPool({
host: dotenv.parsed.DB_HOST,
user: dotenv.parsed.DB_LOGIN,
password: dotenv.parsed.DB_PASSWORD,
database: dotenv.parsed.DB_NAME,
charset: dotenv.parsed.DB_CHAR,
multipleStatements: dotenv.parsed.DB_MULTI
});
/**
* @description gets the user's personnumber and password
* @param {*} personnummer is the personal number issued by the Swedish government for the person in question
*/
async function getPat(personnummer)
{
let patient;
(await connection).getConnection(function (err, connection)
{
if (err) throw err;
connection.query("SELECT * FROM patient where personnummer=?", [personnummer], function (err, result)
{
if (err) throw err;
patient = result;
});
});
return patient;
}
What happens in the code above is inside the connection. query function there is results but as soon as we go out of it then the results are empty I can't seem to figure what the cause is.
According to the documentation, mysql.createPool() returns a pool synchronously, therefore does not need to be awaited.
// mysql.createPool() returns a pool synchronously
const pool = mysql.createPool({
'host': dotenv.parsed.DB_HOST,
'user': dotenv.parsed.DB_LOGIN,
'password': dotenv.parsed.DB_PASSWORD,
'database': dotenv.parsed.DB_NAME,
'charset': dotenv.parsed.DB_CHAR,
'multipleStatements': dotenv.parsed.DB_MULTI
});
What the documentation doesn't tell you is how to promisify pool.query(), and that's what you are missing.
There are utilities that will promisify for you but it's pretty simple to do manually.
/**
* @description returns a Promise that delivers the user's personnumber and password (or Error)
* @param {*} personnummer is the personal number issued by the Swedish government for the person in question
*/
async function getPat(personnummer) {
return new Promise((resolve, reject) => {
pool.query("SELECT * FROM patient where personnummer=?", [personnummer], function (err, result) {
if (err) {
reject(err);
} else {
resolve(result);
}
});
});
}
Since getPat() returns Promise, its caller must use either .then() synatax or await syntax in order to access the result.