How to bulk insert into SQL using MySQL2?

I have an array of data I want to insert into SQL:

let arr = [
  {name:"john", age:8, country:"america"},
  {name:"jack", age:9, country:"england"},
  {name:"bob", age:12, country:"france"},
  {name:"lance", age:6, country:"spain"}
]

I am using MySQL2 library with Node.js. And this is what I am doing right now:

const transfer = async() => {
  for(person of arr){
    let sql = `INSERT INTO users(name, age, country) VALUES("${person.name}", ${person.age}, "${person.country}")`;
    const [res, _] = await db.execute(sql);
  }
}

This works fine, but is there's method in which I can enter all the objects in arr in one SQL query (vs. a query for every insert)?

Solution

Let me first explain something to you.

let sql = `INSERT INTO users(name, age, country) 
VALUES("${person.name}", ${person.age}, "${person.country}")`;

Do not use template literals to put the inputs into an SQL query. It's dangerous and it puts you at risk of an SQL injection attack. Every database driver these days has a safe way to inject your input into string and you must use them to be safe and in mysql2 you can do it like this.

const query = mysql2.format(
  "INSERT INTO table_test (name , last_name , year) VALUES (? , ? , ?)",
  ["iman", "hpr", 2022]
);
/* Result : 
INSERT INTO table_test (name , last_name , year) VALUES ('iman' , 'hpr' , 2022)
*/

And if you want to make a bulk insert, you can easily do it by providing a nested array of inputs as an argument.

const query = mysql2.format(
  "INSERT INTO table_test (name , last_name , year) VALUES ?",
  [[
    ["iman", "hpr", 2022],
    ["name2", "last_name2", 2024],
    ["name3", "last_name3", 2023],
  ]]
);
/* Result
INSERT INTO table_test (name , last_name , year) VALUES ('iman', 'hpr', 2022), ('name2', 'last_name2', 2024), ('name3', 'last_name3', 2023)
*/