MySQL Integration in Node.js
Learn how to connect a Node.js application to a MySQL database, execute SQL queries, and retrieve data using the mysql2 package.
MySQL is a relational database management system that stores data in tables containing rows and columns. A Node.js application can connect to MySQL to insert, retrieve, update, and delete records.
For example, an application can store product details in a MySQL table and retrieve those products whenever a client requests them.
1. What Is MySQL Integration?
MySQL integration means connecting a Node.js application to a MySQL database so that the application can execute SQL queries and work with stored data.
For example, a product table might contain the following records:
| ProductId | ProductName | Price |
|---|---|---|
| 1 | Laptop | 50000 |
| 2 | Mobile | 20000 |
| 3 | Keyboard | 1500 |
Node.js can send a SELECT query to MySQL and receive these records as the result of the query.
2. How Node.js Connects to MySQL
The application uses a database driver to establish a connection with MySQL. After connecting, it can execute SQL statements and process the returned results.
Sends a request to the application
Processes the request and prepares an SQL query
Communicates with the MySQL database server
Executes the SQL query and returns the result
Sends the result back to the client
3. Installing the Required Packages
First, create a Node.js project. Open a terminal in the project folder and initialize the project using npm.
npm init -y
Next, install Express and mysql2. Express will handle HTTP requests, while mysql2 will connect the application to MySQL.
npm install express mysql2
The project structure can look like this:
mysql-node-app/
│
├── node_modules/
├── package.json
├── package-lock.json
└── app.js
4. Creating a MySQL Database and Table
Before connecting from Node.js, create a database and a table in MySQL. You can execute these statements in MySQL Workbench or another MySQL client.
CREATE DATABASE nodejsdb;
USE nodejsdb;
CREATE TABLE Products (
ProductId INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL
);
INSERT INTO Products (ProductName, Price)
VALUES
('Laptop', 50000),
('Mobile', 20000),
('Keyboard', 1500);
SELECT * FROM Products;
After running the statements, the Products table contains sample data.
| ProductId | ProductName | Price |
|---|---|---|
| 1 | Laptop | 50000.00 |
| 2 | Mobile | 20000.00 |
| 3 | Keyboard | 1500.00 |
5. Connecting Node.js to MySQL
The mysql2 package provides a createConnection() method that establishes a connection to the MySQL server.
const mysql = require('mysql2');
const connection = mysql.createConnection({
host: 'localhost',
user: 'your_mysql_user',
password: 'your_mysql_password',
database: 'nodejsdb'
});
connection.connect((error) => {
if (error) {
console.error('Database connection failed:', error.message);
return;
}
console.log('Connected to MySQL successfully!');
});
Understanding the connection properties:
| Property | Purpose |
|---|---|
host |
Specifies the MySQL server address. |
user |
Specifies the MySQL username. |
password |
Specifies the password for the database user. |
database |
Specifies the database to use. |
createConnection() |
Creates a database connection. |
6. Retrieving Data Using SELECT
After connecting to MySQL, Node.js can execute a SELECT statement to retrieve product records from the database.
connection.query(
'SELECT * FROM Products',
(error, results) => {
if (error) {
console.error('Query failed:', error.message);
return;
}
console.log(results);
}
);
The query() method executes the SQL statement. If the query succeeds, the results argument contains the returned rows.
Example output:
[
{ ProductId: 1, ProductName: 'Laptop', Price: '50000.00' },
{ ProductId: 2, ProductName: 'Mobile', Price: '20000.00' },
{ ProductId: 3, ProductName: 'Keyboard', Price: '1500.00' }
]
The exact output representation can vary by driver configuration. For example, DECIMAL values are commonly returned as strings by mysql2 unless numeric conversion is configured.
7. Using Parameterized Queries
When a query uses values supplied by a client, use placeholders instead of building SQL statements by joining strings. Parameterized queries help protect against SQL injection.
const productId = 1;
connection.execute(
'SELECT * FROM Products WHERE ProductId = ?',
[productId],
(error, results) => {
if (error) {
console.error('Query failed:', error.message);
return;
}
console.log(results);
}
);
The question mark is a placeholder. The value in the array is supplied separately to the query.
8. Complete Example: Express with MySQL
The following example creates an Express API that retrieves all products from MySQL. It uses mysql2's promise-based connection pool so the route can use async/await.
const express = require('express');
const mysql = require('mysql2/promise');
const app = express();
app.use(express.json());
const pool = mysql.createPool({
host: 'localhost',
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: 'nodejsdb',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
app.get('/api/products', async (req, res) => {
try {
const [rows] = await pool.execute(
'SELECT * FROM Products'
);
res.status(200).json(rows);
} catch (error) {
console.error('Database query failed:', error.message);
res.status(500).json({
message: 'Unable to retrieve products'
});
}
});
async function startServer() {
try {
await pool.query('SELECT 1');
app.listen(3000, () => {
console.log('Server running at http://localhost:3000');
});
} catch (error) {
console.error('Unable to connect to MySQL:', error.message);
await pool.end();
process.exit(1);
}
}
startServer();
Set the DB_USER and DB_PASSWORD
environment variables before running the application.
Start the application:
node app.js
Open this URL in a browser or send a GET request from Postman:
GET http://localhost:3000/api/products
If the database connection and query succeed, the API returns the products as a JSON array.
9. Common MySQL Connection Problems
| Problem | Possible Cause | What to Check |
|---|---|---|
| Access denied | Incorrect credentials or insufficient privileges | Verify the username, password, and database permissions. |
| Connection refused | MySQL server is stopped or host/port is incorrect | Confirm the server is running and the connection settings are correct. |
| Unknown database | The database does not exist or the name is incorrect | Verify that nodejsdb has been created. |
| Table doesn't exist | The expected table was not created in the selected database | Confirm that the Products table exists in nodejsdb. |
| Module not found | The package is missing from the project | Run npm install mysql2 express. |
Important Points to Remember
- MySQL stores structured data in tables.
- Install
mysql2to connect Node.js to MySQL. - Create the database and tables before querying them.
- Use the correct database host, username, password, and database name.
- Use SELECT queries to retrieve records.
- Use parameterized queries for values supplied by clients.
- Handle connection and query errors properly.
- Use a connection pool for applications that serve multiple requests.
- Keep database credentials out of source code.
Summary
MySQL integration allows a Node.js application to store and retrieve structured data. The mysql2 package provides the connection and query functionality needed to communicate with MySQL. Express can expose API endpoints that execute database queries and return results as JSON.
In the next lesson, we will learn how to connect Node.js applications to MongoDB.