CODEWITHSUNDEEP
node.jspostgresqlpg-promise
deanna
deanna

Reputation: 45

pg-promise resolving multiple queries in a map function

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.

Upvotes: 0

Views: 1015

Answers (2)

deanna
deanna

Reputation: 45

Thanks! so to fully separate concerns, I should do something like:

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

and then when I call this function, say to add it to an express api, do

  router.get('/:id', async (req, res, next) => {
    const all = await get(req.params.id)
      .then(data => {
        return {success: true, response: data}
      })
      .catch(error => {
        return {success: false, response: error.message || error}
      })
    res.json(all)
  })

Upvotes: 0

vitaly-t
vitaly-t

Reputation: 25840

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.

Upvotes: 1

Related Questions