How to automatically close mysql connection in nodejs

Question

Currently my process is hanging because the mysql pool isn't being closed automatically.

I assumed that when I called connection.release() the connection would get closed if no one else was using it. But it seems that is not the case. My code looks like:

getConnection.js:

var mysql = require('mysql');

var pool = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  user     : 'root',
  password : 'mysql',
  database : 'xxx'
});

module.exports = function getConnection(callback){
    pool.getConnection(function(err, connection){
        if (err) {
            throw err;
        }
        callback(connection);
    });
}

And i'm using it like this:

var getConn = require('./getConnection');

function selectOne(query, map, cb){

    getConn(function(conn){
        conn.query(query, map, function(err, rows){
            if (err) {
                throw err;
            }

            cb(rows.length ? rows[0] : false);
            conn.release();
        });
    });
}

I think I'm missing something. Shouldn't node-mysql close the connection for me when I release it?

score 3 · Answer 1 · answered Jun 23 '14 at 15:24

From https://github.com/felixge/node-mysql#pooling-connections conn.release(); allows the connection to be released to the connection pool so it doesn't close it. If you want to close the connection use conn.end(); as per the docs: https://github.com/felixge/node-mysql#terminating-connections.

score 2 · Answer 2 · answered Aug 18 '15 at 05:46

I took a similar approach as Ishaan, but i reckon mine is a bit cleaner for you to understand.

here is a snippet of my code.

// Dependencies
var mysql   = require('mysql'),
    config  = require("../config");

/*
 * @sqlConnection
 * Creates the connection, makes the query and close it to avoid concurrency conflicts.
 */
var sqlConnection = function sqlConnection(sql, values, next) {

    // It means that the values hasnt been passed
    if (arguments.length === 2) {
        next = values;
        values = null;
    }

    var connection = mysql.createConnection(config.db);
    connection.connect(function(err) {
        if (err !== null) {
            console.log("[MYSQL] Error connecting to mysql:" + err+'\n');
        }
    });

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

        connection.end(); // close the connection

        if (err) {
            throw err;
        }

        // Execute the callback
        next.apply(this, arguments);

    });
}

module.exports = sqlConnection;

Than you can use it anywhere just doing like

var mysql_query = require('../models/mysql_query');
mysql_query('SELECT * from your_table where ?', {id: '1'}, function(err, rows)   {
    console.log(rows);
});

Hope this helps.

score -1 · Answer 3 · answered Jun 23 '14 at 16:40

I am very new here so forgive me If I am wrong.

This is how I am doing it

module.exports = {
      getDBConnection: function () {
        return mysql.createConnection(config.db);
      },

       connectToDB: function (connection) {
            connection.connect(function (err) {
                   if (err) {
                            throw err;
                    }
                    });
       },

      endDBConnection: function (connection) {
        connection.end(function (err) {
          if (err) {
            throw err;
          }
        });
      },

      exec: function (query, data, cb) {
        var connection = this.getDBConnection();
        this.connectToDB(connection);
        connection.query(query, data, function(err, res) {
          if (err) {
            cb(err);
          }
          cb(null, res);
        });
        this.endDBConnection(connection);
      }
}

this is the config variable

 db: {
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
},

I thought about that, but this creates and closes a new connection every time you need to run a query, isn't? I'm concerned about performance issues. — lucaswxp, Jun 23 '14 at 17:29
I am running this a on large database 1M+ rows, on a regular bluehost VPS. No such performance issues. — Ishaan Sharma, Jun 23 '14 at 17:41
You aren't waiting for the connection to happen (i.e. you aren't using the callbacks). Also, you're opening a connection every time you make a query, which is terrible for performance. You mentioned you're using it on a 1M+ row database; that isn't the problem. The problem is when you have many concurrent client requests, or if you make a lot of queries in your app. — Cully, Aug 15 '16 at 02:53

How to automatically close mysql connection in nodejs

3 Answers3