使用 Node.js 进行同步数据库查询

Question

提问by Rick

I have a Node.js/Express app that queries a MySQL db within the route and displays the result to the user. My problem is how do I run the queries and block until both queries are done before redirecting the user to the page they requested?

我有一个 Node.js/Express 应用程序，它在路由中查询 MySQL 数据库并将结果显示给用户。我的问题是如何在将用户重定向到他们请求的页面之前运行查询并阻止直到两个查询都完成？

In my example I have 2 queries that need to finish before I render the page. I can get the queries to run synchronously if i nest query 2 inside the 'result' callback of query 1. This however will become very convoluted when the number of queries increase.

在我的示例中，我有 2 个需要在呈现页面之前完成的查询。如果我将查询 2 嵌套在查询 1 的“结果”回调中，我可以让查询同步运行。但是，当查询数量增加时，这将变得非常复杂。

How do I go about running multiple (in this case 2) database queries synchronously without nesting the subsequent query in the prior query's 'result' callback?

如何同步运行多个（在本例中为 2）数据库查询，而不将后续查询嵌套在先前查询的“结果”回调中？

I've looked at the 'Flow control / Async goodies' in the Node modules and tried flow-js but I can't get it to work with the async queries.

我查看了 Node 模块中的“流量控制/异步好东西”并尝试了 flow-js，但我无法让它与异步查询一起工作。

Listed below are the 2 queries that I'm attempting to execute from the '/home' route. Can the Node experts explain the 'right' way to do this.

下面列出的是我试图从“/home”路由执行的 2 个查询。节点专家能否解释执行此操作的“正确”方法。

app.get('/home', function (req,res) {
    var user_array = [];
    var title_array = [];

    // first query
    var sql = 'select user_name from users';
    db.execute(sql)
        .addListener('row', function(r) {
            user_array.push( { user_name: r.user_name } );
        })
        .addListener('result', function(r) {
            req.session.user_array = user_array;
        });

    // second query
    var sql = 'select title from code_samples';
    db.execute(sql)
        .addListener('row', function(r) {
            title_array.push( { title: r.title } );
        })
        .addListener('result', function(r) {
            req.session.title_array = title_array;
        });

        // because the queries are async no data is returned to the user
        res.render('home.ejs', {layout: false, locals: { user_name: user_array, title: title_array }});
});

Answer 1

回答by jslatts

The goal with node is not to care what order things happen in. This can complicate some scenarios. There is no shame in nesting callbacks. Once you are used to how it looks, you may find that you actually prefer that style. I do; it is very clear what order callbacks will fire. You can forgo the anonymous functions to make it less verbose if you have to.

node 的目标是不关心事情发生的顺序。这可能会使某些场景复杂化。嵌套回调没有什么可耻的。一旦习惯了它的外观，您可能会发现您实际上更喜欢这种风格。我愿意; 很清楚什么订单回调将被触发。如果需要，您可以放弃匿名函数以使其不那么冗长。

If you are willing to restructure your code a bit, you can use the "typical" nested callback method. If you want to avoid callbacks, there are numerous async frameworks that will try and help you do this. One that you might want to check out is async.js (https://github.com/fjakobs/async.js). Example of each:

如果您愿意稍微重组您的代码，您可以使用“典型的”嵌套回调方法。如果您想避免回调，有许多异步框架会尝试帮助您做到这一点。您可能想要查看的一个是 async.js (https://github.com/fjakobs/async.js)。每个示例：

app.get('/home', function (req,res) {
    var lock = 2;
    var result = {};
    result.user_array = [];
    result.title_array = [];

    var finishRequest = function(result) {
        req.session.title_array = result.title_array;
        req.session.user_array = result.user_array;
        res.render('home.ejs', {layout: false, locals: { user_name: result.user_array, title: result.title_array }});
    };

    // first query
    var q1 = function(fn) {
      var sql = 'select user_name from users';
      db.execute(sql)
          .addListener('row', function(r) {
              result.user_array.push( { user_name: r.user_name } );
          })
          .addListener('result', function(r) {
              return fn && fn(null, result);
        });
    };

    // second query
    var q2 = function(fn) {
      var sql = 'select title from code_samples';
      db.execute(sql)
          .addListener('row', function(r) {
              result.title_array.push( { title: r.title } );
          })
          .addListener('result', function(r) {
              return fn && fn(null, result);
          });
    }

    //Standard nested callbacks
    q1(function (err, result) {
      if (err) { return; //do something}

      q2(function (err, result) {
        if (err) { return; //do something}

        finishRequest(result);
      });
    });

    //Using async.js
    async.list([
        q1,
        q2,
    ]).call().end(function(err, result) {
      finishRequest(result);
    });

});

For a one-off, I would probably just use a reference counting type approach. Simply keep track of how many queries you want to execute and render the response when they have all finished.

对于一次性，我可能只使用引用计数类型的方法。只需跟踪您要执行的查询数量，并在它们全部完成后呈现响应。

app.get('/home', function (req,res) {
    var lock = 2;
    var user_array = [];
    var title_array = [];

    var finishRequest = function() {
        res.render('home.ejs', {layout: false, locals: { user_name: user_array, title: title_array }});
    }

    // first query
    var sql = 'select user_name from users';
    db.execute(sql)
        .addListener('row', function(r) {
            user_array.push( { user_name: r.user_name } );
        })
        .addListener('result', function(r) {
            req.session.user_array = user_array;
            lock -= 1;

            if (lock === 0) {
              finishRequest();
            }
        });

    // second query
    var sql = 'select title from code_samples';
    db.execute(sql)
        .addListener('row', function(r) {
            title_array.push( { title: r.title } );
        })
        .addListener('result', function(r) {
            req.session.title_array = title_array;
            lock -= 1;

            if (lock === 0) {
              finishRequest();
            }
        });
});

An even nicer approach would be to simply call finishRequest() in each 'result' callback an check for non-empty arrays before you render the response. Whether that will work in your case depends on your requirements.

更好的方法是在每个“结果”回调中简单地调用 finishRequest() 以在呈现响应之前检查非空数组。这是否适用于您的情况取决于您的要求。

Answer 2

回答by Raynos

Here's a really easy trick to handle multiple callbacks.

这是处理多个回调的一个非常简单的技巧。

var after = function _after(count, f) {
  var c = 0, results = [];
  return function _callback() {
    switch (arguments.length) {
      case 0: results.push(null); break;
      case 1: results.push(arguments[0]); break;
      default: results.push(Array.prototype.slice.call(arguments)); break;
    }
    if (++c === count) {
      f.apply(this, results);
    }
  };
};

Example

例子

Usage:

用法：

var handleDatabase = after(2, function (res1, res2) {
  res.render('home.ejs', { locals: { r1: res1, r2: res2 }):
})

db.execute(sql1).on('result', handleDatabase);
db.execute(sql2).on('result', handleDatabase);

So basically you need reference counting. This is the standard approach in these situations. I actually use this small utility function instead of flow control.

所以基本上你需要引用计数。这是这些情况下的标准方法。我实际上使用这个小的实用程序函数而不是流量控制。

If you want a full blown flow control solution I would recommend futuresJS

如果你想要一个完整的流量控制解决方案，我会推荐futuresJS

Answer 3

回答by loganfsmyth

I find that the async library is the best for things like this. https://github.com/caolan/async#parallel

我发现异步库最适合这样的事情。https://github.com/caolan/async#parallel

I can't test this or anything, so forgive me if there are some typos. I refactored your query function to be reusable. So, calling queryRows will return a function that matches the format of the async module's parallel callback functions. After both queries are complete, it will call the last function and pass the result of the two queries as an argument, which you can read to pass to your template.

我无法测试这个或任何东西，所以如果有一些错别字，请原谅我。我重构了您的查询功能以使其可重用。因此，调用 queryRows 将返回一个与异步模块的并行回调函数格式匹配的函数。两个查询都完成后，它会调用最后一个函数并将两个查询的结果作为参数传递，您可以读取该结果以传递给您的模板。

function queryRows(col, table) {
  return function(cb) {
    var rows = [];
    db.execute('SELECT ' + col + ' FROM ' + table)
      .on('row', function(r) {
        rows.push(r)        
      })
      .on('result', function() {
        cb(rows);
      });
  }
}

app.get('/home', function(req, res) {
  async.parallel({
    users: queryRow('user_name', 'users'),
    titles: queryRow('title', 'code_samples')
  },
  function(result) {
    res.render('home.ejs', { 
      layout: false,
      locals: {user_name: result.users, title: result.titles} 
    });
  });
});

Answer 4

回答by EscapeNetscape

There are some solutions here, but in my opinion the best solution is to make the code synchronously in a very easy way.

这里有一些解决方案，但在我看来，最好的解决方案是以非常简单的方式同步编写代码。

You could use the "synchonize" package.

您可以使用“同步”包。

Just

只是

npm install synchronize

npm 安装同步

Then var sync = require(synchronize);

然后 var sync = require(synchronize);

Put logic which should be synchronous into a fiber by using

通过使用将应该同步的逻辑放入光纤中

sync.fiber(function() { //put your logic here }

An example for two mysql queries:

两个 mysql 查询的示例：

var express = require('express');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var sync = require('synchronize');

var db = mysql.createConnection({
    host     : 'localhost',
    user     : 'user',
    password : 'password',
    database : 'database'
});

db.connect(function(err) {
    if (err) {
        console.error('error connecting: ' + err.stack);
        return;
    }
});

function saveSomething() {
    var post  = {id: newId};
    //no callback here; the result is in "query"
    var query = sync.await(db.query('INSERT INTO mainTable SET ?', post, sync.defer()));
    var newId = query.insertId;
    post  = {foreignKey: newId};
    //this query can be async, because it doesn't matter in this case
    db.query('INSERT INTO subTable SET ?', post, function(err, result) {
        if (err) throw err;
    });
}

When "saveSomething()" is called, it inserts a row in a main table and receives the last inserted id. After that the code below will be executed. No need for nesting promises or stuff like that.

当“saveSomething()”被调用时，它会在主表中插入一行并接收最后插入的id。之后将执行下面的代码。不需要嵌套承诺或类似的东西。

Answer 5

回答by Andrey Sidorov

option one: if all your queries related to each other, create stored procedure, put all your data logic into it and have a single db.execute

选项一：如果您的所有查询都相互关联，则创建存储过程，将所有数据逻辑放入其中并有一个 db.execute

option two: if your db uses one connection then commands a guaranteed to be executed serially and you can use this as async helper

选项二：如果您的数据库使用一个连接，则命令 a 保证串行执行，您可以将其用作异步助手

db.execute(sql1).on('row', function(r) {
   req.session.user_array.push(r.user);
});
db.execute(sql2)
.on('row', function(r) {
   req.session.title_array.push(r.title);
})
.on('end'), function() {
   // render data from req.session
});

Answer 6

回答by Alexey Petrushin

You can use fibers to write pseudo-synchronous code with Node.JS take a look at these tests for DB https://github.com/alexeypetrushin/mongo-lite/blob/master/test/collection.coffeethey are asynchronous but looks like synchronous, more details http://alexeypetrushin.github.com/synchronize

您可以使用纤程使用 Node.JS 编写伪同步代码，看看这些 DB 测试 https://github.com/alexeypetrushin/mongo-lite/blob/master/test/collection.coffee它们是异步的，但看起来喜欢同步，更多细节http://alexeypetrushin.github.com/synchronize

使用 Node.js 进行同步数据库查询

提问by Rick

回答by jslatts

回答by Raynos

回答by loganfsmyth

回答by EscapeNetscape

回答by Andrey Sidorov

回答by Alexey Petrushin

相关推荐

最近更新

标签

使用 Node.js 进行同步数据库查询

提问by Rick

回答by jslatts

回答by Raynos

回答by loganfsmyth

回答by EscapeNetscape

回答by Andrey Sidorov

回答by Alexey Petrushin

相关推荐

在 node.js 中找不到模块错误

如何清除 node.js 中的超时

node.js 为什么我可以在“res.send”之后执行代码？

在 NodeJS 中读取第 N 行文件

相关推荐

最近更新

标签