How to Connect to MySQL with mysql2

Table Of Contents

• Problem
• Solution
• Explanation

Problem

You need to connect your Node.js application to a MySQL database for data operations with better performance than the original mysql package.

Solution

const mysql = require('mysql2/promise');

// Basic connection
async function connectMySQL() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'myapp'
    });
    
    console.log('Connected to MySQL');
    
    // Test query
    const [rows] = await connection.execute('SELECT 1 as test');
    console.log('Test query result:', rows[0]);
    
    await connection.end();
  } catch (error) {
    console.error('MySQL connection error:', error);
  }
}

// Connection pool (recommended)
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Basic queries with pool
async function queryWithPool() {
  try {
    // Simple query
    const [rows] = await pool.execute('SELECT * FROM users LIMIT 5');
    
    // Prepared statement
    const [userRows] = await pool.execute(
      'SELECT * FROM users WHERE id = ? AND status = ?',
      [1, 'active']
    );
    
    return { allUsers: rows, user: userRows[0] };
  } catch (error) {
    console.error('Query error:', error);
    throw error;
  }
}

// CRUD operations
async function userCRUD() {
  // Create
  const [result] = await pool.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['John Doe', 'john@example.com']
  );
  
  const userId = result.insertId;
  
  // Read
  const [users] = await pool.execute(
    'SELECT * FROM users WHERE id = ?',
    [userId]
  );
  
  // Update
  await pool.execute(
    'UPDATE users SET name = ? WHERE id = ?',
    ['Jane Doe', userId]
  );
  
  // Delete
  await pool.execute('DELETE FROM users WHERE id = ?', [userId]);
  
  return users[0];
}

// Transaction example
async function withTransaction() {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    await connection.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      ['John', 'john@example.com']
    );
    
    await connection.execute(
      'INSERT INTO orders (user_id, total) VALUES (?, ?)',
      [1, 99.99]
    );
    
    await connection.commit();
    console.log('Transaction completed');
  } catch (error) {
    await connection.rollback();
    console.error('Transaction failed:', error);
    throw error;
  } finally {
    connection.release();
  }
}

Install mysql2:

npm install mysql2

Explanation

mysql2 is faster and supports promises natively. Use createPool() for multiple connections and better performance. Always use prepared statements (? placeholders) to prevent SQL injection.

The pool automatically manages connections and is more efficient for applications with frequent database operations than creating individual connections.