Sunday, December 3, 2017

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.

database-creation-mysql

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.

create-table-mysql

and Here is the SQL Script for creating a table.

database-table-script-mysql

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.

table-sample-data

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.

npm-init

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.

express-installer

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.

node-run-express-application

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

output-as-json
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
Share:
Saturday, November 25, 2017

Video: How to use IIS Express with Visual Studio Code

Visual Studio Code is a Great Editor by Microsoft being Developed Open Source on Github and It is gaining lots of popularity and lots of Developers are using for creating applications in various languages. Their lots of features that are there for Developers and Still lots of features is not been explored or lots of useful extensions are not being explored. So I am creating few videos for explaining it’s feature and this video is one of that features.

IIS Express is a built-in web server for C# applications with Visual Studio and lots of Developers are using this. This video explains how we can use IIS Express with Visual Studio code. Please have a look at it and let me know how it was.



The plugin used in this video can be found at following location.
https://marketplace.visualstudio.com/items?itemName=warren-buckley.iis-express

And If you don’t know what is Visual Studio code then you find more about it at following location.
https://code.visualstudio.com/

Stay tuned for more!!.
Share:
Sunday, July 9, 2017

Microsoft MVP for the 6th time! Thank you, Microsoft

First all, Sorry for the late post. I am very happy and proud to announce that I have been awarded Microsoft Most Valuable Professional for the 6th time in “Visual Studio and Development" Category.



On this occasion, I would to Thank Microsoft for again such a wonderful gesture. I would also like to thank my MVP Community lead Deepak Rajendran  & team for their constant support. I would like to thanks, Biplab Paul and Gadharv Rawat for doing same earlier. Without the support of these guys, it would not have been possible to achieve this.

Also, I would like to thank my family for their constant support for compromising their time to do the community work. Special thank to my wife Reena. Behind every men’s success, there is a woman and she is the woman responsible for my success. I would like to dedicate this award to my family this time especially my little champ who is the inspiration for me to staying strong in any condition.

Also, I would like to thank Ahmedabad User Group and Microsoft for providing awesome opportunity to speak at various events.

Last but not least, THANK YOU my dear blog readers without you. It was not possible.
Share:

Video: How to debug Node.js Application with Visual Studio Code

Visual Studio code is a Great Editor and I like it very much. It support’s a lot of cool features our of box. Node.js Application debugging is one of them. It provides great debugging with node.js just like Visual Studio. I have created a small video demonstrating the same. Please have a look.


Share:
Wednesday, June 21, 2017

Video: Visual Studio 2017 Features

Visual Studio 2017 got lots of new features and so that I’ve created a new video for demonstrating all the features of video. Please have a look at that video at below.



Let me know what kind of video you want to watch more!. Stay tuned for more!
Share:
Saturday, June 17, 2017

Code Navigation features in Visual Studio Code

Visual Studio Code is an open source, free, Cross-platform editor and I have been using it now more than the year. I just simply loved this editor. It’s a got lot of feature and there are some cool navigation features there. In this blog post, we are going to explore those navigation features.

Quick Open File Navigation Features(Ctrl+Tab):

If you press Ctrl + Tab then it will show all the open files in Visual Studio Code.

quick-file-navigation-feature

Go To Definition(F12):

If Language is supported in Visual Studio Code then you have that feature available just like have it in full Visual Studio IDE.  You can press F12 to go to definition and the same way you can also right click and select Go To Definition.

go-to-definition

Peek Definition(Alt + F12):

Peek Definition is working same as Go To Definition the only difference is it will load a windows inline and show. It is quite useful when you want to quickly see something. You can use this feature via pressing Alt+F12.

peek-definition

Open Symbol(Ctrl+T):

It also contains some Resharper like features you can go to any symbol via pressing Ctrl + T. Just type the first letter of a type you want to navigate to, regardless of which file.

open-symbol

Go To File(Ctrl + P):

You can also go to any file with Go To File feature of Visual Studio code via pressing Ctrl + P.   This quite useful when you have large code base.

go-to-file


There tons of other features available with Visual Studio code. So if you have not tried this editor I would highly recommend to try it. I’m sure you fall love in it. You can download Visual Studio code from the following location.

https://code.visualstudio.com/

Stay tuned for more!.
Share:
Saturday, May 13, 2017

My blog awarded top 60 Dot Net Blogs on Web from FeedSpot

It’s been a great year for me overall. It was a roller coaster ride for me. Recently I got an email from Anuj Founder of FeedSpot that My blog has been awarded as Top 60 DotNet Blog from FeedSpot.

Top60DotNetBlog

You can find complete list of Top 60 blog at following link.

http://blog.feedspot.com/dotnet_blogs/

My blog at 22 positions there.It’s great pleasure being part of Top 60 Dot Net Blog in Web. On this occasion I would like to thank my Family without there support it was not possible to bee there. Specially my wife Reena who sacrifies her time when I’m writing blog posts. Also I, would like to thank my entire audience and readers without them it was not possible to achieve such a great milestone.

Thank you very much everyone!!. Please keep reading my blog. Stay tuned!
Share:
Search This Blog
About Me
My Photo

Full Stack Developer,Blogger,Microsoft Mvp, Mentor, Life long learner, 12+ Years of Experience for JavaScript, web and Microsoft technologies. ASP.NET MVC,Web Forms,C#,AngularJs,Golang
Mvp profile
Subscribe to my blog

Enter your email address:

Follow us on facebook
Blog Archive
Total Pageviews