Question 20165 in Mysql Question posted in
The official MySQL documentation can be found here.

How to add multiple rows to a SQL database with MySQL on a REST application

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!

Tags:

2

Answers


  1. 0

    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])]
);
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search