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!!

node-js mysql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Node JS with Typescript TypeORM Mysql (Controller & Service ) #12

Full Playlist https://www.youtube.com/playlist?list=PLT5Jhb7lgSBNmwHVnjEY7uwTv9_lDV1_s GitHub - Node JS with Typescript Mysql https://github.com/tkssharma/Ty...

Node JS with Typescript TypeORM Mysql (Docker compose) #04

Full Playlist https://www.youtube.com/playlist?list=PLT5Jhb7lgSBNmwHVnjEY7uwTv9_lDV1_s GitHub - Node JS with Typescript Mysql https://github.com/tkssharma/Ty...

How to Hire Node.js Developers And How Much Does It Cost?

A Guide to Hire Node.js Developers who can help you create fast and efficient web applications. Also, know how much does it cost to hire Node.js Developers.

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

Node JS with Typescript TypeORM Mysql (Mysql DB Service ) #08

Full Playlist https://www.youtube.com/playlist?list=PLT5Jhb7lgSBNmwHVnjEY7uwTv9_lDV1_s GitHub - Node JS with Typescript Mysql https://github.com/tkssharma/Ty