Close connection MySQL Node.js

Question

I'm developing a Node application with ExpressJS and MySQL. I'm working with this module https://github.com/felixge/node-mysql/ and I'm learning its use yet. I get troubles about how to close connections properly.

This is what I do:

app.post('/example', function (req, res) {

    //BD
    var connection = mysql.createConnection({
        host: config.database.host,
        port: config.database.port,
        user: config.database.user,
        password: config.database.password,
        database: config.database.database
    });

    var sql = '';

    if (varExample != null) {

         sql = 'Random query';

         connection.query(sql, function (err, results) {

             //Get results

             connection.end();
         }); 
    }
});

And sometimes I have to call this method several times to insert data in a DB. At that moment I get an error 'Too many connections'.

What is the way to close a connection in these cases?

Answer 1

What you should not do is to open a connection every time you get a request. It is slow to connect everytime, second the driver normally opens a pool of connections for you so should not be a problem. There's no need to close the connection to mysql.

Basically you have to do this

//BD
var connection = mysql.createConnection({
    host: config.database.host,
    port: config.database.port,
    user: config.database.user,
    password: config.database.password,
    database: config.database.database
});
app.post('/example', function (req, res) {
    var sql = '';

    if (varExample != null) {

         sql = 'Random query';

         connection.query(sql, function (err, results) {

            //Get results
         });  
    }
});

EDIT: Add pool option Having a pool of connections is basically what most of us want for a server which has to do many queries. it just changes slightly how you create the connection.

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

Answer 2

I realize there is already an accepted answer but what you really should be doing is creating a database pool which the other answers really don't give an example of. You have to set this up a little differently than creating a normal database connection with the library.

-edit You do not have to worry about closing connections.

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

    pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
      if (err) throw err;

      console.log('The solution is: ', rows[0].solution);
    });

exports.Pool = pool;