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.

user_2185093 · Answer

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
})

user_1766831 · Answer

Barking up the wrong tree.

The STATUS Connections is a counter that starts at zero when the server (mysqld) starts up. Connections/Uptime is more interesting -- that might be around 1 per second or 1 per hour. I have seen Connections exceed a billion, but the server had been running for several weeks.

On the other hand, there may be some form of "connection pooling", but that could be introduced by any of several components in your stack.

I am trying to understand what is the best way to handle connections to the database.

The simple answer is "don't worry about it".

One web page should (in most cases) involve one MySQL/MariaDB connection. If your code makes multiple connections to build one web page, then it is inefficient. You seem to have some back and forth via various JS components? Probably that is no "state" saved, so they may have to make separate connections. Draw a diagram (or make a list) of the "data flow". If it is going back and forth "too much" that is "not good". Normal web pages have one connection, build the entire page, then go away. However with JS and AJAX, it is tempting to do back and forth -- resist it except when the interaction is needed.

NodeJS + ExpressJS proper way to manage connection to mysql DB

2 Answers