CRUD Mysql with Nodejs: how to use Pool correctly?

Question

I created two file to test the use of Mysql with Nodejs as follows below, but I’m finding the answer a little slow and I think I’m doing something wrong:

DOUBTS:

1) Is this the most correct way to access the database? I mean, every time I need to save or access something in the bank mysql.createPool, getConnection and execute the release() for each operation?

2) When I execute retrievein my file test.js, I recover the data using callback even as I indicated?

model js.

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  database : 'dbtest',
  user     : 'root',
  password : '123456'
});

exports.retrieveOne = function(id, callback) {
    var sql = "SELECT * FROM test WHERE id = ?";
    var params  = [];

    params.push(id);
    sql = mysql.format(sql, params);

    pool.getConnection(function(err, connection) {
        if (err)
            console.log('Connection error: ', err);
        else
            connection.query(sql, callback);

        connection.release();
    });
}

exports.retrieveAll = function(callback) {
    var sql = "SELECT * FROM test";
    pool.getConnection(function(err, connection) {
        if (err)
            console.log('Connection error: ', err);
        else
            connection.query(sql, callback);

        connection.release();
    });
}

//exports.create = function() { }
//exports.update = function() { }
//exports.delete = function() { }

test js.

var getRow = require('./model.js');

//Retorna todos os registros
getRow.retrieveAll(function(err, rows) {
    console.log('Err: ', err);
    console.log('Rows: \n', rows);
});

// Retorna 1 registro
var id = 2;
getRow.retrieveOne(id, function(err, rows) {
    console.log('Err: ', err);
    console.log('Rows: \n', rows);
});

Answer 1

In your model.js flames once mysql.createPool. This is correct and only happens once, when the program loads, then every call to the database uses getConnection and release.

To use Mysql return you must use asynchronous logic, you can use for example callbacks or Promisses.

If you do this to Predomises I could stay like this:

model js.

exports.retrieve = function(id) {
  var sql = "SELECT * FROM test WHERE id = ?";
  var params = [];
  params.push(id);
  sql = mysql.format(sql, params);

  return new Promise(function(res, rej) {
    pool.getConnection(function(err, connection) {
      if (err) rej(err);
      connection.query(sql, function(err, rows) {
        if (err) rej(err);
        else res(rows);
        connection.release();
      });
    });
  });

}

js test.

getRow.retrieve('4').then(rows => {
    console.log(rows);
})