July 6, 2020
Mysql with NodeJs Connection and CRUD

Mysql with NodeJs Connection and CRUD

Mysql with NodeJs is a divine combination! You could use the relational database with the power of javascript server runtime like nodejs. In this tutorial we will set up MySQL community server, installer, and workbench, followed by nodejs application from scratch, and then finally establish the connection using mysql node module. If you already have mysql installed or nodejs application setup, you could directly move on to the last part. So, let’s get started with the tutorial.

1.Set up MySql Community Server

This is an instruction manual to set up oracle MySQL community server and workstation using MySQL installer. We will also create a testdatabase and add userprofile table to it.

To install MySQL Installer: Go to the link below

https://dev.mysql.com/downloads/installer/

 

Mysql with NodeJs Connection and CRUD

Once installed which would take 10-15 mins. You can add other products. In this particular tutorial, we require MySQL server and workstation.  Add both of these to your system. Next step in this mysql with nodejs tutorial is to create a database named testdatabase. Add userprofile table with fields id, fullname and insert 1 row into the table.

Mysql with NodeJs Connection and CRUD

Mysql with NodeJs Connection and CRUD

2.Set up NodeJs Application Server

Setting up nodeJs application is easy, but you need to have node and npm installed. Create a folder. In that on terminal type,

npm init to initialize the repository as node.js application. you should see package.json file in the directory.

MongoDB with NodeJs CRUD Tutorial Guide

Create app.js file and type the following code

var msg=’Hello world!’;

console.log(msg)

Now, to run the application type node app.js

MongoDB with NodeJs CRUD Tutorial Guide

Now, you have successfully set up your node application.

3.Establish Mysql with NodeJs Connection

As stated, we would be using mysql node module. So, on terminal type npm install mysql. Next step to establish connection of mysql with nodejs paste the code in app.js.

Mysql with NodeJs Connection and CRUD

var mysql = require(“mysql”);
      let connection = mysql.createConnection({
        host: ‘localhost’,
        user: ‘root’,
        password: ‘xxxxxx’,
        database: ‘testdatabase’
    });
    connection.connect(function(err) {
        if (err) {
          return console.error(‘error: ‘ + err.message);
        }
        console.log(‘Connected to the MySQL server.’);
    });
Note:If you are getting an error similar to below:
Mysql with NodeJs Connection and CRUD
Then on installer go to reconfigure and on next next in authentication method choose use legacy authentication method, it should work fine.
Mysql with NodeJs Connection and CRUD

4.Perform CRUD operations in Mysql with NodeJs

After you have connected to database you need to execute the queries. The exact string queries you use in MySQL works well in mysql with nodejs too.

var mysql = require(“mysql”);
      let connection = mysql.createConnection({
        host: ‘localhost’,
        user: ‘root’,
        password: ‘xxxx’,
        database: ‘testdatabase’
    });
    connection.connect(function(err) {
        if (err) {
          return console.error(‘error: ‘ + err.message);
        }
        console.log(‘Connected to the MySQL server.’);
        let selectQuery = `SELECT * FROM userprofile`;
        connection.query(selectQuery, (error, results, fields) => {
        if (error) {
            return console.error(error.message);
        }
        console.log(results);
        });
        let insertQuery = `insert into userprofile (id,fullname) values(2,’Nidhi’)`;
        connection.query(insertQuery, (error, results, fields) => {
        if (error) {
            return console.error(error.message);
        }
        console.log(“inserted”);
        console.log(results);
        });
        let updateQuery = `update userprofile set fullname=’Nidhi2′ where id=2`;
        connection.query(updateQuery, (error, results, fields) => {
        if (error) {
            return console.error(error.message);
        }
        console.log(“updated”);
        console.log(results);
      });
      let deleteQuery = `delete from userprofile  where id=2`;
      connection.query(deleteQuery, (error, results, fields) => {
      if (error) {
          return console.error(error.message);
      }
      console.log(“Deleted”);
      console.log(results);
    });
    });
So, this was a short and complete tutorial on connecting mysql with nodejs. Keep on coding. Keep on learning. And if you find this tutorial helpful comment would be loved♥♥
Similar Blogs:
http://engineerdiaries.com/mean-stack-tutorial-in-30-minutes-2020/
http://engineerdiaries.com/express-js-routing-tutorial-beginners/
http://engineerdiaries.com/sql-server-with-nodejs-crud-operations/
http://engineerdiaries.com/mongodb-with-nodejs-crud-tutorial-guide/

 

 

admin

Engineer Diaries started with the need to bridge the huge gap in what we are taught vs what the industry demands. We are based in Delhi, India but our blog is for everyone, in and outside tech industry❤ Feel free to reach out to us at engineerdiaries@gmail.com for any business/personal query.

View all posts by admin →

Leave a Reply