Instructions for connecting Node.js with MySQL

Instructions for connecting Node.js with MySQL

Instructions for connecting Node.js with MySQL - In this article, we will learn how to do database operations like create, update, insert, delete with Nodejs Mysql database and NodeJs application...

Instructions for connecting Node.js with MySQL - In this article, we will learn how to do database operations like create, update, insert, delete with Nodejs Mysql database and NodeJs application...

Before we go into the coding part, let’s get a brief introduction about Node.js and MySQL technologies.

Node Js: It‘s a platform built on Chrome’s JavaScript runtime for scalable network applications and easily building and fast building applications. It’s an open-source platform which will execute server-side javascript code.

** MySQL**: MySQL is an open-source RDBMS (Relational Database Management System) that uses SQL(Structured Query Language). It is the most popular language for doing multiple operations with the database. So, In this blog, we will use MySQL as a database for node application.

NodeJS MYSQL tutorial prerequisites:

Before you join in this tutorial it is assumed that you meet requirements below.

  • Make sure that Node Js has installed on your system.
  • Understanding on basic Node JS and Express JS.

  • Knowledge on SQL queries.

  • I recommend you to install xampp on your system.

    Node.js and MySQL are one of the necessary binding needed for the development of web applications.

MySQL is one of the most popular open-source databases, and it is efficient as well.

Almost every popular programming language provides a driver to access and perform operations with MySQL.

Now, let’s move to the NodeJS MYSQL coding part:

Step 1:

Make a directory for our application

 $ mkdir nodeMySQL
 $ cd nodeMySQL

Step 2:

Now, we will generate package.json, so that all dependencies can be listed. Run the following command.

 $ npm init

Step 3:

Now install all the required modules.

Express:

$ npm install express

Body-parser:

$ npm install body-parser

Express-session:

$ npm install express-session

MYSQL:

$ npm install mysql

Nodemon:

$ npm install nodemon

Cors:

$ npm install cors

Step 4:

Now require all modules in index.js and start the server with 3000 port. Find below code of index.js

const express = require('express');

const bodyParser = require('body-parser');

const session = require('express-session');

const cors = require('cors');

const mysql = require('mysql');

const { promisify } = require('util')



const app = express();

app.use(bodyParser.json());

app.use(session({

   secret : 'thesecret',

   saveUninitialized: false,

   resave : false

}));



router = express.Router({

   mergeParams: true

}), router.use(cors());



const startServer = async () => {

   const port = process.env.SERVER_PORT || 3000;

   await promisify(app.listen).bind(app)(port);

   console.log(`Listening on port ${port}`);

}

startServer();



module.exports = router;

MySQL Database Operations

Create Database:

To create a database, first we have to connect to the database and we can create a database using “CREATE DATABASE dbName” query. Please find the following code.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd"

});



conn.connect((err) => {

   if(err) throw err;

   /*Create Database name using "mydatabase" */

   conn.query("CREATE DATABASE mydatabase", (err, result) => {

       if(err) throw err;

       console.log("Database is created..");

   })

});



Create a Table:

Create a table with table name called users and make id as Int, primary key and auto_increment. Which will insert a unique number for each insert.

 const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let createTableQuery = "CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255), address VARCHAR(255))";

   conn.query(createTableQuery, (err, result) => {

       if (err) throw err;

       console.log("Table created");

   })

});

Alter Table:

Now the table has created with table name called users. Now we want to add one more column for user table. Then we will use ALTER keyword and follow the code below.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let createTableQuery = "ALTER TABLE users ADD COLUMN phone_number BIGINT(10)";

   conn.query(createTableQuery, (err, result) => {

       if (err) throw err;

       console.log("Table is altered..");

   })

});

Insert Into Table:

To fill data in a table, we have to insert data using INSERT INTO statement as follows.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err)=> {

   if (err) throw err;

   console.log("Connected!");

   let insertQuery = "INSERT INTO users (name, address, phone_number) VALUES ('Narendra Modi', 'India', '1090290987')";

   conn.query(insertQuery, (err, result) => {

       if (err) throw err;

       console.log("1 record inserted");

   })

})

Select From:

To select data from a table we use SELECT statement and please find the following code. Assume that we have 100 number of rows in the user table.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let selectQuery = "SELECT * FROM `users`";

   conn.query(selectQuery, (err, result) => {

       if (err) throw err;

       console.log(result);

   })

});

Where:

Let’s take we have 120 rows in our user table. Now, we want to fetch the records name matches with Narendra then we will use *WHERE *clause in the query. Please find the following code.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let selectQueryWhere = "SELECT * FROM `users` where name = 'Narendra Modi'";

   conn.query(selectQueryWhere, (err, result) => {

       if (err) throw err;

       console.log(result);

   })

})

ORDER BY:

After getting records using SELECT statement, now we want in order by ascending, then we will apply ORDER BY to the select query as follows.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let selectQueryOrderBy = "SELECT * FROM `users` ORDER BY id asc";

   conn.query(selectQueryOrderBy, (err, result) => {

       if (err) throw err;

       console.log(result);

   })

})

Update:

Suppose we want to update a particular record with a particular matching columns rows, then we will use UPDATE statement as follows.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let updateQuery = "UPDATE users SET address = 'America 123' WHERE address = 'India'";

   conn.query(updateQuery, (err, result) => {

       if (err) throw err;

       console.log(result);

   })

})

Delete:

Now if we want to delete a particular row or rows we can use DELETE statement as follows.

const conn = mysql.createConnection({

   host: "localhost",

   user: "root",

   password: "mypasswd",

   database: "mydatabase"

});



conn.connect((err) => {

   if (err) throw err;

   console.log("Connected!");

   let deleteQuery = "DELETE FROM `users` WHERE name='Narendra Modi'";

   conn.query(deleteQuery, (err, result) => {

       if (err) throw err;

       console.log(result);

   })

});

If you’ve any doubts, please let us know through comment!!

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

How to Use Express.js, Node.js and MongoDB.js

In this post, I will show you how to use Express.js, Node.js and MongoDB.js. We will be creating a very simple Node application, that will allow users to input data that they want to store in a MongoDB database. It will also show all items that have been entered into the database.

Node.js for Beginners - Learn Node.js from Scratch (Step by Step)

Node.js for Beginners - Learn Node.js from Scratch (Step by Step) - Learn the basics of Node.js. This Node.js tutorial will guide you step by step so that you will learn basics and theory of every part. Learn to use Node.js like a professional. You’ll learn: Basic Of Node, Modules, NPM In Node, Event, Email, Uploading File, Advance Of Node.

Top 7 Most Popular Node.js Frameworks You Should Know

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser. In this post, you'll see top 7 of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).