Node.js MySQL Result Object – Examples

Node.js MySQL Result Object

When a MySQL Query is executed in Node.js, an object called Result Object is returned to the callback function.

The Result Object contains result set or properties that provide information regarding the execution of a query in MySQL Server.

In this tutorial, we will go through Result objects returned by different SQL queries and how to access their properties using DOT operator.

The contents of Result Object depends on the SQL query made to MySQL Server. Following table contents describe the result object for queries like select, insert, update and delete.

MySQL Query	Result Object
SELECT FROM	Result Set containing Record
INSERT INTO	Object containing Execution Status
UPDATE	Object containing Execution Status
DELETE FROM	Object containing Execution Status

We shall see how to access properties of records in a result set and how to access properties of execution status with the help of following examples.

• MySQL SELECT FROM Query – Accessing ResultSet
• MySQL INSERT INTO Query – Accessing properties of Result Object
• MySQL UPDATE Query -Accessing properties of Result Object
• MySQL DELETE FROM Query -Accessing properties of Result Object

Example 1 – MySQL SELECT FROM Query – Accessing ResultSet

We can access the records in Result Set as an array and properties of a record using DOT (.) Operator.

In this example, we will execute “SELECT FROM” SQL query, and we access the rows and column values using DOT operator.

example.js

// Node.js MySQL Result Object Example
// 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("SELECT * FROM students", 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
    // iterate for all the rows in result
    Object.keys(result).forEach(function(key) {
      var row = result[key];
      console.log(row.name)
    });
  });
});

Run the above program using node in Terminal or Command Prompt.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node example.js 
John
Arjun
Prasanth
Adarsh
Raja
Sai
Ross
Monica
Lee
Bruce
Sukumar

Example 2 – Result Object of MySQL INSERT INTO Query

In this example, we will execute “INSERT INTO” SQL query. query() method returns an object with properties specifying the result of this insert operations. The result object contains fields like fieldCount, affectedRows, insertId, etc. We will access these fields using DOT operator on the result object.

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

Run the above program using node in Terminal or Command Prompt.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node example.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

Example 3 – Result Object of MySQL UPDATE Query

In this example, we will execute DELETE query on MySQL Table. query() function returns Result object with properties like fieldCount, affectedRows, warningCount, message, etc.

We will these access properties of result object using DOT Operator.

example.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("UPDATE students SET marks=84 WHERE marks=74", 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);
  });
});

Run the above program using node in Terminal or Command Prompt.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node example.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 3  Changed: 3  Warnings: 0',
  protocol41: true,
  changedRows: 3 }
Number of rows affected : 3
Number of records affected with warning : 0
Message from MySQL Server : (Rows matched: 3  Changed: 3  Warnings: 0

Example 4 – Result Object of MySQL DELETE FROM Query

In this example, we will execute DELETE query on MySQL Table. query() function returns Result object with properties like fieldCount, affectedRows, warningCount, message, etc.

We will these access properties of result object using DOT Operator.

example.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 connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("DELETE FROM students WHERE rollno>10", 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);
  });
});

Run the above program using node in Terminal or Command Prompt.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node example.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 6,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
Number of rows affected : 6
Number of records affected with warning : 0
Message from MySQL Server :

Conclusion

In this Node.js Tutorial – Node.js MySQL – Result Object, we have learnt to access records of a result set and also went through examples to access properties of result object containing information about query execution.