Create Mysql Connection

To create a MySQL connection in a Node.js application.

we will need to use a MySQL driver. One of the most popular drivers for MySQL in Node.js.

To Connect Mysql Database Locally:

We need to `Start Mysql Server` and Download Mysql Workbench GUI.

Example:

MySQL Server Installation Steps Click Here

Install the mysql package:

Using npm we can do it.

Example:

npm install mysql

Create a MySQL connection:

In the Node.js application, we can create a connection to our MySQL database using the "mysql" module.

Example:

const mysql = require("mysql");


function connectDB() {
  // Create a connection to the MySQL database
  const connection = mysql.createConnection({
    host: "localhost", // MySQL server hostname
    user: "root", // MySQL username
    password: "password", // MySQL password
    database: "your_database_name", // MySQL database name
  });

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

  // Perform database operations here

  // Close the MySQL connection when done
  connection.end();
}

module.exports = connectDB;

For Cloud Database: Replace the connection options (host, user, password, database) with the appropriate values for your MySQL database server.

For Local Database: Replace the connection options (user, password, database) with the appropriate values for your MySQL database server.

Note:

// If you encounter with error: Error connecting to MySQL database: Error: ER_BAD_DB_ERROR: Unknown database `your_database_name`.

# Then, navigate to mysql Workbench and Run Command in Query Tab:
# Command: CREATE DATABASE your_database_name;

// it will explicitly create database in your mysql workbench.

Handle Connection Events:

We can listen for various events emitted by the MySQL connection to handle connection-related events, such as errors and disconnections.

Example:

// Handle MySQL connection errors
connection.on('error', (err) => {
  console.error('MySQL connection error:', err);
});

// Handle MySQL connection end
connection.on('end', () => {
  console.log('MySQL connection ended');
});

We can add event handlers to handle errors and other connection-related events.

Note: Now we can Perform Database Operations:

Close the Connection:

When done with the MySQL connection, make sure to close it to release resources and prevent memory leaks.

Example:

// Close the MySQL connection
connection.end((err) => {
  if (err) {
    console.error('Error closing MySQL connection:', err);
    return;
  }
  console.log('MySQL connection closed');
});

Call the "end()" method of the "connection object" to close the connection. Optionally, we can provide a callback function to handle any errors that occur during the closing process.

Full Code:

create file name `db.js` in the root folder directory and paste the below code.

Example:

const mysql = require("mysql");

function connectDB() {
  // Create a connection to the MySQL database
  const connection = mysql.createConnection({
    host: "localhost", // MySQL server hostname
    user: "root", // MySQL username
    password: "password", // MySQL password
    database: "your_database_name", // MySQL database name
  });

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

  // Close the MySQL connection when done
  connection.end();
}

module.exports = connectDB;

Create a file name `server.js` in corresponding to `db.js` and paste the below code.

Example:

let http = require("http");
const dbConnection = require("./db");

http
  .createServer((req, res) => {
    res.writeHead(200, { "Content-Type": "text/html" });
    res.end("Server Listening...");
  })
  .listen(3002, () => {
    console.log(`server listening on port 3002!`);
    dbConnection();
  });

`server.js` is the entry point of our project and we have imported `db.js` in this file. when the server starts listening to incoming requests it will establish a connection with `mysql` database.

Create Database -->