engineer diaries Logo

Mysql with NodeJs Connection and CRUD

by Priyanka Arora, May 31, 2020, 1206 Views, 1
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.

Set up MySql Community Server

To install MySQL Installer: Go to the link belowhttps://dev.mysql.com/downloads/installer/

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 idfullname and insert 1 row into the table.

Mysql with NodeJs Connection and CRUD
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.


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.

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.’);
    });

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

Frequently Asked Questions

Mongodb is nosql based database and based on document structure providing more flexibility than mysql

Yes the query part remain exact same.

Yes. You can find that post in similar post below

Upload the file on server and store its link in the database

Similar Posts:

Mean stack tutorial in 30 minutes-2020
May 31, 2020, 1206 Views, 1
Express Js Routing Tutorial | Beginners
May 31, 2020, 1206 Views, 1
Sql Server with NodeJs CRUD Operations
May 31, 2020, 1206 Views, 1
MongoDB with NodeJs CRUD Tutorial Guide
May 31, 2020, 1206 Views, 1

Comments Section:

Viraj

Nov 12, 2020

Worked in first go man!