Make an Api call with javascript promises in recur

I would like to use the gitter api to get all the messages from a room.

What I need to do is to send the get api request with e.g. 50 items, onComplete I need to send another request with 50 items and skip 50 items I already received. Do this request till they won't return any items. So:

send api request
json parse it
: request has items
- make a sql query with this items
- proceed the query
- send next api request (recursion?)
- ? if no more items in next api request - show done message
: request does not have items
- abort with message

I am trying Promises for that, but I got a bit confused with them and don't know, if I am doing everything right. The main problem is the next Api call and callback if all calls are done. This is my code:

class Bot {
  //...

  _mysqlAddAllMessages(limit, skip) {
    promise('https://api.gitter.im/v1/rooms/' + this.room + '/chatMessages' +
        '?access_token=' + config.token + '&limit=' + limit + '&skip=' + skip)
        .then(function (response) {
          return new Promise(function (resolve, reject) {
            response = JSON.parse(response);

            if (response.length) {
              console.log(`Starting - limit:${limit}, skip:${skip}`);

              resolve(response);
            }
          })
        }).then(response => {
          let messages = response,
              query = 'INSERT INTO messages_new (user_id, username, message, sent_at) VALUES ';

          for (let message of messages) {
            let userId = message.fromUser.id,
                username = message.fromUser.username,
                text = message.text.replace(/["\\]/g, '|'),
                date = message.sent;

            query += '("' + userId + '", "' + username + '", "' + text + '", "' + date + '"), ';
          }

          query = query.substr(0, query.length - 2);

          return new Promise((resolve, reject) => {
            this.mysql.getConnection((error, connection) => {
              connection.query(query, (err) => {
                if (err) {
                  reject(`Mysql Error: ${err}`);
                } else {
                  connection.release();

                  resolve(console.log(`Added ${messages.length} items.`));
                }
              });
            });
          });
        })
        .then(()=> {
          // what to do here
          return this._mysqlAddAllMessagesf(limit, skip += limit)
        })
        .catch(function (er) {
          console.log(er);
        })
        .finally(function () {
          console.log('Message fetching completed.');
        });
  }
}

let bot = new Bot();
bot._mysqlAddAllMessages(100, 0);

Maybe you can check and help me? Or provide similar code for such things?

update

Here is what I refactored the code to: jsfiddle

Your code has me pretty confused. The simplest way to use promises with async operations is to "promisify" your existing async operations and then write all the logic after that using promises. To "promisify" something means to generate or write a wrapper function that returns a promise rather than uses only a callback.

First, lets look at the overall logic. Per your question, you said you have an API that you want to call to fetch 50 items at a time until you've gotten them all. That can be done with a recursive-like structure. Create an internal function that does the retrieving and returns a promise and each time it completes call it again. Assuming you have two core functions involved here, one called getItems() that gets items from your API and returns a promise and one called storeItems() that stores those items in your database.

function getAllItems(room, chunkSize, token) {
    var cntr = 0;
    function getMore() {
        return getItems(room, cntr, chunkSize, token).then(function(results) {
            cntr += results.length;
            if (results.length === chunkSize) {
                return storeItems(results).then(getMore);
            } else {
                return storeItems(results);
            }
        });
    }
    return getMore();        
}

This code makes use of chaining promises which is a slightly advanced, but extremely useful feature of promises. When you return a promise from a .then() handler, it is chained onto the previous promise, automatically linking them all together into a series of operations. The final return result or error is then returned back through the original promise to the original caller. Similarly, any error that might happen in this chain is propagated all the way back to the original caller. This is extremely useful in complicated functions with multiple async operations where you cannot just simply return or throw if using regular callbacks.

This would then be called like this:

getAllItems(this.room, 50, config.token).then(function() {
    // finished successfully here
}, function(err) {
    // had an error here
});

Now, I'll work on some examples for created promisified versions of your lower levels calls to implement getItems() and storeItems(). Back in a moment with those.

I don't quite fully understand all the details in your async operations so this will not be a fully working example, but should illustrate the overall concept and you can then ask any necessary clarifying questions about the implementation.

When promisifying a function, the main thing you want to do is to encapsulate the dirty work of handling the callback and error conditions into one core function that returns a promise. That then allows you to use this function in nice clean flowing promise-based code and allows you to use the really great error handling capabilities of promises in your control flow.

For requesting the items, it looks like you construct a URL that takes a bunch of arguments in the URL and you get the results back in JSON. I'm assuming this is a node.js environment. So, here's how you could do the getItems() implementation using the node.js request() module. This returns a promise whose resolved value will be the already parsed Javascript object representing the results of the api call.

function getItems(room, start, qty, token) {
    return new Promise(function(resolve, reject) {
        var url = 'https://api.gitter.im/v1/rooms/' + room + '/chatMessages' + '?access_token=' + token + '&limit=' + qty + '&skip=' + start;
        request({url: url, json: true}, function(err, msg, result) {
            if (err) return reject(err);
            resolve(result);
        });
    });
}

For storing the items, we want to accomplish the same thing. We want to create a function that takes the data to store as arguments and returns a promise and it does all the dirty work inside the function. So logically, you want to have this structure:

function storeItems(data) {
    return new Promise(function(resolve, reject) {
        // do the actual database operations here
        // call resolve() or reject(err) when done
    });
}

Sorry, but I don't quite understand what you're doing with your mySql database enough to fully fill out this function. Hopefully this structure gives you enough of an idea how to finish it off or ask some questions if you get stuck.

Note: if you use a Promise library like Bluebird to add additional promise functional functionality, then promisifying an existing operation is something that is built into Bluebird so getItems() just becomes this:

var Promise = require('bluebird');
var request = Promise.promisifyAll(require('request'));

function getItems(room, start, qty, token) {
    var url = 'https://api.gitter.im/v1/rooms/' + room + '/chatMessages' + '?access_token=' + token + '&limit=' + qty + '&skip=' + start;
    return request.getAsync({url: url, json: true});
}