Insert multiple records into Mysql using NODEJS

Question

How do I add multiple records at once in Mysql by passing an array of, for example, products?

HTTP-POST request, where products is an array with an x number of products. FRONT-END

  addProduct(products: any): Observable<any> {
    return this.http.post<any>(this.productURL, products, httpOptions)
  }

BACK-END

// Add Products  
app.post('/products', function (req, res) {

    let products = req.body;
    console.log(products)

    if (!products) {
        console.log('Deu erro na bagaça')
        return res.status(400).send({ error: true, message: 'Please provide products' });
    }

    mydb.query(`INSERT INTO products SET ${products} `, function (error, results, fields) {
        if (error) throw error;
        // console.log(results)
        return res.send(results);
    });
});

console.log() in the BACK-END of the variable sent to the back

Answer 1

Considering that the names of your fields in the table are equal to those reported in the question, your code would be as follows:

const values = products.map((product) => {
  return [
    product.ProductName,
    product.ProductElement,
    product.ProductAttribute,
    product.ProductAttributeValue,
    product.Quantity,
    product.ProductNotes,
    product.FK_ID_QUOTE,
    product.GroupNumber,
    product.ID_POF,
    product.ID_POE
   ]);
});

mydb.query('INSERT INTO products (ProductName, ProductElement, ProductAttribute, ProductAttributeValue, Quantity, ProductNotes, FK_ID_QUOTE, GroupNumber, ID_POF, ID_POE) VALUES ?', values, function (error, results, fields) {
  if (error) throw error;
  // console.log(results)
  return res.send(results);
});

You would transform the array of products in a array of values and so would insert in the table.

Answer 2

I built the VALUE structure and stored it in a variable, ai and only concatenate with the query.

let arrayExemplo = [
{a: "aaa", b: 111, c: "pastel"}, 
{a: "bbb", b: 222, c: "frango"},
{a: "ccc", b: 333, c: "carne"},
]

let valueSql = ''; // irá armazenar a estrutura do VALUE dentro do loop
let newArrayExemplo = []; // array que ira armazenar os múltiplos registros
arrayExemplo.forEach(item => {
  valueSql = "(" + item.a + ", " + item.b + ", " + item.c + ")"
  newArrayExemplo.push(valueSql)
})

let insertValue = newArrayExemplo.toString()
console.log("insertValue: ", insertValue)

mydb.query(`INSERT INTO exemplo (a, b, c) VALUES ${insertValue} `, function (error, results, fields) {
    if (error) throw error;
    // console.log(results)
    return res.send(results);
});