Retool DB update db name

💬 Self Hosted Retool
1

Hey, I have Self Hosted Retool deployed on GCP. I had created a Retool Managed DB resource and it is working fine. However, someone else on my team just trigger the endpoint /resources?setupRetoolDB=1 and created another Retool Managed DB resource. This action created a new clean database for staging/production. Now, I need help to "rollback" this change and point it back to the previous database instance. How can I achieve that? Thanks in advance!

2

Welcome to the community, @Marcus_Santos!

I was working with another customer on a similar problem just the other day and, unfortunately, there isn't a native way to do this. The quickest way to get unblocked is to transfer your existing data into the newly created database. We outline that process here.

This has been a pain point, though, and I'd ideally like to see native support for pointing Retool DB towards an existing database. I'm talking to the team about this currently and will will document any updates here.

3

Do you have any additional questions about this, @Marcus_Santos?

4

Hi Darren, sorry. I forgot you answer you back. No, after your suggestion, I found out the following approach to make it work:

  1. Identify the target database you want to connect.
  2. Update the password for the user in this database.
  3. Define the connection string for this database.
  4. Create a Configuration Variable with the name old_connection_string. The value will be the connection string defined in the previous step. You must create the variable as a secret value.
  5. Run the following query on the main Retool database:
  • SELECT value FROM config_vars cv LEFT JOIN config_var_values cvv ON cv.uuid = cvv."configVarUuid" WHERE name = 'old_connection_string';
    note: this will return the encrypted connection string for the target database.
  1. Run the following query to get the production resource ID and the connection string:
  • SELECT id, options FROM "public"."resources" WHERE type = 'retoolDb' AND environment='production';
  1. Run the following query to replace the connection string with the generated one that you've generated on step 5.
  • UPDATE "public"."resources" set options='{"version": "SqlQueryUnified", "connectionString": "<CONNECTION_STRING>", "queryEditorModes": {"allowGuiMode": true, "allowSqlMode": true}, "rejectUnauthorized": false, "userExplicitlySetRejectUnauthorized": true}' WHERE id = <RESOURCE_ID>;

This allowed me to switch to the new database.

1 Like