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.

FYI, your code is wide open to SQL injection attacks. Never concatenate arbitrary data into the context of SQL. Use parameterized queries. — Brad
– Brad, Commented Jun 9, 2019 at 20:49

Dan · Accepted Answer · 2021-02-13 06:07:43Z

5

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

edited Feb 13, 2021 at 6:07

answered Jun 9, 2019 at 20:35

Dan

63.5k18 gold badges113 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

devamat Over a year ago

Thanks, this seems to be what I was looking for. I guess that what you mean is having a .js file containing the code you posted and then use const { query } = require('../db.js') and call it to execute the queries. Is this correct?

Dan Over a year ago

@devamat - Well.. this is a bit of a delayed response, but... yes, exactly. Sorry, going through old answers and saw this missed reply.

devamat Over a year ago

Better late than never ;) Thanks.

Rick James · Accepted Answer · 2019-06-09 20:11:24Z

1

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.

answered Jun 9, 2019 at 20:11

Rick James

144k15 gold badges144 silver badges254 bronze badges

3 Comments

Alexis Wilke Over a year ago

At the same time, if the connection is ready when you get a hit, you could save a good 50ms. in your total transaction time.

Rick James Over a year ago

@AlexisWilke - 50ms? Are your client and server on opposite sides of the country? I find it is more like 1ms for "nearby". I tried a local connection on the same machine and got 10us. Yes, that's microseconds for connecting to MySQL (from PHP). Could you get some timings?

Alexis Wilke Over a year ago

By default PHP has a pool of connections so the "connect" doesn't really create the TCP/IP connections, it's already there, unless you get a large number of of clients back to back or "simultaneously", then the pool will probably dry up. That being said, yes, on modern computers even on a local network, this is really fast.

Collectives™ on Stack Overflow

NodeJS + ExpressJS proper way to manage connection to mysql DB

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related