Pass Runtime Parameters to Queries

In Appsmith, you have the ability to pass static parameters to queries using mustache bindings {{}}. But if you need to execute a query multiple times with dynamic parameters, such as iterating through an array of ids and running a query to delete each record, then you will need to pass them in the run() function of the query.

Using the run() Function with Parameters

To pass different parameters every time a query is executed, you can leverage the run() function's params argument. Within the query, you can access the passed parameters by using the Mustache notation {{this.params.key}}.

Example Scenario: Bulk User Deletion

Imagine having to delete a set of user records identified by their unique IDs. We will write a query to delete a user based on an ID and create a JavaScript function that iterates through an array of IDs to perform the deletion for each one.

Configuring the Bulk Delete Operation

1. Begin by writing a query named DeleteUserById that deletes a user record based on the passed ID:

   DELETE FROM users WHERE id = '{{this.params.userId}}'

2. Create a JavaScript Object (JS Object) that will contain the utility function for bulk deletion. In the entity explorer, create a new JS Object named UserDeletionUtils:

   export default {
     bulkDeleteUsers: async (userIds) => {
       // Array to store the promises from the delete operations
       const deletePromises = userIds.map((userId) => {
         // Executing the delete query with the current userId
         return DeleteUserById.run({ userId });
       });
   
       try {
         // Wait for all delete operations to complete
         const results = await Promise.all(deletePromises);
         // Optional: Handle the results, such as updating the UI or notifying success
         return results;
       } catch (error) {
         // Optional: Error handling logic
         console.error("Bulk delete operation failed", error);
         throw error;
       }
     },
   };

3. To initiate the bulk deletion process, trigger UserDeletionUtils.bulkDeleteUsers from an event in your application (like a button click):

   {{ UserDeletionUtils.bulkDeleteUsers([12, 34, 56, 78, 90]) }}

   This snippet executes the bulkDeleteUsers function with an array of user IDs to be deleted.

Important Considerations

• Ensure that the DeleteUserById query correctly references the userId parameter.
• Be mindful of potential race conditions or limits imposed by your data source during bulk operations.
• It is critical to handle exceptions and rejections from the promises to avoid uncaught errors that could interrupt your application flow.
• If your datasource query supports a bulk delete operation, it will be more efficient to use that.

Conclusion

The run() function in Appsmith empowers developers to perform dynamic and complex database operations. By passing runtime parameters and processing responses, one can create robust applications capable of handling batch processing tasks seamlessly. Always remember to handle errors gracefully and test the system under different scenarios to ensure reliability.