Why Use Node.js to Select Data from MySQL?
- Dynamic Data Retrieval: Fetch data based on user input or API requests.
- Asynchronous Execution: Node.js handles database queries without blocking other processes, ensuring fast and responsive applications.
- Ease of Integration: MySQL queries can be easily integrated into your Node.js logic.
Prerequisites
MySQL Installed: Ensure MySQL is installed and running on your system.
Node.js Installed: Install Node.js if you haven’t already.
MySQL Package Installed: Install the MySQL module using:
npm install mysql
Setting Up the Node.js Project
Initialize a New Project:
npm init -y
Install the MySQL Package:
npm install mysql
Create a File: Create a file named app.js.
Connecting to the MySQL Database
Before fetching data, establish a connection to your MySQL database.
Example: Connect to MySQL
const mysql = require('mysql');
// Create a connection to the database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'nodejs_demo', // Replace with your database name
});
// Connect to MySQL
connection.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err);
return;
}
console.log('Connected to MySQL successfully!');
});
Using the SELECT Statement in Node.js
The SELECT statement is used to fetch data from one or more columns in a table.
Example 1: Select All Data from a Table
const sql = 'SELECT * FROM users';
connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Data fetched successfully:', results);
});
// Close the connection
connection.end();
Explanation:
- SELECT *: Retrieves all columns and rows from the users table.
- results: Contains an array of all fetched rows.
Example 2: Select Specific Columns
If you only need specific columns, list them explicitly in the query.
const sql = 'SELECT name, email FROM users';
connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Selected columns:', results);
});
// Close the connection
connection.end();
Explanation:
- Only the name and email columns are retrieved, reducing unnecessary data.
Filtering Data with WHERE Clause
You can filter results using the WHERE clause to specify conditions.
Example 3: Fetch Data Based on a Condition
const sql = 'SELECT * FROM users WHERE age > ?';
const age = 25;
connection.query(sql, [age], (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Users older than 25:', results);
});
// Close the connection
connection.end();
Explanation:
- WHERE age > ?: Fetches users older than 25.
- Parameterized Query: Prevents SQL injection by using placeholders (?).
Sorting Data with ORDER BY
You can sort the fetched data using the ORDER BY clause.
Example 4: Sort Data in Ascending Order
const sql = 'SELECT * FROM users ORDER BY age ASC';
connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Users sorted by age:', results);
});
// Close the connection
connection.end();
Explanation:
- ORDER BY age ASC: Sorts users by age in ascending order. Use DESC for descending order.
Limiting Results with LIMIT
The LIMIT clause restricts the number of rows returned.
Example 5: Fetch Limited Rows
const sql = 'SELECT * FROM users LIMIT 5';
connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('First 5 users:', results);
});
// Close the connection
connection.end();
Explanation:
- LIMIT 5: Returns only the first 5 rows.
Combining Multiple Clauses
You can combine clauses like WHERE, ORDER BY and LIMIT in a single query.
Example 6: Fetch and Sort Data with Conditions
const sql = 'SELECT * FROM users WHERE age > ? ORDER BY name ASC LIMIT 3';
const age = 20;
connection.query(sql, [age], (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Filtered and sorted users:', results);
});
// Close the connection
connection.end();
Handling Errors During Data Fetching
Always handle errors to ensure your application remains stable.
Example: Error Handling
connection.query('SELECT * FROM non_existing_table', (err, results) => {
if (err) {
console.error('Error fetching data:', err.message);
return;
}
console.log('Data fetched:', results);
});
Output:
Error fetching data: Table 'nodejs_demo.non_existing_table' doesn't exist
Real-World Use Case
Imagine building an admin dashboard where you need to display user data. You can use the SELECT statement to fetch the required data dynamically.
Example: Fetch Data for Display
const fetchUsers = () => {
const sql = 'SELECT name, email, age FROM users';
connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching user data:', err);
return;
}
console.log('User data for dashboard:', results);
});
};
// Call the function
fetchUsers();