NodeJs Tutorials
- NodeJs
- Install NodeJs
- Why use NodeJs
- NodeJs Process Model
- create First Application
- Run NodeJs Application
- Node.js Console
- Node.Js Modules
- URL Modules
- node.js Callback
- Node.js Events
- Upload Files
- Upload single & Multiple Files
- NodeJs File System
- NodeJs Email
- Debugging NodeJs
- .ENV
- NodeJs Mysql
- Helpers
- encription and decription in nodeJs
- Query string
- Date & Time
- Express Js
- Template Engine with Express
- MVC Pattern in Node.Js
- list of NPM Module
- Middleware
- Body Parser
- Render
- Nodemon module
- Morgan module
- Flash Message in ExpressJs
- Session
- Session store in database
- Cookies
- Helmet
- Multer
- Router: How To Use Routing In Node.Js
- App.Js
- express.json() and express.urlencoded()
- REST APIs in NodeJs
- Gloabal Objects
- Submit Form Data
- How to get Post Data in Node.js
- How to Get URL Parameters in Node.js
- How to create Node Project
- How to Insert Form Data Into the MySql Table Using Node.js
- How to fetch Data from MySQL database table using Node.js
- CRUD Example
- Await and Async
- Promises
- Login Example
- Password Encription
- How to validate Form (Form Validation) in Node.Js?
- Registration & Login form usingn Node.Js & MySql?
- Forgot & Reset Password
- File Upload in Node.Js with ExpressJs
- Resize Image Before Upload using Multer Sharp
- Upload multiple file using node.js with multer module
- Upload file using node.js with multer module
- Client IP Address
- REST API Downloading File in NodeJs
- Export and Download CSV From MySQL Database
- CRUD REST API
- CRUD REST API Example 2
- Enable HTTPS using Node
- How to send EMAIL using NodeJs?
- Dynamic dependent dropdown using NodeJs?
- Autocomplete Search
- Get and Send Data From Ajax Request
- Get and Post Data using Ajax
- Passport Authentication
- Node Js Data type
- Node Js Error
- Node Js Array Function
- Node Js String Function
- Puppeter Module
Node.Js Connection with MySQL Database
Install MySQL Driver
First install mySql Driver to access a MySQL database with Node.js.
File Name :
npm install mysql
Node.js can use this module to manipulate the MySQL database:
File Name :
var mysql = require('mysql');
Create Connection
File Name : connection.js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: ""
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
add the modules mysql.
const mysql = require("mysql");
use the createConnection() method to create a bridge between the Node.js and MySQL.
use the connect() method to connect to the database. This method will also throw an error if any issues arise while connecting to the MySQL database.
File Name :
Now open the command terminal and use the following command:
node connection.js
Query a Database
The query method takes an sql statements as a parameter and returns the result.
File Name :
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Result: " + result);
});
});
Creating a Table in nodeJS
File Name : create-table.js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "ittutorial"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = "CREATE TABLE users(name VARCHAR(255), address VARCHAR(255))";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created");
});
});
MySQL Insert
File Name : insert-record.js
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "ittutorial"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
//Insert a record in the "users" table:
var sql = "INSERT INTO users (name, address) VALUES ('ittutorial', 'delhi')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});
});
Select Query
File Name :
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "ittutorial"
});
con.connect(function(err) {
if (err) throw err;
//Select all users and return the result object:
con.query("SELECT * FROM users", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
});
Where Clause
File Name :
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "ittutorial"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM users WHERE name= 'sana'", function (err, result) {
if (err) throw err;
console.log(result);
});
});
Upate Query
File Name :
var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "ittutorial"
});
con.connect(function(err) {
if (err) throw err;
var sql = "UPDATE users SET address = 'delhi' WHERE address = 'Patna'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
});
MySql Connection Error :-
Error : {
"code": "ER_NOT_SUPPORTED_AUTH_MODE",
"errno": 1251,
"sqlMessage": "Client does not support authentication protocol requested by server; consider upgrading MySQL client",
"sqlState": "08004",
"fatal": true
}
File Name :
Solution :-
1)open -> "mysql intsaller"
2)press reconfig mysql server
3)select left side "authentication method tab"
4)select radio button -->use legacy authentication method
5)now stop and restart the db
########################### OR ####################
mysql -u root -p
(then type your password)
After that:
USE mysql;
UPDATE user SET authentication_string=password(''), plugin='mysql_native_password' WHERE user='root';
MySql Connection Error Solution:-
File Name :
1) Open mysql intsaller.
2) Click on Reconfigure (to the left mysql server)
3) Go to Authentication method tab (by clicking on Next twice.)
4) Then select the radio button Use Legacy Authentication Method. Click Next.
5) Enter your password. Click on Check. Go to Apply Configuration (by clicking on Next twice).
6) Click on Execute. Then Finish.
var mysql = require('mysql');
const app = express();
app.get("/createdb", (req, res) => {
let sql = "CREATE DATABASE mydb";
con.query(sql, (err) => {
if (err) {
throw err;
}
res.send("Database created");
});
});
Here we are using the Express module to create the web server, then you create a variable named app that behaves as an object of the express module.
We use the GET API request to create the database. first defined the SQL query for creating the database. then pass this query to the query() method along with the error callback method. if The error occurs then it throws an error if the query wasn't successfully executed.
File Name :
Create Table :-
create a table called “Users” to store the Users details into the database
File Name :
// Create table
app.get("/createusers", (req, res) => {
let sql =
"CREATE TABLE users(id int AUTO_INCREMENT, name VARCHAR(255), designation VARCHAR(255), PRIMARY KEY(id))";
db.query(sql, (err) => {
if (err) {
throw err;
}
res.send("ittutorial users table created");
});
});
Insert Users :-
File Name :
app.get("/users1", (req, res) => {
let post = { name: "sana", designation: "developer" };
let sql = "INSERT INTO users SET ?";
let query = db.query(sql, post, (err) => {
if (err) {
throw err;
}
res.send("user added");
});
});
Update users :-
we can use a GET request on that user id, and update user info.
File Name :
app.get("/updateuser/:id", (req, res) => {
let newName = "Updated name";
let sql = `UPDATE user SET name = '${newName}' WHERE id = ${req.params.id}`;
let query = db.query(sql, (err) => {
if (err) {
throw err;
}
res.send("user info updated...");
});
});
Delete user
File Name :
app.get("/deleteuser/:id", (req, res) => {
let sql = `DELETE FROM user WHERE id = ${req.params.id}`;
let query = db.query(sql, (err) => {
if (err) {
throw err;
}
res.send("user deleted");
});
});
Why is Connection Pooling better than Single Connection
There are two ways to establish a connection with a database in node.Js.
What is Single Connection?
In Single database connection using only one shared connection to execute all the queries triggered by all your application users.
With this type of connection, when a user makes a query, the application creates a connection and sends the user query to the database for execution. After that, the application gets the results and displays the data to the user and closes the connection which means disconnect the database. So the application will create a new connection for the next new query. This process of closing and creating connection may slow down your application and reduce its performance.
File Name :
What is Connection Pooling?
Connection pools are used to improve the performance of the queries’s execution on a database without exception, prevent opening and closing connections frequently and reduce the number of new connections.
In connection pooling, a connection can be an active connection or an available connection. When a new connection is created, it is placed in the pool and used to execute the query has just arrived. After execution, the application closes the connection, so pool is notified that the connection is available and ready for reuse. Having connections created in advance reduce the user waiting time needed to create new connection to execute new queries.
File Name :
How Connection Pooling works
When an application requests a connection from the connection pool, the Pool assigns an available connection. If an unused connection exists, the pool return it. Otherwise, if there is no available connection, the Pool will create a new connection and assigns it to the application as an active connection.
File Name :
File Name :
File Name :
File Name :