Node.js - MySQL Select From

---

---

The most frequent operation performed on a MySQL database is retrieval of data from the tables. The SELECT query corresponds to the Retrieve action in the CRUD acronym. To perform retrieval with a Node.js program, the SELECT query string should be passed to the query() method of MySQL connection object.

The SELECT FROM statement in MySQL has the following syntax −

SELECT field1, field2, ... FROM table_name;

To select data from all the fields defined in the table, you can use the Asterix (*) character.

SELECT * FROM table_name;

There are additional clauses that can be used with the primary SELECT statement, such as LIKE, WHERE, ORDER BY etc.

The SELECT statement returns a resultset, containing the list of rows retrieved from the table.

The MySQL command-line client is like the Node.js REPL environment. The resultset retrieved by the SELECT statement is displayed immediately after the query statement.

mysql> use mydb;
Database changed
mysql> SELECT name, age, salary FROM employee;
+-------+------+--------+
| name  | age  | salary |
+-------+------+--------+
| Ravi  |   25 |  25000 |
| Anil  |   26 |  30000 |
| Meena |   26 |  27000 |
+-------+------+--------+

However, with Node.js, when the SELECT query is passed to the query() method of MySQL connection object, it returns an array of RowDataPacket objects. To process the retrieved data, it must be JSONified.

Example

In the following code, we pass the SELECT query string to the con.query() method and display the resultset on the console.

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`SELECT name,salary FROM employee;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });
   con.end();
});

Output

[
  RowDataPacket { name: 'Ravi', salary: 25000 },
  RowDataPacket { name: 'Anil', salary: 30000 },
  RowDataPacket { name: 'Meena', salary: 27000 }
]

Instead of the raw resultset, we can use the JSON.stringify() method to convert the RowDataPacket object to string.

var qry =`SELECT name,salary FROM employee;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(JSON.stringify(results));
   });
   con.end();
});

Output

[{"name":"Ravi","salary":25000},{"name":"Anil","salary":30000},{"name":"Meena","salary":27000}]

Example

In this example, the * wild card is used to retrieve data from all the fields in employee table. We also employ forEach loop to iterate row-wise over the resultset returned by SELECT query. The value of each field in the row is fetched by the putting the field-name in the square brackets. For example, the ID of a row is given by row[‘id’].

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`SELECT * FROM employee;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      results.forEach((row) => {
         console.log(`ID: ${row['id']}, NAME: ${row['name']}, AGE: ${row['age']}, SALARY: ${row['salary']}`);
      });
   });
   con.end();
});

Output

ID: 1, NAME: Ravi, AGE: 25, SALARY: 25000
ID: 2, NAME: Anil, AGE: 26, SALARY: 30000
ID: 3, NAME: Meena, AGE: 26, SALARY: 27000

You can include derived or computed column in the field list of SELECT statement, where its value depends on an expression involving the fields defined in the structure. You can also give an alias name to a field with AS keyword.

SELECT expression AS COLUMN_name FROM table_name;

In the following example, the SELECT statement has a TAX column. Its value is computed as 5 percent of salary field.

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`SELECT name, salary, salary*0.05 as tax FROM employee;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(JSON.stringify(results));
   });
   con.end();
});

Output

[{"name":"Ravi","salary":25000,"tax":1250},{"name":"Anil","salary":30000,"tax":1500},{"name":"Meena","salary":27000,"tax":1350}]