Error Handling When Connecting Node.js to MySQL

Handling errors properly when connecting Node.js to MySQL is crucial to prevent your application from crashing and to provide meaningful feedback to the user. You can handle errors both during the connection process and while executing queries.

Here are some common strategies for error handling with MySQL in Node.js:

1. Connection Error Handling

You should check for errors when establishing a connection. This can be done with a callback function for mysql or using try/catch for mysql2.

Using mysql (with Callback):

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your-username',
  password: 'your-password',
  database: 'your-database-name'
});

connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected as id ' + connection.threadId);
});

Using mysql2 (with Promises and async/await):

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

(async () => {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'your-username',
      password: 'your-password',
      database: 'your-database-name'
    });

    console.log('Connected to MySQL!');
  } catch (err) {
    console.error('Error connecting to MySQL:', err.message);
  }
})();

2. Query Error Handling

While executing queries, you should also handle errors. Whether using the callback-based mysql or promise-based mysql2, you should check for errors after the query execution

Using mysql (Callback-based):

connection.query('SELECT * FROM your_table', (error, results, fields) => {
  if (error) {
    console.error('Error executing query:', error.stack);
    return;
  }
  console.log('Query Results:', results);
});

Using mysql2 (Promise-based):

(async () => {
  try {
    const [rows, fields] = await connection.execute('SELECT * FROM your_table');
    console.log('Query Results:', rows);
  } catch (err) {
    console.error('Error executing query:', err.message);
  }
})();

3. Graceful Error Handling

You should also handle cases where the database becomes temporarily unavailable, connection times out, or other unexpected failures occur. Here are some examples:

Reconnect on Failure: Attempt to reconnect to the MySQL database when the connection is lost.

const handleDisconnect = () => {
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'your-username',
    password: 'your-password',
    database: 'your-database-name'
  });

  connection.connect((err) => {
    if (err) {
      console.error('Error connecting to MySQL:', err.stack);
      // Reconnect after 2 seconds if connection fails
      setTimeout(handleDisconnect, 2000);
    } else {
      console.log('Connected as id ' + connection.threadId);
    }
  });

  // Handle disconnects and reconnect automatically
  connection.on('error', (err) => {
    console.error('MySQL error:', err);
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      handleDisconnect(); // Reconnect if connection is lost
    } else {
      throw err;
    }
  });
};

handleDisconnect();

4. Logging Errors

It’s good practice to log errors for debugging purposes or future analysis. You can use packages like winston, pino, or just the default console for logging errors.

Example with console.error:

if (err) {
  console.error(`[${new Date().toISOString()}] Error:`, err);
}

Example with winston:

const winston = require('winston');

const logger = winston.createLogger({
  level: 'error',
  format: winston.format.json(),
  transports: [
    new winston.transports.File({ filename: 'error.log' })
  ]
});

if (err) {
  logger.error(`[${new Date().toISOString()}] Error: ${err.message}`);
}

5. Handling Specific MySQL Errors

You can handle specific MySQL errors by checking their error codes. For example:

ER_ACCESS_DENIED_ERROR: Wrong credentials
ER_BAD_DB_ERROR: Database does not exist
PROTOCOL_CONNECTION_LOST: Connection was closed unexpectedly

connection.connect((err) => {
  if (err) {
    if (err.code === 'ER_ACCESS_DENIED_ERROR') {
      console.error('Invalid username or password');
    } else if (err.code === 'ER_BAD_DB_ERROR') {
      console.error('Database does not exist');
    } else {
      console.error('Error connecting to MySQL:', err.stack);
    }
    return;
  }
  console.log('Connected as id ' + connection.threadId);
});

Summary of Best Practices:

Always handle connection errors: Provide meaningful messages to the user or retry connections when appropriate.
Handle query execution errors: Check for errors after running queries.
Reconnect on lost connections: Implement logic to handle connection loss and automatically reconnect if necessary.
Log errors: Use logging tools to store error details for analysis and debugging.
Handle specific errors: Use MySQL error codes to handle common errors gracefully (e.g., bad credentials, database not found).

Tech Radioo