NodeJS MySQL Select Unique

NodeJS MySQL Select Unique Thumbnail

NodeJS is an open-source, cross-platform runtime environment that uses JavaScript for creating fast, data-intensive, and secure server-side applications. 

These applications require some ways to store data in order to use them again. NodeJS supports all kinds of Databases, relational, and non-relational out of which MySQL can be the best choice.

MySQL is a relational database management system that stores data in one or more tables of rows and columns. It can be used in NodeJS to store large amounts of data and retrieve data quickly due to its simple structure using SQL. SQL stands for structured query languages which we can use to select the unique record from the MySQL table.

Selecting Unique Records with SQL using NodeJS

For selecting a unique record we can pass an additional parameter followed by the WHERE keyword after the query of selecting records.

SELECT * FROM tableName WHERE condition

We have already coved the entire concept of selecting the records from a MySQL table in another tutorial NodeJS MySQL Select Record.

Selecting Unique MySQL Records using Node.js

Unique Record is selected in NodeJs from a MySQL table by passing the above SQL query as an argument to query() method of the connection object. This method also uses a callback function as an argument to check for the error and get the selected records. 

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

con.query("SELECT * FROM tableName WHERE condition", 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
  • condition is used as a filter to select unique records

Example of Selecting Unique Records in Node.js

Let’s see an example of selecting a unique record from a table.

Step 1: 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.  

XAMPP Control Panel 3

Step 2: Create a folder with a file “app.js” where we write our code and open it in a code editor.

Folder Structure 3

Step3: Open the terminal and type the below command to initiate NPM. 

npm init -y
Initiate NPM 2

Step 4: Type the below command to install the MySQL module.

npm i mysql
Install Sql 2

Step 5: Open the “app.js” file and import the MySQL module.

const mysql = require('mysql');

Step 6: Connect to the MySQL server using the createConnection() and connect() methods.

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

con.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log("Connected to MySQL!");
});

We have a separate tutorial on creating MySQL connections using NodeJS if you want to read it.

We specified the database as “newdatabase” which we have created in another tutorial NodeJS MySQL Create Database.

Step 7: Create a new table. We have a separate tutorial on NodeJS MySQL Create Table if you want to read it.

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

Step 8: Insert multiple records. We have covered the process of inserting multiple records in a separate tutorial NodeJS MySQL Insert Record.

var values = [  
    ['1', 'Aditya', 'aditya@gail.com', '22'],  
    ['2', 'Example', 'example@gmail.com', '22'], 
    ['3', 'Rack', 'rack@gmail.com', '17'],  
    ['4', 'Jack', 'jack@gmail.com', '15'],
]; 

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

Step 9: Use the snippet we explain at the staring of this tutorial to run the query of selecting the unique records and pass a condition id = ‘1’ to select the record having id 1.

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

Step 10: Type the below command to run the application.

node app.js

Compete Code:

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', 'aditya@gail.com', '22'],  
    ['2', 'Example', 'example@gmail.com', '22'], 
    ['3', 'Rack', 'rack@gmail.com', '17'],  
    ['4', 'Jack', 'jack@gmail.com', '15'],
]; 

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

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

Output:

Output 3

Summary and References

The MySQL connection object has a method query() to run a SQL query directly in NodeJS. Records can be selected uniquely from a table by passing the SQL queries of selecting unique records as an argument to the query() method. Hope this tutorial helps to learn the process of selecting unique records from a table.

https://www.npmjs.com/package/mysql