In this article, we are going to learn about one of the most used databases with Node Js,MySQL.We will learn how to integrate Node JS and MySQL,make basic APIs for CRUD operations and the pros and cons of using MySQL as a Database.Now we assume that you have learnt about the basics of Node JS and having it in your systems, if not then go through this article.

This article is split into 2 parts:



What is MySQL?

MySQL is a popular open-source relational database management system (RDBMS) that is developed, distributed and supported by Oracle Corporation. Like other relational systems, MySQL stores data in tables and uses structured query language (SQL) for database access.

Installation :

Check if your system is already having mysql installed or not by typing this command in your terminal:

$  mysqladmin –version

You will see the version of MySQL as output with some more details.If MySQL is not installed on your machine, you should head to MySQL Installation Guide, download and install the version compatible with your operating system before you continue.

Let’s Dive In:

Create a new folder ( “projectNew” or whatever you want or wherever you want and open that folder in your favourite text editor and open the integrated terminal and enter the following command and answer the questions to create the initial structure for your new project:

$  npm init

Now the next step would be to install driver dependencies.The three basic packages we are going to install are:

  • 1- ExpressJS: It’s a flexible Node.JS web application that has many features for web and mobile applications
  • 2- mysql: the MySQL driver for Node.JS.
  • 3- body-parser: package that can be used to handle JSON requests.

These packages can be installed by following command in the terminal:

$  npm install –save express body-parser mysql

Now create a file server.js in the same directory and require all the dependencies and assigning a port as stated below(you can copy paste this exact code)

const express = require(‘express’);
const bodyParser = require(‘body-parser’);
const app = express();

// initializing our app
let port = 3000;
app.listen(port, () => {    console.log(‘Server is up and running on port number ‘ + port);});

And after this step we need to use bodyparser. Body Parser is an npm package that is used to parse the incoming request bodies in a middleware. Paste the following code just below the one you just pasted.

app.use(bodyParser.urlencoded({extended: false}));

Save this and head over to terminal to test this by typing node server.js command. By doing so you will see this in your terminal Server is up and running on port number 3000 and this shows that we have successfully created our app.

Creating Database and Table

To create a database,headover to a new terminal. You can name your database whatever you want, I am naming it here as ‘mydb’, you can create your database from mysql shell by typing mysql and hitting enter then by following command:(Change mydb below with the name you want for your DB)

mysql> create database mydb;

This will create a database of same name. After this you need to create a table in this database you can do so by following, but before that we need to specify which database we are going to use:

mysql> use mydb;

After this,copy paste the following code in your terminal:

mysql> CREATE TABLE IF NOT EXISTS `todos` ( `id` int(11) NOT NULL,`task` varchar(200) NOT NULL,`status` tinyint(1) NOT NULL DEFAULT ‘1’, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql>ALTER TABLE `todos` ADD PRIMARY KEY (`id`);

mysql>ALTER TABLE `todos` MODIFY `id` int(11) NOT NULL                              AUTO_INCREMENT ;

This will create your table todo.And as we have just created this table so this is empty and we need to insert some values into it, we can do so by following commands executed one by one:

mysql>INSERT INTO `todos` (`id`, `task`, `status`, `created_at`) VALUES
(1, ‘Find bugs’, 1, ‘2016-04-10 23:50:40’),
(2, ‘Review code’, 1, ‘2016-04-10 23:50:40’),
(3, ‘Fix bugs’, 1, ‘2016-04-10 23:50:40’),
(4, ‘Refactor Code’, 1, ‘2016-04-10 23:50:40’),
(5, ‘Push to prod’, 1, ‘2016-04-10 23:50:50’);
Query OK, 5 rows affected (0.06 sec)

Connecting With MySQL-DB

We need to inform our app that it should be communicating with the database. Remember the ‘mysql’ package we installed before? Now is the right time to use.All we have to head over to our server.js file and paste the following code in it just below the line where we initialized our app. Just remember to update the “mcvariable with the connection string of your database.

const mysql = require(“mysql”);
// connection configurations
const mc = mysql.createConnection({  host: “localhost”,
  user: “root”,
  password: “”,
  database: “mydb”});
// connect to database

Now, let’s create a test API just to verify our app is still working.Paste this hello API code in you server.js file just above the line where you assigned the port.

app.get(“/”, function(req, res) {
  return res.send({ message: “hello” });

And with this our final app.js file will look like this:

const express = require(“express”);
const bodyParser = require(“body-parser”);
const app = express();
// initializing our app
const mysql =require(“mysql”);
app.use(bodyParser.urlencoded({    extended: true   }) );
// connection configurations

const mc = mysql.createConnection({
host: “localhost”,
user: “root”,
password: “”,
database: “mydb”
// connect to database

app.get(“/”, function(req, res) {
return res.send({ message: “hello” });
let port = 3000;
app.listen(port, () => {
console.log(“Server is up and running on port number ” + port);

Now restart you app by same command in your terminal and head over to you browser and enter this url http://localhost:3000/.

If you also see the same as above then we are ready to move on to the next step which is creating CRUD APIs. (Note- Before creating APIs download postman in your system from here, it is a very useful tool in testing your APIs.

That’s all I have and thanks a lot for reading. Please let me know if any corrections/suggestions. Please do share and comments if you like the post. Thanks in advance… 😉

Thanks Yash Kesarwani for helping us to grow day by day.

Categories: Nodejs


Leave a Reply

Your email address will not be published. Required fields are marked *

Insert math as
Additional settings
Formula color
Text color
Type math using LaTeX
Nothing to preview