How do I add table columns dynamically on Appsmith?

How do I do X?
1

Requirement:

  • There will be a table with a fixed number of columns. example: id, name

  • There will be an “Add column” button on clicking which opens a modal to enter the name of the column. On pressing add, the new column will be added to the table or as well as on the database.

Example Forkable App:

https://app.appsmith.com/applications/61e11a42eb0501052b9fab3e/pages/624bed6351a8863d6c401b91

Solution:

We could dynamically add columns to a table on both UI and Database using some simple JavaScript.

Say, you’re adding columns dynamically from an input widget (name of the column), and a button to submit it, here’s how the button’s onClick function should look like:

{{
add_column.run()
	.then(() => fetch_users_data.run())
	.catch(() => showAlert('Failed to Add Column','error'))
	.finally(() => {
		resetWidget('column_name');
		closeModal('addColumnModal');
	})
}}

In the above code snippet, we first alter the table to create a new column, update the data on the table by re-running the table data, next we show if there are very errors and finally reset the widget and close the modal.

Below is the code for the queries:

add_column query:

ALTER TABLE users 
ADD COLUMN {{column_name.text}} text;

fetch_users_data query:

SELECT * FROM users WHERE name ilike '%{{Table1.searchText}}%' ORDER BY id LIMIT {{Table1.pageSize}} OFFSET {{(Table1.pageNo - 1) * Table1.pageSize}};