Nodejs mysql insert multiple rows

Asked 10 years, 7 months ago

Viewed 202k times

Bulk inserts are possible by using nested array, see the github page

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

You just insert a nested array of elements.

An example is given in here

var mysql = require('mysql'); var conn = mysql.createConnection({ ... }); var sql = "INSERT INTO Test (name, email, n) VALUES ?"; var values = [ ['demian', '', 1], ['john', '', 2], ['mark', '', 3], ['pete', '', 4] ]; conn.query(sql, [values], function(err) { if (err) throw err; conn.end(); });

Note: values is an array of arrays wrapped in an array

[ [ [...], [...], [...] ] ]

There is also a totally different node-msql package for bulk insertion

answered Jan 10, 2013 at 13:44

Ragnar123Ragnar123

4,8744 gold badges22 silver badges34 bronze badges

19

I ran into this today (mysql 2.16.0) and thought I'd share my solution:

const items = [ {name: 'alpha', description: 'describes alpha', value: 1}, ... ]; db.query( 'INSERT INTO my_table (name, description, value) VALUES ?', [items.map(item => [item.name, item.description, item.value])], (error, results) => {...} );

answered Aug 26, 2018 at 22:04

3

@Ragnar123 answer is correct, but I see a lot of people saying in the comments that it is not working. I had the same problem and it seems like you need to wrap your array in [] like this:

var pars = [ [99, "1984-11-20", 1.1, 2.2, 200], [98, "1984-11-20", 1.1, 2.2, 200], [97, "1984-11-20", 1.1, 2.2, 200] ];

It needs to be passed like [pars] into the method.

honk

8,39111 gold badges74 silver badges77 bronze badges

answered May 16, 2017 at 5:27

CodendaalCodendaal

5075 silver badges14 bronze badges

3

I was looking around for an answer on bulk inserting Objects.

The answer by Ragnar123 led me to making this function:

function bulkInsert(connection, table, objectArray, callback) { let keys = Object.keys(objectArray[0]); let values = objectArray.map( obj => keys.map( key => obj[key])); let sql = 'INSERT INTO ' + table + ' (' + keys.join(',') + ') VALUES ?'; connection.query(sql, [values], function (error, results, fields) { if (error) callback(error); callback(null, results); }); } bulkInsert(connection, 'my_table_of_objects', objectArray, (error, response) => { if (error) res.send(error); res.json(response); });

Hope it helps!

answered Aug 3, 2017 at 22:41

All props to Ragnar123 for his answer.

I just wanted to expand it after the question asked by Josh Harington to talk about inserted IDs.

These will be sequential. See this answer : Does a MySQL multi-row insert grab sequential autoincrement IDs?

Hence you can just do this (notice what I did with the result.insertId):

var statement = 'INSERT INTO ?? (' + sKeys.join() + ') VALUES ?'; var insertStatement = [tableName, values]; var sql = db.connection.format(statement, insertStatement); db.connection.query(sql, function(err, result) { if (err) { return clb(err); } var rowIds = []; for (var i = result.insertId; i < result.insertId + result.affectedRows; i++) { rowIds.push(i); } for (var i in persistentObjects) { var persistentObject = persistentObjects[i]; persistentObject[persistentObject.idAttributeName()] = rowIds[i]; } clb(null, persistentObjects); });

(I pulled the values from an array of objects that I called persistentObjects.)

Hope this helps.

answered Dec 29, 2015 at 2:24

3

This is a fast "raw-copy-paste" snipped to push a file column in mysql with node.js >= 11

250k row in few seconds

'use strict'; const mysql = require('promise-mysql'); const fs = require('fs'); const readline = require('readline'); async function run() { const connection = await mysql.createConnection({ host: '1.2.3.4', port: 3306, user: 'my-user', password: 'my-psw', database: 'my-db', }); const rl = readline.createInterface({ input: fs.createReadStream('myfile.txt') }); let total = 0; let buff = []; for await (const line of rl) { buff.push([line]); total++; if (buff.length % 2000 === 0) { await connection.query('INSERT INTO Phone (Number) VALUES ?', [buff]); console.log(total); buff = []; } } if (buff.length > 0) { await connection.query('INSERT INTO Phone (Number) VALUES ?', [buff]); console.log(total); } console.log('end'); connection.close(); } run().catch(console.log);

answered May 21, 2019 at 15:19

Manuel SpigolonManuel Spigolon

9,2375 gold badges46 silver badges68 bronze badges

2

If Ragnar's answer doesn't work for you. Here is probably why (based on my experience) -

1. I wasn't using node-mysql package as shown my Ragnar. I was using mysql package. They're different (if you didn't notice - just like me). But I'm not sure if it has anything to do with the ? not working, since it seemed to work for many folks using the mysql package.

2. Try using a variable instead of ?

The following worked for me -

var mysql = require('node-mysql'); var conn = mysql.createConnection({ ... }); var sql = "INSERT INTO Test (name, email, n) VALUES :params"; var values = [ ['demian', '', 1], ['john', '', 2], ['mark', '', 3], ['pete', '', 4] ]; conn.query(sql, { params: values}, function(err) { if (err) throw err; conn.end(); });

Hope this helps someone.

answered Feb 8, 2017 at 0:31

2

In case that needed here is how we solved insert of array

request is from postman (You will look at "guests" )

{ "author_id" : 3, "name" : "World War II", "date" : "01 09 1939", "time" : "16 : 22", "location" : "39.9333635/32.8597419", "guests" : [2, 3, 1337, 1942, 1453] }

And how we scripted

var express = require('express'); var utils = require('./custom_utils.js'); module.exports = function(database){ var router = express.Router(); router.post('/', function(req, res, next) { database.query('INSERT INTO activity (author_id, name, date, time, location) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), date = VALUES(date), time = VALUES(time), location = VALUES(location)', [req.body.author_id, req.body.name, req.body.date, req.body.time, req.body.location], function(err, results, fields){ if(err){ console.log(err); res.json({ status: utils.respondMSG.DB_ERROR }); } else { var act_id = results.insertId; database.query('INSERT INTO act_guest (user_id, activity_id, status) VALUES ? ON DUPLICATE KEY UPDATE status = VALUES(status)', [Array.from(req.body.guests).map(function(g){ return [g, act_id, 0]; })], function(err, results, fields){ if(err){ console.log(err); res.json({ status: utils.respondMSG.DB_ERROR }); } else { res.json({ status: utils.respondMSG.SUCCEED, data: { activity_id : act_id } }); } }); } }); }); return router; };

answered Feb 22, 2017 at 20:38

SamirSamir

6,0954 gold badges37 silver badges39 bronze badges

I was having similar problem. It was just inserting one from the list of arrays. It worked after making the below changes.

1. Passed [params] to the query method.
2. Changed the query from insert (a,b) into table1 values (?) ==> insert (a,b) into table1 values ? . ie. Removed the paranthesis around the question mark.

Hope this helps. I am using mysql npm.

answered Mar 6, 2018 at 16:11

Few things I want to mention is that I'm using mysql package for making a connection with my database and what you saw below is working code and written for insert bulk query.

const values = [ [1, 'DEBUG', 'Something went wrong. I have to debug this.'], [2, 'INFO', 'This just information to end user.'], [3, 'WARNING', 'Warning are really helping users.'], [4, 'SUCCESS', 'If everything works then your request is successful'] ]; const query = "INSERT INTO logs(id, type, desc) VALUES ?"; const query = connection.query(query, [values], function(err, result) { if (err) { console.log('err', err) } console.log('result', result) });

answered Nov 23, 2018 at 17:50

SagarSagar

3,8753 gold badges29 silver badges36 bronze badges

Bulk insert in Node.js can be done using the below code. I have referred lots of blog for getting this work.

please refer this link as well. //www.technicalkeeda.com/nodejs-tutorials/insert-multiple-records-into-mysql-using-nodejs

The working code.

const educations = request.body.educations; let queryParams = []; for (let i = 0; i < educations.length; i++) { const education = educations[i]; const userId = education.user_id; const from = education.from; const to = education.to; const instituteName = education.institute_name; const city = education.city; const country = education.country; const certificateType = education.certificate_type; const studyField = education.study_field; const duration = education.duration; let param = [ from, to, instituteName, city, country, certificateType, studyField, duration, userId, ]; queryParams.push(param); } let sql = "insert into tbl_name (education_from, education_to, education_institute_name, education_city, education_country, education_certificate_type, education_study_field, education_duration, user_id) VALUES ?"; let sqlQuery = dbManager.query(sql, [queryParams], function ( err, results, fields ) { let res; if (err) { console.log(err); res = { success: false, message: "Insertion failed!", }; } else { res = { success: true, id: results.insertId, message: "Successfully inserted", }; } response.send(res); });

Hope this will help you.

answered Apr 11, 2020 at 0:26

If you want to insert object, use this:

currentLogs = [ { socket_id: 'Server', message: 'Socketio online', data: 'Port 3333', logged: '2014-05-14 14:41:11' }, { socket_id: 'Server', message: 'Waiting for Pi to connect...', data: 'Port: 8082', logged: '2014-05-14 14:41:11' } ]; console.warn(currentLogs.map(logs=>[ logs.socket_id , logs.message , logs.data , logs.logged ]));

The output will be:

[ [ 'Server', 'Socketio online', 'Port 3333', '2014-05-14 14:41:11' ], [ 'Server', 'Waiting for Pi to connect...', 'Port: 8082', '2014-05-14 14:41:11' ] ]

Also, please check the documentation to know more about the map function.

sɐunıɔןɐqɐp

2,95515 gold badges34 silver badges38 bronze badges

answered Sep 17, 2020 at 11:12

Client.createClient = (clientReqData, result) =>{ var command = 'INSERT INTO client (name,email,phone,country_code,city,state,address,salaes_account_manager,internal_notes) VALUES (?,?,?,?,?,?,?,?,?)' ; dbConn.query(command,[clientReqData.name, clientReqData.email,clientReqData.phone,clientReqData.country_code,clientReqData.city,clientReqData.state,clientReqData.address,clientReqData.salaes_account_manager,clientReqData.internal_notes],(err,res)=>{ if(err){ console.log(err) }else { client_id = res.insertId; var command = 'INSERT INTO client_contact_person (name, email ,phone,client_id) VALUES ?'; dbConn.query(command, [clientReqData.contact.map(item => [item.name, item.email, item.phone,client_id])], (err, res) => { if(err) throw err } ); result(null,res); } })

}

answered Sep 23, 2021 at 10:37

kushkush

4795 silver badges7 bronze badges

Not the answer you're looking for? Browse other questions tagged mysql node.js or ask your own question.