In this post we'll see examples of Select statement using Node.js and MySQL to fetch data from DB. We'll be using the Promise Based API provided by MySQL2 library and a connection pool to connect to database in the examples provided here.
Refer Node.js - MySQL Connection Pool to get more information about using MySQL Connection Pool with Node.js
Table used
Table used for the example is Employee table with columns as- id, name, age, join_date.
CREATE TABLE `node`.`employee` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NULL, `join_date` DATE NOT NULL, `age` INT NULL, PRIMARY KEY (`id`));
Id is auto incremented so we don't need to send that data from our code.
Node.js MySQL Select statement example
In this example all the records are fetched from the Employee table.
We'll keep the DB connection configuration in a separate file that can be used wherever it is needed by importing it.
util\database.js
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'admin',
database: 'node',
waitForConnections: true,
connectionLimit: 10,
});
module.exports = pool;
As you can see pool object is exported here so that it can be used in other files.
app.js
In this file let's create a function to select all the records from the Employee table.
const pool = require('./util/database');
async function getEmployees(){
const sql = "SELECT * FROM EMPLOYEE";
try{
const conn = await pool.getConnection();
const [results, fields] = await conn.query(sql);
console.log(results); // results contains rows returned by server
console.log(fields);
conn.release();
}catch(err){
console.log(err);
}
}
getEmployees(30);
Important points to note here-
- getEmployees () function is a async function as we are using async/await (Promise based API) rather than callback based API.
- We are using await with pool.getConnection() method.
- By using array destructuring we get the returned values for results and fields.
- results contains rows returned by server.
- fields variable contains extra meta data about results, if available.
- After the task, connection is released using conn.release() which means connection goes back to the pool.
On running the file-
>node app.js
[
{
id: 1,
name: 'Ishan',
join_date: 2024-03-23T18:30:00.000Z,
age: 28
},
{
id: 2,
name: 'Rajesh',
join_date: 2023-06-16T18:30:00.000Z,
age: 34
}
]
[
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(45),
`join_date` DATE(10) NOT NULL,
`age` INT
]
Select with where condition example
In this example we'll have a condition using Where clause, only those records are selected that fulfil the condition. For the condition, parameterized query is used.
app.js
In this file let's create a function to select data from the Employee table in MySQL based on the age condition.
const pool = require('./util/database');
async function getEmployeesByAge(age){
const sql = "SELECT * FROM EMPLOYEE where age >?";
const values = [age];
try{
const conn = await pool.getConnection();
const [result, fields] = await conn.execute(sql, values);
console.log(result); // results contains rows returned by server
console.log(fields); // fields contains extra meta data about results, if available
conn.release(); // connection sent back to pool
}catch(err){
console.log(err);
}
}
getEmployeesByAge(30);
On running it
>node app.js
[
{
id: 2,
name: 'Rajesh',
join_date: 2023-06-16T18:30:00.000Z,
age: 34
}
]
[
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(45),
`join_date` DATE(10) NOT NULL,
`age` INT
]
That's all for this topic Node.js MySQL Select Statement Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!
Related Topics
You may also like-
