How to bulk insert into SQL using myql2?

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 nodejs. 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 I am wondering if 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 SQL injection attack.Every database driver these days have 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)
*/