YouTipMysql Nodejs Intro
MySQL is one of the most popular open-source relational databases, while Node.js is a JavaScript runtime environment based on Chrome's V8 engine. Combining the two can build powerful backend services.
**Why Choose MySQL + Node.js?**
* MySQL provides reliable data storage and management
* Node.js's non-blocking I/O model is suitable for database operations
* JavaScript full-stack development (same language for frontend and backend)
* The rich npm ecosystem has many MySQL-related packages
### Installing Required Dependencies
Before starting, we need to install the `mysql2` package, which is a popular choice for connecting to MySQL in Node.js.
npm install mysql2
**Why Choose mysql2 Instead of mysql?**
* Better performance
* Supports Promise API
* Supports prepared statements
* Actively maintained
* * *
## Establishing Database Connection
### Basic Connection Configuration
## Example
const mysql = require('mysql2');
// Create connection pool (recommended for production environments)
const pool = mysql.createPool({
host:'localhost',// Database server address
user:'root',// Database username
password:'password',// Database password
database:'test_db',// Database name to connect
waitForConnections:true,
connectionLimit:10,// Maximum connections in pool
queueLimit:0
});
// Get a Promise version of the connection
const promisePool = pool.promise();
### Connection Pool vs Single Connection
**Connection Pool Advantages:**
* Reuse connections, reduce overhead
* Automatically manage connection lifecycle
* Prevent connection leaks
* Better performance
**Single Connection Applicable Scenarios:**
* Simple scripts
* Test environments
* Low-concurrency applications
* * *
## Executing Basic CRUD Operations
### Query Data (SELECT)
## Example
async function getUsers(){
try{
const[rows, fields]= await promisePool.query('SELECT * FROM users');
console.log(rows);
return rows;
}catch(err){
console.error('Query error:', err);
throw err;
}
}
### Insert Data (INSERT)
## Example
async function addUser(user){
try{
const= await promisePool.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
[user.name, user.email]
);
console.log('Insert ID:', result.insertId);
return result;
}catch(err){
console.error('Insert error:', err);
throw err;
}
}
### Update Data (UPDATE)
## Example
async function updateUser(id, updates){
try{
const= await promisePool.query(
'UPDATE users SET name = ?, email = ? WHERE id = ?',
[updates.name, updates.email, id]
);
console.log('Affected rows:', result.affectedRows);
return result;
}catch(err){
console.error('Update error:', err);
throw err;
}
}
### Delete Data (DELETE)
## Example
async function deleteUser(id){
try{
const= await promisePool.query(
'DELETE FROM users WHERE id = ?',
);
console.log('Deleted rows:', result.affectedRows);
return result;
}catch(err){
console.error('Delete error:', err);
throw err;
}
}
* * *
## Advanced Features and Best Practices
### Transaction Handling
## Example
async function transferFunds(fromId, toId, amount){
let connection;
try{
// Get connection from pool
connection = await promisePool.getConnection();
// Begin transaction
await connection.beginTransaction();
// Execute transfer operations
await connection.query(
'UPDATE accounts SET balance = balance - ? WHERE id = ?',
[amount, fromId]
);
await connection.query(
'UPDATE accounts SET balance = balance + ? WHERE id = ?',
[amount, toId]
);
// Commit transaction
await connection.commit();
console.log('Transfer successful');
}catch(err){
// Rollback on error
if(connection) await connection.rollback();
console.error('Transfer failed:', err);
throw err;
}finally{
// Release connection back to pool
if(connection) connection.release();
}
}
### Prepared Statements
Prepared statements can improve performance and prevent SQL injection:
## Example
async function getUserById(id){
try{
// Prepare prepared statement
const= await promisePool.execute(
'SELECT * FROM users WHERE id = ?',
);
return rows;
}catch(err){
console.error('Query error:', err);
throw err;
}
}
### Connection Pool Event Monitoring
## Example
pool.on('connection',(connection)=>{
console.log('New connection established');
});
pool.on('acquire',(connection)=>{
console.log('Connection acquired');
});
pool.on('release',(connection)=>{
console.log('Connection released');
});
pool.on('enqueue',()=>{
console.log('Waiting for available connection');
});
* * *
## Error Handling and Debugging
### Common Error Types
1. **Connection errors**: Database server unreachable, authentication failure, etc.
2. **Query syntax errors**: SQL statement errors
3. **Constraint violations**: Such as duplicate primary keys, foreign key constraints, etc.
4. **Timeout errors**: Query execution time too long
### Error Handling Strategies
## Example
async function safeQuery(sql, params){
try{
const= await promisePool.query(sql, params);
return rows;
}catch(err){
// Take different measures based on error type
switch(err.code){
case'ER_DUP_ENTRY':
console.warn('Duplicate entry:', err.sqlMessage);
throw new Error('Data already exists');
case'ECONNREFUSED':
console.error('Cannot connect to database');
throw new Error('Service unavailable, please try again later');
default:
console.error('Database error:', err);
throw err;
}
}
}
* * *
## Performance Optimization Suggestions
1. **Set connection pool size appropriately**: Usually 2-3 times the number of CPU cores
2. **Use connection pool instead of single connection**: Especially in web applications
3. **Use indexes reasonably**: Accelerate query performance
4. **Batch operations**: Reduce round trips
5. **Use prepared statements**: Improve repeated query performance
6. **Release resources regularly**: Avoid connection leaks
**Batch Insert Example:**
## Example
async function batchInsertUsers(users){
const values = users.map(user =>[user.name, user.email]);
try{
const= await promisePool.query(
'INSERT INTO users (name, email) VALUES ?',
);
console.log('Inserted rows:', result.affectedRows);
return result;
}catch(err){
console.error('Batch insert error:', err);
throw err;
}
}
* * *
## Security Considerations
1. **Never concatenate SQL strings**: Use parameterized queries to prevent SQL injection
2. **Limit database user permissions**: Application accounts only need necessary permissions
3. **Encrypt sensitive data**: Such as passwords should be stored with salted hashing
4. **Use SSL connections**: Production environments recommend encrypted connections
5. **Update dependencies regularly**: Keep mysql2 package up to date
* * *
## Complete Example Project Structure
project/├── config/│ └── db.js # Database configuration├── models/│ └── userModel.js # Data models├── services/│ └── userService.js # Business logic├── app.js # Main application file└── package.json
**db.js Example:**
## Example
const mysql = require('mysql2');
const pool = mysql.createPool({
host: process.env.DB_HOST||'localhost',
user: process.env.DB_USER||'root',
password: process.env.DB_PASSWORD||'',
database: process.env.DB_NAME||'test_db',
waitForConnections:true,
connectionLimit:10,
queueLimit:0
});
module.exports= pool.promise();