Zapier + Retool DB not fully working

External Apps
1

I have connected to a Retool Database with PostgreSQL. However, I'm unable to get any test data to come through. I'm trying to use the new or Updated Row trigger, but have also tested with just the "New Row" trigger. Both of them give me a similar error:

Unable to pull rows Please reload the page. If the issue persists, troubleshoot error or contact Support. Error code: Error while retrieving: 'id'

Unable to pull new rows Please reload the page. If the issue persists, troubleshoot error or contact Support. Error code: Error while retrieving: 'type'

I know it is connected as I can choose the table, but nothing else populates correctly in dropdowns.

I've been working with Zapier's support team on the issue as well, so here's my progress/more info:

Here is a video of what is happening: Troubleshooting Zapier Integration for Database

Zapier Support:
Thank you for your patience as I reached out to a colleague to get another set of eyes on this one.

We did determine that the columns not appearing in the dropdown should not have much of an affect on how the Zap behaves with custom values as you have set up here. One thing I did notice, however, is that the "date_updated" text added to the Order By field does not appear as a column within your view according to the video. This field would need to be set as a column, and should ideally be a datetime column that is set to the current datetime whenever a row is updated. Once that is set up, that should be able to be used in the trigger.

Another theory is that no test records could be loaded due to us getting NULL for the sort fields. You should be able to workaround this by creating a view that excludes null values for the Order By field.

Ben: I did try testing with those suggestions, but continue to receive an error. Here is another video.

Zapier Support:
I took a look into this and I have to admit, I'm also stumped as to why this is occurring. My first instinct is to return to the connection/authentication and have you completely remove the one that exists and restart the authentication process from the start.

You should be able to do that from your connections page here -
https://zapier.com/app/connections

Can you tell me a bit more about the Schema being used here? Are any tables in this database being referenced from another database? Is there anything strange that you can think to mention about its structure?

Ben: 1. I deleted the connection and reconnected. I still got the error.

  1. I don't think there is anything strange with the database. It's on Retool and there is only 1 basic (we do reference other tables, but it's all one database).

3. Here's a video overview of everything above.

4. Here is the forum post I mentioned in the video.

2

@joeBumbaca Zapier got back to me:

The initial request to get the list of the tables/views is made when you click on the Table/View field to populate the options, and you can see the detail of the request in the log:


(view larger)

Log: https://gists.security.zapier.com/gists/01c7df63-1b13-4054-b2e7-048072a265b0

Then, when you're loading any of the subsequent fields, we make another request to get the columns, but the response to the request keeps sending back the same response to populate the list even though the endpoint specifies that we need the columns in the endpoint:

Log: https://gists.security.zapier.com/gists/513b6e2f-e7ec-44c7-9150-f2dddf4810b7

It's plausible that something needs a bit of tweaking in, for example, the database you're connecting to to specify the table at the moment of the connection or that, as you were thinking, the schema field in the connection needs to be modified, but that's something that either Retool or the developer (or, to be honest, a collaboration between them both) should look into further, in my opinion.

I really hope the information above helps you move this forward, but as always, please let us know if you have further questions.

3

@joeBumbaca do you have any updates or thoughts on this?

Thanks!

4

Hey @benvalerian sorry for the delay here. I've linked Zapier to a few RetoolDB instances and external (AWS RDS) instances and haven't been able to get into the same state, it all seems to work as expected. I'm currently digging into the actual SQL queries that run (the gists supplied show their API calls, but not the raw SQL) so that I can try and run those individually on your Retool DB. I'll update you here as soon as I get that going.

5

Thanks, @joeBumbaca! Any progress?

6

Hi @joeBumbaca, have you had a chance to review?

7

@joeBumbaca can we set up a time to work through this? I really need to get the Zapier connection working to complete our workflows and integrations. Thank you!

8

@benvalerian This is definitely a bug on Zapier side. They likely failed to parse the table data somehow. I saw in your table that there are columns (website_id, care_plan_id) that may reference another table and I think that likely trip Zapier up. Their support also called something like that that:

Are any tables in this database being referenced from another database

For debugging, I'd create another simple table without any references, test that, then add more complexity to it until you see the error appears. Then you'd know what triggers the error. We (@joeBumbaca) have verified that for simple table cases, it should work.

9

Thank you. I did create a simple new table on an office hours call a month or so ago. It had the same issues and continues to do so.

It's frustrating as we've invested a lot in building our portal on Retool with the expectation that it would work with Zapier. However, both companies are pointing us to each other and we've been left for a couple of months now having to manually input data. It doesn't give us a ton of confidence that this was the right decision.

10

@benvalerian can you try creating a new org (could be on cloud) and create a simple table there? It seems Zapier try to fetch the whole schema at once, so if one table has some complex config it may mess their code up.

11

I'm sorry you're feeling this way. We'll try our best to give you debugging direction. What makes this hard is that we've tried but we can't reproduce the issue I guess without diving into your data. It's likely an issue dependent on your data & data schema.

13

We created a new retool org, database, and sample table (no linking to other tables, the only table in the database). We are still experiencing the same issue, it will not pick up the primary key in Zapier.

Screenshot (394)
Screenshot (394)1920×1080 299 KB

Resources Retool Database 2024-05-10 at 8.47.08 AM
Resources Retool Database 2024-05-10 at 8.47.08 AM3456×1404 366 KB

14

I know this post got unlisted, but in case anyone else runs into this issue, we figured out the resolution. It goes back to the original thread with the connection details:

After reviewing the Zapier logs I posted above with another developer, we saw this: https://sqlzero.k8s.zapier.com./pgsql/retool?sslmode=require/care_plan_subscription/columns

The ?sslmode=require breaks this and if it's removed from the account connection details for the database in Zapier, everything works as it should.

16

Hey @benvalerian! Thanks for the update here, re-listing this so it is discoverable for others. I'll submit a report internally so we can look into anything that can be done to mitigate this behavior if the ?sslmode=require is appended to the db name in the connection details. That's Zapiers internal request, and not the one sent to the underlying Postgres db, but we will further investigate what gets sent along from them.