How to get results from multiple queries at once with pg-promise?

Question

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)

Heartstricken

Answer 1

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 use a task:

db.task('get-user-events', async t => {
        const user = await t.one('select * from users where id = $1', 123);
        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 between them:

db.task('get-everything', async t => {
    const users = await t.any('select * from users');
    const count = await t.one('select count(*) from events', [], a => +a.count);
    return {users, count};
})
    .then({users, count} => {

    })
    .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 independent queries in a single command, which is much more efficient than all of the previous solutions:

const {users, count} = await db.multi('SELECT * FROM users;SELECT count(*) FROM events');

The library implements helpers.concat, to format and concatenate multiple queries.

See also methods: multi and multiResult.

Vaso

Thanks this is what I was looking for. I did read the documentation but I missed the crucial part (at least for me): // data[0] = result from the first query; // data[1] = result from the second query; (result data as an array) – Heartstricken

@Vaso Thanks for your examples. However, how would you go if you had to mix both ? In my task I run 5 independent queries. Each one return the latest insert id. Once this task is done, I need to run a last query, which would use all the ids produces by the task. I haven't managed to mix both so far. Do you have any idea. Thanks for your help. – Malone

@Stanislasdrg what you are asking is more about basic use of promises rather than this library. You do return t.batch([...your 5 inserts...]).then(data=>t.query(...query based on data...)). – Vaso

@Vaso Thanks for your example. How will you do a more complicated chain? for example "get all users named john" and then "get their children's name" and return as [{ firstname:"John", lastname:"dep", children:[{name:...},{...}] } ,{ ... ] – Diverge

@Diverge are you talking about a simple one-to-many request? Anyhow, this message board is a poor place for such discussion. Ask a separate question on StackOverflow, I will answer it ;) – Vaso

@Vaso Many to Many is more of what I have in mind. I just posted #36586986 . Thanks – Diverge

@DavidNoriega The answer has received an update for the latest version of the library. – Vaso

@Vaso When/why would one use batch instead of multi if the latter is better? – Feculent

@Feculent Actually, it is neither. In practice, you would need to format queries a lot, and so if they do not have dependency, then you can use helpers.concat, and only then multi. Other than that, approach with multi is simply newer. Method batch allows for some dependency, as it can execute functions that return queries. – Vaso

I see @Vaso answer, I upvote :) curious question, if there is an error in await t.one wont it trigger an unhandledpromiserejection? – Festschrift

@Festschrift No, it will be handled by catch on the task. – Vaso