CRUD MySQL with NodeJS: How to use Pool correctly?

Question

CRUD MySQL with NodeJS: How to use Pool correctly?

Navigation

#1 by (1 votes)

1

I created two files to test the use of MySQL with NodeJS as below, but I'm finding the answer slow and I think I'm doing something wrong:

DOUBTS :

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

2) When I run retrieve in my test.js file, do I recover the data using same callback 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);
});

mysql node.js

asked by anonymous 23.07.2017 / 17:39

1 answer

Remove and insert characters in the same position Problems passing object instantiated by function parameter with php

score 1 · Accepted Answer

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 you use getConnection and release .

To use the MySQL return you must use asynchronous logic, you can use for example callbacks or promisses.

If you do this with promises it could look 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();
      });
    });
  });

}

test.js

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