Insert multiple records in mysql using node.js fails

Question

I am trying to INSERT multiple records in a MySQL Table, but whatever I do, it inserts only one row.

Here is the node.js code:

let orderID = 15; //It is a foreign key. Noted it from the table. 
itemParams = [
        [ orderID, 'd', 34.6, '12', '123.jpeg' ],
        [ orderID, 'd', 30.6, '2', '456.jpeg' ]
];

let addOrderItems = await pool.query(`INSERT INTO orderitems (oid, description, weight, quantity, image_url) VALUES (?)`, itemParams);

When I console log addOrderItems the affectedRows count is 1, so I also checked my table in Workbench to be sure and only one row was added. (oid is a foreign key). Table does have a primary key which auto increments.

The query I found online didn't have paranthesis around the question mark ? but when I remove the paranthesis, I get the following error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19, 'd', 34.6, '12', '123.jpeg'' at line 1
    at Query.Sequence._packetToError (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (Z:\Jura_SaaS\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (Z:\Jura_SaaS\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:309:12)
    --------------------
    at Pool.query (Z:\Jura_SaaS\node_modules\mysql\lib\Pool.js:199:23)
    at internal/util.js:297:30
    at new Promise (<anonymous>)
    at Pool.query (internal/util.js:296:12)
    at Z:\Jura_SaaS\routes\order.js:190:44
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19, 'd', 34.6, '12', '123.jpeg'' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "INSERT INTO orderitems (oid, description, weight, quantity, image_url) VALUES 19, 'd', 34.6, '12', '123.jpeg';"
}

NOTE: I have promisified pool.query that is the reason you don't see the callback format of it.

Answer 1

I had the same issue. Here is an article detailing your case. For your case I would do the following :

let orderID = 15; //It is a foreign key. Noted it from the table. 
itemParams = [
        [ orderID, 'd', 34.6, '12', '123.jpeg' ],
        [ orderID, 'd', 30.6, '2', '456.jpeg' ]
];

let addOrderItems = await pool.query(`INSERT INTO orderitems (oid, description, weight, quantity, image_url) VALUES ?`, [itemParams]);