NodeJS MySQL Select Record

MySQL is a relational database management system that stores data in one or more tables of rows and columns and uses structured query languages to perform different operations on them.

MySQL can be used in NodeJS to store large amounts of data, and we can retrieve data quickly due to its simple structure using SQL queries.

Selecting Records in NodeJS Using MySQL

The records are selected in NodeJS from the MySQL database by passing the SQL query of selecting records as an argument to the query() method provided by the MySQL connection object. This connection object is created during the connection of NodeJS and MySQL.

Selecting All Records in NodeJS

The SQL query is given below for selecting all the records from a table.

SELECT * FROM tablename

We have to pass this query in the query() method in order to run it. This method also takes a callback function as an argument in which we can check for the error and get the selected records.

The code snippet for selecting all the records from a table in NodeJS is given below.

con.query("SELECT * FROM tablename", function (err, result) {
    if (err) throw err;
    console.log(result);
});

where

con is the connection object,
tableName is the name of the table from which the record is retrieved,
* indicates that we are setting all columns

Example:

Let’s see an example of selecting a table’s records using NodeJS.

const mysql = require('mysql');

const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "newdatabase"
});

con.connect(function (err) {
    if (err) {
        throw err;
    }
});

con.query("CREATE TABLE temptable (id INT, name VARCHAR(255),email VARCHAR(255), age INT)", function (err, result) {
    if (err) throw err;
});

var values = [  
    ['1', 'Aditya', '[email protected]', '22'],  
    ['2', 'Example', '[email protected]', '22'], 
    ['3', 'Rack', '[email protected]', '17'],  
    ['4', 'Jack', '[email protected]', '15'],
]; 

con.query("INSERT INTO temptable VALUES ?", [values], function (err, result) {
    if (err) throw err;
});

con.query("SELECT * FROM temptable", function (err, result) {
    if (err) throw err;
    console.log(result);
});

In the above code, we have connected the NodeJS to the MySQL server using the createConnection() and connect() methods which we have covered in MySQL connections using NodeJS. Then we created a new table and inserted multiple records. We have also covered the process of creating a new table and inserting multiple records in separate tutorials NodeJS MySQL Create Table and NodeJS MySQL Insert Record if you want to read them. Then we passed the query of selecting all the records from that table and printing the result in the console.

Run the Application:

Create a JavaScript file ‘app.js’ and write the above code, locate the file in the terminal, and type the below command.

node app.js

Make sure that you have run the MySQL server in the background. For this, you can use XAMPP which is extremely fast, easy to install, and can run a MySQL server in one click.

Output:

Selecting Particular Column Records in NodeJS

We can select some particular columns to retrieve their record by passing the columns name after the SELECT statement.

The code snippet for selecting the records of particular columns is given below.

con.query("SELECT name, email FROM tablename", function (err, result) {
    if (err) throw err;
    console.log(result);
});

where

con is the connection object,
tableName is the name of the table from which the record is retrieved,
columnName is the name of the column that records we want to retrieve

Example:

For selecting the record of particular columns, we can use the previous example by replacing the * sign with the columns’ name which data you want to select.

const mysql = require('mysql');

const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "newdatabase"
});

con.connect(function (err) {
    if (err) {
        throw err;
    }
});

con.query("CREATE TABLE temptable (id INT, name VARCHAR(255),email VARCHAR(255), age INT)", function (err, result) {
    if (err) throw err;
});

var values = [  
    ['1', 'Aditya', '[email protected]', '22'],  
    ['2', 'Example', '[email protected]', '22'], 
    ['3', 'Rack', '[email protected]', '17'],  
    ['4', 'Jack', '[email protected]', '15'],
]; 

con.query("INSERT INTO temptable VALUES ?", [values], function (err, result) {
    if (err) throw err;
});

con.query("SELECT name, email FROM temptable", function (err, result) {
    if (err) throw err;
    console.log(result);
});

Run the Application:

Create a JavaScript file ‘app.js’ and write the above code, locate the file in the terminal, and type the below command.

node app.js

Make sure that you have run the MySQL server in the background.

Output:

Summary of MySQL Database Selection in NodeJS

We can select data from a table of MySQL database in NodeJs by executing the SQL query of selecting records using the query() method. We can also select records columns-wise to get the data of particular columns, this will enable us to filter the irrelevant data. Hope this tutorial helps to learn the process of selecting records from a table in NodeJS.