Real-world Problems When Connecting MySQL in Node.js
I remember when I first started doing backend work in Node.js, the first way I learned to connect to MySQL looked like this:
const mysql = require('mysql');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'myapp'
});
connection.connect();
app.get('/user/:id', (req, res) => {
const id = req.params.id;
connection.query(`SELECT * FROM users WHERE id = ${id}`, (err, results) => {
res.json(results);
});
});
The code ran fine, tested locally without issues, pushed to production… then users started complaining about a slow site and occasional connection errors. Then I discovered an even more serious problem: the URL /user/1 OR 1=1 returned the entire users table from the database.
Three bugs at once: SQL Injection, no connection pooling, and using an unmaintained legacy library. This article addresses all three, using mysql2 — a modern replacement with native Promise/async-await support and significantly better performance compared to the original mysql package.
Root Cause Analysis
Why Is a Single Connection Not Enough?
Node.js handles requests via an event loop — multiple requests can arrive simultaneously. With only one MySQL connection, requests must queue up and wait for each other. Worse, when that connection drops due to a timeout or MySQL server restart, the entire application fails immediately with no self-recovery mechanism.
How Does SQL Injection Happen?
When you concatenate strings directly into a query:
// DANGEROUS — never do this
const query = `SELECT * FROM users WHERE username = '${username}'`;
An attacker enters ' OR '1'='1 as the username, and the query becomes:
SELECT * FROM users WHERE username = '' OR '1'='1'
This statement returns all users. Even more dangerous, '; DROP TABLE users; -- can wipe out the entire table.
Why Not Use the Old mysql Package?
The mysql package has not been actively maintained for years. mysql2 offers native Promise support, better performance, full backward compatibility with the old API, and complete MySQL 8.0 support.
Solutions
Installing mysql2
npm install mysql2
Solution 1: Connection Pooling
Instead of creating a single connection and holding onto it forever, create a pool — a collection of connections ready to use. When a request comes in, grab a connection from the pool, use it, then return it:
// db.js — shared module used across the entire application
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'appuser',
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME || 'myapp',
waitForConnections: true,
connectionLimit: 10, // maximum 10 concurrent connections
queueLimit: 0 // no limit on requests waiting in the queue
});
module.exports = pool;
My production database runs MySQL 8.0 with around 50GB of data, and connectionLimit: 10 is a reasonable starting point — don’t set it too high since each connection consumes RAM on both the application and MySQL server side. I monitor Threads_connected via SHOW STATUS and adjust based on real metrics rather than guessing.
Solution 2: Prepared Statements — Blocking SQL Injection
Prepared statements separate SQL code from data. The MySQL engine receives the SQL statement first, compiles it, then receives the data separately — the data is never interpreted as SQL code:
const pool = require('./db');
async function getUserById(id) {
// The ? is a placeholder; mysql2 automatically escapes the value
const [rows] = await pool.query(
'SELECT id, username, email FROM users WHERE id = ?',
[id]
);
return rows[0] || null;
}
async function searchUsers(keyword) {
const [rows] = await pool.query(
'SELECT id, username FROM users WHERE username LIKE ? OR email LIKE ?',
[`%${keyword}%`, `%${keyword}%`]
);
return rows;
}
If an attacker inputs ' OR '1'='1, mysql2 converts it into an escaped string literal — completely harmless, the query only searches for that exact value.
Solution 3: execute() vs query() — When to Use Which?
mysql2 offers two ways to run queries with placeholders:
// query() — client-side escaping, sends a single pre-processed SQL string
const [rows1] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
// execute() — true prepared statement, MySQL server compiles it separately
const [rows2] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
- Use
execute()for queries that run repeatedly — MySQL caches the execution plan, giving significantly better speed under high load - Use
query()for one-off queries or complex queries that don’t work well with prepared statements
Solution 4: Transactions for Data Integrity
Transactions guarantee “all or nothing” — either all operations succeed, or everything rolls back to the original state. Here’s an example of transferring money between two accounts:
async function transferMoney(fromId, toId, amount) {
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
// Deduct from source account (balance check included in the same statement)
const [result] = await connection.execute(
'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?',
[amount, fromId, amount]
);
if (result.affectedRows === 0) {
throw new Error('Insufficient balance or account does not exist');
}
// Credit to destination account
await connection.execute(
'UPDATE accounts SET balance = balance + ? WHERE id = ?',
[amount, toId]
);
// Log the transaction
await connection.execute(
'INSERT INTO transactions (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())',
[fromId, toId, amount]
);
await connection.commit();
return { success: true };
} catch (error) {
await connection.rollback();
throw error;
} finally {
// IMPORTANT: always release back to the pool whether it succeeds or fails
connection.release();
}
}
The try/catch/finally pattern with connection.release() in the finally block is mandatory. If you forget to release, the connection “leaks” out of the pool — after a few hours of running, the system runs out of connections and all requests hang with no clear reason why.
The Best Approach — Combining All Three Techniques
The most effective organization I’ve found is to separate the database module and write wrapper functions for each operation:
// models/user.js
const pool = require('../db');
class UserModel {
async findById(id) {
const [rows] = await pool.execute(
'SELECT id, username, email, created_at FROM users WHERE id = ?',
[id]
);
return rows[0] || null;
}
async create(username, email, hashedPassword) {
const [result] = await pool.execute(
'INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, NOW())',
[username, email, hashedPassword]
);
return result.insertId;
}
// Transaction: create user + profile in a single atomic operation
async registerWithProfile(userData) {
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const [userResult] = await conn.execute(
'INSERT INTO users (username, email, password) VALUES (?, ?, ?)',
[userData.username, userData.email, userData.password]
);
const userId = userResult.insertId;
await conn.execute(
'INSERT INTO profiles (user_id, display_name, bio) VALUES (?, ?, ?)',
[userId, userData.displayName, userData.bio || '']
);
await conn.commit();
return userId;
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release();
}
}
}
module.exports = new UserModel();
// server.js — verify database connection on startup
const pool = require('./db');
async function startServer() {
try {
const [rows] = await pool.query('SELECT 1 AS health');
console.log('Database:', rows[0].health === 1 ? 'Connected OK' : 'FAIL');
} catch (err) {
console.error('Cannot connect to database:', err.message);
process.exit(1);
}
const app = require('./app');
app.listen(3000, () => console.log('Server running on port 3000'));
}
startServer();
Pre-ship Checklist
- Never concatenate strings into SQL — always use the
?placeholder, no exceptions - Pool is a singleton — initialize once in
db.js, import wherever needed - Always release connections after a transaction in the
finallyblock, not insidetry - Credentials from environment variables — never hardcode in source code, never commit
.env - execute() for repeated queries, query() for one-time queries
Ever since switching to this pattern, my system has been noticeably more stable — no more connection exhaustion under high traffic, no more worrying about SQL Injection. These optimizations have also made queries significantly faster thanks to MySQL caching execution plans for prepared statements called repeatedly, especially noticeable when query counts reach several hundred per minute.
