Node.js MySQL SELECT

In this post, we will discuss how to select rows from an MySQL Table in XAMPP Server using Node.js Script.

Command to install the mysql package:

CopiedCopy Code
npm install mysql
				

SELECT Clause

SELECT Clause in MySQL is used to return the records from the specified table. It can also be possible to select particular or all columns from the table.

Steps:

Now let's see steps

  1. First start your XAMPP Server (Both Apache and MySQL).
  2. Open Notepad or any text-editor and write the Node.js script
  3. In that script, first we have to load the mysql package using the below syntax
  4. var mysql_package = require('mysql');
  5. Create the connection using the server,username and password.
  6. CopiedCopy Code
    
    var connection_data = mysql_package.createConnection({
      host: "localhost",
      user: "root",
      password: "",
      database:"database_name"
    });
    
  7. Write the sql query to select records from the table. It will take two parameters. The first parameter is the SQL Query and the second parameter will handle the result.
  8. CopiedCopy Code
    
    connection_data.connect(function(error) {
    connection_data.query("SELECT * FROM table_name", function (error, result) { 
        console.log(result);
      });
    });
    
  9. Now type the following command in your command prompt to run the script.
  10. node file_name.js

It will return the records one by one in the following format:

CopiedCopy Code

[
  RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},

 RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},
.....
.....

Here, each RowDataPacket represent a row.

SELECT Example 1:-

CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to select all the fields from village table.
  connection_data.query("SELECT * FROM village", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

CopiedCopy Code

[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  }
]

So we can see that all the columns and all records were returned.

SELECT Example 2:-

Let's select village_name and people columns from village table.

CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to select village_name,people fields from village table.
  connection_data.query("SELECT village_name,people FROM village", 
  function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});
Output:
CopiedCopy Code

[
  RowDataPacket { village_name: 'Kakumanu', people: 110 },
  RowDataPacket { village_name: 'delhi', people: 30 },
  RowDataPacket { village_name: 'patna', people: 100 },
  RowDataPacket { village_name: 'bapatla', people: 40 },
  RowDataPacket { village_name: 'gogulamudi', people: 67 }
]

So we can see that only village_name and people column records were returned.

Summary

So we seen how to select particular records with one or all columns from XAMPP Server through Node.js script with two examples. make sure that you have to install mysql package and xampp server.