Bulk insert SQL mode. Is it possible?

Queries and Resources
,
1

Non GUI bulk insert

Hello fellow retoolers.
Please consider the following scenario:

I have three tables in my PostgreSQL DB - 1. Lines, 2. Clients, 3. Subscriptions.
Each subscription is made of a relation between a line and a client.

When adding lines into the database there's also an option to automatically create a subscription for the inserted lines with the given client.

The problem? Wehn trying to create a subscription I need to know the line_id of the line that were just inserted and it's not possible with the GUI mode.
If I was using regular SQL query I would’ve done in like this:

WITH ins AS (
   INSERT INTO lines VALUES
      (default, phone_number, sim_number, ...),
      (default, phone_number, sim_number, ...),
      (default, phone_number, sim_number, ...),
      (default, phone_number, sim_number, ...)
   RETURNING id)
SELECT array_agg(id) INTO line_ids_list
FROM ins;

And then I would have another query that is in-charge of inserting the rest based on the returned ids.

But since for bulk update I have to use the GUI Mode, then I can't think of a way to pull this off inside of retool.

Any help would be greatly appreciated !

2

You would need to run a trigger in the DB itself AFTER the insert happens.....to get that ID

3

Thank you for your help.

You would need to run a trigger in the DB itself AFTER the insert happens.....to get that ID

I didn't want to go the extra mile, and have another table just to be able to perform a small part of the application.

I guess that's the only option though as Retool will not let me add any SQL to the bulk insert query.

1 Like
4

@Mendy

Hey there :wave: For a bulk update in SQL mode, one option could be to write a JS query to trigger a single row update for each record in an array by following a setup similar to this example from our docs. You can create a single insert query and loop through the array of values you'd like to insert in a JS query, passing each one to the query as additionalScope. Hope that helps!

5

Bit of an old thread, but you can bulk insert with SQL mode queries in order to return the created IDs back to the app if the resource doesn't have the prepared statement setting enabled.

You essentially need to craft the values part of the SQL query with JS (specifics will depend on your array and DB structure), and then reference that in your insert query set to return whatever created record data.

screenshot-4BN9dwLT@2x
screenshot-4BN9dwLT@2x1790×1078 78.7 KB