Retool Database - multiple resources each with different settings

đź’¬ Queries and Resources
1

In an app, I am programmatically building a sql query in a transformer, to then be used in a retool database resource query. When running the query, I am getting the same thing others have gotten – Error in converting query to prepared statement. You can disable this feature by checking the 'Disable converting queries to prepared statements' checkbox on the resource configuration page.

I have over 20 applications using the Retool Database Resource, so I can’t disable this feature. A recommended approach was to create a duplicate resource, only difference being this disabled feature. However, I can’t figure out how to create a second Retool Database Resource, so I can’t disable it. Any ideas on how to do it?

FWIW – below is the extent of the query in the transformer.

const includeUnprocessed = {{ checkbox1.value }};
const categories = {{ multiselect1.value }}.map(c => `'${c}'`);
const onlyShowCasesWithActions = {{ checkbox2.value }};

let sql = `
SELECT *
FROM some_case
WHERE TRUE
  AND initially_processed = ANY(ARRAY[${ includeUnprocessed ? [true, false] : [true] }])
  AND EXISTS (
    SELECT *
    FROM run_result_overall result_overall
    WHERE result_overall.case_id = some_case.case_id
      AND result_overall.email_category = ANY(ARRAY[${ categories }])
  )
`;

if(onlyShowCasesWithActions) {
  sql += `
    AND EXISTS (
      SELECT *
      FROM run_result_overall result_overall
      JOIN run_result_actions result_actions
        ON result_actions.case_id = some_case.case_id
          AND result_actions.workflow_run_id = result_overall.workflow_run_id
      WHERE result_overall.case_id = some_case.case_id
    )    
  `
}

return sql.replace(/\s+/g, ' ').trim();

What I don’t completely understand, is if I were to view the above transformers value (in state), copy/paste that to the Retool Database Resource Query’s SQL box, it works great. Here is an example of the transformer value:

SELECT * FROM some_case WHERE TRUE AND initially_processed = ANY(ARRAY[true,false]) AND EXISTS ( SELECT * FROM run_result_overall result_overall WHERE result_overall.case_id = some_case.case_id AND result_overall.email_category = ANY(ARRAY['Update Admin','Spam or Auto Response']) )
2

Thanks to ChatGPT, I was able to workaround this limitation but adjusting my sql. It makes it less readable and less intuitive, but it works. It still would be nice to be able to use transformers in sql though.

SELECT *
FROM support_case
WHERE TRUE

  -- includeUnprocessed:
  -- If true => no filter. If false => only initially_processed = true
  AND ( {{ checkbox1.value }} = true OR support_case.initially_processed = true )

  -- categories:
  AND EXISTS (
    SELECT 1
    FROM run_result_overall result_overall
    WHERE result_overall.case_id = support_case.case_id
      AND result_overall.email_category = ANY({{ multiselect1.value }}::text[])
  )

  -- onlyShowCasesWithActions:
  AND (
    {{ checkbox2.value }} = false
    OR EXISTS (
        SELECT 1
        FROM run_result_overall ro
        JOIN run_result_actions ra
          ON ra.case_id = support_case.case_id
         AND ra.workflow_run_id = ro.workflow_run_id
        WHERE ro.case_id = support_case.case_id
    )
  );
3

Hey there @lkiss,

To create another resource with Retool's database but with Prepared STatements disabled, you can do the following:

1- Create a Postgres resource
2- Copy your Retool's Database Connection string, see below where to find it when in the database:

image
image607Ă—374 21.9 KB

3-Paste it in the Autofill Using Connnection String when creating the Postgres Resource

image
image574Ă—620 26.5 KB

4- Use SSL/TLS and other settings as per below:

image
image804Ă—659 31.3 KB

You can now use this resource as any other resource.

Hope this helps!

2 Likes
4

Whoa – that’s awesome!! Thank you thank you

1 Like