Nodejs - Mysql Error: Too Many Connections

Question

I recently made a refactor of my code to adopt the MVC standard, because it was very disorganized!

However, an error called "Too Many Connections" started to appear. This error causes my Node-mysql to crash and no more requests until I re-start Node.

Code link: https://github.com/LeonardoVini/node-refctoring/blob/master/refctoring-backend.zip

I don’t know if ES6 is causing this or if I did something wrong. I tried several times to fix this mistake.

Error that appears:

Connection code:

const mysql = require('mysql')

const connMySQL = () => {
    return mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'eqix1998',
        database: 'quotedb',
        port: 3306
    });
}

module.exports = () => {
    return connMySQL;
}

server.js:

const express = require('express');
const consign = require('consign')
const bodyParser = require('body-parser');
const cors = require('cors');

const corsOptions = {
    origin: 'http://localhost:4200',
    optionsSuccessStatus: 200
}

const app = express();

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(cors(corsOptions))

consign()
    .include('app/routes')
    .then('config/dbConnection.js')
    .then('app/models')
    .then('app/controllers')
    .into(app);

module.exports = app;

Route archive:

module.exports = (application) => {
    application.get('/quotes-createdBy/:username', (req, res) => {
        application.app.controllers.quotes_by_name.getQuotesByName(application, req, res);
    });

    application.get('/quotes-closed-createdBy/:username', (req, res) => {
        application.app.controllers.quotes_by_name.getClosedQuotesByName(application, req, res);
    });

    application.get('/quotes-qss-createdBy/:username', (req, res) => {
        application.app.controllers.quotes_by_name.getQSSQuotesByName(application, req, res);
    });
}

Controller file:

module.exports.getQuotesByName = (application, req, res) => {

    let username = req.params.username

    var connection = application.config.dbConnection();
    var quotes_by_nameDAO = new application.app.models.Quotes_By_NameDAO(connection);

    quotes_by_nameDAO.getQuotesByName(username, (error, results) => {
        if (error) throw error
        return res.send(results)
    });
}

module.exports.getClosedQuotesByName = (application, req, res) => {

    let username = req.params.username

    var connection = application.config.dbConnection();
    var quotes_by_nameDAO = new application.app.models.Quotes_By_NameDAO(connection);

    quotes_by_nameDAO.getClosedQuotesByName(username, (error, results) => {
        if (error) throw error
        return res.send(results)
    });
}

module.exports.getQSSQuotesByName = (application, req, res) => {

    let username = req.params.username

    var connection = application.config.dbConnection();
    var quotes_by_nameDAO = new application.app.models.Quotes_By_NameDAO(connection);

    quotes_by_nameDAO.getQSSQuotesByName(username, (error, results) => {
        if (error) throw error
        return res.send(results)
    });
}

Model file:

function Quotes_By_NameDAO(connection) {
    this._connection = connection;
}

// Get quotes by nome - QuoteStatus = 'In Progress' OR QuoteStatus = 'Stand By'
Quotes_By_NameDAO.prototype.getQuotesByName = function (username, callback) {
    this._connection.query(`
        SELECT * FROM quotes
        WHERE
            (QuoteStatus = 'In Progress' OR QuoteStatus = 'Stand By')
        AND
            CreatedBy = ?
    `, username, callback)
}

// Get closed quotes by nome - QuoteStatus = 'Closed'
Quotes_By_NameDAO.prototype.getClosedQuotesByName = function (username, callback) {
    this._connection.query(`
        SELECT * FROM quotes
        WHERE
            QuoteStatus = 'Closed'
        AND
            CreatedBy = ?
    `, username, callback)
}

// Get QSS quotes by nome - QuoteStatus = 'Sent to QSS'
Quotes_By_NameDAO.prototype.getQSSQuotesByName = function (username, callback) {
    this._connection.query(`
        SELECT * FROM quotes
        WHERE
            QuoteStatus = 'Sent to QSS'
        AND
            CreatedBy = ?
    `, username, callback)
}

module.exports = () => {
    return Quotes_By_NameDAO;
}

Answer 1

I built an example based on the code you sent using the pool of connections of MySQL. In the example I put up there will be at most 10 simultaneous connections. This means that the others will be lined up.

app js.

const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');

const quotes = require('./app/routes/quotes');

const corsOptions = {
    origin: 'http://localhost:4200',
    optionsSuccessStatus: 200,
};

const app = express();

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(cors(corsOptions));

app.use('/quotes', quotes);

const server = app.listen(process.env.PORT || 9095, () => {
    console.log(`Node app is running on port ${server.address().port}`);
});

app/Routes/Quotes.js

const express = require('express');
const controller = require('../controllers/quotes');

const router = express.Router();

router.get('/', controller.list);

module.exports = router;

app/controllers/Quotes.js

const model = require('../models/quotes');

const list = async (req, res) => {
  try {
    res.send(await model.list());
  } catch (e) {
    res.status(500).send(e.message);
  }
};

module.exports = {
  list,
};

app/models/Quotes.js

const { promisify } = require('utils');
const pool = require('./connection');

const list = async () => {
  const query = promisify(pool.query);

  return query('SELECT * FROM quotes');
};

module.exports = {
  list,
};

app/models/Connection.js

const mysql = require('mysql');

const pool  = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'eqix1998',
  database: 'quotedb',
  port: 3306,
  connectionLimit : 10,
});

module.exports = pool;

Answer 2

What happens is that every time you invoke application.config.dbConnection(); a new connection is created with the bank, instead of exporting a function that creates a connection, create a connection and export it directly:

const mysql = require('mysql')

const connMySQL = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'eqix1998',
    database: 'quotedb',
    port: 3306
});


module.exports = () => {
    return connMySQL;
}