How to make HttpGet service pagination with a limit of a million or more?

Question

Offset can take only 2000 records, but I need more. How can I send to the callout side 1 mln or more records with pagination and step of 100-200 records per 1 page? I mean when callout request for 100 records, then for 100 more etc., till we have records on the HttpGet service side.

@HttpGet
global static List<Item__c> getItem(){
    RestRequest req = RestContext.request;

    List<Item__c> itemList;
    RestResponse res = new RestResponse();
    RestContext.response = res;

    try{
        itemList = [SELECT Id, Full_Name__c FROM Item__c];
        res.responseBody = Blob.valueOf(JSON.serialize(itemList));
    } catch(Exception e){
        System.debug('Error:  ' + e.getMessage());
    }
    return itemList;
}

Callout:

public class Callout {    
public static String webTokenBody(){
        String requestBody = 'my_web_token_string';
        return request;
}

public class webTok{
        public String webToken{get;set;}    
}

public static HttpRequest createRequestForToken(String service){
        String requestBody = webTokenBody();
        HttpRequest ourRequest = new HttpRequest();
        ourRequest.setBody(requestBody);
        ourRequest.setMethod(service);
        ourRequest.setEndpoint('https://my.salesforce.com/services/oauth2/token');
        return ourRequest;
}

public static HttpRequest createRequestForService(String token, String method){
        HttpRequest finalRequest = new HttpRequest();
        finalRequest.setHeader('Authorization','Bearer ' + token);
        finalRequest.setHeader('Content-Type','application/json');
        finalRequest.setHeader('accept','application/json');
        finalRequest.setMethod(method);
        finalRequest.setEndpoint('https://my.salesforce.com/services/apexrest/endpoint/');
        return finalRequest;
}

public static HttpResponse getCalloutResponseContents() {   
        Http ourHttp = new Http();
        HttpRequest requestForToken = createRequestForToken('GET');
        HttpResponse responseToken = ourHttp.send(requestForToken);
        webTok objAuthenticationInfo = (webTok)JSON.deserialize(responseToken.getbody(), webTok.class);

        if(objAuthenticationInfo.webToken != null){
            HttpRequest requestForService = createRequestForService(objAuthenticationInfo.webToken, 'GET');
            HttpResponse responseService = ourHttp.send(requestForService);
            itemsList = (List<Item2__c>)JSON.deserialize(responseService.getBody(), List<Item2__c>.class);                
                for(Item2__c i : itemsList) {
                    i.Id =null;
                }
                return responseService;
            }
    }
    }

Can I do that with Database.QueryLocator on the HttpGet service side? If yes - how?

Answer 1

Simply put, you can't. While is trivial to build a service that returns, say, up to 50,000 rows, there's simply no mechanism for preserving a QueryLocator across Apex Code transactions, and with one exception (Visualforce), you can't query more than 50,000 rows per transaction (and even that limit is still only exactly 1,000,000, no more). The best you'd be able to do is create a service where the client-side programs passes in the last known ID, and you'd just continue from there:

@HttpGet
global static List<Item__c> getItem(){
  Id reqId = RestContext.request.params.get('recordId');
  if(reqId != null) {
    return [SELECT Full_Name__c FROM Item__c WHERE Id > :reqId ORDER BY Id LIMIT 200];
  } else {
    return [SELECT Full_Name__c FRIM Item__c ORDER BY Id LIMIT 200];
  }
}

Your client would then do something like this:

/services/apexrest/Items

Which would give a list of 200 items; your code would then call the next page:

/services/apexrest/Items?recordId=a010000000abcde

---

For replicating millions of records, consider just using a normal query. Unless you're specifically trying to circumvent security/sharing settings, your client can just request the data directly:

/services/data/v44.0/query?q=select+full_name__c+from+item__c

It will include automatic pagination, requires no code, and is a standard function.