Most Popular Tutorials
Most Popular Tutorials :-

Simply Easy Learning at Your Fingertips. Click Tutorials Menu to view More Tutorial List





How to connect node.js 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.

  • Host: Specifies the host that runs the database
  • User: Sets the mysql user’s name :- root
  • User: Sets the mysql Password :- ****
  • Database: Name of the database

    • 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.

  • Single Connection
  • Connection Pool

    • 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 :

    ittutorial

    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.

  • When a connection is created, it is added to the pool for multiple uses.
  • After connection, the application sends a query for execution, obtains the results and display it to the user.
  • Then the application closes the connection and returns the connection to the pool as an available connection.
  • If a connection is corrupted, it will be removed from the pool.

    • File Name :

    ittutorial

    Example :- Single Database connection.

    File Name : database/db.js

    var mysql=require('mysql');
    var conn=mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'root',
    database:'itech_xpert'
    });
    conn.connect(function(error){
    if(!!error){
    console.log(error);
    }else{
    console.log('database Connected!:)');
    }
    });
    module.exports = conn;





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link