MySQL queries in Node.js

7 months ago

Lately I have found myself writing an API in Node.js, but that's not what I want to really talk about, instead - as I'm writing the API in Node.js, I have stumbled upon a problem when writing MySQL queries.

How I did it

// set up
const mysql = require('mysql')
const pool = mysql.createConnection({
  host: 'host',
  user: 'user',
  password: 'password',
  database: 'database'
})

// connect
connection.connect();

// query
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {

  console.log('The solution is: ', results[0].solution);

});

And this works fine. Well, up to a point where your server is inactive for enough time for it to close the active MySQL connection - then all the subseguent tries result in an error; "The server closed the connection", it will tell you. But then what do you do? Do you try to keep the connection alive? Maybe you can just setInterval some innocent query to ping the database? Well, you can .. but it's not a very elegant solution.

How I do it now

// set up
const mysql = require('mysql')
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'host',
  user: 'user',
  password: 'password',
  database: 'database'
})

// use an active connection or create a new one
pool.getConnection(function(err, connection) {

  connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {

    console.log('The solution is: ', results[0].solution);

    // release the connection back to the pool
    connection.release();

  })
})

You see the above creates a pool where your connections live. Think of it as a swimming pool. In this swimming pool we let 10 people swim (as an example, you can change it as you see fit) and as soon as one leaves, we give that spot to someone new. In other words, it will re-use existing connections if it can by releasing them back to the pool once its done with it. This will also create a new connection if there are none to re-use and thus solving the problem I had before.