如何使用pg-promise同时从多个查询中获得结果？ [英] How to get results from multiple queries at once with pg-promise?

问题描述

目前我有以下代码来获取两个查询的结果

  dbro.many（SELECT geoname_id，country_name FROM paises WHERE locale_code = $ 1 LIMIT 10，data.lang）
 .then（function（countriesData）{
 data.countries = countriesData; 
 dbro.many（SELECT * FROM categorias） 
 .then（function（categoriesData）{
 data.categories =（categoriesData）
 console.log（data）; 
 res.render（'layout'，data）; 
 res.end（）; 
}）
 .catch（function（err）{
 console.log（获取类别数据时出错）; 
}） 
}）
 .catch（function（err）{
 console.log（获取国家/地区数据时出错，错误）; 
}）; 
  

不知怎的，我认为这是不对的。如果我需要在返回回调之前获得许多查询的结果怎么办？几个then / catch的嵌套变得丑陋。目标是在呈现页面之前准备好所有数据（在Express中）

解决方案

pg-promise 文档大量示例，了解如何执行多个查询。

初始化

  const pgp = require（'pg-promise'）（/ *初始化选项* /）; 
 const db = pgp（'postgres：// username：password @ host：port / database'）; 
  

当查询相互依赖时，我们应在任务中链接它们：

  db.task（'get-user-events'，t => {
 return t.one（ 'select * from id = $ 1'的用户，123）
 .then（user => {
 return t.any（'select * from login where login = $ 1'，user.name）; 
}）; 
}）
 .then（data => {
 // data =上次查询的结果; 
}）
。 catch（错误=> {
 //错误
}）; 
  

当查询没有依赖关系时，我们应该将它们作为批处理：

  db.task（'get-everything'，t => {
 return t.batch（[
 t.any（'select *来自用户'），
 t.one（'从事件中选择计数（*）'
]）; 
}）
 .then（data => {
 // data [0] =来自第一个查询的结果; 
 // data [1] =来自第二个查询的结果; 
}）
 .catch（error => {
 //错误
}）; 
  

当查询更改数据时，我们应该替换任务， tx 。

<请注意，我强调每个语句都带有should，因为您可以执行任务或事务之外的所有内容，但由于数据库连接的管理方式，不建议这样做。

当您需要执行a 时，只需对根协议（ db 对象）执行查询每个HTTP请求的单个查询。一次多次查询应始终在任务/交易中执行。

另请参阅 Chaining Queries ，其主要点位于底部：

< blockquote>

如果你不遵循建议的方法，你的应用程序将在较小的负载下表现更好，因为并行分配了更多的连接，但是在负载很重的情况下，它会很快耗尽连接池，致使您的应用程序的性能和可扩展性。

更新

从 pg-promise v7.0.0开始，我们可以从多个查询中提取结果在一个命令中，它比以前的所有解决方案都高效得多：

  db.multi（'SELECT * FROM users ; SELECT count（*）FROM events ）
。然后（数据=> {
 // data [0] =第一个查询的结果; 
 // data [1] =第二个查询的结果; 
}）
 .catch（错误=> {
 //错误
}）; 
  

或者如果你使用 Bluebird ，那么：

  db.multi（'SELECT * FROM users; SELECT count（*）FROM events'）
 .spread （（users，[{count}]）=> {
 // users =用户记录数组; 
 // count =带计数的字符串; 
}）
 .catch（error => {
 //错误
}）; 
  

查看新方法 multi 和 multiResult 。

Currently I have the following code to get the results of two queries

dbro.many("SELECT geoname_id, country_name FROM paises WHERE locale_code=$1 LIMIT 10",data.lang)
   .then(function(countriesData){
      data.countries=countriesData;
      dbro.many("SELECT * FROM categorias")
       .then(function(categoriesData){
         data.categories=(categoriesData)
         console.log(data);
         res.render('layout', data);
         res.end();
      })
       .catch(function(err){
        console.log("error while fetching categories data");
      })
    })
    .catch(function(err){
      console.log("error while fetching countries data",err);
    });

Somehow I think this is not right. What if I need to get the results of many queries before returning the callback? The nesting of several then/catch becomes hideous. The objective is to have all the data ready before rendering a page (in Express)

解决方案

pg-promise documentation has plenty of examples of how to execute multiple queries.

Initialization

const pgp = require('pg-promise')(/* initialization options */);
const db = pgp('postgres://username:password@host:port/database');

When queries depend on each other we should chain them within a task:

db.task('get-user-events', t => {
        return t.one('select * from users where id = $1', 123)
            .then(user => {
                return t.any('select * from events where login = $1', user.name);
            });
    })
    .then(data => {
        // data = result from the last query;
    })
    .catch(error => {
        // error
    });

When queries have no dependencies, we should execute them as a batch within a task:

db.task('get-everything', t => {
        return t.batch([
            t.any('select * from users'),
            t.one('select count(*) from events')
        ]);
    })
    .then(data => {
        // data[0] = result from the first query;
        // data[1] = result from the second query;
    })
    .catch(error => {
        // error
    });

And when the queries change the data, we should replace task with tx for transaction.

Note that I emphasized each statement with "should", as you can execute everything outside of tasks or transactions, but it is not recommended, due to the way database connections are managed.

You should only execute queries on the root protocol (db object) when you need to execute a single query per HTTP request. Multiple queries at once should always be executed within tasks/transactions.

See also Chaining Queries, with its main point at the bottom there:

If you do not follow the advised approach, your application will perform better under a small load, due to more connections allocated in parallel, but under a heavy load it will quickly deplete the connection pool, crippling performance and scalability of your application.

UPDATE

Starting from pg-promise v7.0.0 we can pull results from multiple queries in a single command, which is much more efficient than all of the previous solutions:

db.multi('SELECT * FROM users;SELECT count(*) FROM events')
    .then(data => {
        // data[0] = result from the first query;
        // data[1] = result from the second query;
    })
    .catch(error => {
        // error
    });

Or if you are using Bluebird, then:

db.multi('SELECT * FROM users;SELECT count(*) FROM events')
    .spread((users, [{count}]) => {
        // users = array of user records;
        // count = string with the count;
    })
    .catch(error => {
        // error
    });

See new methods multi and multiResult.

这篇关于如何使用pg-promise同时从多个查询中获得结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！