Node.js MySQL INSERT INTO Query Examples

Node.js MySQL INSERT INTO

Node.js MySQL INSERT INTO Query is used to insert one or more records into MySQL Table.

In this tutorial, we will learn how to insert one or more documents to a MySQL database from Node.js program using mysql module.

We will cover the following scenarios with examples.

• Node.js MySQL Example to INSERT a record INTO a table
• Node.js MySQL Example to INSERT multiple records INTO a table
• Accessing properties of Result Object

Example 1 – INSERT Row into MySQL Table via Node.js

In this example, we will connect to MySQL database, and insert a record into students table.

insertIntoExample.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("INSERT INTO students (name,rollno,marks) values ('Anisha',12,95)", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Run above Node.js MySQL program in Terminal.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node insertIntoExample.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

Example 2 – INSERT Multiple Rows into MySQL Table via Node.js

In this example, we will connect to MySQL database, and insert three records into students table in a single statement.

insertMulIntoExample.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  var records = [
    ['Miley', 13, 85],
    ['Jobin', 14, 87],
    ['Amy', 15, 74]
  ];
  con.query("INSERT INTO students (name,rollno,marks) VALUES ?", [records], function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Run above Node.js MySQL program in Terminal.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node insertMulIntoExample.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '&Records: 3  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }

Accessing properties of Result Object

Once the INSERT INTO Query is executed, MySQL Server responds with a result object. The properties of result object like affectedRows, serverStatus, changedRows etc., could be accessed using DOT (.) operator.

InsertMulIntoExample.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  var records = [
    ['Jack', 16, 82],
    ['Priya', 17, 88],
    ['Amy', 15, 74]
  ];
  con.query("INSERT INTO students (name,rollno,marks) VALUES ?", [records], function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
    console.log("Number of rows affected : " + result.affectedRows);
    console.log("Number of records affected with warning : " + result.warningCount);
    console.log("Message from MySQL Server : " + result.message);
  });
});

Open a terminal or command prompt and run this script using node command as shown in the following.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node InsertMulIntoExample.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '&Records: 3  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }
Number of rows affected : 3
Number of records affected with warning : 0
Message from MySQL Server : &Records: 3  Duplicates: 0  Warnings: 0

Conclusion

In this Node.js Tutorial – Node.js MySQL – Node.js MySQL INSERT INTO Query, we have learned to insert one or more records into a table and access properties of the result object.