Database backups / snapshots

Hi-

I'm working on our second retool app now that's using the Retool Database for data storage. One thing that's a bit of a concern is that retool doesn't seem to provide any mechanism for database backups / snapshots. I'm not concerned with a scenario where there is an infrastructure failure (I assume retool is internally covering that case with some sort of backup / recovery strategy), but rather with a scenario where, for example, I have a bug in my code and I accidentally delete some important application data from a table.

Is there any system / mechanism / process for me to control the creation of snapshots (or equivalent), including how often they are taken, how many are saved before deletion, etc, and also have access to recovering data from a snapshot myself? Or do I have to roll my own solution using external tools?

Thanks!

Hey @sr112233! You are correct that we are covering the case of infrastructure failure and can access backups of everything in case of emergency. You are also correct that there currently isn't any native way to handle backups yourself. You could use an external tool, or a combination of workflows and CSV exports or additional (essentially duplicate) RetoolDB tables. I'll also submit this as a feature request for you.

+1, There should definitely be an option to backup Retool database.

1 Like

@joeBumbaca are there any updates on this?

Hey @Matti, no updates on this yet. It's not on the near term roadmap, but I'll update this topic as soon as I get any additional information.

Hi! you talked about third party tool - can you pls list few examples which 100% works properly with retool

Hi! In the hypothetical scenario where we accidentally deleted some information , or even an entire database, would Retool be able to help us restore that data using its backups?

Hi @Jeffrey_Yu,

Yup, you can reach out directly to support or to some of the retool folks active in the forum, provide them a timestamp to which yo uwould like your database to be restored to and they can do that for you.

A good option is also going into Office hours so you can make your question directly there

1 Like

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!

1 Like