CODEWITHSUNDEEP
mysqlnode.jsnode-mysql
Diogo
Diogo

Reputation: 33

Perform query on tables dependent on first result in mysql node

I have a table called Vaccines. For each vaccine theres a certain number of batches and a certain number of Pathologies so I have created one table for the batches (VacLots) and one table for the Pathologies (VacPathologies).

Both the VacLots and VacPathologies are indexed using the Vaccine identifier.

To create a Vaccine I then do a query to retrieve all the Vaccines (for a specific country) and for each identifier I need to perform two extra queries: one to identify its batches and another one to identify its pathologies.

The code: This is the function used to execute a query with the specified arguments.

executeQueryWithParams(queryToExecute, queryArgs, processRow) {
    this.pool.getConnection((err, conn) => {
        if (err) {
            console.log(`${err} executing the query ${queryToExecute} for database ${this.pool}`);
            cb({reason: 'Failed to get connection', err});
        } else {
            let query = conn.query(queryToExecute, queryArgs, (error, rows) => {
                conn.release();
                if (error) {
                    cb({reason: `Failed to execute query ${queryToExecute}`, error});
                } else {
                    cb(null , rows);
                }
        });
}

This is the function which receives the country id and is responsible for creating Vaccine objects.

getVaccinesForCountryID = function (countryID) {
 return new Promise((resolve, reject) => {
   database.executeQueryWithParams(queries[GET_VACCINE_QUERY_INDEX], [countryID], function(err, rows) {
     if (err || rows.length == 0) {
       reject(utils.isNullOrUndefined(err) ? new Error('Empty results') : err);
     } else {
       resolve(processResultRows(rows));
     }
   });
 });

The process results function. 

function processResultRows(rows) {
  const promiseArray = [];
  for (const row of rows) {
    const vaccine = instantiateVaccine(row);
    promiseArray.push(hydrateVaccine(vaccine));
  }
  return Promise.all(promiseArray);
}

The instantiateVaccine function does nothing but create a Vaccine object with some of the information already available in the row.

function hydrateVaccine(vaccine) {
    return getLotsForVaccine(vaccine).then(data => getPathologiesForVaccine(data));
}

Retrieves the lots for a specific vaccine.

function getLotsForVaccine(vaccine) {
  return getDetailsVaccine(GET_VACCINE_LOTS_QUERY_INDEX, vaccine, (vac, rows) => {
    const lotsArray = [];
    for (const row of rows) {
      lotsArray.push({lot: row.lot});
    }
    vac.lots = lotsArray;
  });
}

Retrieves the pathologies for a specific vaccine.

function getPathologiesForVaccine(vaccine) {
  return getDetailsVaccine(GET_VACCINE_PATHOLOGIES_QUERY_INDEX, vaccine, (vac, rows) => {
    const pathologiesArray = [];
    for (const row of rows) {
      pathologiesArray.push({pathology: row.pathology});
    }
    vac.pathologies = pathologiesArray;
  });
}

Generic function to retrieve some details (either the lots or pathologies)

function getDetailsVaccine(queryIndex, vaccine, hydrateFunction) {
  return new Promise((resolve, reject) => {
    database.executeQueryWithParams(queries[queryIndex], [vaccine.identifier], (err, rows) => {
      if (err) {
        reject({error: err, query: queries[queryIndex], identifier: vaccine.identifier});
      } else {
        hydrateFunction(vaccine, rows);
        resolve(vaccine);
      }
    });
  });

This all works well if not a lot of requests are occurring but as soon as more than 10 requests occur (for instance using ab -k -c 2 -n 10) I get connection refused and the pool has a maximum number of connections of 1000.

I feel like my mix of callbacks and Promises might be a problem but I'm currently unable to implement the executeQueryWithParams function as a Promise.

EDIT: This is the code used to create the pool (referenced as this.pool in executeQueryWithParams function)

const pool = mysql.createPool({
    connectionLimit : '1000',
    host                        : 'localhost',
    user                    : 'readOnlyUser',
    password                :   'hRND74SKUjuH4uLU',
    database                :   'Calendar',
  debug           : false
});

Upvotes: 0

Views: 271

Answers (1)

Diogo
Diogo

Reputation: 33

I'll post my own solution as indicated by @abdulbarik.

Implementing the executeQueryWithParams in the following way allows the user to use mysql pool with a Promise, this has been tested with 500 concurrent connections on a total of 100000 connections and theres no issue with the TOO_MUCH_CONNECTIONS error and can be used as a Promise if you wish to chain.

function executeQueryWithParams(queryToExecute, queryArgs) {
  return new Promise((resolve, reject) => {
    this.pool.query(queryToExecute, queryArgs, function(err, rows) {
      if (err) {
        reject({reason: 'Error executing query', err});
      } else {
        resolve(rows);
      }
    });
  });
}

Upvotes: 1

Related Questions