Categories
AWS JavaScript Programming Tutorials Web Dev

Easy RESTful API with Node.js Express and MySQL

One of the modern paradigms of Software Development is the use of RESTful APIs which can simplify and make life a lot easier for engineers when it comes to building APIs and webservices.

The JavaScript programming language

This tutorial is far from explaining you all the details about REST and Microservices, nonetheless it can give you good guidance for the beginning of your learning of these technologies.

The first thing you’re about to do is to install Node.js, and then install and configure your AWS CLI, which will be helpful for the purposes of our tutorial.

In fact, to spice up things a little, our persistency will be based on AWS RDS, which has a MySQL engine; instead of a local installation of it.

Let’s begin by installing Express and our MySQL bridge for Node.js; with npm init you will have to answer simple questions, leave the default ones if you’re not sure of what to do:

$ mkdir restful-express-mysql
$ cd restful-express-mysql
$ npm init
$ npm install express --save
$ npm install mysql --save

Now, let’s create an empty database instance and get its endpoint on our AWS account. I suggest you not to use your main account but to create a child one, with minor privileges.

$ aws rds create-db-instance \
    --db-instance-identifier restful-tutorial \
    --allocated-storage 5 \
    --db-instance-class db.t2.micro \
    --engine mysql \
    --master-username master \
    --master-user-password putyourownpassword

Let’s get our instance endpoint and security group, which will be helpful when connecting to it:

$ aws rds describe-db-instances \
    --db-instance-identifier restful-tutorial | grep Address

$ aws rds describe-db-instances \
    --db-instance-identifier restful-tutorial | grep VpcSecurityGroupId

Let’s authorize our current IP address to acces to our database instance, make sure you put your own security group and actual IP:

$ aws ec2 authorize-security-group-ingress \
    --group-id sg-00000000 \
    --protocol tcp \
    --port 3306 \
    --cidr 000.00.000.0/00

Let’s create a database and a table in it, with the help of this simple Node.js script, which is hosted on my GitHub.

And now, we’ll build our code to implement all the verbs a RESTful API can have. At least all the main ones. Let’s begin by the create verb, we will use the post method:

app.post('/end-point', (req, res) => {
	
	var con = mysql.createConnection(db);
	
	con.connect(function(err) {
		
		if (err) throw err;
		console.log("Connected!");

		var title = req.param('title');
		var author = req.param('author');
		var body = req.param('body');
		var url = req.param('url');

		var sql = "INSERT INTO articles (title, author, body, url) VALUES ('" + title + "', '" + author + "', '" + body + "', '" + url + "')";
		con.query(sql, function (err, result) {
			if (err) throw err;
			res.send('Article Inserted.');
			con.end();
		});
		
	});
	
});

Let’s see what we have created, through meas of our read verb. We will use the get method:

app.get('/end-point', (req, res) => {
	
	var con = mysql.createConnection(db);
	
	con.connect(function(err) {
		
		if (err) throw err;
		console.log("Connected!");

		var id = req.param('id');
		var where = id == 'all' ? '' : ' WHERE id = ' + id;
	  
		con.query("SELECT * FROM articles" + where, function (err, result, fields) {
			if (err) throw err;
			res.send(result);
			con.end();
		});
		
	});
	
});

We can now update our record and use the put method of our RESTful API:

app.put('/end-point', function (req, res) {
	
	var con = mysql.createConnection(db);
	
	con.connect(function(err) {

		if (err) throw err;
		console.log("Connected!");

		var id = req.param('id');

		var title = req.param('title');
		var author = req.param('author');
		var body = req.param('body');
		var url = req.param('url');
	  
		var sql = "UPDATE articles SET title = '" + title + "', author = '" + author + "', body = '" + body + "', url = '" + url + "' WHERE id = " + id;
		con.query(sql, function (err, result) {
			if (err) throw err;
			res.send(result);
			con.end();
		});
	  
	});
	
});

What about deleting our records, via the delete method of Express, the framework we chose:

app.delete('/end-point', function (req, res) {

	var con = mysql.createConnection(db);
	
	con.connect(function(err) {
		
		if (err) throw err;
		console.log("Connected!");
		
		var id = req.param('id');
		
		var sql = "DELETE FROM articles WHERE id = " + id;
		con.query(sql, function (err, result) {
			if (err) throw err;
			res.send(result);
			con.end();
		});
	
	});

});

Alright then, it’s time to test our API. Remember that you can find the whole application on my GitHub. We will make use of Postman to accomplish our mission. But first, let’s start our application:

$ node index.js

I’ll show you just one screenshot, for the sake of the example, the rest is your duty. Choose the right method and the right end-point:

Postman RESTful API Express MySQL
If you can’t read it clearly, just download it and make a zoom with your viewer.

The last thing is to do the cleanup and delete our database instance, because we don’t want to pay for an unneeded resource:

$ aws rds delete-db-instance \
    --db-instance-identifier restful-tutorial

For the full code of this tutorial, please refer to this GitHub repository. Should you have any more questions, do not hesitate to contact me in person. And we must leave now, but don’t worry: I’ll be back soon with another tutorial.

Did you like this post? Please comment here below and share it on your preferred social networks, thank you!

By Mirko Benedetti

Hi there. My name is Mirko Benedetti, I'm a Software Developer and I founded this website. Excellence is what I consider to be our ultimate goal, and passion for technology constantly drives me to it. I began programming self-taught at a very young age. Since then I learned a lot, and every day I enjoy learning new things. Here is my Linkedin Curriculum, feel free to connect.

4 replies on “Easy RESTful API with Node.js Express and MySQL”

Hello, I read your blogs regularly. Your story-telling style is awesome,
keep doing what you’re doing! I could not refrain from commenting.
Exceptionally well written!

I’ve read this post and I just I desire to counsel you some issues or tips.
Maybe you could write next articles relating to this article.
I desire to read even more things!

Hello Kevin,
you’re flattering me, you’re too kind.
I always try to give the best that I can to my readers, and sometimes it happens I write something interesting.
But I keep humble and try to help people the more that I can.

If you find these tutorials instructive, please share them through your prefererred social network. It would help me a lot.
Let’s make this blog even more interesting and build a community.

Thanks again
and keep in touch

I’m always emailing this website post page to all my associates, cause if I like to read it then they will too.

Leave a Reply

Give me your opinion, I will be grateful.