使用 Node.js 进行同步数据库查询 [英] Synchronous database queries with Node.js

问题描述

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

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?

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

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 个)数据库查询而不将后续查询嵌套在先前查询的结果"回调中?

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?

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

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.

下面列出了我试图从/home"路由执行的 2 个查询.Node 专家能否解释一下执行此操作的正确"方法.

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.

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 }});
});

推荐答案

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

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.

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

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:

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();
            }
        });
});

一个更好的方法是在每个结果"回调中简单地调用 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.

这篇关于使用 Node.js 进行同步数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！