pg-promise resolving multiple queries in a map function

Question

related to pg-promise transaction with dependent queries in forEach loop gives warning Error: Querying against a released or lost connection , I am now trying to return multiple queries from within a map function

const {db} = require('../db')

async function get(id) {
  return await db
    .task(async t => {
      const items = await t.any(`SELECT i.* FROM item i WHERE i.parent_id = $1#`, id)
      const itemDetails = items.map(async item => {
        const layers = await t.any(`SELECT l.* FROM layer l WHERE l.item_id = $1#`, item.id)
        const bases = await t.any(`SELECT b.* FROM base b WHERE b.item_id = $1#`, item.id)
        return [layers, bases]
      })
      // Not resolving properly!
      await t.batch(itemDetails.flat())

      return {items: items, itemDetails: itemDetails}
    })
    .then(data => {
      return {success: true, response: data}
    })
    .catch(error => {
      return {success: false, response: error.message || error}
    })
}

However, I'm not sure how to properly resolve the multiple queries (layers and bases). If I was returning either one or the other, as per the linked question, I could batch resolve the array of promises before moving on. However, when returning more than one query in each map iteration, I'm not sure how to properly resolve everything before moving on.

Answer 1

There are a few things that you are doing in an odd way. Here's rectified version:

function get(id) {
  return db.task(async t => {
      const items = await t.any(`SELECT i.* FROM item i WHERE i.parent_id = $<id>`, {id});
      const itemDetails = items.map(async item => {
        const layers = await t.any(`SELECT l.* FROM layer l WHERE l.item_id = $<id>`, item);
        const bases = await t.any(`SELECT b.* FROM base b WHERE b.item_id = $<id>`, item);
        return {layers, bases};
      });

      const details = await t.batch(itemDetails);

      return {items, details};
    })
    .then(data => {
      return {success: true, response: data};
    })
    .catch(error => {
      return {success: false, response: error.message || error};
    })
}

Note that you still would have a mix of concerns here, because .then->.catch should be outside of the get function here, i.e. avoid mixing database logic with HTTP controllers.