Node.js SQL server crashes when receiving multiple requests

Question

I have a NodeJS application which is my server and I created a Database class to help me handle querying my SQL DB. If I send requests a second between each other, everything runs fine.. no problems.. But if I start spamming requests to my server it crashes due to Error: Cannot enqueue Quit after invoking quit.

Here's my query function inside my Database class

static query(query: string): Promise<any> {
    console.log('Query: ' + query);
    return new Promise((resolve, reject) => {
        this.connect().then(success => {
            sqlConn.query(query, (err, results) => {
                if (err) { return reject(err);
                } else {
                    return resolve(results);
                }
            });
        }).catch(err => {
            return reject(err);
        }).then( () => {
           if (sqlConn.state !== 'disconnected') {
            sqlConn.end();
           }
        });
    });
};

and here's the this.connect() function

static connect(): Promise<any> {
    return new Promise((resolve, reject) => {
        sqlConn = mysql.createConnection(this.connectionData);
        sqlConn.connect(err => {
            if (err) { return reject(err); } else {
                return resolve('SQL connection established');
            }
        });
    });
};

I'm pretty sure the problem appears sometimes, it would still be processing one query, and then another query comes before the first one finishes, so it would call sqlConn.end() twice, even when it's already disconnected? Any help is greatly appreciated...

> Main goal is for the query to wait till it's 100% done before it runs the next one..

Answer 1

You can simplify your code by using the npm module mysql and use it's built-in connection pool.

From the documentation:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

You can, of course, create your own function that promisifies that call like this:

function query (sql) {
  return new Promise((resolve, reject) => {
    pool.query(sql, (error, results, fields) => 
      error ? reject(error) : resolve({ results, fields });
  };
}     

Answer 2

If you really wants to use this approach then please use eachSeries function of async library.

var chunkedArray= [];
async.eachSeries(chunkedArray, startUpload, endUpload);

funtion startUpload(data,cb){
    //iterate over every single item in array 1 at a time
    }

function endUplaod(err){

//finally call this
}

This might help:- https://caolan.github.io/async/docs.html#eachSeries

But i rather suggest you to use pooling of connection which make less overhead on your db and you can use your mysql more efficiently then making multiple connection.

// Load module
var mysql = require('mysql');
// Initialize pool
var pool      =    mysql.createPool({
    connectionLimit : 10,
    host     : '127.0.0.1',
    user     : 'root',
    password : 'root',
    database : 'db_name',
    debug    :  false
});    
module.exports = pool;