Error Handling: Inserts into DB tables

💬 Queries and Resources
,
1

Hi guys!

Desperately needing some advice.

I am working on an app that in essence acts as a processing tool for hubspot deals. I have forms, 5, that are associated with some database tables.

Since I am inserting user inputs from forms into the database, I am using a mix of SQL and component references, ie {{textInput2.value}} for example.

Since there are about 2 or 3 queriesper form that are being triggered each time that the form submit button is being clicked, I used query_name.trigger() to trigger each insert query.

Problem is that sometimes there are issues along the way (lets say query 1 was triggered and inserted successfully, but query2 fails), then it means that when the user rectifies the issue and clicks submit again, insert starts from the very beginning - causing duplicates etc.

My insert query looks like this:

INSERT INTO company (

  company_name,
  company_legal_name,
  company_country,
  company_comment,
  company_modif_who,
  company_reg_type_id,
  company_modif_why
)

VALUES (
  {{ numberInput229.value }},
  {{ textInput371.value }},
  {{ select184.selectedItem.country_id }},
  {{ textArea101.value }},
  {{ textInput372.value }},
  {{ numberInput230.value }},
  {{ textArea100.value }}
)
RETURNING company_id;

I've set up an event handler that looks like this:

insert_company_1.trigger({
  onSuccess: () => {
    console.log("Company Created Successfully!");

    refresh_company_query.trigger({
      onSuccess: (response) => {
        console.log("company refreshed");
        button32.setDisabled(true);
        formButton55.setDisabled(true);// Disable button after success
      },
      onFailure: (error) => {
        console.error("company_query failed:", error);
        alert("company_query failed. Please try again.");
      }
    });
  },
  onFailure: (error) => {
    console.error("insert_company_1 failed:", error);
    alert("Insert company failed. Please try again.");
  }
});

My limitation is that I can't use a classic sql rollback function, since the inserts are not purely SQL.

My initial idea was to try and get this restart from the point where it failed and not restart the entire flow of inserts.
Is this a possibility or else is there some advice on how to handle this?

Thanks!

2

Hello :slight_smile:

Check this thread, I was fighting with something similar and the solution is to make things as a single transaction. So if one fails, all fail. (normal world, which Retool is not)

Unfortunately, the only way to do that is with stored procedures.

Then for it to be able to be used with dynamic variables (like your text boxes) is to prep the entry payload in a JS query, and pass that as an argument to your stored proc. At least I'm doing that and it works.

Transactions with multiple queries, inserts and updates are a basic thing for many business purposes like yours and mine, no idea why Retool hasn't implemented anything to enable them yet. I guess if we all put our +1 in the queue, the feature would get a priority bump

1 Like
3

Adding to myself, it's always good as well to validate an entire entry before committing.

So if there's any form, you would expect to validate the entire form with JS queries and create the necessary exception handlers before triggering a query to hit the DB.

1 Like