Implementing Server Side Pagination and Search using PostgreSQL in Appsmith

Managing large datasets is a common challenge in web development, and pagination is a technique that helps overcome this hurdle. With Appsmith, developers have the option to implement server-side pagination or client-side pagination using the Table widget. While client-side pagination simplifies searching, it may not be efficient with large datasets. Server-side pagination, on the other hand, improves application performance and delivers faster response times. In this article, we’ll focus on server-side pagination and explore how to efficiently paginate data, and perform search operations using PostgreSQL in Appsmith.

I have created a sample app to demonstrate the implementation of the server-side pagination which you can find here. In my application, I have the following already set up.

• A query that fetches the Posts in my database called Query_getPosts
• A query that fetches the total number of posts in my database called Query_getPostsCount
• A query dedicated to searching the database based on the title of the post called Query_searchPost
• A query for inserting a single record into my database called Query_insertPost (we won’t really be needing this, I only added it for the purpose of inserting a dummy post into my database for testing)

I have also created two JsObjects in my application named:

• Js_populate: This JsObject is responsible for populating my desired data and also populating my database with dummy data.
• Js_tableChangeMonitor: This JsObjects contains methods that handle searching, filtering, and page change events.

Setting up the Datasource.

1. Go to the Data sources section in your application and connect your application to your Postgres database server
2. Create a query from the Data source connected in step 1. In the sample app, I name this query Query_getPosts which is responsible for getting the posts from my database. The actual query for fetching the posts from my database is shown below:

SELECT * FROM posts

3. Create another query that fetches the total count of records in our database. We’ll be needing this query to tell our table how many records in total so that it can know how to display the page number:

SELECT COUNT(*) FROM posts;

We’ll update this query to be more efficient later but, for now, let’s move on to setting up the table that will display our data.

Setting up the table

1. Drag your table widget to the canvas and toggle the server-side pagination option and also set the total records option in the table widget settings as shown below:

Screenshot 2023-03-29 at 09.32.27808×410 33.3 KB

2. Set the data for this table to the query you created earlier as shown below:

Screenshot 2023-03-28 at 15.45.202516×1198 298 KB

Right now, my table is displaying the 100 records returned from the database. This is not very efficient so we want to fix this by implementing server-side pagination. We’ll start by updating our query to return a certain number of records at a time (I’ll be using 10 records, you can use any number you want, have in mind that this will be the number of records displayed per page).

Updating the Query

SELECT * FROM posts LIMIT 10 OFFSET 0

The LIMIT introduced in the query above specifies how many posts should be selected while the OFFSET specifies how many posts should be skipped before starting the selection. However, we need to make the values dynamic in such a way that, when the user clicks > on the table, it fetches the next 10 records, and if they click <, it fetches the previous 10 records.

The way we can do this is by introducing variables in our query which will allow us to control the LIMIT and OFFSET values. The updated query is as follows:

SELECT * FROM posts LIMIT {{this.params.queryLimit ?? 10}} OFFSET {{this.params.queryOffset ?? 0}};

The updated query above expects us to pass an object that contains a queryLimit and a queryOffset field when we want to call the query like so: Query_getPosts.run({queryLimit: 10, queryOffset: 0}). The values in object passed to the run function will be accessible within the query as this.params.queryLimit and this.params.queryOffset. We also added a default value for the LIMIT and OFFSET in case nothing gets passed to the query, our query will still work as expected.

Now that we’ve updated our query, how can we connect this to the table?

Updating the Table to use pagination

I’m going to take a different approach with this so I’ll like you to pay close attention:

1. I’ll create a new JsObject (named Js_populate) with a method (called populateTable) that calls this query. The populateTable method will then take the response of the query and store it in the Appsmith store. The populateTable method contains the following code:

populateTable: async () => {
	const posts = await Query_getPosts.run();
	await storeValue('tableData', posts);
	return posts;
}

2. I’ll set the default value of my table to the data stored in. the Appsmith store. The default value now looks like this:
    

   Screenshot 2023-03-29 at 08.49.292757×796 189 KB

    

3. Lastly, I’ll set that method to execute on page load. The reason for this is so that my store can already be populated with the needed value to display on the table: To do this, go to the JsObject, click settings, and then toggle the run-on page load option to yes. It should look like so:
    

   Screenshot 2023-03-29 at 08.55.011807×728 37.8 KB

   
   At this point, our table should be displaying 10 records already.

    

4. One last thing we have to do now is to fetch the consequent records when the page changes on the table.

Implementing the OnPageChange event

1. Create a new JsObject (I call this Js_tableChangeMonitor) which I dedicate to monitoring events on the table. I have a method in this JsObject called onPageChange that contains the following code:

onPageChange: async () => {
	const pageNumber = Table1.pageNo;
	const queryLimit = 10;
	const posts = await Query_getPosts.run({
		queryLimit: queryLimit, 
		queryOffset: (pageNumber -1) * queryLimit
	});
	await storeValue('tableData', posts);
},

2. Go to the table widget settings and update the onPageChange event to run the method above. It’ll look like the following:

Screenshot 2023-03-29 at 09.12.14758×187 17.4 KB

Now, our table should be paginated the way we expect it to be.

Implementing Server-Side Search.

One thing you should note is that, when using server-side pagination, not all records are available on your table at the same time (because you’re simply not fetching all the data at a time). What you can do here is implement a search functionality that will manually search through our database for the data of our choice.

1. We’ll create a query that’ll search for a post in our database (called Query_searchPost) that will contain the following:

SELECT * FROM posts WHERE title LIKE {{"%" + this.params.searchText + "%"}} ORDER BY id LIMIT {{this.params.queryLimit ?? 10}};

Note that this query expected us to pass a searchText and a queryLimit to it so when we want to call the query, we should remember to do this.

2. I’ll create another method in my Js_tableChangeMonitor called onSearchTextChanged (which will monitor when a user enters something in the search section of the table and call the Query_searchPost query created in step 1. The method will contain the following:

onSearchTextChanged: async () => {
	const searchText = Table1.searchText;
	if(searchText.length == 0) {
	  // if the search text is empty, then just populate the table with default values;
	  await Js_populate.populateTable();
	}
	const searchResult = await Query_searchPost.run({ searchText });
	await storeValue('tableData', searchResult);
},

3. We’ll then update the table to run this function anytime the user enters a search text in the search field:
    

   Screenshot 2023-03-29 at 09.27.10758×184 20 KB

That’s it! We have successfully implemented server-side pagination and searching using PostgreSQL in Appsmith.