Hướng dẫn nodejs mysql close connection

I am programming a NodeJS server with express and MySQL. This server is only supposed to work a a Rest API, providing JSON data to a mobile app and a Angular front-end application.

I'm currently making a connection to the database using the mysql npm module. I am noticing an increasing amount of threads everytime I reload my application since they are not closing. The reason for that is that I start the connection when I first start the server. I am wondering if my coding approach is actually the best or if in due time the connection will be interrupted if many people start accessing it.

In the end what I want to know is if I should be: 1) Making the HTTP request 2) Opening the connection 3) Retrieving the data 4) Closing the connection after the data is retrieved.

Or if I should keep my approach of: 1) Starting the connection when the service is started 2) Using that reference to make all requests/responses

Code of how I'm doing it now:

db_config.ts

    import mysql from 'mysql';
import DevTools from '../dev_tools/devtools';

export function connectToDatabase(): any {

    let connection = mysql.createConnection({
        host: 'localhost',
        port: 3306,
        user: 'userapp',
        password: 'app!@#',
        database: 'db_app'
    })

    connection.connect((err) => {
        if (err) {
            DevTools.printIfInDevMode('DATABASE', `  ${err.code} - Error while connecting:  ${err.name} - ${err.message} `)
            return;
        }
        DevTools.printIfInDevMode('DATABASE', `Connected with id ${connection.threadId}`)

        return connection;
    })
}

server_config.ts

    import express from "express";
import DevTools from "../dev_tools/devtools";
const app = express();

//Configuração para receber parâmetros via POST
app.use(express.urlencoded(
    {
        extended: true
    }
));
app.post('/', (req, res) => {
    DevTools.printIfInDevMode('SERVER', 'Chegou aqui')
    res.send(req.body);
})

export default app;

server_start.ts

    // node-typescript/src/entry.ts
import app from './server_config';
import { connectToDatabase } from './db_config';

export default function startServer() {
    app.listen(3000, () => {
        console.log(`[SERVER] Servidor Iniciado `);
        connectToDatabase();

    });
}

app_dev.ts

    import startServer from './config/server_start';
import DevTools from './dev_tools/devtools';

console.log("[SERVER] Starting in developer mode")
DevTools.setDevMode(true);
startServer();

What happens basically:

1. I use npm run on app_dev.ts
2. app_dev.ts makes the application run in developer mode which allows me to easily debug via the "printIfInDevMode" method.
3. It calls the "startServer" method which is imported from server_start.ts
4. server_start.ts imports the app variable from server_config.ts which contains the configuration for the express service.
5. server_start.ts executes the listen method on port 3000 and after that tries to connect to the database via the "connectToDatabase" method imported from db_config.ts
6. The "connectToDatabase" method simply starts a connection to a MySQL server running on the localhost and return an error if it fails to do so.
7. If it succeeds, it prints out the ThreadId of the connection.

I'm wondering if I should keep the "connectToTheDatabase" method inside the app.listen(3000) and never close this connection, or if I should put it inside the GET routes, opening a connection when someone acesses that route, retrieving the data and then closing the connection.

Photo by John Barkiple on Unsplash

It’s very important to keep track of your database connections in order to not fill up the maximum number of connections. If you are careless you will easily reach the limit, resulting in the dreadful error “Too many connections” when you to try to connect to your MySQL server and causing the database server to choke.

This article is a study of how bad (and good) coding will affect the numbers of threads connected to your MySQL database server when using the npm package mysql2

The maximum number of connections to your MySQL server is decided by its variable “max_connection” and can be read by querying the database server:

SHOW VARIABLES LIKE 'max_connections';

To check how many threads that are connected, we can run this query:

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

If we are on a database server that we know no others are connected to, preferably a database server on you own laptop, we probably will see the following result:

Threads_connected 1

The one connection is yourself right now looking up how many connections there are, so you will never see 0 here.

I my case, I have a local MySQL server installed, so it’s safe to do some bad practice examples. So if you want to try some of these bad practises yourself, make sure you don’t do it on a production server!

Creating connections

For example, the code below will create 100 connections, and leave them open:

const mysql = require(‘mysql2’);for (let i = 0; i < 100; i++) {
  let connection = mysql.createConnection({
    host: ‘localhost’,
    user: ‘root’,
  });
}

Let’s run the code by:

node your-file-name.js

Checking our number of threads now read:

Threads_connected 101

This is because when you create a connection, it will remain open until you close it. To close a connection, we can use connection.end(), so let’s restart our MySQL server, to make sure we reset all connections, and then try the following code:

const mysql = require(‘mysql2’);for (let i = 0; i < 100; i++) {
  let connection = mysql.createConnection({
    host: ‘localhost’,
    user: ‘root’,
  });
  connection.end();
}

Checking our number of threads now read:

Threads_connected 1

That means that all our 100 connections was closed, which is of course is much better. BUT, if you are going to query your database multiple times during a request, setting up a pool is the best practice.

Photo by Cory Bjork on Unsplash

Using pools

How should we use a pool then? Let’s start by looking at how to NOT use a pool. If we restart our MySQL server, and then run the following code:

const mysql = require(‘mysql2’);for (let i = 0; i < 100; i++) {
  let pool = mysql.createPool({
    host: ‘localhost’,
    user: ‘root’,
    connectionLimit: 10,
  });
}

As you can see, we’re still looping a hundred times, but instead of creating a connection, we’re now creating a pool. The pool options introduces a new parameter, connectionLimit. It’s value is by default 10, and it means that the pool will never create more connections than this limit.

Checking our number of threads now read:

Threads_connected 1

So that’s better then createConnection() right? Yes, as long as we don’t use our pool that is, and what good is that? No good.

To use our pool, I recommend using it via the promise wrapper (I can’t really see why not to use it).

So let’s apply the promise wrapper, and then use the pool to do a query, like this:

const mysql = require(‘mysql2’);for (let i = 0; i < 100; i++) {
  let pool = mysql
    .createPool({
      host: ‘localhost’,
      user: ‘root’,
      connectionLimit: 10,
    })
    .promise();
  pool.query(‘SELECT 1’)
    .then(res => {
  });
}

Checking our number of threads now read:

Threads_connected 101

In other words, just as bad as when we used createConnection(). But (as you probably already has figured out) the whole idea of a pool is to create it once, then reuse it multiple times. So let’s move the creation of the pool outside the loop:

const mysql = require(‘mysql2’);let pool = mysql
  .createPool({
    host: ‘localhost’,
    user: ‘root’,
    connectionLimit: 10,
  })
  .promise();for (let i = 0; i < 100; i++) {
  pool.query(‘SELECT 1’)
    .then(res => {
  });
}

If we restart our MySQL server, and then run the code above, checking our number of connected threads now read:

Threads_connected 11

This means that our pool used all the connections it had available according to our option parameter connectionLimit, but they are still left open and are occupying slots among our number of max connections.

Ending pools

Fortunately there is a solution for this also, and it is called pool.end(), so let’s try it, by doing this:

const mysql = require(‘mysql2’);let pool = mysql
  .createPool({
    host: ‘localhost’,
    user: ‘root’,
    connectionLimit: 10,
  })
  .promise();for (let i = 0; i < 100; i++) {
  pool.query(‘SELECT 1’)
    .then(res => {
  });
}
pool.end();

If we run this code, we will get an error as in “Pool is closed”. And (as you probably would imagine) this is because we are closing the pool before our asynchronous queries has finished.

It’s time to introduce async/await functionality (only available in Node.js version 8 or newer). To use await, we need to follow some rules, as NodeJS says:

await is only valid in async functions and the top level bodies of modules.

So therefore I will wrap my code in a self invoking async function, like this:

const mysql = require(‘mysql2’);(async () => {
  let pool = mysql
    .createPool({
      host: ‘localhost’,
      user: ‘root’,
      connectionLimit: 10,
    })
    .promise();
  for (let i = 0; i < 100; i++) {
    let rows = await pool.query(‘SELECT 1’);
  }
  pool.end();
})();

If we now restart our MySQL server and run the code above, checking our number of threads now read:

Threads_connected 1

Reaching our goal?

Finally we’ve done it. We queried the database a 100 times and left no trace of it among the connected threads. Isn’t that Great? Well, lets continue our study.

If you remove pool.end() in this example, and then run it, checking our number of threads will then read:

Threads_connected 2

That’s because we’re never running the queries in parallell, due to await is awaiting the query to finish before the next query, so the pool will never use more than one connection even if it has up to 10 availble connections according to the value of connectionLimit.

If you add pool.end() again and rerun you code, without restarting the MySQL server, checking our number of threads will then read:

Threads_connected 1

That’s because the pool used the old connection that was still held open, and then closed it when it was done.

So why use a pool then, if we never use more than one simultaneous connection?

Well, what if we’re not just calling our node.js file manually, or setting up a cron job that calls it, but we’re creating an REST API that should be able to handle simultaneously requests?

When using a http server

Let’s study this by setting up a minimal http server in Node.js, like this:

const mysql = require(‘mysql2’);
const http = require(‘http’);
const pool = mysql
 .createPool({
  host: ‘localhost’,
  user: ‘root’,
  connectionLimit: 10,
 })
  .promise();
const server = http.createServer((req, res) => {
  if (req.method != ‘GET’ || req.url != ‘/’) {
    // exclude all other request (to favicon.ico etc)
    res.end();
    return;
  }
  // console.log(req.method, req.url);
  (async () => {
    for (let i = 0; i < 100; i++) {
      let rows = await dbPool.execute('SELECT 1');
    }
    pool.end();
  })();
  res.end();
});
server.listen(3000);

Let’s walk though the code a little bit, so we know what’s happening here. At the top we require our http object from Node’s native library.

Then we create our pool so it will be available among all requests as long as our server is running.

Then we create a http server. The first thing we do “inside” our server, is to filter out unwanted request — cause we want to be able to test our code in a ordinary browser, and the browser may do additional requests when we load the page (e.g checking for a favicon.ico etc) and we want to make sure our code is only executed once in this case.

Then you probably recognize our earlier code (the res.end() part is required to tell Node.js to end the request).

An finally we make sure we listen to port 3000.

To start our server, we run, as before, in our command line:

node your-file-name.js

This will start our server and leave it open for requests.

If we restart our MySQL server, and then open a browser and go to http://localhost:3000, and then if check our connections we read:

Threads_connected 1

So we left no connection open which is great. At least it look like that. But if we reload our web page again, we get error “Pool is closed”. This is because we closed our pool as the first request ended. A web page will of course manage multiple request, so in this case, we should NOT close our pool, because we created it so it should be available for the entire app/server as long as it is running.

So let’s remove pool.end(), which will leave us with the final code of:

const mysql = require(‘mysql2’);
const http = require(‘http’);
const pool = mysql
 .createPool({
  host: ‘localhost’,
  user: ‘root’,
  connectionLimit: 10,
 })
  .promise();
const server = http.createServer((req, res) => {
  if (req.method != ‘GET’ || req.url != ‘/’) {
    // exclude all other request (to favicon.ico etc)
    res.end();
    return;
  }
  // console.log(req.method, req.url);
  (async () => {
    for (let i = 0; i < 100; i++) {
      let rows = await dbPool.execute('SELECT 1');
    }
  })();
  res.end();
});
server.listen(3000);

If we now:

1. Close our Node server (press CTRL + C at the command line)
2. Start our Node server again by node your-file-name.js

Then we can reload our web page in the browser multiple times, and the active connections will never go above 2 in our example. If you do simultaneously queries (instead of async/await), you will still never use more than up to 10 connections (as in our connectionLimit) open towards the MySQL server.

That’s it for this study. Please contribute and criticize in the comments below so we all can improve.