Best way to insert to 5 diff mysql tables when i press a button?

Queries and Resources
1

Hi theres any easier way to insert to 5 different mysql tables? rather than creating a query for each of the 5 tables? im using the GUI mode

image

any tips will be appreciated

thank you

2

I have several situations where I need to do exactly that. I create a stored procedure and pass all of the needed values. You can also then use transactions because when you need to add to multiple tables you usually need to make sure they all succeed or all fail to maintain data integrity.

If you do not have access to change your database, then you are stuck with multiple queries. But do use a JS query to run all of the other queries asynchronously. You can also check each query after it runs to make sure it worked right before proceeding to the next query and run a rollback query if it didn't, but that can get pretty complicated when trying to do 5 queries!

1 Like
3

Thank you for the answer, you got any example of that jQuery to run MySQL queries? With that rollback

4

Yes I think it may be better to stay with 5 diff queries on retool

5

No jQuery - a JS Query in Retool. And it works on any query to any database (I use Azure SQL and Postgres myself)

let data1 = await qrySourceUpdate.trigger() 					          
let data2 = await jsSetView.trigger({additionalScope: {skipSelect: true}})	
await jsSourceRowSelect.trigger()  // trigger subtable setups

But I do not have any examples with rollback - I always use Stored Procedures when I need transactions - too damn much work the other way. I tried and decided it was much easier to get better at programming SPs.

You would only do it the other way when you do not have access or permissions to do SPs.

The docs here have a couple of other ways to trigger queries from JS:

https://docs.retool.com/docs/scripting-retool#promises-and-async-queries

1 Like
6

But with SP you can now if one or two insert were successful? And if one of them gives errors what you do?

7

With an SP, if you use transactions and one query fails, all changes get rolled back as if nothing happened. In my professional opinion, transactions are required almost any time you have more than one table to modify at one time, they make things simpler to program and make your application more robust.

However the subject of SPs is beyond the scope of this forum. There are some good tutorials out there on what they are, how they work and how to program them. It is a whole subject of study, but if you are going to do anything moderately complex (and from your forum post history, it sure looks like you are) SP programming is a very worthwhile subject to take the time to learn.

I don't use MySQL so I am sorry to say I don't have any personal recommendations for you.

1 Like
8

Yeah thinking in moving out to postgresql, I was thinking supabase but not sure yet. Also one question you think with an API endpoint I can run 5 queries? Example to create one product I can use API rest with one endpoint and that in background insert to 5 tables? Or should I avoid API in my case

9

API endpoint would work fine, still more work than a SP for this however. I also use SPs when I am making API end points in C#. I also forgot to mention that an SP will perform faster than the Retool JS query or even the API.

I have played with Supabase some and it works fine. I am still evaluating moving from Azure Postgres to Supabase to save some $$. I have not finished stress testing its performance for very large databases, but seems to hold up fine for light workloads so far.

1 Like