NodeJS + ExpressJS proper way to manage connection to mysql DB

Question

I am developing an application using ReactJS / NodeJS + ExpressJS.

I am trying to understand what is the best way to handle connections to the database. This code works, but it seems that it constantly increases the number of connections and I don't think it's good, but you might enlighten me on this one.

When I freshly started my mysql server (without running my app) the connections were already 60, what are those?

MariaDB [(none)]> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_accept          | 0     |
| Connection_errors_internal        | 0     |
| Connection_errors_max_connections | 0     |
| Connection_errors_peer_address    | 0     |
| Connection_errors_select          | 0     |
| Connection_errors_tcpwrap         | 0     |
| Connections                       | 60    |
+-----------------------------------+-------+
7 rows in set (0.001 sec)

Then once I run my application the connections increased to 64:

MariaDB [(none)]> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_accept          | 0     |
| Connection_errors_internal        | 0     |
| Connection_errors_max_connections | 0     |
| Connection_errors_peer_address    | 0     |
| Connection_errors_select          | 0     |
| Connection_errors_tcpwrap         | 0     |
| Connections                       | 64    |
+-----------------------------------+-------+
7 rows in set (0.000 sec)

Then everytime I restart my back end application or make a request from the front end the number of connections seems to increase.

I am not sure how to manage the connections, and I admit that some parts of the code I'm using are not crystal clear to me (I'm new to react, express and node), so be patient.

This is part of the code I'm using, hopefully you can help me out finding the best way to manage connections.

Since the connection will be used in several areas of my application I created a .js file containing the following:

class Connection {

    static connect() {

        var mysql = require('mysql');
        var connection = null;

        var connection_settings = {
            host     : 'localhost',
            database : '..',
            user     : '..',
            password : '..',
        }

        connection = mysql.createConnection(connection_settings);

        connection.connect(function(err) {
            if(err) {
                console.log('error when connecting to db:', err);
            } 
        });

        return connection;

    }

}

module.exports = { Connection }

And then the file containing the code that queries the db:

const { Connection } = require('../database.js')

function openLessonSections (lessonID, connection){

    return new Promise(function (resolve, reject){
        const sql = "SELECT * FROM sections WHERE lesson_id=" + lessonID;
        connection.query (sql, function (error, result){
            console.log('Loading lesson "' + lessonID + '".');
            if (error) reject (error);
            resolve (result);
        });
    });
}

async function openLesson(lessonID, connection){
    return await openLessonSections(lessonID, connection);
}

exports.lessonOpen = function(req, res, next) {

    const connection = Connection.connect();

    console.log('request received');

    const lessonID = JSON.parse(req.body.lessonID);

    console.log('Opening lesson: ' + lessonID);

    openLesson(lessonID, connection)
        .then(function(result){
            console.log('The lesson was opened successfully.');
            res.status(200).json({sections: result});
        })
        .catch(function (error){
            res.status(500).json(error);
            console.log('There was an error while opening the lesson. ' + error);
        });

    connection.end();

}

I know I'm doing something wrong and I am a bit confused about what is the best approach.

Answer 1

One option is to create a connection pool (once):

const mysql = require('mysql');
const pool = mysql.createPool({
  host: 'myhost',
  user: 'myuser',
  password: 'mypass',
  database: 'mydb',
  connectionLimit: 10,
  supportBigNumbers: true
})

Then whenever there is a query, it grabs a connection from the pool:

function query(sql, args) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function(err, connection) {
      if (err) {
        return reject(err);
      }
      connection.query(sql, args, function(err, result) {
        connection.release();
        if (err) {
          return reject(err);
        }
        return resolve(result);
      });
    });
  });
}

^{Note: This example has been wrapped in a promise for promise-style code (rather than callback-style code.)}

Export the query function:

module.exports = {
  query
};

Put all of the above code in a module, for example db.js, and use it as follows:

const { query } = require('db.js');

const sql = 'SELECT * FROM table';
query(sql).then(result => {
  // Do something with result
})