AppDividend
Latest Code Tutorials

How To Connect Nodejs Application To MySQL Database

1,191

Get real time updates directly on you device, subscribe now.

In this tutorial, we will see, How To Connect Nodejs Application To MySQL Database. Node.js can be used in database applications, and one of the most popular databases is MySQL. We have used MongoDB almost time when we choose Node.js as a platform. That is why today’s example is for MySQL. For this first, you need to install MySQL server and then install the mysql package. You can download a free MySQL database at https://www.mysql.com/downloads/.

Earn a Tech Degree and get the skills like Frontend Development or Javascript Development that can help you to launch a career. Join the program

Connect Nodejs Application To MySQL Database

First, create a node.js project folder by the following command.

mkdir nodemysql
cd nodemysql

Now, create the package.json file by the following command.

npm init -y

Install the mysql package using the following command.

yarn add mysql

# or

npm install mysql --save

So you have downloaded the MySQL database driver and installed that driver successfully. Node.js can use this module to manage the MySQL database.

#Create a connection.

Create one file inside the root of the project called server.js and add the following code inside it.

// server.js

const mysql = require('mysql');

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

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

So, here we have imported the mysql module and called the createConnection() method and pass the three parameters. 

  1. host
  2. user
  3. password

In my case, username and password is root. You also can use the environment variable for this because it is significant credentials. Save the file and type the following command.

node server

If your local MySQL database credentials are right then you can connect your MySQL database with the Node.js application and in your terminal, you can see that we have connected our node app to the MySQL database.

 

How To Connect Nodejs Application To MySQL Database

#Query a Database.

We can use the SQL statements to read from a MySQL database or write the new data into the database. This is also called “to query” the database. We have already created the connection object created in the example above, and that object has a method for querying the database.

Till now, we have connected our node app to the mysql database, but now we will create a database in MySQL using the query. So let us see how we can create the new database.

// server.js

const mysql = require('mysql');

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

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  let sql = `CREATE DATABASE nodemysql`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("The Database is created!!");
  });
});

Save the file, and if the database is not previously created, then it will create a new one. Otherwise, it will throw an error.

#Node.js MySQL Create Table

To create the table in MySQL, we can use the “CREATE TABLE” statement. You need to make sure you define the name of the database when you create the connection. So we need to set one more parameter, while we are connecting our Node app to the MySQL database.

// server.js

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

The next thing is to write the following code inside the server.js file.

// server.js

const mysql = require('mysql');

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

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  let sql = `CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("The customers table is created!!");
  });
});

Save the file and restart the node.js server. We have not used the nodemon, so we need to restart the server manually.

node server

So in the console, we can see that the customers’ table is created. Now go to the MySQL client. You can either use phpMyAdmin or Sequel Pro.

 

node mysql example tutorial

Related Posts
1 of 17

So, we have successfully created the MySQL database and table.

That is it for the How To Connect Nodejs Application To MySQL Database. Thanks for taking.

 

1 Comment
  1. Chirag Shah says

    Hey krunal,
    Can you help me regarding Connector/Node.Js which is Mysql xdevapi ?

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.