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?

DevAlien · Accepted Answer · 2015-09-18T15:15:12.857

9

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

edited Sep 18 '15 at 15:15

answered Sep 18 '15 at 11:25

DevAlien

2,456
15
17

There is a problem with this solution. In the case I reuse the same connection to make different queries Node answers 'cannot enqueue the query after invoke quit' – Ulyarez Sep 18 '15 at 14:47
SOrry, you have to remove the `connection.end()` – DevAlien Sep 18 '15 at 14:48
Maybe this is a silly question but, how Node manages to close the connection after a request ? – Ulyarez Sep 18 '15 at 14:52
1

Why do you have to close the connection? The driver creates a pool of connections and they are eeady to be used... you close rhe connections when you dont have to use it anymore. But being a node server which i guess will run for a long time and i guess also that you will use mysql more times you just keep it open – DevAlien Sep 18 '15 at 14:55
@DevAlien The driver does not create a pool of connections by default. Take a look at my answer for creating a database pool. The setup is slightly different than what you have here. – tier1 Sep 18 '15 at 15:01
@tier1 Oh yes, I gave for granted what I thought was the right behaviour, but apparently is not (i do not understand this choice, since having a pool is what nearly everyone will want). I will update as well this answer since is the one marked. I will put both versions. – DevAlien Sep 18 '15 at 15:12

score 7 · Answer 2 · answered Sep 18 '15 at 15:00

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;

The problem with not closing is that if running from batch, node never exits. So how do we get an orderly termination going ? — mckenzm, Nov 04 '21 at 01:25

Close connection MySQL Node.js

2 Answers2