2

I am developing a backend route for my application and it needs to add multiple rows (one row for each item in an array received from the client client). My first thought is to just do a for loop through my data and do a database query for each array item as shown below:

router.get('api/postGuests', (req, res) => {
  const arrayOfPeople = req.body;
  // arrayOfPeople looks like this for example: [{name: Jason, age: 24}, {name: Tyler, age: 34}]

  for (let i=0; i<arrayOfPeople.length; i++) {
    db.query('INSERT INTO people (name, age) VALUES (?, ?)', [arrayofPeople[i].name, arrayOfPeople[i].age])
  }
})

The problem is I don't think it is efficient to make a separate query for each row, and given that these queries are asynchronous, I can see there being a lot of issues arising as I scale.

Can someone give me some advice on how to best tackle this situation? Thank you!

buddemat
  • 4,552
  • 14
  • 29
  • 49
Toacin Patwary
  • 23
  • 3

2 Answers2

2

There is no need to use multiple statements. You can insert multiple rows with a single INSERT statement.

Just adjust your prepared statement and pass all values at once. Note that the argument passed to db.query needs to be an array of arrays wrapped in an array.

Short example:

const arrayOfPeople = [
    {name: "Jason", age: 24}, 
    {name: "Tyler", age: 34},
    ...
];

db.query(
    'INSERT INTO people (name, age) VALUES ?',
    [arrayOfPeople.map(item => [item.name, item.age])]
);
buddemat
  • 4,552
  • 14
  • 29
  • 49
  • That's interesting; MySQL will infer the "shape" of each `VALUES` phrase? I'm not a MySQL user and I've never tried it in PostgresQL. Maybe it works there too. – Pointy Apr 07 '23 at 13:53
  • Any particular reason why the array of arrays need to be wrapped in yet another array? – Toacin Patwary Apr 10 '23 at 22:00
  • The outermost array is the array in which the parameters for substitution need to be wrapped. In your question, the parameters that substitute the question marks are inside such an array as well. In my example it only seems a bit weird since it is just a single question mark placeholder which is substituted. – buddemat Apr 11 '23 at 00:54
1

The VALUES clause can contain a list of (?, ?) phrases. You can build the query with a number of those corresponding to the number of pairs you want to insert, and then perform just one query to do it.

Pointy
  • 405,095
  • 59
  • 585
  • 614