NodeJS MySQL Create Connection

MySQL is a relational database management system that stores data in one or more tables of columns and uses structured query languages to perform different operations on them.

For interacting with MySQL Database it is required to run a MySQL server in the background. You can install MySQL from https://www.mysql.com/downloads/.

Installation and Setup of MySQL from the above link might be complex for beginners, what we promote is simplicity so you can prefer installing XAMPP.

Installation of XAMPP to Run MySQL

XAMPP is an open-source package that can run a MySQL server locally on your system with a single click. It also provides a graphical interface to create a database, insert data, delete data, etc., making it easy to interact with a relational database.

XAMPP can also be used to monitor database activity such as whether the database is successfully created or whether the query we are performing is accurate. You can install XAMPP from https://www.apachefriends.org/.

After installation, open the XAMPP Control Panel and start the MySQL server.

Interacting with MySQL Database using NodeJS

To create a connection to the MySQL database using NodeJS it is required to install the MySQL module using NPM.

Initial NPM

NPM stands for Node Package Manager used to install modules in Node.js.

npm init -y

This command will initial NPM for a project.

Install MySQL Module

MySQL Module is used to interact with the MySQL database.

npm i mysql

This command will install the MySQL module.

Import MySQL Module

For using MySQL in a project, it is necessary to import it.

const mysql = require('mysql');

This command will import the MySQL module.

createConnection() Method

This method takes an object as an argument containing the host, username, and password.

const sql = mysql.createConnection({
    host: "localhost",
    user: "username",
    password: "password"
});

Use the username and password from your MySQL database. If you haven’t set up any credentials then pass “root” as a username, and an empty string as a password.

connect() Method

In order to connect with the configuration we just set up, we have to use dot notation after the “sql” constant created in the above step then use a method connect() and pass a callback as an argument to check for the error, and then the rest of the callback body.

sql.connect(function (err) {
    if (err) {
        throw err;
    }
    // callback body
});

Example of Connecting to MySQL using NodeJS

Let’s see an example to wrap up what we learn.

Step 1: Make sure that the MySQL server running in the background.

Step 2: Create a folder, with the file “app.js”.

Step 3: Open the terminal and type the below command to Initial NPM.

npm init -y

Step 4: Open the terminal and type the below command to Install the MySQL module.

npm i mysql

Step 5: Inside the “app.js” file, import the MySQL module.

const mysql = require('mysql');

Step 6: Create a Connection using the createConnection() method and pass an object containing the given key-value pairs.

const sql = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: ""
});

Step 7: Connect the MySQL server using connect() method.

sql.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log("Connected to MySQL!");
});

Step 8: Type the below command to run the application.

node app.js

Complete Code:

const mysql = require('mysql');

const sql = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: ""
});

sql.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log("Connected to MySQL!");
});

Output:

Summary

NodeJS can interact with MySQL database using “mysql” module that can install using npm. This module provides many methods in order to perform different operations. We can use createConnetion() and connect() methods to create a connection to MySQL and connect to it to create a database, create a table, insert data into a table, etc. Hope this tutorial helps you to understand the process of creating the MySQL connection using NodeJS.