Welcome to this tutorial on pagination in Sequelize! Imagine browsing a huge library where books are organized in sections. Rather than trying to view every single book at once, the library displays a few books per page and lets you navigate to the next set. This is exactly what pagination does – it breaks down query results into smaller, more manageable chunks.
Pagination is the process of splitting the results of a database query into separate pages or chunks. Consider how Google displays search results: even though there might be thousands of results, only 10 or so appear on the first page. This not only makes the data easier to digest but also improves performance by limiting the number of rows processed and transferred at once.
In web applications, efficient pagination is essential. For example, an ed-tech platform with hundreds or thousands of student records needs to display a limited number of records per page, ensuring that the interface remains responsive and user-friendly.
In SQL, pagination is typically achieved using the LIMIT and OFFSET clauses. These keywords let you specify the maximum
number of results to return and the number of rows to skip, respectively.
SELECT * FROM exampleTable
LIMIT 10 OFFSET 30;
This SQL query skips the first 30 rows and returns the next 10 rows, which would represent page 4 if each page shows 10 results.
Sequelize makes pagination simple by using the same concepts. Instead of writing raw SQL, you pass the limit and offset
options to the finder methods such as findAll.
For example, to get the 5th page of results with 10 records per page, you would set the limit to 10 and the offset to 40:
const results = await ExampleModel.findAll({
limit: 10,
offset: 40
});
This command tells Sequelize to skip the first 40 rows and then return the next 10, which is equivalent to page 5.
Often, pagination is combined with ordering to display data in a meaningful sequence.
For instance, a student portal might display assignments in chronological order.
By using the order key along with limit and offset, you can ensure the correct subset of data is shown.
const paginatedAssignments = await Assignment.findAll({
order: [['dueDate', 'ASC']],
limit: 10,
offset: 20
});
In this example, assignments are ordered by their due date in ascending order, and only 10 assignments are returned starting after skipping 20.
In a real-world application, you would typically pass pagination parameters as query parameters in your API requests.
For example, a GET request to /artists?page=5&size=4 would request the 5th page with 4 results per page.
Your backend would then convert these parameters into appropriate limit and offset values:
const page = parseInt(req.query.page) || 1;
const size = parseInt(req.query.size) || 10;
const offset = (page - 1) * size;
const artists = await Artist.findAll({
limit: size,
offset: offset
});
res.json(artists);
This method ensures that your API returns only a manageable chunk of data, which is essential for performance and a smooth user experience.
Let’s apply what we’ve learned:
Imagine you are building an e-commerce application. Your database contains thousands of product records, but you want to display only 10 products per page. Follow these steps:
Step 1: Open your route handler for the GET request (e.g., /products).
app.get('/products', async (req, res) => {
const page = parseInt(req.query.page) || 1;
const size = parseInt(req.query.size) || 10;
const offset = (page - 1) * size;
const products = await Product.findAll({
order: [['name', 'ASC']],
limit: size,
offset: offset
});
res.json(products);
});
Step 2: Test this endpoint by visiting /products?page=3&size=10 in your browser or using a tool like Postman.
Step 3: Verify that the response contains only 10 products starting from the correct position in your dataset.
In this tutorial, you learned how to implement pagination in Sequelize using the limit and offset options.
We discussed why pagination is crucial for building scalable web applications, especially when handling large datasets.
By combining pagination with ordering, you can provide a smooth, efficient user experience on your website or application.
With this knowledge, you are now ready to implement pagination in your own projects, whether you’re building an e-commerce site, a social media platform, or any application that requires efficient data retrieval. Happy coding, and may your queries always be perfectly paginated!