Not sure how to make your API more efficient? Check out our guide to pagination!

What is pagination, and why do we need it?

Imagine you’ve been given a book that contains all the information on a single page. Having all the information on a single page would make reading difficult and the information difficult to process. Books are therefore made easier to read by using pagination, which results in the information being separated over multiple pages.

Likewise, when an API delivers content, it can also paginate the responses to make it easier for its clients to handle the data and, therefore, the stability of the API server to handle the client requests.

If the API server has to respond with all the data in one single request, then there may be a couple of issues in terms of response time, and sometimes, the requests may fail depending on the data sizes. To avoid these problems, Rest API has pagination as a design best practice while developing APIs.

So, if you are not sure how to make your API more efficient? Check out our guide to pagination!

How to paginate an API request

We will use the two main parameters below to enable pagination for an API:

Offset – identifies the starting element to be delivered in response.
Limit – the total number of records to be delivered in a one-page response.

Based on these two parameters, the API server will calculate the total number of pages and the current page in each response. It will also provide the first, self, previous, next, and last page links in the same response.

It is the client’s responsibility to ensure it has received all of the response pages.

A worked example of pagination

Let’s look at a simple API and apply the above mentioned parameters as query parameters.

Below is the RAML definition where the API provides functionality to query the employees with pagination.

#%RAML 1.0
title: Employees API

version: v1

description: Employees API is the system API which can return the employee details to its clients.

baseUri: http://host:port/s-employees-{version}/api/

mediaType:
– application/json

/employees:
get:
description: returns the employees ]
displayName: GET /employees
queryParameters:
offset:
type: integer
required: false
default: 0
minimum: 0
description: The `offset` parameter specifies the first entry to return from a collection.
limit:
type: integer
required: false
minimum: 1
maximum: 1000
description: The `limit` parameter restricts the number of entries returned.
responses:
200:
body:
application/json:
example: |
{
“employees”:[
{
“id”:123100,
“first_name”:”Chris”,
“last_name”:”Taylor”,
“email”:”chris.taylor@devoteam.com”
},
{
“id”:123101,
“first_name”:”Raveena”,
“last_name”:”Tandon”,
“email”:”craveena.tandon@devoteam.com”
}
],
“currentPage”:1,
“totalPages”:2,
“links”: {
“self”: “http://localhost:8081/employees?offset=5&limit=5”,
“first”: “http://localhost:8081/employees?offset=0&limit=5”,
“prev”: “http://localhost:8081/employees?offset=0&limit=5”,
“next”: “http://localhost:8081/employees?offset=10&limit=5”,
“last”: “http://localhost:8081/employees?offset=10&limit=5”
}
}

How to paginate an API

Let’s implement the API in Anypoint Studio.

First, create a new API Specification project (Employees API). Use the RAML provided above and publish this API to exchange in your Anypoint account exchange by right-clicking on the project and “Manage API specification/Publish to Exchange”.

(Note: You have to configure your account in Studio at Studio/Preferences/Anypoint Studio/Anypoint Platform)

Next, create a new Mule Project (sys-employees) and in this next menu, under “import published API section”, add your API specification from the exchange and click finish. The studio will generate the API flows with mocked responses given in the RAML for GET /employees resources.

Now, Before implementing the API for GET /employees resource endpoint, let’s create a scheduled process to create the required database tables and insert the data which we will use in the implementation. Here we are using the h2 in-memory database. To use the h2 database in the API implementation, we would need the below dependencies in the pom.xml file.

The Mule maven plugin should need the following shared library and the dependency.

<plugin>
<groupId>org.mule.tools.maven</groupId>
<artifactId>mule-maven-plugin</artifactId>
<version>${mule.maven.plugin.version}</version>
<extensions>true</extensions>
<configuration>
<sharedLibraries>
<sharedLibrary>
<groupId>org.mule.connectors</groupId>
<artifactId>mule-db-connector</artifactId>
</sharedLibrary>
<sharedLibrary>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</sharedLibrary>
</sharedLibraries>
</configuration>
</plugin>

How to paginate an API

<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>
<dependency>
<groupId>org.mule.connectors</groupId>
<artifactId>mule-db-connector</artifactId>
<version>1.13.6</version>
<classifier>mule-plugin</classifier>
</dependency>

How to paginate an API

Next, let’s create a scheduler process with a flow “h2-db-scriptFlow” in a separate Mule configuration file “h2-db-script.xml”. Configure the source with a scheduler with a fixed frequency of 1000 DAYS and with a start delay of 1000 DAYS to make sure it will not run after our manual run for quite some time. Add DDL and DML scripts to create a table and insert the data.

<?xml version=”1.0″ encoding=”UTF-8″?>

<mule xmlns:ee=”http://www.mulesoft.org/schema/mule/ee/core” xmlns:db=”http://www.mulesoft.org/schema/mule/db”
xmlns=”http://www.mulesoft.org/schema/mule/core”
xmlns:doc=”http://www.mulesoft.org/schema/mule/documentation” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=”http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd”>

<db:config name=”Database_Config” doc:name=”Database Config” doc:id=”436e060e-30fe-4030-bfc8-54e47a7403d2″ >
<db:generic-connection url=”jdbc:h2:file:/Users/pavannagineni/Work/workspaces/h2Test” driverClassName=”org.h2.Driver” />
</db:config>

<flow name=”h2-db-scriptFlow” doc:id=”50c4bfb8-0959-48a9-aa06-be7af31a1793″ >
<scheduler doc:name=”Scheduler” doc:id=”376dd135-a119-4ca1-8595-249d738ca7f6″ >
<scheduling-strategy >
<fixed-frequency frequency=”10000″ timeUnit=”DAYS” startDelay=”10000″/>
</scheduling-strategy>
</scheduler>
<logger level=”INFO” doc:name=”Start Logger” doc:id=”cafbd239-3e50-415a-a614-bc2c9492ceff” message=’#[“========== Starting the H2 Script run as part of the database readiness activity ============”]’/>
<db:execute-ddl doc:name=”Execute DDL” doc:id=”fb434123-d1f2-4e45-b0c4-14db66188d8a” config-ref=”Database_Config”>
<db:sql ><![CDATA[CREATE TABLE IF NOT EXISTS employees (
emp_id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101100) ,
first_name VARCHAR(50) ,
last_name VARCHAR(50) ,
email VARCHAR(50)
)]]></db:sql>
</db:execute-ddl>
<db:insert doc:name=”Insert” doc:id=”d6c93453-764d-4d61-a882-888cb08f01bc” config-ref=”Database_Config”>
<db:sql ><![CDATA[INSERT INTO employees(first_name, last_name, email) values(‘Eva’, ‘Max’, ‘eva.max@devoteam.com’);
INSERT INTO employees(first_name, last_name, email) values(‘Eon’, ‘Morgan’, ‘eon.morgan@devoteam.com’);
INSERT INTO employees(first_name, last_name, email) values(‘AB’, ‘Devilliers’, ‘ab.d@devoteam.com’);
INSERT INTO employees(first_name, last_name, email) values(‘John’, ‘Thompson’, ‘john.thompson@devoteam.com’);
COMMIT;]]></db:sql>
</db:insert>
<db:select doc:name=”Select” doc:id=”486ac7b3-9416-4869-90fe-181b938c965d” config-ref=”Database_Config”>
<db:sql ><![CDATA[SELECT * from employees]]></db:sql>
</db:select>
<ee:transform doc:name=”Transform Message” doc:id=”afb52184-8278-4dc6-a6ec-13c5cfaacac1″ >
<ee:message >
<ee:set-payload ><![CDATA[%dw 2.4
output application/json
—
payload]]></ee:set-payload>
</ee:message>
</ee:transform>
<logger level=”INFO” doc:name=”Logger” doc:id=”923fd1c5-88bd-4290-bc85-b8bb5e77e78b” message=”#[payload]”/>
<logger level=”INFO” doc:name=”Finish Logger” doc:id=”b794aabc-c07c-4fb9-b586-7e9ca7ff08af” message=’#[“========== Completed the H2 Script run as part of the database readiness activity ============”]’ />
</flow>
</mule>

How to paginate an API

Note: Above, in the h2 database configuration, url attribute is referred as “jdbc:h2:file:{localLocationToSaveH2DBFile}/h2Test”, where in above scenario localLocationToSaveH2DBFile is /Users/pavannagineni/Work/workspaces likewise you would need to update this to one of your local folders.

Once we run this, the “employees” table will be created, and the 4 entries will be inserted. To note, if you run this multiple times, the same data will be inserted repeatedly since there are no constraints configured to the database script.

Moving on to implementing the GET /employees resource endpoint, let’s focus now on the “get:\employees:employees-api-config” flow. Set the two variables to offset and limit, taking them from the query parameters. The offset would be 0 if the user doesn’t send this information in the request. Otherwise, it will equal the value in the request. Similarly, the Limit would be 1000 if the user doesn’t specify this in the request. Otherwise, it will equal the value in the request.

Limit:

%dw 2.4
output application/java
—
if (isEmpty(attributes.queryParams.limit)) 1000
else attributes.queryParams.limit as Number

How to paginate an API

Offset:

%dw 2.4
output application/java
—
if (isEmpty(attributes.queryParams.offset)) 0
else attributes.queryParams.offset as Number

How to paginate an API

The next step is to query the total number of records and store the result in the totalRecords variable.

SELECT COUNT(*) from employees

How to paginate an API

After this, we will select the actual data from the database with the given offset and limit.

SELECT * from employees LIMIT (:limit) OFFSET (:offset)

How to paginate an API

It’s time to calculate the currentPage and totalPages variables to be added in the response along with the records from the database query.

totalPages:

//come from first db count(*) query
var totalRecords = vars.totalRecords[0].’COUNT(*)’ as Number

// Arrow Function to calculate totalPages
var totalPages = (limit) ->
ceil(totalRecords / limit)

output application/json
—
totalPages(vars.limit)

How to paginate an API

currentPage:

%dw 2.4

// Arrow Function to calculate currentPage
var currentPage = (offSet,limit) ->
floor((offSet/limit) + 1)

output application/json
—
currentPage(vars.offset,vars.limit)

How to paginate an API

Prepare the links for self, first, previous, next, last page links with appropriate offset and limit query parameters to be added to the response for ease of access.

%dw 2.4

//come from first db count(*) query
var totalRecords = vars.totalRecords[0].’COUNT(*)’ as Number

var offset = vars.offset

var limit = vars.limit

//last offset
var lastOffset = totalRecords – (totalRecords mod limit)

fun pageParameters(inOffset, inLimit) =
if(!isEmpty(inOffset) and (inOffset == 0 or inOffset > 0) and (inOffset <= lastOffset) )
(“offset=” ++ (inOffset as String) ++ “&limit=” ++ (inLimit as String))
//handle next page url if next offset is > lastOffset
else if(!isEmpty(inOffset) and (inOffset == 0 or inOffset > 0) and (inOffset > lastOffset) )
(“offset=” ++ (lastOffset as String) ++ “&limit=” ++ (inLimit as String))
//handle previous page url for the first request incase the offset goes negative
else if(!isEmpty(inOffset) and (inOffset < 0))
(“offset=” ++ (0 as String) ++ “&limit=” ++ (inLimit as String))
else “”

output application/java
—
{
self: “http://localhost:8081/employees?” ++ pageParameters(offset, limit),
first: “http://localhost:8081/employees?” ++ pageParameters(0, limit),
prev: “http://localhost:8081/employees?” ++ pageParameters((offset – limit), limit),
next: “http://localhost:8081/employees?” ++ pageParameters((offset + limit), limit),
last: “http://localhost:8081/employees?” ++ pageParameters(lastOffset, limit)
}

How to paginate an API

Prepare the response payload:

%dw 2.4
output application/json
—
{
employees: payload map (val, idx) ->
val mapObject ((value, key, index) ->
(lower(key)): value
),
currentPage: vars.currentPage,
totalPages: vars.totalPages,
links: vars.links
}

How to paginate an API

So, overall implementation of the get:\employees:employees-api-config flow is as follows;

<flow name=”get:\employees:employees-api-config”>
<logger level=”INFO” doc:name=”Logger” doc:id=”5114cbc9-853e-44de-8a0c-8f0f19942454″ message=’#[“API GET /employees request process started”]’/>
<ee:transform doc:name=”limit, offset” doc:id=”d8aecf20-8ce4-4edc-bd7e-56d5d9ca504c” >
<ee:message >
</ee:message>
<ee:variables >
<ee:set-variable variableName=”offset” ><![CDATA[%dw 2.0
output application/java
—
if (isEmpty(attributes.queryParams.offset)) 0
else attributes.queryParams.offset as Number]]></ee:set-variable>
<ee:set-variable variableName=”limit” ><![CDATA[%dw 2.0
output application/java
—
if (isEmpty(attributes.queryParams.limit)) 1000
else attributes.queryParams.limit as Number]]></ee:set-variable>
</ee:variables>
</ee:transform>
<db:select doc:name=”DB Select count” doc:id=”14892a95-2641-4e63-b69b-30dab7d71c2b” config-ref=”Database_Config” target=”totalRecords”>
<db:sql><![CDATA[SELECT COUNT(*) from employees]]></db:sql>
</db:select>
<db:select doc:name=”DB Select data” doc:id=”a53b1c80-47e9-4405-977d-eb7575dd5b0e” config-ref=”Database_Config” >
<db:sql ><![CDATA[SELECT * from employees LIMIT (:limit) OFFSET (:offset)]]></db:sql>
<db:input-parameters ><![CDATA[#[output application/java
—
{
offset: vars.offset,
limit: vars.limit
}]]]></db:input-parameters>
</db:select>
<ee:transform doc:name=”currentPage, totalPages” doc:id=”a906dc19-2644-43a2-a196-a02f2aa054ea” >
<ee:message >
</ee:message>
<ee:variables >
<ee:set-variable variableName=”currentPage” ><![CDATA[%dw 2.4

// Arrow Function to calculate currentPage
var currentPage = (offSet,limit) ->
floor((offSet/limit) + 1)

output application/json
—
currentPage(vars.offset,vars.limit)]]></ee:set-variable>
<ee:set-variable variableName=”totalPages” ><![CDATA[%dw 2.4

//come from first db count(*) query
var totalRecords = vars.totalRecords[0].’COUNT(*)’ as Number

// Arrow Function to calculate totalPages
var totalPages = (limit) ->
ceil(totalRecords / limit)

output application/json
—
totalPages(vars.limit)]]></ee:set-variable>
</ee:variables>
</ee:transform>
<ee:transform doc:name=”links” doc:id=”af9c70dd-4ea8-4341-9e0f-7c69b1456e2a” >
<ee:message >
</ee:message>
<ee:variables >
<ee:set-variable variableName=”links” ><![CDATA[%dw 2.4

//come from first db count(*) query
var totalRecords = vars.totalRecords[0].’COUNT(*)’ as Number

var offset = vars.offset

var limit = vars.limit

//last offset
var lastOffset = totalRecords – (totalRecords mod limit)

fun pageParameters(inOffset, inLimit) =
if(!isEmpty(inOffset) and (inOffset == 0 or inOffset > 0) and (inOffset <= lastOffset) )
(“offset=” ++ (inOffset as String) ++ “&limit=” ++ (inLimit as String))
//handle next page url if next offset is > lastOffset
else if(!isEmpty(inOffset) and (inOffset == 0 or inOffset > 0) and (inOffset > lastOffset) )
(“offset=” ++ (lastOffset as String) ++ “&limit=” ++ (inLimit as String))
//handle previous page url for the first request incase the offset goes negative
else if(!isEmpty(inOffset) and (inOffset < 0))
(“offset=” ++ (0 as String) ++ “&limit=” ++ (inLimit as String))
else “”

output application/java
—
{
self: “http://localhost:8081/employees?” ++ pageParameters(offset, limit),
first: “http://localhost:8081/employees?” ++ pageParameters(0, limit),
prev: “http://localhost:8081/employees?” ++ pageParameters((offset – limit), limit),
next: “http://localhost:8081/employees?” ++ pageParameters((offset + limit), limit),
last: “http://localhost:8081/employees?” ++ pageParameters(lastOffset, limit)
}]]></ee:set-variable>
</ee:variables>
</ee:transform>
<ee:transform doc:name=”Transform Message” doc:id=”cf9ca3fb-70ca-4072-af3a-b6930490a550″ >
<ee:message >
<ee:set-payload ><![CDATA[%dw 2.4
output application/json
—
{
employees: payload map (val, idx) ->
val mapObject ((value, key, index) ->
(lower(key)): value
),
currentPage: vars.currentPage,
totalPages: vars.totalPages,
links: vars.links
}]]></ee:set-payload>
</ee:message>
</ee:transform>
<logger level=”INFO” doc:name=”Logger” doc:id=”e596507e-fb67-4698-ab04-4e090b43b2b1″ message=’#[“API GET /employees request process finished”]’/>
</flow>

How to paginate an API

Let’s, deploy the API and test GET /employees endpoint by limiting 5 records each time (I have 12 records in total in my h2 database):

1st request: Endpoint: http://localhost:8081/api/employees?offset=0&limit=5

{
“employees”: [
{
“last_name”: “Max”,
“first_name”: “Eva”,
“email”: “eva.max@devoteam.com”,
“emp_id”: 101100
},
{
“last_name”: “Morgan”,
“first_name”: “Eon”,
“email”: “eon.morgan@devoteam.com”,
“emp_id”: 101101
},
{
“last_name”: “Devilliers”,
“first_name”: “AB”,
“email”: “ab.d@devoteam.com”,
“emp_id”: 101102
},
{
“last_name”: “Thompson”,
“first_name”: “John”,
“email”: “john.thompson@devoteam.com”,
“emp_id”: 101103
},
{
“last_name”: “Max”,
“first_name”: “Eva”,
“email”: “eva.max@devoteam.com”,
“emp_id”: 101104
}
],
“currentPage”: 1,
“totalPages”: 3,
“links”: {
“self”: “http://localhost:8081/employees?offset=0&limit=5”,
“first”: “http://localhost:8081/employees?offset=0&limit=5”,
“prev”: “http://localhost:8081/employees?offset=0&limit=5”,
“next”: “http://localhost:8081/employees?offset=5&limit=5”,
“last”: “http://localhost:8081/employees?offset=10&limit=5”
}
}

How to paginate an API

2nd request: Endpoint: http://localhost:8081/api/employees?offset=5&limit=5

{
“employees”: [
{
“last_name”: “Morgan”,
“first_name”: “Eon”,
“email”: “eon.morgan@devoteam.com”,
“emp_id”: 101105
},
{
“last_name”: “Devilliers”,
“first_name”: “AB”,
“email”: “ab.d@devoteam.com”,
“emp_id”: 101106
},
{
“last_name”: “Thompson”,
“first_name”: “John”,
“email”: “john.thompson@devoteam.com”,
“emp_id”: 101107
},
{
“last_name”: “Max”,
“first_name”: “Eva”,
“email”: “eva.max@devoteam.com”,
“emp_id”: 101108
},
{
“last_name”: “Morgan”,
“first_name”: “Eon”,
“email”: “eon.morgan@devoteam.com”,
“emp_id”: 101109
}
],
“currentPage”: 2,
“totalPages”: 3,
“links”: {
“self”: “http://localhost:8081/employees?offset=5&limit=5”,
“first”: “http://localhost:8081/employees?offset=0&limit=5”,
“prev”: “http://localhost:8081/employees?offset=0&limit=5”,
“next”: “http://localhost:8081/employees?offset=10&limit=5”,
“last”: “http://localhost:8081/employees?offset=10&limit=5”
}
}

How to paginate an API

3rd request: Endpoint: http://localhost:8081/api/employees?offset=10&limit=5

{
“employees”: [
{
“last_name”: “Devilliers”,
“first_name”: “AB”,
“email”: “ab.d@devoteam.com”,
“emp_id”: 101110
},
{
“last_name”: “Thompson”,
“first_name”: “John”,
“email”: “john.thompson@devoteam.com”,
“emp_id”: 101111
}
],
“currentPage”: 3,
“totalPages”: 3,
“links”: {
“self”: “http://localhost:8081/employees?offset=10&limit=5”,
“first”: “http://localhost:8081/employees?offset=0&limit=5”,
“prev”: “http://localhost:8081/employees?offset=5&limit=5”,
“next”: “http://localhost:8081/employees?offset=10&limit=5”,
“last”: “http://localhost:8081/employees?offset=10&limit=5”
}
}

How to paginate an API

The Code of the API can be referred from the below location;

https://github.com/naginenipavan/api-pagination

Conclusion

Within this blog, we have seen how to paginate an API in an effective and straightforward way. Next, we must consider how to consume a paginated API without receiving a StackOverFlow error. I’ll go through this concept in another blog on how to consume a paginated API effectively.

Please contact us if you want to learn more about API pagination or need help implementing pagination.