Node.js MySQL Select from Table

  431 views   1 year ago NodeJS

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.

Author : Harsukh Makwana
Harsukh Makwana

Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]