Connect Node.js to MySQL Server

To connect Node.js with a MySQL server, you can use the mysql or mysql2 package. Below are the steps for setting up a basic Node.js application with MySQL:

1. Install MySQL Package

First, you need to install the MySQL driver for Node.js. You can do this using npm:

$ npm install mysql

Or, if you prefer mysql2 (which supports promises and other advanced features):

$ npm install mysql2

2. Create MySQL Connection

In your Node.js application, require the MySQL package and create a connection to your MySQL server. Here’s how:

Using mysql:

const mysql = require('mysql');

// Create a connection
const connection = mysql.createConnection({
  host: 'localhost',    // MySQL server host
  user: 'your-username', // MySQL username
  password: 'your-password', // MySQL password
  database: 'your-database-name' // The database to connect to
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL!');
});

// Example query
connection.query('SELECT * FROM your_table', (error, results, fields) => {
  if (error) throw error;
  console.log(results); // Output the query result
});

// Close the connection
connection.end();

Using mysql2 (with Promises):

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

(async () => {
  try {
    // Create a connection
    const connection = await mysql.createConnection({
      host: 'localhost',    // MySQL server host
      user: 'your-username', // MySQL username
      password: 'your-password', // MySQL password
      database: 'your-database-name' // The database to connect to
    });

    console.log('Connected to MySQL!');

    // Example query
    const [rows, fields] = await connection.execute('SELECT * FROM your_table');
    console.log(rows); // Output the query result

    // Close the connection
    await connection.end();
  } catch (err) {
    console.error('Error connecting to MySQL:', err);
  }
})();

3. Configuration Options

You can customize the connection options to meet your needs. Here are some additional options:

port: If MySQL is running on a custom port, specify it using the port option.
multipleStatements: Set this to true if you need to execute multiple SQL statements in a single query.

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your-username',
  password: 'your-password',
  database: 'your-database-name',
  port: 3306, // Optional: MySQL port (default is 3306)
  multipleStatements: true, // Allow multiple SQL statements in one query
});

4. Handle Errors

It’s essential to handle connection errors gracefully. You can use a try/catch block with async/await or handle it via callbacks.

Tech Radioo