Promise-MySQL cannot release connections back to the pool

Question

I am very new to Node.js development and I am working on an app that requires me to pull users from a mysql database. I am using the promise-mysql library to query a mysql database. I am trying to use a connection pool like this:

var pool = mysql.createPool({
  host:     hgh.host,
  user:     hgh.user,
  password: hgh.pw,
  database: hgh.name,
  connectionLimit: 10
});

As a global variable in my module.

I then have the above function to return a connection from the pool.

function connect() {
  return pool.getConnection().then(function(connection) {
    return connection
  }).catch(function(error) {
    console.log("Connect failed");
    throw ErrorModel.generateErrorObject(error, 500);
  });
}

Below is a function I am using to query the database with:

function getUser(username) {
  var sql_query = `SELECT * FROM userstable WHERE userName = ` + `'` + username + `'`;
  return connect().then(function(conn) {
    return conn.query(sql_query).then(function(rows) {
      console.log("HGH getUser Then");
      console.log(pool);
      conn.release();
      return rows;
    });
  }).catch(function(error) {
      console.log("HGH getUser Catch");
      console.log(error);
      throw ErrorModel.generateErrorObject(error, 500);
  });
}

I am getting this issue:

conn.release is not a function when trying to release my current connection into the pool. Is my logic wrong here? My goal is to have a pool with a bunch of connections (up to a certain number) and if a user needs to query, the getConnection() function just grabs them either a free connection from the pool, or creates them one. Problem is I cannot for the life of me release it back to the pool as free..Every time I make a request with conn.end() instead of release the connection remains in the _allConnections array of the pool when I console it out, and there are absolutely no connections in the _freeConnections array.

Anyone know how I can make connections free again??

score 4 · Accepted Answer · answered Feb 18 '16 at 20:11

4

Looking at the module's code I found the function for releasing a connection from a pool:

pool.prototype.releaseConnection = function releaseConnection(connection) {
    //Use the underlying connection from the mysql-module here:
    return this.pool.releaseConnection(connection.connection);
};

So if all of these functions live in the same file you could do the following in the getUser function: replace

conn.release();

with

pool.releaseConnection(conn);

answered Feb 18 '16 at 20:11

Aaron Harrington

532
3
13

Oh I missed that one! Better solution than mine =D – robertklep Feb 18 '16 at 20:15
Ok thanks a lot. Would there be a way to release the connection, in the connection method so I wouldn't have to re-write something every time I want to write a method that uses `connect()` to query? – Dominic Farolino Feb 18 '16 at 20:55
I would make a generic query(queryString) function that fetches a connection, runs the query, returns the connection to the pool and then returns the results from the query. I hope that makes sense. – Aaron Harrington Feb 18 '16 at 21:16
@DomFarolino,"Would there be a way to release the connection, in the connection method ...". Try try the [Disposer Pattern](http://stackoverflow.com/questions/28915677/what-is-the-promise-disposer-pattern) – Roamer-1888 Feb 19 '16 at 10:03

score 2 · Answer 2 · answered Feb 18 '16 at 20:13

Looking at the code, promise-mysql wraps the original connection object, which doesn't expose the release method. However, the original is exposed as a property called connection, so this works:

conn.connection.release();

A few random thoughts:

You should probably escape your query input:

var sql_query = `SELECT * FROM userstable WHERE userName = ${ pool.escape(username) }`;

Your code doesn't release connections when an error occurs (because the code in the .then() callback wouldn't get called); it's better to use .finally() to do the releasing, as that will get called for both resolved and rejected cases:

function connect() {
  var conn = null;
  return pool.getConnection().then(function(connection) {
    conn = connection;
    return connection;
  }).catch(function(error) {
    console.log("Connect failed", error);
  }).finally(function() {
    if (conn) {
      conn.connection.release();
    }
  });
}

Yeah I was using conn.end() in a finally earlier for testing, not sure why I reverted it. And yes I wasn't escaping because we only run this query if that use exists in another database. We check if they do through an API call on another internal company application however that's a good idea thanks, can never be too safe — Dominic Farolino, Feb 18 '16 at 20:23

Promise-MySQL cannot release connections back to the pool

2 Answers2

Linked