Node.js MySQL Needing Persistent Connection

Question

I need a persistent MySQL connection for my Node web app. The problem is that this happens about a few times a day:

Error: Connection lost: The server closed the connection.
at Protocol.end (/var/www/n/node_modules/mysql/lib/protocol/Protocol.js:73:13)
at Socket.onend (stream.js:79:10)
at Socket.EventEmitter.emit (events.js:117:20)
at _stream_readable.js:895:16
at process._tickCallback (node.js:415:13)
error: Forever detected script exited with code: 8
error: Forever restarting script for 2 time
info: socket.io started

Here is my connection code:

// Yes I know multipleStatements can be dangerous in the wrong hands.
var sql = mysql.createConnection({
    host: 'localhost',
    user: 'my_username',
    password: 'my_password',
    database: 'my_database',
    multipleStatements: true
});

sql.connect();

function handleDisconnect(connection) {
    connection.on('error', function(err) {
        if (!err.fatal) {
            return;
        }
        if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
            throw err;
        }
        console.log('Re-connecting lost connection: ' + err.stack);
        sql = mysql.createConnection(connection.config);
        handleDisconnect(sql);
        sql.connect();
    });
}

handleDisconnect(sql);

As you can see, the handleDisconnect code does not work..

Answer 1

Use the mysql connection pool. It will reconnect when a connection dies and you get the added benefit of being able to make multiple sql queries at the same time. If you don't use the database pool, your app will block database requests while waiting for currently running database requests to finish.

I usually define a database module where I keep my queries separate from my routes. It looks something like this...

var mysql = require('mysql');
var pool  = mysql.createPool({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

exports.getUsers = function(callback) {
  pool.getConnection(function(err, connection) {
    if(err) { 
      console.log(err); 
      callback(true); 
      return; 
    }
    var sql = "SELECT id,name FROM users";
    connection.query(sql, [], function(err, results) {
      connection.release(); // always put connection back in pool after last query
      if(err) { 
        console.log(err); 
        callback(true); 
        return; 
      }
      callback(false, results);
    });
  });
});