MySQL Node.js Connection and Usage

MySQL is one of the most popular open-source relational databases, and Node.js is a JavaScript runtime environment based on the Chrome V8 engine. Combining the two allows you to 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 (using the same language for frontend and backend)
  • The rich npm ecosystem has many MySQL-related packages

Installing Required Dependencies

Before we begin, we need to install themysql2package, 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
  • Active maintenance

Establishing Database Connection

Basic Connection Configuration

Example

const mysql = require('mysql2');

// Create a connection pool (recommended for production)
const pool = mysql.createPool({
  host: 'localhost',     // Database server address
  user: 'root',          // Database username
  password: 'password',  // Database password
  database: 'test_db',   // Name of the database to connect to
  waitForConnections: true,
  connectionLimit: 10,   // Maximum number of connections in the pool
  queueLimit: 0
});

// Get a Promise-based connection
const promisePool = pool.promise();

Connection Pool vs Single Connection

Advantages of connection pool:

  • Reuse connections, reduce overhead
  • Automatically manage connection lifecycle
  • Prevent connection leaks
  • Better performance

Scenarios suitable for single connection:

  • Simple scripts
  • Test environment
  • Low-concurrency applications

Performing Basic CRUD Operations

Querying 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;
  }
}

Inserting Data (INSERT)

Example

async function addUser(user) {
  try {
    const [result] = 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;
  }
}

Updating Data (UPDATE)

Example

async function updateUser(id, updates) {
  try {
    const [result] = 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;
  }
}

Deleting Data (DELETE)

Example

async function deleteUser(id) {
  try {
    const [result] = await promisePool.query(
      'DELETE FROM users WHERE id = ?',
      [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 a connection from the pool
    connection = await promisePool.getConnection();
   
    // Start a transaction
    await connection.beginTransaction();
   
    // Perform the transfer operation
    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 the transaction
    await connection.commit();
    console.log('Transfer successful');
  } catch (err) {
    // Roll back on error
    if (connection) await connection.rollback();
    console.error('Transfer failed:', err);
    throw err;
  } finally {
    // Release the connection back to the pool
    if (connection) connection.release();
  }
}

Prepared Statements

Prepared statements can improve performance and prevent SQL injection:

Example

async function getUserById(id) {
  try {
    // Prepare a prepared statement
    const [rows] = await promisePool.execute(
      'SELECT * FROM users WHERE id = ?',
      [id]
    );
    return rows[0];
  } catch (err) {
    console.error('Query error:', err);
    throw err;
  }
}

Connection Pool Event Listening

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: incorrect SQL statements
  3. Constraint violations: such as duplicate primary keys, foreign key constraints, etc.
  4. Timeout errors: query execution time is too long

Error Handling Strategies

Example

async function safeQuery(sql, params) {
  try {
    const [rows] = 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('Unable to connect to database');
        throw new Error('Service unavailable, please try again later');
      default:
        console.error('Database error:', err);
        throw err;
    }
  }
}

Performance Optimization Tips

  1. Set the connection pool size appropriately: usually 2-3 times the number of CPU cores
  2. Use a connection pool instead of a single connection: especially in web applications
  3. Use indexes appropriately: speed up query performance
  4. Batch operations: reduce round trips
  5. Use prepared statements: improve performance of repeated queries
  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 [result] = await promisePool.query(
      'INSERT INTO users (name, email) VALUES ?',
      [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: for example, passwords should be stored with salted hashes
  4. Use SSL connections: encrypted connections are recommended in production
  5. Regularly update dependencies: keep the mysql2 package up to date

Complete Example Project Structure

project/
├── config/
│   └── db.js          # 数据库配置
├── models/
│   └── userModel.js   # 数据模型
├── services/
│   └── userService.js # 业务逻辑
├── app.js             # 主应用文件
└── 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();
Other Extensions