Node.js + MySQL Integration
After completing this topic
You will be able to write code to connect to MySQL in Node.js and execute CRUD queries.
Why use a database in Node.js?
Until now, you have entered SQL directly into the MySQL client. However, in a real web service, the code must automatically execute SQL.
When a user clicks a signup button β the server executes INSERT INTO users ... β and returns the result as a response. This process is implemented in Node.js code.
Installing and connecting mysql2
npm install mysql2const mysql = require('mysql2');
// Connection settings
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'mypassword',
database: 'myapp'
});
// Connection check
connection.connect((err) => {
if (err) {
console.error('Connection failed:', err.message);
return;
}
console.log('MySQL connection successful');
});mysql2 is the standard library for using MySQL in Node.js. There is also an older package called mysql, but mysql2 is faster and supports Promises.
Executing queries β CRUD
Data retrieval (SELECT)
connection.query('SELECT * FROM users', (err, rows) => {
if (err) {
console.error(err);
return;
}
console.log(rows);
// [ { id: 1, name: 'κΉν', email: 'hoon@example.com' }, ... ]
});The result is returned as a JavaScript array. Each row is a single object.
Data insertion (INSERT)
const name = 'λ°μ§';
const email = 'jin@example.com';
connection.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
[name, email],
(err, result) => {
if (err) {
console.error(err);
return;
}
console.log('Inserted ID:', result.insertId);
}
);? is a placeholder. If you pass the values as an array, mysql2 will safely substitute them.
Data update (UPDATE)
connection.query(
'UPDATE users SET email = ? WHERE id = ?',
['new@example.com', 1],
(err, result) => {
console.log('Rows updated:', result.affectedRows);
}
);Data deletion (DELETE)
connection.query(
'DELETE FROM users WHERE id = ?',
[3],
(err, result) => {
console.log('Rows deleted:', result.affectedRows);
}
);Placeholders (?) β Why are they important?
// β Never do this
const userInput = "'; DROP TABLE users; --";
connection.query(`SELECT * FROM users WHERE name = '${userInput}'`);
// β
Always use placeholders
connection.query('SELECT * FROM users WHERE name = ?', [userInput]);If you directly insert user input into the SQL string, you are exposed to SQL injection attacks. Using placeholders (?) allows mysql2 to automatically escape dangerous characters.
Connection pool β Real-world pattern
In a real server, instead of creating and disconnecting connections for each request, a connection pool is used.
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'mypassword',
database: 'myapp',
waitForConnections: true,
connectionLimit: 10
});
// Execute queries from the pool (automatic connection management)
pool.query('SELECT * FROM users WHERE id = ?', [1], (err, rows) => {
console.log(rows);
});The pool pre-creates several connections and assigns an available connection when a query arrives. After the query is finished, the connection is returned to the pool. This eliminates the cost of connecting and disconnecting for each request.
Promise style (async/await)
If the callback pattern is too complex, you can use the Promise style.
const mysql = require('mysql2/promise');
async function main() {
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'mypassword',
database: 'myapp'
});
const [rows] = await pool.query('SELECT * FROM users');
console.log(rows);
const [result] = await pool.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
['μμ¬μ©μ', 'new@example.com']
);
console.log('Insert ID:', result.insertId);
}
main();When you import mysql2/promise, all methods return Promises. You can use them cleanly with await.