Node.js MySQL Result Object – Examples

Node.js MySQL Result Object

When a MySQL query is executed in Node.js using the mysql package, the callback function receives a result value. This value is commonly called the Node.js MySQL result object.

The result object does not have the same structure for every SQL query. A SELECT query returns rows from the table, while INSERT, UPDATE, and DELETE queries return an execution status object with properties such as affectedRows, insertId, warningCount, message, and changedRows.

In this tutorial, we will go through result objects returned by different SQL queries and how to access their properties using the DOT operator. We will also see when the result should be treated as an array of rows and when it should be treated as a query status object.

In most callback examples, the function has the form function (err, result, fields). The result argument contains the rows or execution status. The fields argument is mainly useful for SELECT queries because it contains column metadata.

Node.js MySQL result object returned by SELECT, INSERT, UPDATE, and DELETE

The contents of the result object depend on the SQL query made to MySQL Server. The following table shows what you can expect from common MySQL queries in Node.js.

MySQL Query	Result Object	How to read it in Node.js
SELECT FROM	Result set containing records	Read it as an array of row objects, such as result[0].name.
INSERT INTO	Object containing execution status	Read properties such as affectedRows, insertId, and warningCount.
UPDATE	Object containing execution status	Read affectedRows and changedRows to understand matched and changed rows.
DELETE FROM	Object containing execution status	Read affectedRows to know how many rows were deleted.

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
• Node.js MySQL fields object in SELECT queries
• Node.js MySQL result object checklist
• Node.js MySQL result object FAQs

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

For a SELECT query, result is an array-like collection of row objects. Each row object has properties matching the selected column names. In the above example, row.name reads the name column from each row in the students table.

You can also access a specific row directly by its index. For example, result[0] gives the first row returned by the query.

con.query("SELECT * FROM students", function (err, result, fields) {
  if (err) throw err;

  if (result.length > 0) {
    console.log("First student name: " + result[0].name);
    console.log("First student marks: " + result[0].marks);
  }
});

If the query does not return any matching row, result is still an array, but its length is 0. Always check result.length before reading a row by index.

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.

For an INSERT query, affectedRows tells how many rows were inserted. If the table has an auto-increment primary key and a single row is inserted, insertId usually contains the generated id for that inserted row.

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

In this example, affectedRows is 3 because three records are inserted. warningCount is 0, which means MySQL did not report warnings for this insert operation.

Node.js MySQL insertId in single-row INSERT result object

When you insert one row into a table that has an auto-increment column, the result object can be used to read the generated id.

var sql = "INSERT INTO students (name, rollno, marks) VALUES (?, ?, ?)";
var values = ["Kiran", 18, 91];

con.query(sql, values, function(err, result) {
  if (err) throw err;

  console.log("Inserted rows: " + result.affectedRows);
  console.log("Inserted row id: " + result.insertId);
});

If the table does not use an auto-increment column, or if the query inserts multiple rows in a way where a single id is not meaningful, insertId may not be useful for your application logic.

Example 3 – Result Object of MySQL UPDATE Query

In this example, we will execute an UPDATE query on a MySQL table. The query() function returns a result object with properties like fieldCount, affectedRows, warningCount, message, and changedRows.

We will access these properties of the result object using the 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

For an UPDATE query, affectedRows and changedRows are both important. affectedRows shows the number of rows matched by the WHERE condition. changedRows shows how many of those rows were actually modified.

These two values can be different. If a row already has the new value, it may be counted as matched but not changed.

con.query("UPDATE students SET marks = ? WHERE rollno = ?", [84, 16], function(err, result) {
  if (err) throw err;

  console.log("Rows matched: " + result.affectedRows);
  console.log("Rows actually changed: " + result.changedRows);
});

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 :

For a DELETE query, affectedRows tells how many rows were deleted. In this example, six rows match the condition rollno > 10, so six rows are removed from the table.

Be careful with DELETE queries. If the WHERE clause is missing, MySQL will delete all rows from the table. Check the condition before running the query on important data.

Node.js MySQL fields object in SELECT queries

The third callback argument, usually named fields, contains metadata about the columns returned by a query. It is most useful with SELECT queries when you want to inspect column names or table information programmatically.

con.query("SELECT name, rollno, marks FROM students", function(err, result, fields) {
  if (err) throw err;

  fields.forEach(function(field) {
    console.log(field.name);
  });
});

The above example prints the selected column names. For normal application code, you usually read data from result. Use fields when you need details about the columns themselves.

Node.js MySQL result object properties for write queries

For INSERT, UPDATE, and DELETE, the result object is an execution status object. The following properties are commonly checked after write queries.

Property	Useful for	Meaning
affectedRows	INSERT, UPDATE, DELETE	Number of rows affected or matched by the query.
insertId	INSERT	Auto-increment id generated by an insert operation, when available.
changedRows	UPDATE	Number of matched rows whose values actually changed.
warningCount	INSERT, UPDATE, DELETE	Number of warnings reported by MySQL for the query.
message	INSERT, UPDATE, DELETE	Text status message returned by MySQL.
fieldCount	Write query status	Usually 0 for write queries because no result fields are returned.

Accessing Node.js MySQL result object values with dot and bracket notation

When column names are normal JavaScript-friendly names, the DOT operator is simple and readable.

console.log(result[0].name);
console.log(result[0].marks);

If a column name contains a space, hyphen, reserved word style, or is built dynamically, use bracket notation.

console.log(result[0]["student name"]);
console.log(result[0]["total-marks"]);

For cleaner application code, prefer simple column aliases in the SQL query and then read those aliases from the result rows.

SELECT student_name AS name, total_marks AS marks FROM students;

Common mistakes while reading Node.js MySQL result objects

• Treating SELECT result as a single object: A SELECT query returns an array of rows, so use result[0] or loop through result.
• Reading insertId after every INSERT: insertId is useful only when MySQL generates an auto-increment value.
• Using affectedRows as changedRows: In UPDATE queries, affectedRows can count matched rows, while changedRows counts rows that actually changed.
• Ignoring empty SELECT results: Check result.length before reading result[0].
• Assuming fields is always needed: Most application logic reads data from result; fields is mainly for column metadata.

Node.js MySQL result object checklist

• For a SELECT query, confirm that the code treats result as an array of row objects.
• Before reading result[0], check that result.length is greater than 0.
• For an INSERT query, check affectedRows and use insertId only when the table has an auto-increment id.
• For an UPDATE query, check both affectedRows and changedRows.
• For a DELETE query, check affectedRows and verify that the WHERE condition is correct.
• Use fields only when column metadata is needed.

Node.js MySQL result object FAQs

What is the result object in Node.js MySQL?

The result object is the value returned to the query callback after MySQL executes a query. For SELECT queries, it contains rows from the table. For INSERT, UPDATE, and DELETE queries, it contains execution status properties such as affectedRows, insertId, warningCount, and changedRows.

How do I access rows from a SELECT result object in Node.js?

Use array access and then read the column property. For example, result[0].name reads the name column from the first row. To read all rows, loop through the result array.

What is affectedRows in a Node.js MySQL result object?

affectedRows shows how many rows were affected or matched by the query. It is commonly used after INSERT, UPDATE, and DELETE queries to confirm how many records were involved.

What is changedRows in a Node.js MySQL UPDATE result?

changedRows shows how many rows were actually changed by an UPDATE query. If a row matches the WHERE condition but already has the new value, it may be counted in affectedRows but not in changedRows.

What is the fields argument in the Node.js MySQL query callback?

The fields argument contains metadata about selected columns, such as column names. It is mainly useful for SELECT queries. Most application code reads actual table data from the result argument.

Key takeaways for Node.js MySQL result objects

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.

Remember the main difference: SELECT returns rows, while INSERT, UPDATE, and DELETE return execution status. Once this distinction is clear, reading values from a Node.js MySQL result object becomes straightforward.