Hey, I've actually made a solution here to back up your Retool database tables to your local computer!
You can also download and import the .json app here:
You first need to go to your Retool db and for the moment, click "Disable converting queries to prepared statements" as checked. You can change it back after the backup is completed.
Then you need to create a Retool DB query to get all of the tables in your public tables list:
Query: getAllTablesQuery
SELECT
tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
Run this query to get the list of your tables.
Then, create a variable called table_to_download
Set the initial value of the variable to null
Then, create another Retool DB query that will take in the table name and select all from the table:
Query: selectAllFromTableQuery
SELECT * FROM {{ table_to_download.value }}
On the Event Handlers of the selectAllFromTableQuery, have an Action that is Export data.
- Set the data to be {{ selectAllFromTableQuery.data }}
- Set the File name to be {{ table_to_download.value + '_' + (new Date()).toDateString() }}
Then, set up a javascript function with the following code:
/**
* JavaScript to Download All Tables in Retool Database
* This script iterates through all tables in the database,
* triggers a query to fetch data from each table, and downloads it.
* If an error occurs, it stops execution.
*/
async function downloadAllTables() {
try {
// Get the list of all tables from the query result
const tables_list = getAllTablesQuery.data?.tablename; // Ensure this returns an array of table names
if (!tables_list || tables_list.length === 0) {
console.error("No tables found or query failed.");
return;
}
for (let table of tables_list) {
table_to_download.setValue(table); // Set the value for the table name input
await selectAllFromTableQuery.trigger(); // Trigger the query
// Check if the query encountered an error
if (selectAllFromTableQuery.data.error) {
console.error(`Error fetching data for table: ${table}`);
return; // Stop execution if an error occurs
}
await new Promise(resolve => setTimeout(resolve, 5000)); // Pause for 5 seconds before processing the next table
}
} catch (error) {
console.error("An unexpected error occurred:", error);
console.info("Hey there!")
}
}
// Execute the function
downloadAllTables();
/*
* End of Script
*/
Then run the script ONCE. Don't click the button twice. It will grab all of the contents from your tables and download them to .csv
Then don't forget to reset your Retool DB to being unchecked on the disable queries to being prepared statements after it's completed!
If you want to download these to an external source like a Google Sheet automatically, let me help you! Contact me @ Tropic Flare (https://tropicflare.com) so I can help you automate your Retool DB backups!
You can also download and import the .json app here:
If you like this solution and want to say thanks, buy me a coffee!