[mysql] How do I do a bulk insert in mySQL using node.js

How would one do a bulk insert into mySQL if using something like https://github.com/felixge/node-mysql

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

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', '[email protected]', 1],
    ['john', '[email protected]', 2],
    ['mark', '[email protected]', 3],
    ['pete', '[email protected]', 4]
];
conn.query(sql, { params: values}, function(err) {
    if (err) throw err;
    conn.end();
});

Hope this helps someone.

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) => {...}
);

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.

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;
};

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)
});

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!

@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.

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. https://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.

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.

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.