Implementing Server-side Pagination using PostgreSQL in Appsmith (Part 2 - Pagination)

[Olawale]

Welcome to part two of this article which aims to explain how to properly set up server-side pagination using PostgreSQL in Appsmith.

In the first part of this series, we talked about how to set up our table and connect it to a query to display 100 posts from our Postgres database. In this second part, we’ll be implementing server-side pagination.

First steps!

To enable server-side pagination, we must disable the client-side pagination feature, indicating to Appsmith that we wish to manage our own data pagination using customized logic. This can be accomplished by navigating to the Table’s settings, locating the “Pagination” section, and enabling Server Side Pagination (refer to the image below). Upon completion of this step, Appsmith will no longer paginate our data for us, and we will be responsible for implementing our own pagination logic.

Now that we have server-side pagination turned on, we have to tell Appsmith how to paginate our data.

Paginating our Data!

First of all, we have to tell Appsmith how many records in total are in our data. The reason for this is that, unlike client-side pagination, we do not fetch the entire data at once, so, Appsmith does not know how many records we have in total, it just knows how many records we’re providing at the time. Also, Appsmith needs this information to be able to divide your table into the appropriate number of pages.

1. I’ll create a query that will get the total number of records from my database, I’ll name this query Query_getPostsCount which will contain the following SQL query:
   SELECT COUNT(*) FROM posts
2. Go to your Table settings and set the total record to the data returned from the query above like so:
   
   
3. Now, we want to update our query to fetch only 10 records per page. The updated query will look so:
   SELECT * FROM posts LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}};

What you’ll realize here is that we’ve introduced both a LIMIT and an OFFSET operator to our query. LIMIT tells our query how many records should be fetched and OFFSET tells our query how many records to skip before starting the count of records to fetch. Let me paint you a mental picture to better clarify this: Imagine you have 100 records in your database with id 1 - 100. Now, if you want to fetch the first ten records, you’ll fetch the data with id 1 - 10. When you want to fetch the next 10 records, you have to, first of all, skip data with id 1-10 (i.e OFFSET the first ten records), then fetch data with id 11-20. Does that make sense?

4. Now, our data will only record 10 records on each run and we’ve also configured the query so that it can return the 10 records based on the state of our table. When we click on the “Next Page” or “Previous Page” button of our table, we need to re-run this query so that it can use the new state of the Table. To do this, we set the onPageChange and onPageSizeChange properties to run the query again. These should look like the following:
   
   

Now, our data is well-paginated and ready to be used, at least, for now.

What’s next?

Despite the fact that we’ve successfully paginated the data in our table, we still have some things to worry about. One of those things is searching. When using client-side pagination, our entire data is available to our Table all at once and so our table can search through whenever we input something in the search bar on the table. However, when using server-side pagination, only a part of our data is available in our Table and so our search results won’t be very accurate. Because of this, we have to come up with a way to search through our database rather than the record in our table whenever our users enter something into the search bar of the Table widget.

In the next part of this series, this is what we’ll be doing.
See you in the next part!!!

You can find the completed application here - Appsmith