Using AWS RDS with Node.js and Express.js

Introduction

It's not an overstatement to say that information and data runs the world. Almost any application, from social media and e-commerce websites to simple time tracker and drawing apps, relies on the very basic and fundamental task of storing and retrieving data in order to run as expected.

Amazon's Relational Database Service (RDS) provides an easy way to get a database set up in the cloud using any of a wide range of relational database technologies. In this article, we're going to set up a database on RDS, and store data on it with a Node application.

Prerequisites

Amazon Web Services

Amazon Web Services (AWS) provides a collection of tools for building applications in the cloud. As RDS is an AWS service, we'll need to get set up with an AWS account.

AWS has a free tier for a lot of awesome stuff, and RDS is no exception - you get to use 750 hours (31 days) a month of db.t2.micro database usage for free for 12 months.

Postman

Postman is a useful tool for creating and sending requests. We'll be using this in the Node half of the article in order to test the code we've written.

Postman is optional, and really, you can use any tool to test out the endpoint, even your Internet browser.

Setting Up an RDS Instance

First, we're going to create our RDS instance cluster. Head to AWS and log in.

Once you're logged in, click on 'Services' in the top left, and then search for 'RDS'. You'll be presented with a page that looks something like this:

On the menu on the left, select 'Databases'. This would normally display a list of RDS instance clusters that we've created, but we don't have any yet.

To create one, click the orange 'Create database' button. You should be presented with a page that looks like:

AWS has recently introduced an 'Easy create' method for creating new RDS instances, so let's use that.

Under 'Engine type' we'll use 'Amazon Aurora', which is Amazon's own database engine optimized for RDS. For the edition, we'll leave this set to 'Amazon Aurora with MySQL 5.6 compatibility'.

Under 'DB instance size' select the 'Dev/Test' option - this is a less powerful (and cheaper) instance type, but is still more than enough for what we need it for.

The 'DB cluster identifier' is the name of the database cluster that we're creating. Let's call ours my-node-database for now.

For the master username, leave it as admin. Finally, we have the option to have a master password generated automatically. For the ease of this tutorial, let's set our own.

Make sure it's secure as this is the master username and password!

Finally, scroll down and click 'Create database'. It takes a few minutes to fully provision an RDS instance:

Before getting started on our Node application, we need to make sure we can connect to the instance. Select the instance you just created (it'll be the option that ends in instance-1) and take a note of the value under 'Endpoint'.

On the right-hand side, under 'VPC security groups', click the link - this will take you to the security group that's been set up for the database. Security groups are essentially firewall rules as to who is and isn't allowed to make connections to a resource.

Currently, this one is set to only allow connections from resources that have the same security group.

Selecting the 'Actions' drop down at the top, navigate to 'Edit inbound rules'. In this dialog, click 'Add rule'. For the new rule, under 'Type', select 'All traffic'. Under 'Source', select 'Anywhere'.

You should end up with something that looks like this:

Head back to RDS, and 'Modify' the instance. Here, at the 'Network & Security' section, under 'Public accessibility', we want to select 'Yes' - this allows us to connect to the database from our machines - otherwise we would need to put our application on an EC2 instance, which isn't the focus of this article.

Your RDS instance should now be ready to go! Let's write some code to interact with it.

Node Application

In order to interact with our application, we're going to create a very simple API that allows us to store user profiles via Express.js. Before we do that, we need to create a table inside our RDS instance to store data in.

Let's create a folder, move into it and initialize a blank Node.js application with the default configuration:

$ mkdir node-rds
$ cd node-rds
$ npm init -y

Then, let's install the required dependencies:

$ npm install express --save
$ npm install mysql --save

And finally, we want to create two JavaScript files - one of them will be our Express app, the other will be a single-use script to create a table in our database:

$ touch index.js
$ touch dbseed.js

Table Creation Script

Let's start off with the dbseed.js file:

const mysql = require('mysql');

const con = mysql.createConnection({
    host: "<DB_ENDPOINT>",
    user: "admin",
    password: "<DB_PASSWORD>"
});

con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
    con.end();
});

Make sure to swap out <DB_ENDPOINT> for the endpoint that we noted down earlier, and fill in the password. What this piece of code will do is attempt to connect to the database - if it succeeds, it'll run an anonymous function that logs 'Connected!', and then immediately close the connection.

We can quickly check to see if it's properly set up by running:

$ node dbseed.js

The app should return 'Connected!'. If it doesn't, there's likely an issue with the security settings - go back to the RDS set-up and make sure you've done everything correctly.

Now that we know that we can definitely connect to our database, we need to create a table. Let's modify our anonymous function:

con.connect(function(err) {
    if (err) throw err;

    con.query('CREATE DATABASE IF NOT EXISTS main;');
    con.query('USE main;');
    con.query('CREATE TABLE IF NOT EXISTS users(id int NOT NULL AUTO_INCREMENT, username varchar(30), email varchar(255), age int, PRIMARY KEY(id));', function(error, result, fields) {
        console.log(result);
    });
    con.end();
});

The expected output should look something like:

OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

Now that we've got a table to work with, let's set up the Express app to insert and retrieve data from our database.

Insert User Endpoint

Let's write a POST request for user creation in our index.js file:

app.post('/users', (req, res) => {
    if (req.query.username && req.query.email && req.query.age) {
        console.log('Request received');
        con.connect(function(err) {
            con.query(`INSERT INTO main.users (username, email, age) VALUES ('${req.query.username}', '${req.query.email}', '${req.query.age}')`, function(err, result, fields) {
                if (err) res.send(err);
                if (result) res.send({username: req.query.username, email: req.query.email, age: req.query.age});
                if (fields) console.log(fields);
            });
        });
    } else {
        console.log('Missing a parameter');
    }
});

What this code does is pretty straightforward - it packs the information from the POST request and inserts it into a query. This query will create a user in the RDS database and log the results.

Let's check if it works:

$ node index.js

In Postman we want to create a POST request to our server. Let's input the required parameters and send the request:

We should be getting the same data back, which means that it's working correctly and our endpoint has inserted a user.

Get Users Endpoint

Let's devise a simple GET endpoint for a more user-friendly way to check for results. Below the POST request handler, let's make another handler:

app.get('/users', (req, res) => {
    con.connect(function(err) {
        con.query(`SELECT * FROM main.users`, function(err, result, fields) {
            if (err) res.send(err);
            if (result) res.send(result);
        });
    });
});

In your browser, navigate to localhost:3000/users and you should be presented with all the inserted users.

What Next?

Secure your Database

Earlier, we made our RDS instance publicly accessible, and with a completely open security group. You could get set up on EC2 and begin to tighten the security on your instance by removing public access and locking down your security group.

You should also take a look at the codebase as well - it's functional, but might be vulnerable to attacks such as SQL injection - it's worth spending time to make sure that users can't perform actions on your database that you don't intend.

Experiment with Read/Write

If you dive a bit deeper into RDS, you can set up instances specifically to read or write from - this can help with securing your instance, but also if you're dealing with lots of database operations, this can help optimize your database's performance.

Try Different Configurations

We used AWS' 'Easy create' to make our database - it's worth going back and trying out the various configurations that you could set RDS up with to meet your particular needs.

Terminate your RDS Instance!

If you're not planning on continuing to use your RDS instance, make sure to terminate it! Otherwise you'll rack up a hefty bill within a month.

Conclusion

You've created an RDS instance and populated it with data through a Node/Express endpoint. This is a great springboard to leap into more complex web applications that store and serve data for its users, and with data rising meteorically as a valuable commodity this is a good time to think on what sort of data you want to operate with.

To end, remember that any data you store is your duty to protect - always make sure you understand how to build a secure application before you start putting people's personal details in it!!