How to properly close MySQL connection in express.js?

Question

MySQL

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var mysql = require('mysql');

var indexRouter = require('./routes/index');
var usersLoginRouter = require('./routes/usersLogin');
var checkTokenRouter = require('./routes/checkToken');
var usersRegRouter = require('./routes/usersReg');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'twig');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use(function (req, res, next) {
    res.setHeader('Content-Type', 'application/json');

    next();
});

app.use(function (req, res, next) {
    var connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'loto_users',
        password : 'loto_users',
        database : 'loto_users'
    });

    connection.connect();

    connection.on('error', function (error) {

    });

    res.mysql = connection;

    next();
});

app.use('/', indexRouter);
app.use('/users/login', usersLoginRouter);
app.use('/users/check/token', checkTokenRouter);
app.use('/users/reg', usersRegRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

checkToken.js

var express = require('express');
var router = express.Router();
var crypto = require('crypto');

router.get('/', function(req, res, next) {
    const token = req.query.hasOwnProperty('token') ? req.query['token'] : null;
    
    res.mysql.query('SELECT * FROM user_tokens WHERE token = ?', [token], function (error, results, fields) {
        if (error || results.length === 0) {
            return res.send(JSON.stringify({
                ok: false,
                message: 'Wrong token'
            }));
        }

        const user_token = results[0];

        if (user_token['end_at'] < Math.floor(new Date() / 1000)) {
            res.mysql.query('DELETE FROM user_tokens WHERE token = ?', [token]);

            return res.send(JSON.stringify({
                ok: false,
                message: 'Token expire'
            }));
        }

        res.send(JSON.stringify({
            ok: true,
            user_id: user_token['user_id'],
            end_at: user_token['end_at'],
            message: 'OK'
        }));
    });

});

module.exports = router;

Requests to the database are asynchronous. How to properly close a connection?

Answer 1

Solved it via pool.query(). It is possible through getConnection () but there it is constantly necessary to use release ().

Answer 2

Properly close it when the server is stopped.
Why are you creating a new connection to mysql for every query?
You write express server as a cgi script or php, but in express your server is running all the time - open one connection at startup and don't touch it until the process is stopped.
If you really want to do exactly as you do - wait in the controller until all requests to the database are completed, and add another middleware that will close the connection after all other layers have worked.
But it's better to do it the right way.