How to use the transformer inside the SQL query?

App Building
1

Is it possible to use transformer in the Where statement of the SQL thats associated with the table data?

    WHERE
  ac.email ILIKE {{  '%' + emailSearch.value + '%'  }}
  AND cu.first_name || ' ' || cu.last_name ILIKE {{  '%' + nameSearch.value + '%'  }}
  AND (ac.phone_number ILIKE {{  '%' + phoneSearch.value + '%'  }}  {{ phone_sql.value }})

{{ phone_sql.value }} is a transformer

transformer looks like this:

const empty = "";
const query = " OR ac.phone_number is null";
if({{ phoneSearch.value }} !== '') return empty;
return query;

I’m getting this error: syntax error at or near “$4”

2

To me it seems like retool is using standard binding for params in SQL. This is why it ignores complex SQL like string.

Is there a workaround for this?

3

Hey ckissi – you have to explicitly set this setting on your database resource. Go to the resource settings page and check “Disable converting queries to prepared statements”, then it should work how you expect!

4

When I click the checkbox “Disable converting queries to prepared statements” and I click “save” it shows message that it was saved but after refresh its unchecked again.

5

Hmm, I just tried it on my end and it worked. Does this persist after a refresh?

6

Hmm, I've tried now again and I cant save it. When I refresh the page box is unchecked.

image
image788×279 16.8 KB

7

@ckissi Could you try setting up another copy of the resource with the option disabled? It could be good to keep that separate anyways to limit the surface area that you need to worry about SQL injection on. I’m thinking this could have something to do with the active SSH tunnel, but if you have more issues message us in on the blue chat Icon inside Retool so we can get some account specific info from you about the setup.

8

@alex-w where is the documentation on how Retool handles query and IN statements, etc?

9

Hey @mcenedella!

You can check out our SQL cheatsheet to see how Retool deals with arrays in a variety of different SQL resources.

An important thing to keep in mind is that whether or not you have prepared statements impacts how Retool interprets the value of transformers in each SQL query, e.g.

  • {{ 'hello' }} with prepared statements is parsed as 'hello'
  • {{ 'hello' }} without prepared statements is parsed as hello

Curious to know if you're running into any issues in particular! If you are feel free to share them here and we can help work through them with you :slightly_smiling_face: