When is the best time to use transformers?

App Building
1

Hi @sophia,

I'm curious about when is the right time to use Transformers. I'm pretty new to retool, but where I'm finding transformers extremely useful is in transforming API results to an array that maps to a database table that I want to save those results to.

So for example I have a JS resource that calls an API for FreshDesk Tickets:

JS Resource to get Ticket data for a date range...(I won't put it all here):

const queryResponses = await Promise.all(dateRange.data.map(async (row) => { const result = await getTickets_FromJS.trigger({...

This returns an array of Ticket data in its 'raw' form from the endpoint. Some of the information I don't want (e.g. "data.description_text" is the entire body of the ticket/email).

I'm using a Transformer called tfTickets_for_DB to grab what I want from the response so I can bulk insert to the Retool DB. tfTickets_for_DB is basically:

SELECT  
ticket_data.id as ticket_id,
ticket_data.created_at as created_at,
...
FROM {{getTickets.data}}  as ticket_data
order by ticket_id

Each item in the Transformer maps to the data structure of the Retool Postgres DB. I have a very basic Bulk Insert based on:

{{tfTickets_for_DB.data}}

So, I can chain together via Event Handlers:

  1. API call ->
  2. Transformer ->
  3. Bulk insert

Is this an appropriate use of Transformers? Or is there a better way to get from A->B (API call to saved results in the DB)?

Regards,
Nick

2

@nickaus,

That is a perfectly valid use of transformers. Only one suggestion I would make, is if you only use your API's transformed result, then you can put that transformer right into your API query, merging steps 1. and 2. Then you can use the query.data directly in your bulk insert.

If you use your API's results elsewhere in a different form than your bulk insert requires, then you are doing it exactly right.

3

Hi @bradlymathews,
Thank you for replying!

I would certainly like to be able to combine steps 1 & 2 if possible. Can you direct me to where I can learn how to 'put that transformer right into your API query'.

I used a Transformer because:

  1. Each item in the response array contains data nested within a .custom_fields property with values I need to access and save to the DB (plus other properties/values I don't need as per previous post)
  2. If I did a Bulk Insert to the DB based on the 'un-Transformed' data , I save all the un-required data, and the stuff I need from custom_fields is buried in a DB field. Example, custom_fields.max_estimate, which downstream I will need and don't want to have to select from a JSONB DB field type if possible
  3. The only way I could work out through trial and error to perform a Bulk Insert was to perform the transformation and then, as above Bulk Insert: {{tfTickets_for_DB.data}}

I tried a few different approaches - e.g. create a Front-end table which presented just the values I wanted to insert, but the .data property of that table still seemed to be the 'raw' API response data, rather than just the data I actually wanted (and was displaying in the table)

Love to know a better way because the transformer is really just my way of getting from A->B, and I feel like there must be a simpler solution! :slight_smile:

Regards,
Nick

4

Transformers are the way to get from A->B so you are doing it right.

Every query has a place to put a transformer:

image
image1085×1076 47.1 KB

Here's some docs:

5

@bradlymathews
Oh right! Thanks for the pointer. Yep that will be really helpful for me in a lot of situations and here fits my use-case perfectly. Thank you :pray:

Regards,
Nick

6

Hi again @bradlymathews,

I have read the docs and understand the principles of attaching a Transformer to the API resource, but I can't work out the correct syntax.

This gives me the results object of the response:

return data.results

And this gives me the number of results (which I need for pagination):

return data.total

However, I can't work out how to return the specific keys/values from .results. I tried a lot of variations (formatDataAsArray, {}, [], etc but no luck. What I am aiming for is along the lines of:

return {data.results.id, data.results.created_at, data.results.custom_fields.cf_max_estimate, ....}

Any pointers greatly appreciated! For now my 'separate' Transformer works, but I can see this method being helpful in quite a few circumstances :pray:

Regards,
Nick

7

If you already have it working in your other transformer, then you basically just copy/paste that in and replace query1.data with just data and it should just work.

Your example is missing the property names:

return {
    result_id: data.results.id, 
    created_at: data.results.created_at, 
    max_estimate: fc_data.results.custom_fields.cf_max_estimate, 
    ....}
8

Hi @bradlymathews,
Thank you :pray: I could not get this to work immediately for my endpoint, but with some slight adjustment (because the response has a nested .results array of objects), this works:

return data.results.map(item => {
  return { 
    ticket_id: item.id,
    company_id: item.company_id,
    etc: item...}

(for others - hope this helps).

This will be very useful in a lot of places for me so thanks :100: again.

Regards,
Nick

1 Like