node.js pg-promise and pagination from API

Question

Looking at https://github.com/vitaly-t/pg-promise/wiki/Data-Imports there's a very detailed doc on how to use it for importing.

However while that works for the demoed scenario I don't know how to apply it on my case.

When I do my web call, I get the actual JSON data and a paramter in the header which gives me a value for the next page (could be a date or String or a number value).

In the example, it says:

db.tx('massive-insert', t => {
    return t.sequence(index => {
        return getNextData(index)
            .then(data => {
                if (data) {
                    const insert = pgp.helpers.insert(data, cs);
                    return t.none(insert);
                }
            });
    });
})
    .then(data => {
        console.log('Total batches:', data.total, ', Duration:', data.duration);
    })
    .catch(error => {
        console.log(error);
    });

In this case, sequence(index will use index which seems to increment +1. But in my case,

function getNextData(nextPage) {
    //get the data for nextPage
    .....
   //get the nextPage if exists for future use
   nextPage = response.next;

   resolve(data);
}

My question is, how can I replace index with nextPage in this example, as each new Promise needs to use the nextPage from previous one.

LATER EDIT: And if I want to fetch info from a certain value of nextPageInfo?

For instance:

db.any('Select value from table')
      .then(function(value) {

var data = value; //not working

db.tx('massive-insert', t => {
    return t.sequence((index, data) => {
        return getNextData(index, data)
            .then(a => {
                if (a) {
                    const insert = pgp.helpers.insert(a.data, cs);
                    return t.none(insert).then(() => a.nextPageInfo);
                }
            })
    });
})
    .then(data => {
        // COMMIT has been executed
        console.log('Total batches:', data.total, ', Duration:', data.duration);
    })
    .catch(error => {
        // ROLLBACK has been executed
        console.log(error);
    })

}

Uziel

Answer 1

Following this question, I have extended article Data Imports with the new extras section, which gives you exactly the example that you need. The example copied from the article:

function getNextData(t, index, nextPageInfo) {
    // t = database transaction protocol

    // NOTE: nextPageInfo = undefined when index = 0

    return new Promise((resolve, reject) {

        /* pull the next data, according to nextPageInfo */            

        /* do reject(error) on an error, to ROLLBACK changes */
    
        if(/* there is still data left*/) {
            // if whateverNextDetails = undefined, the data will be inserted,
            // but the sequence will end there (as success).
            resolve({data, nextPageInfo: whateverNextDetails});
        } else {
            resolve(null);
        }   
    });
}

db.tx('massive-insert', t => {
    return t.sequence((index, data) => {
        return getNextData(t, index, data)
            .then(a => {
                if (a) {
                    const insert = pgp.helpers.insert(a.data, cs);
                    return t.none(insert).then(() => a.nextPageInfo);
                }
            })
    });
})
    .then(data => {
        // COMMIT has been executed
        console.log('Total batches:', data.total, ', Duration:', data.duration);
    })
    .catch(error => {
        // ROLLBACK has been executed
        console.log(error);
    });

Please note that since we are chaining the result from getNextData to the value of nextPageInfo, then if its value is undefined, it will do the next insert, but then will end the sequence (as success).

Tarango

Can you please explain me what return t.none(insert).then(() => a.nextPageInfo); actually do? Many thanks for your valuable answer – Uziel

Also, if for each inserted item I need to make another data fetch with extra info, where would be the best place to do this? – Uziel

And one last thing, I've tried to set a default nextPageInfo, because I want for instance to call the API from a certain point... I couldn't manage to do so... Any ideas? – Uziel

I've edited the question so you can see what I've tried. – Uziel

When you implement it the way I showed, then inside getNextData you will have this = the transaction protocol, with all the query methods, so you can call any query you like there. I've updated the example to mention it ;) – Tarango

I really appreciate you took your time to clarify things. As you said, in getNextData I don't understand as it makes no sense to me as I need to have this value BEFORE I do the initial db.tx call and not check it everytime getNextData is being called. So basically I need something like this: is the first time I call this?, If no record in table, then start with nextPageInfo=null and process info. If I run it again later, retrieve the last page info from a table BEFORE starting the loop, and then use that value to start calling db.tx from that nextPageInfo. Makes sense? – Uziel

Here's another thing I thought of, I may, based on nextPageInfo need to run either getNextData or maybe getPrevData functions, based on if nextPageInfo exists or not... – Uziel

According to your logic, the easiest approach is to execute the additional query inside getNextData just once, when it is called for the first time, i.e. when index = 0. And then you can reuse its result throughout the transaction. And your nextPageInfo can contain any information, it can be an object with all sorts of properties, including the initial query result. – Tarango

but there's no way to have this nextPageInfo passed to the first transaction sequence, just before db.tx? I've been struggling without success to implement that. – Uziel

It is trivial, actually. You can execute the query before or within the transaction, and use the value for the sequence. The issues you seem to be having at this point are more of using promises, and not pg-promise library, which relies on the generic promise logic. – Tarango

Well I'll just keep on trying until I figure it out. Thank you for your help and for the awesome pg-promise – Uziel

@Uziel I have updated my answer regarding the transaction object, to pass it in explicitely, for it was incorrect previously, sorry about that. – Tarango

thank you for the update. I still don't understand how to use that t in getNextData and why should I use it, but I'll read some more about it. – Uziel