Node mysql bulk insert with express array parameter

Question

I've come across a situation where I need to use a bulk insert with my Node project.

This of course has already been answered here: How do I do a bulk insert in mySQL using node.js

However, I have an express project which I use to create an api. The parameters are turned into an array and I'm having trouble using that array with a bulk insert. Whenever I try to use that route, I get an error of Error: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1

After some digging I found that it tries to insert:

['foo', 'bar', 'test']

When I need it to insert:

['foo']
['bar']
['test']

Anyways, here's the whole code:

Route

router.post("/", function (req, res, next) {
    db.query(
        "REPLACE INTO user (`Name`) VALUES (?)",
        [req.query.array],
        function (error, response) {
            if (error) throw error;

            console.log(response);
         }
    )
});

Route Caller

let requestUrl = "http://localhost:3000/user?";

// External api request which returns a list of users
for (let i = 0; i < body.users.length; i++) {
    requestUrl += `array=${body.users[i]}&`
}

let addUserRequest = {
    url: requestUrl,
    method: "POST"
};

request(addUserRequest, function (error, response, body) {
    console.log(body);
});

The url that is generated is:

http://localhost:3000/user?array=foo&array=bar&array=test

Answer 1

Try this,

var datatoDB = [];
req.query.array.forEach(function(entry) {
    console.log(entry);
    datatoDB.push([entry]);
});

Here we are trying to convert this ['foo', 'bar', 'test'] to this [["foo"], ["bar"], ["test"]]. Now, use datatoDB in your function.

router.post("/", function (req, res, next) {
    db.query(
        "REPLACE INTO user (Name) VALUES ?", 
        [datatoDB],
        function (error, response) {
            if (error) throw error;

            console.log(response);
         }
    )
});