Node.js MySQL Select from Table

In this article, we will guide you to how to select data from MySQL table using Node.js. We will use MySQL select, where, order by etc. statement to get data from MySQL server. 

Perform the following steps to select data from MySQL database server.

Step 1: First create connection between MySQL server and Node.js.

Step 2: After database connection created, use conn.query() method to run INSERT query into MySQL database.

var query = `SELECT * FROM visitors`;

Step 3: After the query executed, close the connection using conn.end() method.

We have created below select_rows.js file with full example.

const mysql = require('mysql');

const conn = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    database: 'charts',
    user: 'root',
    password: 'root',
});

conn.connect((err) => {
    if (err) {
        return console.log(err.message);
    } else {
        // select query
        var query = `SELECT * FROM visitors`;
        // query to database
        conn.query(query, function(err, response, result) {
            if (err) {
                return console.log(err.message);
            } else {
                console.log(response);
            }
        });
    }
    // close the connection
    conn.end();
});

Now run the file into Node server.

node select_rows.js

The application will response with data.

[
    RowDataPacket { id: 1, browser: 'Chrome', percentage: 69.28 },
    RowDataPacket { id: 2, browser: 'Edge', percentage: 7.75 },
    RowDataPacket { id: 3, browser: 'Firefox', percentage: 7.48 },
    RowDataPacket {id: 4, browser: 'Internet Explorer', percentage: 5.21 },
    RowDataPacket { id: 5, browser: 'Safari', percentage: 3.73 },
    RowDataPacket { id: 6, browser: 'Opera', percentage: 1.12 },
    RowDataPacket { id: 7, browser: 'Others', percentage: 5.43 }
]

WHERE and ORDER BY query

We may also pass additional WHERE and ORDER BY statement in SELECT statement. This way, we can filter the data we want.

var query = `SELECT * FROM visitors WHERE percentage > 6 ORDER BY percentage DESC`;

We always want to pass dynamic value into where statement for filtering data. So change variable value from static value in the statement.

var minimumPercentage = 6;
var query = `SELECT * FROM visitors WHERE percentage > ${minimumPercentage} ORDER BY percentage ASC`;

Here is the full code:

const mysql = require('mysql');

const conn = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    database: 'charts',
    user: 'root',
    password: 'root',
});

conn.connect((err) => {
    if (err) {
        return console.log(err.message);
    } else {
        // select query
        var minimumPercentage = 6;
        var query = `SELECT * FROM visitors WHERE percentage > ${minimumPercentage} ORDER BY percentage ASC`;
        // query to database
        conn.query(query, function(err, response, result) {
            if (err) {
                return console.log(err.message);
            } else {
                console.log(response);
            }
        });
    }
    // close the connection
    conn.end();
});

This will return rows only which has percentage greater than 6.

[
  RowDataPacket { id: 3, browser: 'Firefox', percentage: 7.48 },
  RowDataPacket { id: 2, browser: 'Edge', percentage: 7.75 },
  RowDataPacket { id: 1, browser: 'Chrome', percentage: 69.28 }
]

You can access specific row using array index. For example result[1] will return second item from array.

RowDataPacket { id: 2, browser: 'Edge', percentage: 7.75 },

Or even you want specific property from the object property, For example, to get browser value from object,

console.log(response[1].browser); // Edge

I hope it will help you.