How to paginate records in MySQL using Sequelize and Nodejs
Often at times, I find my self struggling with Sequelize to find a direct answer for my query. Recently, I have been working on a fullstack application in which there was a basic requirement of paginating results from backend (REST API) to the frontend. I struggled for two reasons. Firstly, coming from NoSQL background it’s hard to grasp SQL DBs. Second reason being is Sequelize documentation does not provide a clear and direct solution to this very basic abstraction. Lot of people assume things in the world of SQL databases.
Thus, in this post we will be talking about a basic paginating module using Sequelize, MySQL and Node.js. I am using you have some tables and records inside your MySQL database. To setup a new app and making database connection, read my post on Getting started with Sequelize.
Defining a Model
I am directly jumping on
user model definition:
I am using that we a table that contains hundred of user records that we want to display on an web application, say in the admin panel, and we want to show just 50 records at once.
api/user.js I am defining an endpoint
/:page that will fetch number of results we need from the database.
findAndCountAll is the model for searching multiple records in the database and it returns both the data required and the count of elements in that table. The above query will get 50 user records at once until the next page is called to fetch the next 50 records.
offset are required in queries related to pagination in which
limit fetches the number of rows based on the query whereas
offset is used to skip the number of rows in the database table.
Thank you for reading. If you find this post useful, please hit the 💚 button so this story can reach out to more readers. If you’d like to talk about it more, ping me on Twitter.