MySQL: Hands-On Development: Connecting Node.js to MySQL
Last updated: 2026-08-26
For front-end developers learning back-end development, Node.js + MySQL is the best place to start.
This lesson explains how to work with MySQL using Node.js.
graph TB
A[Node.js Applications] --> B[mysql2 Driver]
B --> C[Connection Pool]
C --> D[Get the link]
D --> E[Parametric Queries]
E --> F{Operation Type}
F -->|Search| G[execute SELECT]
F -->|Insert| H[execute INSERT]
F -->|Update| I[execute UPDATE]
F -->|Delete| J[execute DELETE]
F -->|Transactions| K[beginTransaction]
K --> L[commit / rollback]
D --> M[Release the connection back to the pool]
1. What You'll Learn
- Installing and Connecting to the mysql2 Driver
- Connection Pool Configuration
- CRUD operations
- Parameterized Queries (Preventing SQL Injection)
- Transaction Encapsulation
2. A True Story
(1) Pain Point: Having learned SQL but not knowing how to apply it in projects
Front-end developers who have learned SQL are often at a loss when writing back-end code with Node.js: manually concatenating SQL strings makes them vulnerable to injection attacks, poor connection management leads to database connections being exhausted, and nested asynchronous callbacks make the code difficult to maintain.
(2) Solution for MySQL 2+ Connection Pooling + Parameterized Queries
Use the mysql2 driver (which supports Promises), connection pooling to manage connections, and parameterized queries to prevent injection attacks.
| Dimension | Manual SQL Construction | MySQL 2+ Connection Pool |
|---|---|---|
| SQL Injection Risk | Extremely High | Zero (Parameterized Queries) |
| Connection Management | Unmanaged (may be exhausted) | Automatic Management (pooling and reuse) |
| Coding Style | Callback Hell | async/await |
| Production-ready | ❌ | ✅ |
3. Connecting to MySQL
▶ Example: Creating a Connection
Note: The code below uses the mysql2 driver for Node.js. It is shown here for reference—this code cannot run in the browser-based SQL sandbox on this site. To execute it, set up a local Node.js environment.
TEXT
📖 Display only
// Requires: mysql2 package, running MySQL server
const mysql = require('mysql2/promise');
async function main() {
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'Root123!',
database: 'mydb'
});
const [rows] = await connection.execute('SELECT VERSION()');
console.log(rows);
await connection.end();
}
main();
4. Connection Pool
TEXT
📖 Display only
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'Root123!',
database: 'mydb',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// Querying Using a Connection Pool
async function queryWithPool() {
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [1]);
console.log(rows);
}
queryWithPool();
5. CRUD Operations
▶ Example: Querying Data
TEXT
📖 Display only
// Search All Users
async function queryUsers() {
const [users] = await pool.execute('SELECT * FROM users');
// Conditional Query
const userId = 1;
const [user] = await pool.execute(
'SELECT * FROM users WHERE id = ?',
[userId]
);
console.log(users, user);
}
queryUsers();
▶ Example: Inserting Data
TEXT
📖 Display only
async function insertUser() {
const [result] = await pool.execute(
'INSERT INTO users (username, email) VALUES (?, ?)',
['alice', 'alice@email.com']
);
console.log(result.insertId);
}
insertUser();
▶ Example: Updating Data
TEXT
📖 Display only
async function updateUser() {
const [result] = await pool.execute(
'UPDATE users SET email = ? WHERE id = ?',
['new@email.com', 1]
);
console.log(result.affectedRows);
}
updateUser();
▶ Example: Deleting Data
TEXT
📖 Display only
async function deleteUser() {
const [result] = await pool.execute(
'DELETE FROM users WHERE id = ?',
[1]
);
console.log(result.affectedRows);
}
deleteUser();
6. Preventing SQL Injection
(1) Error: String concatenation
TEXT
📖 Display only
// Danger! SQL injection
const sql = `SELECT * FROM users WHERE username = '${username}'`;
(2) Correct: Parameterized Query
TEXT
📖 Display only
// Safe: Parameterized query
async function safeQuery(username) {
const [rows] = await pool.execute(
'SELECT * FROM users WHERE username = ?',
[username]
);
console.log(rows);
}
safeQuery('alice');
7. Transaction Encapsulation
TEXT
📖 Display only
async function transfer(fromId, toId, amount) {
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.execute(
'UPDATE accounts SET balance = balance - ? WHERE id = ?',
[amount, fromId]
);
await conn.execute(
'UPDATE accounts SET balance = balance + ? WHERE id = ?',
[amount, toId]
);
await conn.commit();
return { success: true };
} catch (error) {
await conn.rollback();
return { success: false, error: error.message };
} finally {
conn.release();
}
}
8. Introduction to ORM
| ORM | Description |
|---|---|
| Sequelize | A long-established ORM with a full range of features |
| Prisma | Next Generation, Type-Based Security |
| TypeORM | Good TypeScript support |
| Knex.js | Query Builder |
TEXT
📖 Display only
// Prisma Example
async function getActiveUsers() {
const users = await prisma.user.findMany({
where: { status: 'active' },
include: { orders: true }
});
console.log(users);
}
getActiveUsers();
❓ FAQ
Q Should I use mysql or mysql2?
A We recommend mysql2 (it supports Promises and offers better performance).
Q What should the connection pool size be set to?
A Generally 10–20, adjusted based on concurrency.
Q When should you use ORM?
A Use ORM to simplify development in complex projects; use native SQL for greater flexibility in simple projects.
Q What is the difference between mysql and mysql2?
A mysql is the older driver that only supports callbacks, while mysql2 supports Promises and offers better performance. We recommend mysql2.
Q What should the connection pool size be?
A The formula is number of CPU cores × 2 + number of disks; for a typical web application, 10–20 is sufficient.
📖 Summary
- mysql2 is a MySQL driver for Node.js that supports Promises
- Connection pool reuses connections to improve performance
- Parameterized Queries Prevent SQL Injection
- Transactions ensure data consistency
- ORM simplifies database operations
📝 Exercises
-
Basic Problem (Difficulty: ⭐): Use Node.js to connect to MySQL, query the database, and print the list of users.
-
Advanced Exercise (Difficulty: ⭐⭐): Implement a complete CRUD API (Create, Read, Update, Delete).
-
Challenge (Difficulty: ⭐⭐⭐): Implement a funds transfer feature, including transactions and error handling.
