Fast Bulk queries for postgres and MSSQL using JS ->JSON->SQL

This guide is a faster way to do bulk updates then the JS promise method prescribed by retool.

the process takes a set of arrays with the update data, converts those arrays to JSON, and then passes the JSON to a query that will execute all the updates at once.


  1. You must have all your update data formatted into mapped arrays of values:
  • [client_id1, client_id2]
  • [contract_id1, contract_id2]
  • [amount1, amount2]
  • etc…
  1. Pass these arrays into a javascript query to format into a set of JSON objects using the following code:
const data =; // pull in your data (arrays).
const c_id = data.campaign_id; //extract array from data.
const state_code = data.state_code; //extract array from data.

let temp_array = []; //empty array to hold JSON.

//pass the values into the below code as "column_name" : "${array_value[i]}" separated by commas.
 "campaign_id" : "${c_id[i]}", //input your 1st array value starting here.
 "state_code" : "${state_code[i]}" // input 2nd array value here.

let json = '[' + temp_array.join(',\n') + ']' //joins and completes the JSON code.

return {json: json} // return JSON code to pull into your database query.


Create the update query using the code below:

--follow steps 1-7 below to update this query for your own uses.
 --All variables, column names, ect... must appear in the EXACT SAME ORDER.
 declare @campaign_id INTEGER;
 declare @state_code varchar(255);
 --step 1: (above) declare all the variables exactly as the names exist in the database. declare @col_name [col_type]

 declare cur CURSOR FOR
   SELECT * FROM OpenJson({{}}) --step 2: update this to point towards your JSON query output.
   WITH (campaign_id INT '$.campaign_id',
   state_code VARCHAR(100) '$.state_code'
   --STEP 3: update the (above) values to match your data/col_names: col_name [col_type] '$.col_name' repeat for as many columns as you need to update.
 OPEN cur;
 FETCH NEXT FROM cur INTO @campaign_id, @state_code --STEP 4: input all your @col_names here separated by commas.
 insert into table
   (campaign_id, state_code, created_at, updated_at)
   values (@campaign_id, @state_code, current_timestamp, current_timestamp)
   --STEP 5: replace the insert query above with your insert/update query
   --STEP 6: replace the values(value1, value2) in the query with to your variables declared at the top of the code: values(@col_name1, @col_name2, @etc...)
FETCH NEXT FROM cur INTO @campaign_id, @state_code --STEP 7: input all your @col_names here separated by commas.
 CLOSE cur;


  1. Create update query using the code below:
--follow steps 1-3 below to update this query for your own uses.

INSERT INTO account_payment_schedules
(contract_id, account_id, amount, finance_fee, deleted, schedule_date, status, created_at, updated_at,    payment_number, original_schedule_date, created_by_person_id)
--STEP 1: (Above) paste your update query to replace the one above.

SELECT "data".*
FROM  generate_series(1,1) i

--STEP 2: in the (below) select query, you need to input all the values from your query in the EXACT SAME ORDER.
--lines that start with (j.items->>) must be updated with your columns names and cast to the correct data type.
--values that do not appear in the JSON, such as schedule_date in this example can be hard coded using "value" as col_name. but still appear in the EXACT SAME ORDER as your update/insert query.

   SELECT (j.items->>'contract_id')::int as contract_id,
    (j.items->>'account_id')::int as account_id,
    (j.items->>'amount')::NUMERIC as amount,
    (j.items->>'finance_fee')::NUMERIC as finance_fee,
    false::BOOLEAN as deleted,
    CURRENT_DATE as schedule_date,
    'scheduled' as status,
    current_timestamp as created_at,
    current_timestamp as updated_at,
    (j.items->>'payment_number')::INTEGER as payment_number,
    CURRENT_DATE as original_schedule_date,
    {{[0]}}::INTEGER as created_by_person_id
    FROM json_array_elements({{}}::json) j(items)
   ) "data";
--STEP 3: in the FROM statement above, update the javascript variable to point towards you JSON query output. the code between the {{}}`
1 Like

Welcome to the forum @Kat . Love that your first post is to pass on some knowledge!

I usually make a Stored Procedure on the back end when I need to do complicated things or need to use transactions, I guess I can just put it directly into a SQL query and not bother the server with storing it? Also good for those that do not have permissions to add a stored procedure. I'll have to play with that. But will need some testing to see if it really does what I need (like will it really allow transactions?) Thank you for opening that mental door.

Back to the subject: I think I have an even easier way to accomplish what it looks like you are doing and that is with Bulk Update or Bulk Insert queries. (Write data to SQL databases)

Here is an example: How do I add multiple rows to table in PostgreSQL? - #2 by bradlymathews

Let's say we take your mapped array values (an object of arrays) you first need to change it to an array of objects, put this in Array of records to update: formatDataAsArray(myArray). Teh function formatDataAsArray() is built in and that does what your JS code in Step 2 does.

When using the bulk query, Retool will convert the data into a single query with multiple inserts or updates and pass that to your database. It also know the difference between MySQL, SQL Server, Postgres, etc., so you don't have to worry about it.