3

I deployed a NodeJS API on Heroku and tried to connect it to a MySQL DB, so I created a connectionPool for handling connections on a ClearDB ignite account (free), which allows a maximum of 10 connections.

Every time I execute a query to the database it just adds a new connection to the stack until it reaches 10 connections and the app crashes.

My code is as follows:

connectionFactory:

var mysql = require('mysql');

function createDBConnection() {
    var conn = mysql.createPool({
      host: 'xxx',
      user: 'xxx',
      password: 'xxx',
      database: 'xxx'
    });
    return conn;
}

module.exports = function() {
  return createDBConnection;
}

And here's my select query:

function Dao(connection) {
  this._connection = connection;
}

Dao.prototype.findAll = function (callback) {
  this._connection.query('SELECT * FROM table',
  function(errors, results) {
    callback(errors,results);
  });
};

module.exports = function() {
  return Dao;
}

Finally here's the route I use to call it:

app.get('/products', function (req,res) {
    var connection = app.persistence.connectionFactory();
    var dao = new app.persistence.Dao(connection);

    dao.findAll(function (err, result) {
      res.format({
        json: function () {
          res.json(result);
        }
      });
    });
  });

I tried changing createPool() to createConnection() and then calling .end()/.destroy() function right after each query but it doesn't work at all.

Any hints?

Marcelo
  • 351
  • 1
  • 5
  • 18

1 Answers1

0

In order to close a connection / return a connection to the pool use : connection.release()

var mysql = require('mysql');
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // And done with the connection.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

mysql : Pooling connections Documentation

EMX
  • 6,066
  • 1
  • 25
  • 32
  • If I add connection.release() my code looks like this: `Dao.prototype.findAll = function (callback) { this._connection.query('SELECT * FROM table', function(errors, results) { callback(errors,results); this._connection.release(); }); };`. But then I get the following error: `Error: Connection already released` and the app crashes immediately. – Marcelo Sep 02 '17 at 21:02
  • that is because you are using the same connection you released to perform some action or by the time you are calling the release yourself it has already been released, try doing it before the callback, and connection actually refers to the object returned by `pool.getConnection` – EMX Sep 02 '17 at 21:06
  • I tried calling connection.release() before the callback, but it keeps giving me `Connection already released` error. I tried using `connection.destroy()` instead and **locally** it really destroys the connection and everything's fine. But if I do the very same thing on ClearDB, the command `destroy()` seems not to work. It keeps stacking connections anyway! – Marcelo Sep 03 '17 at 12:10
  • 1
    @Marcelo, why dont you try a simpler aproach just to test `connection.release()` ? I think is the way you are using the connections (opening them using node) and not ClearDB – EMX Sep 03 '17 at 16:22
  • Well I did that. I created one simple application with two simple queries (SELECT/INSERT): Opening, querying and releasing the connection all inside the same file... I tried both createPool() and createConnection() methods but with no success. Connections kept stacking. In the end I gave up using ClearDB and tried Heroku's own PostgreSQL instead and voilá. I made it work without much effort, just adapting my queries and changing my connection provider from `mysql` to `pg`. As I know this is no solution for my problem but a (extreme) workaround, I won't use this as an answer to my question. Ty! – Marcelo Sep 04 '17 at 10:33