How to bulk insert into SQL using MySQL2?

Question

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)?

halfer · Accepted Answer · 2024-11-18 22:48:45Z

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)
*/

Fattie · Accepted Answer · 2024-10-01 19:49:13Z

-1

Some important tips to save time if you're trying to get this working for the first time.

1. You send in a wrapped array, not an array

Like this:
```
[[ [a,b,c], [a,b,c], [a,b,c] ]]
```
not:
```
[ [a,b,c], [a,b,c], [a,b,c] ]
``

2. `execute` is broken for multiples in MySQL2, just change to `.query`

Broken:
```
await pool_PSQL.execute(`insert ..
```
simply change to:

```
await pool_PSQL.query(`insert ..
```

3. Note that the "queries string" is simply one question-mark "?"

So an example:

var multi = []
for (let i = 0; i < items.length; ++i) {
    const item = items[i]
    const r = [item.blah, item.bleh ...]
    multi.push(r)
}

await pool_PSQL.query(`insert into Things
    (bim, bam, ...)
    values ?`,
    [multi]
)

edited Oct 1, 2024 at 19:49

answered Oct 1, 2024 at 13:26

Fattie

9,77076 gold badges455 silver badges769 bronze badges

Collectives™ on Stack Overflow

How to bulk insert into SQL using MySQL2?

2 Answers 2

Comments

1. You send in a wrapped array, not an array

2. `execute` is broken for multiples in MySQL2, just change to `.query`

3. Note that the "queries string" is simply one question-mark "?"

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1. You send in a wrapped array, not an array

2. execute is broken for multiples in MySQL2, just change to .query

3. Note that the "queries string" is simply one question-mark "?"

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

2. `execute` is broken for multiples in MySQL2, just change to `.query`