Retool DB update db name

💬 Self Hosted Retool
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.

2 Likes