Multiple SQL statements into a single Query in Budibase

[AlejandroFNadal]

Hey everyone!

Perhaps this is super obvious and does not require a post, but I have just realized that this is a possibility in Budibase after using it for months, so I will leave it here for those that were in the same boat as me.

Issue:
How to run multiple statements in a single SQL operation in Budibase? (Using MySQL)
Sometimes you need to insert something and update something with a single button, for example, set the state of some process to "STOPPED" and insert a new one called "STARTED".

You could surely create multiple queries for this. But when you start having 30 queries in your budibase data section, it starts to become tricky to track them. Or perhaps you just want to be sure that both things are added.

The problem is that budibase does not accept, for now, multiple statements inside the Field Query section.

Solution

I have tried putting two statements (an INSERT and an UPDATE) into the same Query section, and it failed. However, this field does accept Transactions. So you can do this:

START TRANSACTION

-- Random query examples
INSERT INTO table1 VALUES(sth, sth, sth);
UPDATE table2 SET val1="something else" WHERE version = 23;

COMMIT;

This will be accepted by Budibase for MySQL. The concept of transactions is common in databases so it should surely work with other DBs, surely with some minor syntax changes.

I hope it results useful!

[melohagan]

This is great @AlejandroFNadal

Thanks for sharing! 😃