Node.js MySQL – SELECT FROM Query

We can connect to and access rows of a MySQL Table from Node.js program.

In this tutorial, we will learn how to execute SELECT FROM query from Node.js program to access data of a MySQL Table.

MySQL SELECT Query is used to get one or more rows from MySQL Table.

We shall go through following scenarios with examples.

MySQL Table

We shall use the following MySQL Table, in the following examples.

DATABASE : studentsDB

Table: studends

mysql> select * from students;
+----------+--------+-------+
| name     | rollno | marks |
+----------+--------+-------+
| John     |      1 |    74 |
| Arjun    |      2 |    74 |
| Prasanth |      3 |    77 |
| Adarsh   |      4 |    78 |
| Raja     |      5 |    94 |
| Sai      |      6 |    84 |
| Ross     |      7 |    54 |
| Monica   |      8 |    86 |
| Lee      |      9 |    98 |
| Bruce    |     10 |    92 |
| Sukumar  |     11 |    99 |
+----------+--------+-------+
11 rows in set (0.01 sec)

Example 1 – MySQL SELECT FROM Query via Node.js

In this example, we select all the rows of the MySQL table. The result contains all the rows returned by the SELECT Query.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query 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
    console.log(result);
  });
});

Output

$ node example.js 
[ RowDataPacket { name: 'John', rollno: 1, marks: 74 },
  RowDataPacket { name: 'Arjun', rollno: 2, marks: 74 },
  RowDataPacket { name: 'Prasanth', rollno: 3, marks: 77 },
  RowDataPacket { name: 'Adarsh', rollno: 4, marks: 78 },
  RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Sai', rollno: 6, marks: 84 },
  RowDataPacket { name: 'Ross', rollno: 7, marks: 54 },
  RowDataPacket { name: 'Monica', rollno: 8, marks: 86 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'Bruce', rollno: 10, marks: 92 },
  RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 } ]

Example 2 – Select only Specific Columns of MySQL Table via Node.js

In this example, we will select only two columns: name and marks from MySQL table.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query 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 name,marks 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
    console.log(result);
  });
});

Output

$ node example.js 
[ RowDataPacket { name: 'John', marks: 74 },
  RowDataPacket { name: 'Arjun', marks: 74 },
  RowDataPacket { name: 'Prasanth', marks: 77 },
  RowDataPacket { name: 'Adarsh', marks: 78 },
  RowDataPacket { name: 'Raja', marks: 94 },
  RowDataPacket { name: 'Sai', marks: 84 },
  RowDataPacket { name: 'Ross', marks: 54 },
  RowDataPacket { name: 'Monica', marks: 86 },
  RowDataPacket { name: 'Lee', marks: 98 },
  RowDataPacket { name: 'Bruce', marks: 92 },
  RowDataPacket { name: 'Sukumar', marks: 99 } ]

Example 3 – Access Result Object of MySQL SELECT FROM Query via Node.js

In this example, we will access rows from Result Object using index, columns and DOT operator.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query 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)
	});
  });
});

Output

$ node example.js 
John
Arjun
Prasanth
Adarsh
Raja
Sai
Ross
Monica
Lee
Bruce
Sukumar

Example 4 – Fields Object of MySQL SELECT FROM Query via Node.js

Fields contain information about columns of table. Each field contains all information about a column.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query 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 fields object
	// iterate for all the rows in fields object
	Object.keys(fields).forEach(function(key) {
	  var field = fields[key];
	  console.log(field)
	});
  });
});

Output

$ node example.js 
FieldPacket {
  catalog: 'def',
  db: 'studentsDB',
  table: 'students',
  orgTable: 'students',
  name: 'name',
  orgName: 'name',
  charsetNr: 33,
  length: 150,
  type: 253,
  flags: 0,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }
FieldPacket {
  catalog: 'def',
  db: 'studentsDB',
  table: 'students',
  orgTable: 'students',
  name: 'rollno',
  orgName: 'rollno',
  charsetNr: 63,
  length: 11,
  type: 3,
  flags: 0,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }
FieldPacket {
  catalog: 'def',
  db: 'studentsDB',
  table: 'students',
  orgTable: 'students',
  name: 'marks',
  orgName: 'marks',
  charsetNr: 63,
  length: 11,
  type: 3,
  flags: 0,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }

You may use the elements of a field object using dot operator. Example field.catalog, field.name, field.type, etc.

Conclusion

In this Node.js Tutorial – Node.js MySQL – Node.js MySQL SELECT FROM query, we have learnt to fetch records of table from MySQL database, and to use result object and fields object.