Skip to main content

Download Table data

This page shows how to download the entire Table or Query data in manageable chunks to prevent performance issues on your application.

Download data in chunks​

Follow these steps to download data in chunks:

  1. Create a new query to fetch paginated data

Example 1: Using SQL

To fetch user data using dynamic limit and offset values, create a query like this, passing the limit and offset values through this.params. See Parameterised Queries.

SELECT * FROM public."users" 
LIMIT {{this.params.limit}} 
OFFSET {{this.params.offset}}

Example 2: Using API

  • Create backend code in your API to handle dynamic limits and offsets, like:
// Backend code to fetch data with limit and offset
const fetchData = async (limit = 50, offset = 0) => {
  const response = await fetch(`/api/endpoint?limit=${limit}&offset=${offset}`);
  const data = await response.json();
  return data;
};
  • In Appsmith, configure the API query to fetch data using limit and offset parameters:
GET https://mock-api.appsmith.com/users?limit={{this.params.limit}}&offset={{this.params.offset}}
  1. Create a new JSObject and add a function to fetch the data in chunks to handle large datasets efficiently.

Example:

  • Create a function that retrieves user data in batches of 100 rows from a database, continuously appending each batch to a usersData array until the entire dataset is retrieved.
  • Use the download() function to download the data to your local machine.
  • Upon successfully fetching and downloading all data, it resets the usersData array to clear the data.
export default {
    usersData: [],

    // Method to reset variables
    resetData() {
        this.usersData = [];
    },

    // Method to fetch and download user data in chunks
    async fetchAndDownloadUsers() {
        const chunkSize = 100; // Number of rows per chunk
        let offset = 0; // Offset for pagination

        try {
            while (true) {
                const result = await getUsers.run({ limit: chunkSize, offset });

                // Check if the query returned any data
                if (result && result.length > 0) {
                    // Append the fetched data to usersData
                    this.usersData = [...this.usersData, ...result];
                    offset += chunkSize;
                
                } else {
                    // No more data available, exit the loop
                    break;
                }
            }
            // Download or process the data
            download(this.usersData, 'userdata', 'text/csv');
            console.log('Data downloaded:', this.usersData);

        } catch (error) {
            console.error('An error occurred:', error);
        } finally {
            // Clear the data and reset variables
            this.resetData();
            console.log('Data has been reset:', this.usersData);
    }}}

The code may vary based on your datasource, so update the query and parameters accordingly to fit your specific data structure and requirements.

  1. Execute the defined JS function either directly from the JS editor or by triggering the function through widget events.

Download data as file​

To directly download a file from the datasource instead of fetching data in chunks, follow these steps:

  1. Create a backend API that uses built-in functions provided by your datasource to convert data into a file format. Ensure that the output is either the file content or a URL that provides access to the file.

For example, MySQL, PostgreSQL, GraphQL, and AWS DynamoDB offer functionalities to export data directly to files.

  1. In Appsmith, create a new query to connect with the backend API and access the file.

Example:

http://api.example.com/export-data-to-file
  1. Create a new JSObject and add a function to fetch and download the file:

Example: If the API provides a file URL, you can download the data by using the following code:

// Assuming userAPI.data contains the file URL
const fileUrl = userAPI.data.fileUrl; 

// Fetch the file content
const response = await fetch(fileUrl);
const data = await response.blob();
const fileName = 'data.csv';
const fileType = 'text/csv';

// Trigger the download
await download(data, fileName, fileType);

The code fetches the file content from this URL, and then triggers download() function to download the file.

See also​