In this tutorial, we will guide you through the process of connecting to a MySQL database using Node.js. Establishing a connection between your Node.js application and MySQL database allows you to perform essential database operations, such as retrieving data, inserting records, updating information, and more.

By following this step-by-step guide, you will be able to connect to your MySQL database seamlessly. So, let’s dive in and get started!

Prerequisites

To follow along with this tutorial, you should have a basic understanding of Node.js and have it installed on your machine. Additionally, make sure you have MySQL installed and running.

To Install SQL Server and MySQL Workbench visit here

Install Required Packages

Before connecting to a MySQL database, ensure that you have the necessary Node.js packages installed. Open your terminal and navigate to your project directory.

Then, execute the following command to install the mysql package:

npm install mysql

Require the ‘mysql’ Module

const mysql = require('mysql');

Establish a Connection

To establish a connection to your MySQL database, create a connection object by passing the required configuration options. Insert the following code snippet in your file:

const connection = mysql.createConnection({
  host: 'localhost', // Replace with your MySQL host
  user: 'your_user', // Replace with your MySQL username
  password: 'your_password', // Replace with your MySQL password
  database: 'your_database' // Replace with your MySQL database name
});

Make sure to replace the placeholder values with your actual MySQL credentials.

Connect to the MySQL Database

Now, let’s connect to the MySQL database using the connection object. Insert the following code snippet below the connection object creation:

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

This code establishes the connection to your MySQL database and logs a success message if the connection is successful. If an error occurs, it will be displayed in the console.

Here, is the complete code to establish a connection:

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
  });
  return connection;
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful'): console.log('Connection to database not successfull');

Run this code using following command:

node index.js

You would be able to see following message once you establish the connection.

Connection to database successful

Closing the Database Connection

We can close the database connection using connection.end() function.

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

Here, is the complete code:

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
    database: credentials.db // Replace with your MySQL database name
  });
  return connection;
}

let close_connection = (connection) => 
{
  connection.end((err) => {
    if (err) {
      console.error('Error closing connection:', err);
      return;
     }
    console.log('Connection closed.');
  });
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: ''
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful'): console.log('Connection to database not successfull');
let connection_abort = close_connection(connection_live);

Connection to database successful
Connection closed.

Executing Query in MySQL DB

To execute any query in a database from our node application, we just need to use .query() function.

connection.query(query, callback())

Creating a Database

With the MySQL database connection established, you can now create the database

connection.query(`CREATE DATABASE ${databaseName}`, (err) => {
      if (err) {
        console.error('Error creating database:', err);
        return;
      }
      console.log(`Database '${databaseName}' created.`);
});

Here, is the complete code:

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
    database: credentials.db // Replace with your MySQL database name
  });
  return connection;
}

let create_database = (connection, databaseName) => 
{
  connection.query(`CREATE DATABASE ${databaseName}`, (err) => {
    if (err) {
      console.error('Error creating database:', err);
      return;
    }
    console.log(`Database '${databaseName}' created.`);
});
}

let close_connection = (connection) => 
{
  connection.end((err) => {
    if (err) {
      console.error('Error closing connection:', err);
      return;
     }
    console.log('Connection closed.');
  });
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: ''
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful') : console.log('Connection to database not successful');
connection_live ? create_database(connection_live, 'EmployeeData') : console.log('Connection to database not successful');
connection_live ? close_connection(connection_live) : console.log('No active Connection !');

Above code will do following tasks:

1. Create a Connection with MySQL.
2. If succeeded, it will invoke the function to create a database called ‘EmployeeData‘.
3. Close the connection.

Following would be printed on the console:

Connection to database successful
Database ‘EmployeeData’ created.
Connection closed.

Let’s verify the change from MySQL workspace

Selecting a Database

Once we create a database, we are free to create some tables within it. To use a database, follow this code:

// Select the created database
connection.query(`USE ${databaseName}`, (err) => {
 if (err) {
   console.error('Error selecting database:', err);
   return;
 }
  console.log(`Database '${databaseName}' selected.`);
});

Please note that now we are going to create a table within this database employeedata and will perform some basic CRUD operations. Hence, we are going to add this database in our config.

Modify create_connection() function as:

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host,
    user: credentials.user, 
    password: credentials.pass, 
    database: credentials.db,
  });
  return connection;
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: 'employeedata'
};

Creating Table using Node

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
    database: credentials.db // Replace with your MySQL database name
  });
  return connection;
}

let create_table = (connection) => 
{
  
  const query = `
    CREATE TABLE Employee (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(50),
      email VARCHAR(50),
      salary VARCHAR(50)
    );
  `;

  // Execute the table creation query
  connection.query(query, (err) => {
  if (err) {
    console.error('Error creating table:', err);
    return;
  }
  console.log(`Table Employee created.`);
});
}

let close_connection = (connection) => 
{
  connection.end((err) => {
    if (err) {
      console.error('Error closing connection:', err);
      return;
     }
    console.log('Connection closed.');
  });
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: 'employeedata'
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful') : console.log('Connection to database not successfull');
connection_live ? create_table(connection_live) : console.log('Connection to database not successful')
connection_live ? close_connection(connection_live) : console.log('No active Connection !');

This code demonstrates the process of creating a connection, creating a table, and closing the connection using the MySQL module in Node.js. It provides a structured way to manage the connection and perform table creation operations in a MySQL database.

Now, run this code using following command:

node index.js

Connection to database successful
Table Employee created.
Connection closed.

Let’s verify the change by check our MySQL workspace,

Inserting Data from Employee Table using Node

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
    database: credentials.db // Replace with your MySQL database name
  });
  return connection;
}

let insert_into_table = (connection, data) => 
{
 // SQL query to insert data into the table
 const insertQuery = `INSERT INTO employee SET ?`;

 // Execute the data insertion query
 connection.query(insertQuery, data, (err, results) => {
   if (err) {
     console.error('Error inserting data:', err);
     return;
   } 
   else
   {
    console.log('Data Inserted !');
   }
 });
}

let close_connection = (connection) => 
{
  connection.end((err) => {
    if (err) {
      console.error('Error closing connection:', err);
      return;
     }
    console.log('Connection closed.');
  });
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: 'employeedata'
};

const data = {
  ID: 3123,
  name: 'Shreyansh',
  email: '[email protected]',
  salary: '30 LPA'
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful') : console.log('Connection to database not successful');
connection_live ? insert_into_table(connection_live, data) : console.log('Connection to database not successful')
connection_live ? close_connection(connection_live) : console.log('No active Connection !');

Here’s the output:

Connection to database successful
Data Inserted !
Connection closed.

Let’s verify the changes in our database.

Updating Data from Employee Table using Node

Let’s update the salary of employee from 30 LPA to 40 LPA.

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
    database: credentials.db // Replace with your MySQL database name
  });
  return connection;
}

let update_table_data = (connection, updated_data) => 
{
 // SQL query to update data in the table
 const updateQuery = `UPDATE employee SET ? WHERE id = 3123`;

 // Execute the data update query
 connection.query(updateQuery, updated_data, (err, results) => {
   if (err) {
     console.error('Error updating data:', err);
     return;
   }
   console.log('Data updated successfully!');
   console.log('Affected rows:', results.affectedRows);
  });
}

let close_connection = (connection) => 
{
  connection.end((err) => {
    if (err) {
      console.error('Error closing connection:', err);
      return;
     }
    console.log('Connection closed.');
  });
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: 'employeedata'
};

const data = {
  salary: '40 LPA'
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful') : console.log('Connection to database not successful');
connection_live ? update_table_data(connection_live, data) : console.log('Connection to database not successful')
connection_live ? close_connection(connection_live) : console.log('No active Connection !');

Now, run this code using following command:

node index.js

Here’s the output:

Connection to database successful
Data updated successfully!
Affected rows: 1
Connection closed.

Let’s verify the changes in our database.

Deleting Data from Employee Table using Node

Let’s try to delete the only record that exists in the table Employee.

const mysql = require('mysql');

let create_connection = (credentials) => 
{
  const connection = mysql.createConnection({
    host: credentials.host, // Replace with your MySQL host
    user: credentials.user, // Replace with your MySQL username
    password: credentials.pass, // Replace with your MySQL password
    database: credentials.db // Replace with your MySQL database name
  });
  return connection;
}

let delete_data = (connection) => 
{
 // SQL query to update data in the table
 const query = `DELETE from employee WHERE id = 3123`;

 // Execute the data update query
 connection.query(query, (err, results) => {
   if (err) {
     console.error('Error updating data:', err);
     return;
   }
   console.log('Data deleted successfully!');
   console.log('Affected rows:', results.affectedRows);
  });
}

let close_connection = (connection) => 
{
  connection.end((err) => {
    if (err) {
      console.error('Error closing connection:', err);
      return;
     }
    console.log('Connection closed.');
  });
}

let credentials = {
  host: 'localhost',
  user: 'root',
  password: '',
  db: 'employeedata'
};

const data = {
  salary: '40 LPA'
};

let connection_live = create_connection(credentials);
connection_live ? console.log('Connection to database successful') : console.log('Connection to database not successful');
connection_live ? delete_data(connection_live, data) : console.log('Connection to database not successful')
connection_live ? close_connection(connection_live) : console.log('No active Connection !');

Now, run this code using following command:

node index.js

O/P

Connection to database successful
Data deleted successfully!
Affected rows: 1
Connection closed.

Let’s verify this change on our database side

Phew !! That’s all for now.

You have successfully connected to a MySQL database using Node.js. By following this tutorial, you now have the foundation to interact with your MySQL database, execute queries, and perform various database operations.