在 node.js 中使用 promise 处理 MySQL 返回值

Use promise to process MySQL return value in node.js

提问by user781486

I have a python background and is currently migrating to node.js. I have problem adjusting to node.js due to its asynchronous nature.

我有 python 背景，目前正在迁移到 node.js。由于其异步性质，我在适应 node.js 时遇到问题。

For example, I am trying to return a value from a MySQL function.

例如，我试图从 MySQL 函数返回一个值。

function getLastRecord(name)
{
    var connection = getMySQL_connection();

    var query_str =
    "SELECT name, " +
    "FROM records " +   
    "WHERE (name = ?) " +
    "LIMIT 1 ";

    var query_var = [name];

    var query = connection.query(query_str, query_var, function (err, rows, fields) {
        //if (err) throw err;
        if (err) {
            //throw err;
            console.log(err);
            logger.info(err);
        }
        else {
            //console.log(rows);
            return rows;
        }
    }); //var query = connection.query(query_str, function (err, rows, fields) {
}

var rows = getLastRecord('name_record');

console.log(rows);

After some reading up, I realize the above code cannot work and I need to return a promise due to node.js's asynchronous nature. I cannot write node.js code like python. How do I convert getLastRecord()to return a promise and how do I handle the returned value?

经过一番阅读，我意识到上面的代码无法工作，并且由于 node.js 的异步性质，我需要返回一个 promise。我不能像 python 那样编写 node.js 代码。如何转换getLastRecord()为返回承诺以及如何处理返回值？

In fact, what I want to do is something like this;

其实我想做的是这样的；

if (getLastRecord() > 20)
{
    console.log("action");
}

How can this be done in node.js in a readable way?

这如何在 node.js 中以可读的方式完成？

I would like to see how promises can be implemented in this case using bluebird.

我想看看在这种情况下如何使用 bluebird 实现承诺。

回答by Josh Holbrook

This is gonna be a little scattered, forgive me.

这会有点散，见谅。

First, assuming this code uses the mysql driver API correctly, here's one way you could wrap it to work with a native promise:

首先，假设此代码正确使用了 mysql 驱动程序 API，这里有一种方法可以将其包装为使用本机承诺：

function getLastRecord(name)
{
    return new Promise(function(resolve, reject) {
        // The Promise constructor should catch any errors thrown on
        // this tick. Alternately, try/catch and reject(err) on catch.
        var connection = getMySQL_connection();

        var query_str =
        "SELECT name, " +
        "FROM records " +   
        "WHERE (name = ?) " +
        "LIMIT 1 ";

        var query_var = [name];

        connection.query(query_str, query_var, function (err, rows, fields) {
            // Call reject on error states,
            // call resolve with results
            if (err) {
                return reject(err);
            }
            resolve(rows);
        });
    });
}

getLastRecord('name_record').then(function(rows) {
    // now you have your rows, you can see if there are <20 of them
}).catch((err) => setImmediate(() => { throw err; })); // Throw async to escape the promise chain

So one thing: You still have callbacks. Callbacks are just functions that you hand to something to call at some point in the future with arguments of its choosing. So the function arguments in xs.map(fn), the (err, result)functions seen in node and the promise result and error handlers are all callbacks. This is somewhat confused by people referring to a specific kind of callback as "callbacks," the ones of (err, result)used in node core in what's called "continuation-passing style", sometimes called "nodebacks" by people that don't really like them.

所以一件事：你仍然有回调。回调只是您在将来某个时间点使用其选择的参数传递给某个对象的函数。所以 中的函数参数xs.map(fn)、(err, result)node 中看到的函数以及承诺结果和错误处理程序都是回调。人们将特定类型的回调称为“回调”，这有点令人困惑，这些回调(err, result)在节点核心中以所谓的“连续传递风格”使用，有时被不太喜欢它们的人称为“节点回调”。

For now, at least (async/await is coming eventually), you're pretty much stuck with callbacks, regardless of whether you adopt promises or not.

现在，至少（async/await 最终会到来），无论您是否采用 promise，您几乎都被回调困住了。

Also, I'll note that promises aren't immediately, obviously helpful here, as you still have a callback. Promises only really shine when you combine them with Promise.alland promise accumulators a la Array.prototype.reduce. But they doshine sometimes, and they areworth learning.

另外，我会注意到 Promise 不是立即的，显然在这里很有帮助，因为你仍然有一个回调。Promise 只有在您将它们与Promise.allPromise 累加器 a la结合时才会真正发光Array.prototype.reduce。但他们做的光泽有时，他们是值得学习的。

回答by Piyush Sagar

I have modified your code to use Q(NPM module) promises. I Assumed your 'getLastRecord()' function that you specified in above snippet works correctly.

我已修改您的代码以使用 Q(NPM 模块) 承诺。我假设您在上面的代码片段中指定的“getLastRecord()”函数工作正常。

You can refer following link to get hold of Q module

您可以参考以下链接获取 Q 模块

Click here : Q documentation

单击此处：Q 文档

var q = require('q');

function getLastRecord(name)
{

var deferred = q.defer(); // Use Q 
var connection = getMySQL_connection();

var query_str =
"SELECT name, " +
"FROM records " +   
"WHERE (name = ?) " +
"LIMIT 1 ";

var query_var = [name];

var query = connection.query(query_str, query_var, function (err, rows, fields) {
    //if (err) throw err;
    if (err) {
        //throw err;           
        deferred.reject(err);
    }
    else {
        //console.log(rows);           
        deferred.resolve(rows);
    }
}); //var query = connection.query(query_str, function (err, rows, fields) {

return deferred.promise;
}



// Call the method like this
getLastRecord('name_record')
 .then(function(rows){
   // This function get called, when success
   console.log(rows);
  },function(error){
   // This function get called, when error
   console.log(error);

 });

回答by MikeL

I am new to Node.js and promises. I was searching for a while for something that will meet my needs and this is what I ended up using after combining several examples I found. I wanted the ability to acquire connection per query and release it right after the query finishes (querySql), or to get a connection from pool and use it within Promise.using scope, or release it whenever I would like it (getSqlConnection). Using this method you can concat several queries one after another without nesting them.

我是 Node.js 的新手并承诺。我一直在寻找能够满足我需求的东西，这是我在结合我发现的几个例子后最终使用的东西。我希望能够获取每个查询的连接并在查询完成后立即释放它 ( querySql)，或者从池中获取连接并在 Promise.using 范围内使用它，或者在我想要的时候释放它 ( getSqlConnection)。使用这种方法，您可以一个接一个地连接多个查询，而无需嵌套它们。

db.js

数据库.js

var mysql = require('mysql');
var Promise = require("bluebird");

Promise.promisifyAll(mysql);
Promise.promisifyAll(require("mysql/lib/Connection").prototype);
Promise.promisifyAll(require("mysql/lib/Pool").prototype);

var pool = mysql.createPool({
    host: 'my_aws_host',
    port: '3306',
    user: 'my_user',
    password: 'my_password',
    database: 'db_name'
});

function getSqlConnection() {
    return pool.getConnectionAsync().disposer(function (connection) {
        console.log("Releasing connection back to pool")
        connection.release();
    });
}

function querySql (query, params) {
    return Promise.using(getSqlConnection(), function (connection) {
        console.log("Got connection from pool");
        if (typeof params !== 'undefined'){
            return connection.queryAsync(query, params);
        } else {
            return connection.queryAsync(query);
        }
    });
};

module.exports = {
    getSqlConnection : getSqlConnection,
    querySql : querySql
};

usage_route.js

用法_route.js

var express = require('express');
var router = express.Router();

var dateFormat = require('dateformat');
var db = require('../my_modules/db');
var getSqlConnection = db.getSqlConnection;
var querySql = db.querySql;

var Promise = require("bluebird");

function retrieveUser(token) {
  var userQuery = "select id, email from users where token = ?";
  return querySql(userQuery, [token])
     .then(function(rows){
        if (rows.length == 0) {
          return Promise.reject("did not find user");
        }

        var user = rows[0];
        return user;
     });
}

router.post('/', function (req, res, next) {

  Promise.resolve().then(function () {
    return retrieveUser(req.body.token);
  })
    .then(function (user){
      email = user.email;
      res.status(200).json({ "code": 0, "message": "success", "email": email});
    })
    .catch(function (err) {
      console.error("got error: " + err);
      if (err instanceof Error) {
        res.status(400).send("General error");
      } else {
        res.status(200).json({ "code": 1000, "message": err });
      }
    });
});

module.exports = router;

回答by Jordi Ruiz

You don't need to use promises, you can use a callback function, something like that:

您不需要使用承诺，您可以使用回调函数，如下所示：

function getLastRecord(name, next)
{
    var connection = getMySQL_connection();

    var query_str =
    "SELECT name, " +
    "FROM records " +    
    "LIMIT 1 ";

    var query_var = [name];

    var query = connection.query(query_str, query_var, function (err, rows, fields) {
        //if (err) throw err;
        if (err) {
            //throw err;
            console.log(err);
            logger.info(err);
            next(err);
        }
        else {
            //console.log(rows);
            next(null, rows);
        }
    }); //var query = connection.query(query_str, function (err, rows, fields) {
}

getLastRecord('name_record', function(err, data) {
   if(err) {
      // handle the error
   } else {
      // handle your data

   }
});

回答by CFrei

To answer your initial question: How can this be done in node.js in a readable way?

回答您最初的问题：如何在 node.js 中以可读的方式完成此操作？

There is a library called co, which gives you the possibility to write async code in a synchronous workflow. Just have a look and npm install co.

有一个名为 的库co，它使您可以在同步工作流中编写异步代码。只要看看和npm install co。

The problem you face very often with that approach, is, that you do not get Promiseback from all the libraries you like to use. So you have either wrap it yourself (see answer from @Joshua Holbrook) or look for a wrapper (for example: npm install mysql-promise)

使用这种方法经常遇到的问题是，您无法Promise从喜欢使用的所有库中返回。所以，你要么自己把它包装（请参阅从@Joshua霍尔布鲁克的答案），或者寻找一个包装（如：npm install mysql-promise）

(Btw: its on the roadmap for ES7 to have native support for this type of workflow with the keywords asyncawait, but its not yet in node: node feature list.)

（顺便说一句：它在 ES7 的路线图上使用关键字对此类工作流提供本机支持asyncawait，但它还没有在 node: node feature list 中。）

回答by Andrej Burcev

This can be achieved quite simply, for example with bluebird, as you asked:

这可以很简单地实现，例如使用 bluebird，如您所问：

var Promise = require('bluebird');

function getLastRecord(name)
{
    return new Promise(function(resolve, reject){
        var connection = getMySQL_connection();

        var query_str =
            "SELECT name, " +
            "FROM records " +
            "WHERE (name = ?) " +
            "LIMIT 1 ";

        var query_var = [name];

        var query = connection.query(query_str, query_var, function (err, rows, fields) {
            //if (err) throw err;
            if (err) {
                //throw err;
                console.log(err);
                logger.info(err);
                reject(err);
            }
            else {
                resolve(rows);
                //console.log(rows);
            }
        }); //var query = connection.query(query_str, function (err, rows, fields) {
    });
}


getLastRecord('name_record')
    .then(function(rows){
        if (rows > 20) {
            console.log("action");
        }
    })
    .error(function(e){console.log("Error handler " + e)})
    .catch(function(e){console.log("Catch handler " + e)});

回答by Nicolas Guérinet

Using the package promise-mysql the logic would be to chain promises using then(function(response){your code})

使用包 promise-mysql 的逻辑是使用 then(function(response){your code}) 链接承诺

and

和

catch(function(response){your code}) to catch errors from the "then" blocks preceeding the catch block.

catch(function(response){your code}) 从 catch 块之前的“then”块中捕获错误。

Following this logic, you will pass query results in objects or arrays using return at the end of the block. The return will help passing the query results to the next block. Then, the result will be found in the function argument (here it is test1). Using this logic you can chain several MySql queries and the code that is required to manipulate the result and do whatever you want.

按照此逻辑，您将在块的末尾使用 return 以对象或数组的形式传递查询结果。返回将有助于将查询结果传递到下一个块。然后，结果将在函数参数中找到（这里是 test1）。使用此逻辑，您可以链接多个 MySql 查询和操作结果所需的代码并执行您想要的任何操作。

the Connection object is created to be global because every object and variable created in every block are only local. Don't forget that you can chain more "then" blocks.

Connection 对象被创建为全局对象，因为在每个块中创建的每个对象和变量都只是局部的。不要忘记你可以链接更多的“then”块。

var config = {
    host     : 'host',
    user     : 'user',
    password : 'pass',
    database : 'database',

  };
  var mysql = require('promise-mysql');
  var connection;
  let thename =""; // which can also be an argument if you embed this code in a function

  mysql.createConnection(config
  ).then(function(conn){
      connection = conn;
      let test = connection.query('select name from records WHERE name=? LIMIT 1',[thename]);
      return test;
  }).then(function(test1){
      console.log("test1"+JSON.stringify(test1)); // result of previous block
      var result = connection.query('select * from users'); // A second query if you want
      connection.end();
 connection = {};
      return result;
  }).catch(function(error){
      if (connection && connection.end) connection.end();
      //logs out the error from the previous block (if there is any issue add a second catch behind this one)
      console.log(error);
  });

回答by Dillon Burnett

I am still a bit new to node, so maybe I missed something let me know how it works out. Instead of triggering async node just forces it on you, so you have to think ahead and plan it.

我对 node 还是有点陌生​​，所以也许我错过了一些让我知道它是如何工作的东西。而不是触发异步节点只是强迫你，所以你必须提前考虑并计划它。

const mysql = require('mysql');
const db = mysql.createConnection({
          host: 'localhost', 
          user: 'user', password: 'password', 
          database: 'database',
      });
      db.connect((err) => {
          // you should probably add reject instead of throwing error
          // reject(new Error()); 
          if(err){throw err;}
          console.log('Mysql: Connected');
      });
      db.promise = (sql) => {
          return new Promise((resolve, reject) => {
              db.query(sql, (err, result) => {
                if(err){reject(new Error());}
                else{resolve(result);}
              });
          });
      };

Here I am using the mysql module like normal, but instead I created a new function to handle the promise ahead of time, by adding it to the db const. (you see this as "connection" in a lot of node examples.

在这里，我像往常一样使用 mysql 模块，但我创建了一个新函数来提前处理承诺，方法是将其添加到 db const 中。（在许多节点示例中，您将其视为“连接”。

Now lets call a mysql query using the promise.

现在让我们使用 Promise 调用 mysql 查询。

      db.promise("SELECT * FROM users WHERE username='john doe' LIMIT 1;")
      .then((result)=>{
          console.log(result);
      }).catch((err)=>{
          console.log(err);
      });

What I have found this useful for is when you need to do a second query based on the first query.

我发现这很有用，当您需要根据第一个查询进行第二个查询时。

      db.promise("SELECT * FROM users WHERE username='john doe' LIMIT 1;")
      .then((result)=>{
          console.log(result);
          var sql = "SELECT * FROM friends WHERE username='";
              sql = result[0];
              sql = "';"
          return db.promise(sql);
      }).then((result)=>{
          console.log(result);
      }).catch((err)=>{
          console.log(err);
      });

You should actually use the mysql variables, but this should at least give you an example of using promises with mysql module.

您实际上应该使用 mysql 变量，但这至少应该为您提供一个在 mysql 模块中使用 promise 的示例。

Also with above you can still continue to use the db.query the normal way anytime within these promises, they just work like normal.

同样在上面，您仍然可以在这些承诺中随时以正常方式继续使用 db.query ，它们就像正常工作一样。

Hope this helps with the triangle of death.

希望这有助于解决死亡三角。