CODEWITHSUNDEEP
node.jsnode-mysql
lucaswxp
lucaswxp

Reputation: 2119

How to automatically close mysql connection in nodejs

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?

Upvotes: 3

Views: 17506

Answers (3)

Rafael
Rafael

Reputation: 1325

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.

Upvotes: 2

Ishaan Sharma
Ishaan Sharma

Reputation: 367

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'
},

Upvotes: -1

Tom Grant
Tom Grant

Reputation: 2045

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.

Upvotes: 3

Related Questions