How to Connect Node.js with MySQL

MySQL is one of the most popular relational database management systems (RDBMS) used in web development. On the other hand, Node.js is the popular JavaScript based server side framework that offers asynchronous event-driven architecture for server side application. By connecting MySQL with Node.js allows you to create dynamic applications that can query, store, and manipulate data within a database. In this article, we will follow the steps to setting up a connection between Node.js and MySQL, including the installation of the required packages and establishing a connection.

Step 1: Install the Required Node.js MySQL Package

Node.js does not natively connect with MySQL databases. To enable this, you need to install a MySQL driver for Node.js, such as the mysql package. This mysql package will handle communication between your Node.js application and the MySQL database. Run the following command in your project directory to install the MySQL package.

npm install mysql

Step 2: Create a Database in MySQL

Before Node.js and MySQL connect you need to create a database on your local or online MySQL server.

Step 3: Establishing a Connection to MySQL

Once you have your database set up and the mysql package installed, you can create a connection to MySQL in your Node.js application. In your project directory, create a new JavaScript file.

const con = new mysql.createConnection({
    host : 'localhost',
    user : 'root',
    password : 'Qwerty@2022',
    database : 'testing'
});

const connectDB = async () => {
    con.connect(function (err) {
        if(err) {
            console.error('Error connecting to database: ', err.stack);
            process.exit(1);
        }
        console.log('Connection established with ID: ', con.threadId);
        return con;
    });
}

• host: The host of your MySQL server, usually localhost.

• user: The MySQL username (in most cases root for development purposes).

• password: Your MySQL user password (it may be blank for local servers).

• database: The name of the MySQL database you wish to connect to.

Step 4: Closing the Connection

After opening the connection and performing your queries, it's important to close the connection to MySQL to avoid memory leaks or connection issues. You can do this by calling the connection.end() method.

const endConnection = async () => {
    con.end((err) => {
        if(err) {
            console.error('Error while closing connection: ', err.stack);
            process.exit(1);
        }
        console.log('Connection Closed Successfully!');
        return con;
    });
}

Conclusion

Connecting Node.js to MySQL allows you to interact with databases seamlessly within your Node.js applications. With the help of the mysql module, you can easily execute SQL queries, insert, update, delete, and fetch data from MySQL databases.

const mysql = require('mysql');

const con = new mysql.createConnection({
    host : 'localhost',
    user : 'root',
    password : 'Qwerty@2022',
    database : 'testing'
});

const connectDB = async () => {
    con.connect(function (err) {
        if(err) {
            console.error('Error connecting to database: ', err.stack);
            process.exit(1);
        }
        console.log('Connection established with ID: ', con.threadId);
        return con;
    });
}

const endConnection = async () => {
    con.end((err) => {
        if(err) {
            console.error('Error while closing connection: ', err.stack);
            process.exit(1);
        }
        console.log('Connection Closed Successfully!');
        return con;
    });
}

module.exports = {connectDB, endConnection};