Best way to query a Many to Many Relationship using pg-promise

Question

For example I want to get the user info, emails and it's roles from db and create an object like :

{
  "id": 1,
  "firstname": "John",
  "lastname": "Johnny",
  "emails": [
    {
      "type": "work",
      "email": "work@work.com"
    },
    {
      "type": "personal",
      "email": "personal@personal.com"
    }
  ],
  "roles": [
    {
      "role": "ADM",
      "title": "Admin"
    },
    {
      "role": "PUB",
      "title": "Publisher"
    }
  ]
}

There are three tables I need to query:

• Users table has id, firstname,lastname.
• Emails table has type, email,user_id.
• Roles table has role, title,user_id.

Based on the pg-promise's wiki I am almost sure it has to be done using Tasks but not sure how would you chain them.

UPDATE In my actual project I had to insert a product and use the generated id to insert attributes. Adding my code here in case you have a similar situation:

//Insert a new product with attribites as key value pairs
post_product_with_attr: function(args) {
    return db.task(function(t) {
        return t.one(sql.post_new_product, args)
            .then(function(dt) {
                var queries = [];
                Object.keys(args).forEach(function(key) {
                    queries.push(t.one(sql.post_attr_one, { Id: dt.id, key: key, value: args[key] }));
                 });
                return queries.length ? t.batch(queries) : [dt];
            });
    });
}

Answer 1

What you are describing isn't a Many-to-Many relationship, it is 2 One-To-Many relationships.

Based on the pg-promise's wiki I am almost sure it has to be done using Tasks but not sure how would you chain them.

In your example there is no need to chain anything. One chains promises when the result of one needs to be used as the criteria for the next one, because this is how promises work, not just queries.

Because of that, you can execute all 3 queries either in parallel, or independently, as shown in the example below:

function getUserInfo(userId) {
    return db.task(async t => {
        const user = await t.one('SELECT id, firstname, lastname FROM Users WHERE id = $1', userId);
        const emails = await t.any('SELECT type, email FROM emails WHERE user_id = $1', userId),
        const roles = await t.any('SELECT role, title FROM roles WHERE user_id = $1', userId);

        user.emails = emails;
        user.roles = roles;

        return user;
});

Usage example:

const user = await getUserInfo(123); //=> user object with all details

Note that it is much more efficient to do this via existing PostgreSQL JSON functions, which let you execute just one query then.