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
