How can I get Xano to Retool via PostgreSQL and perform CRUD?

App Building
1

Hi,

We are connecting Xano backend to Retool via PostgreSQL. We were able to person get operations meaning we were able to read data from the table. But when we person update a record or adding a record we are getting errors as update "x2_8_user" set "name" = $1, "roles" = $2 where "id" = $3 returning * - cannot update column "name" of view "x2_8_user"
For example, we are trying to update name column in the user table.
Similarly an error is appearing when inserting new record in the user table.

Is it not possible to perform create, update, delete on Xano database when connected via PostgreSQL? Is there a setup we need to do to be able to achieve this?

image
image1081×1794 134 KB

2

Hi @Snehal did you ever figure this out? running into the same issues, really hoping that the database connector will work in Retool.

3

Hi @vic_ny! Yes We figured it out. Basically you have to use xdo reference of the table. for example: the update query my look like this:

UPDATE mvpw_[table_number_in_xano]
SET xdo =  
JSONB_SET(
    xdo,                                                      
    '{Withdrawal_Information}',
    ''hello",
    true
);

You can ask xano support for what is your mvpw reference for the workspace number. the "x2_8_user" is a view reference to the table therefore we cannot perform update or insert. We have to use the table reference which is mvpw[some_number]_[table_number_in_xano]

1 Like
4

Thanks so much!

5

Just wanted to add to this you can also update records via the GUI write mode and a form following the same principle. Instead of selecting the view of your corresponding table you just use the same principle @Snehal recommended and select the mvpw_ version of the table you want to update. Select xdo for the key value pair to update then insert your form.data variable for the value.

1 Like