How to batch insert data into MySQL in NodeJS?

Question

I am developing a REST API. One of the end points I have recieve a list of data like below.

[
    {
        
        "iduser": 3,
        "title": "House in kandala",
        "description": "Built a house in kandala area"
    },
    {
        
        "iduser": 3,
        "title": "House in NYC",
        "description": "Built a house in greater NYC area"
    }
]

I need to save the list into the database. Below is my code.

const mysql = require('mysql2');
const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');

const con = mysql.createConnection({
    host: prop.get('server.host'),
    user: prop.get("server.username"),
    password: prop.get("server.password"),
    port: prop.get("server.port"),
    database: prop.get("server.dbname")
});


exports.saveSellerPortfolioItem = (event, context, callback) => {

    context.callbackWaitsForEmptyEventLoop = false;

    if (event.body == null && event.body == undefined) {
        var response = errorCodes.missing_parameters;
        callback(null, response)
    }
    else {
        let body = JSON.parse(event.body)
        console.log("body", body);

        let iduser = Number(body.iduser);
        let title = body.title;
        let description = body.description;
     

        if (isNaN(iduser)) {
            var response = errorCodes.invalid_parameter;
            callback(null, response);
        }
        else {
            // allows for using callbacks as finish/error-handlers
            const sql = "INSERT INTO seller_portfolio_item (iduser, title, description) VALUES (?,?,?)";
            con.execute(sql, [iduser, title, description], function (err, result) {
                if (err) {
                    console.log(err.toString());

                    if (err.toString().indexOf('cannot be null') >= 0) {
                        var response = errorCodes.not_null_parameters;
                        callback(null, response);
                    }
                    var response = errorCodes.internal_server_error;
                    callback(null, response);

                }
                else {
                    var response = {
                        "statusCode": 200,
                        "headers": {
                            "Content-Type": "application/json"
                        },
                        "body": JSON.stringify({ insertId: result.insertId }),
                        "isBase64Encoded": false
                    };
                    callback(null, response)
                }
            });
        }


    }
};

My code is capable of inserting just one record, not suitable to save multiple when I am sending a list. As a result, client program will have to call the same method again and again in a loop.

How can I read the list and insert multiple records ?

Answer 1

You are correct that going forward it is better to use mysql instead of mysql2. Below is one approach that can be used to batch insert multiple records.

Be sure to run npm install mysql --save to ensure you have to necessary package installed.

Working with multiple records requires some additional thinking and planning as well. You should consider:

• does your table contain any unique keys other than the primary?
• is it possible your API function will ever attempt to insert a duplicate?
• in the event of a duplicate how should it be handled?
• do you need to know the insert ID for every new record created?
• will every object in your list always have the same number of entries, the same keys, and expected values?

Depending on your answers to the above considerations the example I provided below would require additional code and complications. This example is the simplest implementation of the idea.

// package changed, remember to npm install…
const mysql = require('mysql');

const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');

const con = mysql.createPool({
  connectionLimit: 10,
  host: prop.get('server.host') || '127.0.0.1',
  user: prop.get("server.username") || 'local_user',
  password: prop.get("server.password") || 'local_password',
  database: prop.get("server.dbname") || 'local_database',
  multipleStatements: true, // necessary to run chained queries
  charset: 'utf8mb4' // necessary if you might need support for emoji characters - table charset must match
});

exports.saveSellerPortfolioItem = (event, context, callback) => {
  context.callbackWaitsForEmptyEventLoop = false;

  // It is better to check for the existence of your
  // expected request body in the controller stage of
  // your app but I've included this for consistency
  // with your original code.
  let query_object = event.body ? JSON.parse(event.body) : null;
  console.log('query_object', query_object);
  if (!query_object.length) {
    let response = errorCodes.missing_parameters;
    callback(null, response)
  }

  else {
    // use the keys of the first object to define the field names.
    // you don't have to use this approach but it provides flexibility
    // if you will not always use the same fields
    let keys = Object.keys(query_object[0]);
    // map the values into a supported format
    let values = query_object.map( obj => keys.map( key => obj[key]));
    let sql = 'INSERT INTO seller_portfolio_item (' + keys.join(',') + ') ?;'
    con.query(sql, values, function(error, results, fields) {
      if (error) callback(null, error);

      // when inserting multiples you will only get back the
      // insert id of the first record. if there are updates
      // due to duplicate keys, you won't even get that.
      // results will look like this:
      console.log(results);
      // Expected output
      // OkPacket {
      //   fieldCount: 0,
      //   affectedRows: 3,
      //   insertId: 1,
      //   serverStatus: 2,
      //   warningCount: 6,
      //   message: '&Records: 3  Duplicates: 0  Warnings: 6',
      //   protocol41: true,
      //   changedRows: 0
      // }

      let response = {
        "statusCode": 200,
        "headers": {
          "Content-Type": "application/json"
        },
        "body": JSON.stringify({ records_inserted: results.affectedRows }),
        "isBase64Encoded": false
      };
      callback(null, response)
    });
  }
};