Insert multiple/bulk records into mysql DB using node.js

Some cases we get requirements for bulk data insertion to MySQL database. It is a simple process. In this tutorial, we will learn about how to handle multiple rows or arrays of data insertion. Below are the steps to guide archive the target.

Node Js: It should be installed in your system.

Below are the processes to set up the project,

1. Create project folder(here it is “mysql-bulkinsert-opration”)

2. Navigate to the folder using the command line (cmd)

3. Type “npm init” and press enter . It will create package.json for the project.
[pullquote-right]“package.json conatins all the metadata information required for the project.”[/pullquote-right]

\mysql-bulkinsert-opration> npm init

4. Provide tha asked details and enter yes. Now you are ready with package.json.

Below is the project structure :

As we are doing MySql database operation, we need to have mysql module.

5. Install mysql npm package using below command. It will add to you package.json dependancies as well.

npm install --save mysql

package.json would appear like below,

{
  "name": "mysql-bulkinsert-opration",
  "version": "1.0.0",
  "main": "server.js",
  "dependencies": {
    "mysql": "^2.15.0"
  },
  "devDependencies": {},
  "scripts": {
    "start": "node server"
  },
  "author": "",
  "license": "ISC",
  "description": "bulk insert into MySql DB"
}

Here we are using the mysql module to connect to the database. Below piece of code will guide to successfully connect to the database and return the connection operation for further operation.

We need to mysql module using “require(‘mysql’)” statement. Then we need to provide information like host, port, password and database for creating a connection object. We are using “createPool” method of mysql for persistent connection to the database. “getConnection” to establish the connection to the database.

var mysql = require('mysql');

 //create a connection pool for persitant connection
var connection = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'tda_db',
    debug: false,
});
 
// establish connection to DB
connection.getConnection((err,connect)=>{
    if(err)
    console.log('not able to connect. Error occured while connecting')
    console.log('connection established')
})

//export the connection object
module.exports=connection;

In this section, we are going to create a table called “person”. When code will run, it will check in the database, if the table exists or not. If it is not present, it will create.

We will have created an array of values which will be used as placeholder values. You can read more about it by clicking here . Below is the format

.query(sqlString, values, callback)

You can also use escaping query to prevent SQL injection. i.e.

 connection.escape('insert param here') 

complete code for database operation:

 const connectdb=require('./db.connect')

//checking if table exits or not. if not create the table before insert operation.
connectdb.query('desc person',(err,result,fields)=>{
    if(err){
        console.log(err)
        if(err['errno']==1146){
        let querycreate='CREATE TABLE IF NOT EXISTS `person` ( `first_name` varchar(30) NOT NULL,`last_name` varchar(30) NOT NULL)'
        connectdb.query(querycreate,(err,result,fields)=>{
            if(err)
            console.log(err)
            console.log(result)
        })
    }
}
})
 

//data to be inserted into table
var values = [
                [ 'Jack', 'Patel'],
                [ 'Aftab', 'Ali'],
                
];
 
// query for insert data to the person table
let query = "INSERT INTO person ( first_name, last_name) VALUES ?";
 
connectdb.query(query, [values], function(err, result) {
    if(err)
    console.log(err)
    console.log(result);
});