Creating Rest API with Node.js,Express and MySQL Part-1

With Node.js and express creating a Rest API is a piece of cake. In this blog post, we are going to see how we can create a rest API with Node.js, Express, and MySQL as a database.

Creating Database with MySQL:

So let’s first with creating Database for our APIs. So todo that we need to create a new Schema from MySQL workbench like following.



Here we are going to create a schema(database) called “employee”.  Then it’s time to create a Database Table and Let’s create a table like following.



and Here is the SQL Script for creating a table.



Here we have 4 column of the table EmployeeId(Primary Key), First Name, Last Name and Designation of employee details.

CREATE TABLE `employee` (
  `EmployeeId` int(11) NOT NULL,
  `FirstName` varchar(45) DEFAULT NULL,
  `LastName` varchar(45) DEFAULT NULL,
  `Designation` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`EmployeeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have entered some sample data like following.

Express Node.js application to create Rest API:

Now we have our database ready for MySQL and It’s time to create a new Node.js Express application. We can start with creating Package.Json with NPM init and run that command in command line.

npm init

It will ask you several questions to create your node an application like below.



Once your basic node.js application is ready It’s time to install express via the following command.

npm install express –save

It will install express like below.



Now we have created a node.js and express application. I have also installed MySQL and BodParser package and here’s how the package.json looks like.

{
  "name": "nodejsmysql",
  "version": "1.0.0",
  "description": "A Rest API Example with Node.js and MySQL",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "Jalpesh",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.2",
    "express": "^4.16.2",
    "mysql": "^2.15.0"
  }
}

As now we are done with creating and configuring our node.js application It’s time to write code. First I have written following code to create a route of our API which will be a map to our API URL.

var express = require("express");
var app = express();
var bodyParser = require("body-parser");

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

var port = process.env.por || 3000;
var router = express.Router();

app.use("/api/employee", router);

Here in the above code, you can see that I have created an express object as required and then I created a body-parse object which will be used for the posting/putting data into our API. Then I have created a port and get router of express and then created a route which will be our URL for API.
Now It’s time to write MySQL Connection code like below.

var mysql = require("mysql");

var con = mysql.createConnection({
    host: "youripformysql",
    user: "usrename",
    password: "password",
    database: "employee"
});

In the above code I have to get MySQL object via require and then I created a MySQL connection.  You need to pass the credentials there for your MySQL Credentials. Then I have written following code to get Employee table data with our get API.

router.get("/", function (req, res, next) {
    con.connect(function (err) {
        if (err) throw err;
        con.query("SELECT * FROM employee", function (err, result, fields) {
            if (err) throw err;
            res.send(JSON.stringify({
                "status": 200,
                "error": null,
                "response": result
            }));
        });
    });
});

Here in the above code, You can see that I have written Route.get method which will be used to get All Employees from the MySQL database and then I have written a Select Query to query data with “Employee” table. Then I have put result data as Json with the response object.
Now we have to start this application with app.use like the following code.

app.listen(port, function () {
    console.log("Express server running on port %d", port);
});

Now we can run the application with the following command.

node index.js

You can see same in above image.



Now in a client like postman you can see the output like below.


That’s it. In blog post series we will see that how we can add/update and delete it. Stay tuned for more!!.

You can find complete source code above blog post at following location on github- https://github.com/dotnetjalps/RestAPINodeMySQL

Continue Reading