Need help with Create and Update sql query to Supabase backend

[Youdjin]

Hi, i just started using Retool and trying to love it but have some difficulties with querie. While i can use Supabase AI to generate Read and Delete querries and Views i find it a little challenging with Create and Update querries. Please help. I'm coming from Bubble nocode and decided to move my project to Retool.

I'm trying to create a dashboard to manage Orders, Invoices and Expenses however youtube is kinda limited to Retool tutorials . (Why?)
Please can someone write me a model Create and Update querry based on this form and database table and the rest i'll try myself:

on supabase side the id is generated by supabase. the bucket to upload the logo is also on supabase storage bucket.
Much appreciated.

[Milan_Kalem]

@Youdjin Welcome to the community

First you will need to connect your resource

1. Add a New Resource: - Click on “Resources” in the left sidebar.

• Click the “Create New” button.

2. Choose Resource Type: - Select “PostgreSQL” if you want to connect directly through Supabase's PostgreSQL instance. Alternatively, choose “REST API” if you're planning to use Supabase's API endpoints. For this guide, we'll use “PostgreSQL”.
3. Configure PostgreSQL Resource: - Resource Name: Give your resource a unique name, e.g., SupabaseDB.
4. Test & Save: - Click the “Test” button to ensure the connection works. If successful, click “Create resource” to save it.**
   Here is more info how to set up resource for supabase.

Create Query Using GUI

1. Open Your Retool App:

• Log into Retool and either open an existing app or create a new one.

2. Add a New Query:

• Click on the “Resource” tab from the sidebar.
• Click “Create New” to add a new query.

3. Choose the Query Type and Resource:

• Select your database resource, e.g., Supabase.

4. Set Up the Query:

• In the Query Editor, select "Insert a record" from the dropdown menu.
• Choose your table, e.g., trucks.

5. Add Key-Value Pairs:

• Add key-value pairs for each column you want to insert data into.
  • Example:
    • truckNumber: {{formInputTruckNumber.value}}
    • year: {{formInputYear.value}}
    • make: {{formInputMake.value}}

6. Save the Query:

• Name the query (e.g., createTruck) and save it.

Update Query Using GUI

1. Add Another New Query:

• Click on the “Resource” tab.
• Click “Create New” to add another query.

2. Choose the Query Type and Resource:

• Select your database resource, e.g., Postgres.

3. Set Up the Query:

• In the Query Editor, select "Update a record" from the dropdown menu.
• Choose your table, e.g., trucks.

4. Add Key-Value Pairs:

• Specify which columns to update and their new values.
  • Example:
  • truckNumber: {{formInputTruckNumber.value}}
  • year: {{formInputYear.value}}
  • make: {{formInputMake.value}}
• Set the conditions to determine which records to update (e.g., based on id):
  • Example Condition: id = {{table1.selectedSourceRow.id}}

5. Save the Query:

• Name the query (e.g., updateTruck) and save it.

Connecting Queries to Form Actions via GUI

1. Bind the Queries to Button Actions:

• Add a Button component to your app for the "Create" action.
• In the button’s configuration panel, find the "Action" section.
• Set the action to "Run query" and select the createOrder query.

2. Configure Form Inputs:

• Ensure form inputs have the correct variable names (formInputTruckNumber, formInputYear, formInputMake).

3. Add an "Update" Button:

• Add another Button component for the "Update" action.
• In this button’s configuration panel, set the action to "Run query" and select the updateOrder query.

Hope this is an easy to follow step by step process to set up your supabase resource, and setting the insert and upload queries.

[Youdjin]

Hi Milan,

thank you for the detailed introduction. Very helpful. However when i try to submit the record i get an error about the record's primary key which as i mentioned is autogenerated on supabase. If i set it manually it works, but i need it to be autogenerated and autoincremented which is already set on supabase. here's a screenshot.

[Milan_Kalem]

Got it, so this is more of a Supabase primary key issue than having to do with Retool.
As I see in the screenshot above, you have ID as int8 set as a primary key. What you can do is set uuid type which will randomly generate the key for you.

Hope this resolves it

[Youdjin]

Unfortunately nothing changed. i set id to UUID but still the same error.

[Youdjin]

Actually let me correct myslef, i changed it then saved it, however after the save, the id is still staying as int8 and not uuid, i believe its because of the relationships with other tables. Does this mean that i have to change all the id's for all the tables in the schema to uuid?

[Milan_Kalem]

Did you try to set up the ID column to be auto incrementing?

[Youdjin]

Yes its set by default as Identity

[Milan_Kalem]

Can you edit the column itself in the table and set the default value to trucks_id_seq and try it out again? Also maybe trying if this works on a table where you do not have a foreign key, just to pinpoint if the issue is with the relationship or something else?

[Youdjin]

ok, i created a test table without any relationships and tried a create GUI query. and it worked without the id, however the data in the database is just ...

[Milan_Kalem]

just to be clear, now the ID auto generates correctly, but the data from the input components is not inserted correctly? If you hover over the textInput29 in the GUI form, is it a string "Sam" or what it seems like the whole object?