How do I close mysql connection after each request with Express?

Issue

I have a router file like this:

import express from 'express';
import health from './health';
import surface from './surface';

const router = express.Router();

router.use('/health', health);
router.use('/surface', surface);

router.get('*', (req, res) => {
  res.status(404);
  res.json({
    message: 'Uknown API endpoint'
  });
});

export default router;

Within each route, I am using the NPM mysql package to create a connection and then end it right before I call res.send(). IIRC creating a connection and ending it per query would be a bad idea.

I tried doing middleware like:

router.use((req, res, next) => {
  next();
  mySingletonWrappingMysql.connection.end();
});

Given that my routes are doing async things, the endConnection call is happening too soon. next isn’t really async so I can’t do await next and I’m not even sure this would be the right way/place to do it. This example is using a singleton because I also wasn’t sure of how to properly set a new instance to be use within the route. req.param seemed like an option that isn’t optimal cause that seems to be for special query param vars.

Solution

You need a connection pool to MySQL. This is a pool of serially reusable connection resources. You grab one when you need it, and let it go when you’re done. So, each request, or even each middleware invocation, can use a connection without worrying about concurrency (which does not work on connections). See this. https://www.npmjs.com/package/mysql#pooling-connections

Set up your pool like this, just once. Each node.js server instance needs a pool for each separate MySQL server it uses.

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

This createPool operation creates a pool with up to ten connections. Each time you use the pool, it checks to see whether all its connections are busy. If not, it gives you an idle one to use. If they are all busy, it opens a new one. But, if the pool already has connectionLimit connections in use, it will wait until one is free.

Then casual one-off queries work like this. In a REST server, you may find that some GET queries can be satisfied with a single query to the database. These are super simple. There’s no need to explicitly take or release connections from the pool. And, when you release the connection it stays connected to the DBMS, ready for use by a future request. If satisfying a particular request requires two or three SELECT queries, you can also use this technique. But, each query may use a different connection from the pool.

 pool.query('SELECT whatever FROM whatever', 
   function (error, results, fields) {
     if (error) throw error;
     /* handle your results array */
 })

If you need to grab a connection for multiple queries, it’s almost as simple, but you do need to remember to release() the connection when you’re done. You need to do this when some of your queries depend on the results of previous queries in a sequence.

pool.getConnection(function(err, connection) {
    if (error) throw error;
    connection.query('INSERT INTO sometable ...', 
       function (error, results, fields) {
           if (error) {
               connection.release();
               throw error;
           }
           connection.query('INSERT INTO detail ... (LAST_INSERT_ID()...)', 
               function (error, results, fields) {
                  connection.release();
                  if (error) throw error;
               });
       });
});

You may want to use promises instead, to avoid this nesting of functions, especially if you want to run a sequence of queries.

Answered By – O. Jones

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0