How to insert multiple rows (from a req. body) in a transaction

Question

I would like to ask if there's a way to insert multiple rows through the req. body (coming from the frontend). I have managed to create a way to insert one row, but I want to, if possible, to insert multiple rows of task while only inserting job once.

I've read that a way is to use a json array and loop it, but can't figure out how to do it. If you have another way, it would be a great help too!

I'm currently using node.js / express.js as backend, and node-postgres as connection to db.

//2. Declare an asynchronous function for the PG transaction
        async function execute() {
            // Promise chain for pg Pool client
            const client = await pool
                .connect()

                .catch(err => {
                    console.log("\nclient.connect():", err.name);

                    // iterate over the error object attributes
                    for (item in err) {
                        if (err[item] = undefined) {
                            process.stdout.write(item + " - " + err[item] + " ");
                        }
                    }

                    //end the Pool instance
                    console.log("\n");
                    process.exit();
                });

                try {
                    //Initiate the Postgres transaction
                    await client.query("BEGIN");

                    try {
                        const sqlString = `WITH INSERTED AS (
                            INSERT INTO jobs (job_name) VALUES
                                ($1) RETURNING id)
                            INSERT INTO tasks(
                                employee_id, job_id) VALUES
                                ($2,(
                                    SELECT id FROM inserted
                                ))`;
                        
                        const sqlValues = [job_name, employee_id
                        ];

                        // Pass SQL string to the query() method
                        await client.query(sqlString, sqlValues, function(err, result) {
                            console.log("client.query() SQL result:", result);

                            if (err) {
                            console.log("\nclient.query():", err);

                            // Rollback before executing another transaction
                            client.query("ROLLBACK");
                            console.log("Transaction ROLLBACK called");
                            } else {
                            client.query("COMMIT");
                            console.log("client.query() COMMIT row count:", result.rowCount);
                            }
                        });

                    } catch (er) {
                        // Rollback before executing another transaction
                        client.query("ROLLBACK");
                        console.log("client.query():", er);
                        console.log("Transaction ROLLBACK called");
                    }
                } finally {
                    client.release();
                    console.log("Client is released");
                }
        }

        execute();

        } catch (err) {
            console.error(err.message);
            res.status(500).send("Server Error");
    }
});

Thank you!

Answer 1

Use a SELECT in the second INSERT and UNNEST() to get all items from the array:

WITH INSERTED AS (  
    INSERT INTO jobs (job_name) 
    VALUES($1) 
    RETURNING id
)
INSERT INTO tasks(employee_id, job_id) 
SELECT  UNNEST($2) -- PostgreSQL array input, you might need another function for your type of input
    ,   id
FROM inserted;

Edit: Example using some json-object as input:

WITH INSERTED AS (  
    INSERT INTO jobs (job_name) 
    VALUES($1::json #>> '{name}') -- selects the name from the json object as TEXT
    RETURNING id
)
INSERT INTO tasks(employee_id, job_id) 
SELECT  column1 
    ,   id
FROM inserted
    , json_to_recordset($1::json #> '{tasks}') AS t(column1 int, column2 text);

$1 is a single json-object that is used twice. PostgreSQL will extract the elements that are needed for the INSERTs.