DEV Community

Cover image for AWS RDS (MySQL), Express, Vue, and Node
Kevin Odongo
Kevin Odongo

Posted on

AWS RDS (MySQL), Express, Vue, and Node

Hey Dev's

In our previous tutorial https://dev.to/kevin_odongo35/mongodb-express-vue-and-node-2cml and https://dev.to/kevin_odongo35/aws-rds-postgresql-express-vue-and-node-1k99, we talked about configuring MongoDB or PostgreSQL, Express, Vue, and Node. Assuming we want to use a different database instead of Mongo DB and PostgreSQL we need to do a few changes to the previous tutorial.

MySQL Database

Let us begin by making the changes:

We need to install the following packages and remove mongodb, pg, pg-hstore and mongoose that we had previously installed

$ yarn remove mongodb mongoose pg pg-hstore
$ yarn add sequelize mysql2 
Enter fullscreen mode Exit fullscreen mode

package.json

Once we have installed everything, This is how your package.js file should be:

{
  "name": "blog-vue-back-end",
  "version": "1.0.0",
  "description": "Tutorial Backend for Blog Application",
  "main": "index.js",
  "scripts": {
    "start": "node server/index.js",
    "dev": "nodemon server/index.js",
    "test-dev": "jest"
  },
  "keywords": [
    "MySQL",
    "DB",
    "Express",
    "Node"
  ],
  "author": "Kevin Odongo",
  "license": "MIT",
  "dependencies": {
    "@shelf/jest-mongodb": "^1.2.3",
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "dotenv": "^8.2.0",
    "express": "^4.17.1",
    "jest": "^26.6.3",
    "mysql2": "^2.2.5",
    "sequelize": "^6.5.0"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

Enter fullscreen mode Exit fullscreen mode

In our previous tutorial, this was the structure of our application

Alt Text

To begin let's initialize the database.

/app/config/db.config.js

Update the db.config.js. We will have to replace the HOST, USER, PASSWORD once we have set up a MySQL Database on AWS.

module.exports = {
  HOST: process.env.MYSQL_DB_HOST,
  USER: process.env.MYSQL_DB_USER,
  PASSWORD: process.env.MYSQL_DB_PASSWORD,
  DB: process.env.MYSQL_DB,
  dialect: "mysql",
  // declaring pool is optional
  // pool: {
//   max: 5,
//   min: 0,
//   acquire: 30000,
//   idle: 10000
// }
};
Enter fullscreen mode Exit fullscreen mode

For more details about sequelize modeling and configuration https://sequelize.org/master/identifiers.html.

To get a MySQL database let us create one on AWS. You can host it on Aurora which will enhance the benefits of having a serverless backend because AWS manages Aurora and it will scale automatically.

NOTE:

Aurora does not have a free quota and therefore you will have to pay for its usage. But you can host your MySQL on an EC2.
Search for RDS and create

Alt Text

Select MySQL:

Alt Text

Then update username and password:

Alt Text

Add a database name.

Alt Text

For this tutorial enable public access:

Alt Text

NOTE
In production never allow direct internet, public access to your database.

Ensure you have selected the free tier:

Alt Text

That's it save and create your database. Once the database has completed initializing get the connection URL for the database.

Download MySQL Workbench so we can test the connection of our database https://dev.mysql.com/downloads/workbench/

Alt Text

Go to parameters add your details and save.

Alt Text

Test connection and if successful save.

Alt Text

Once connected you can monitor your database. This test will ensure you will be able to connect from your application. If you are not able to connect then you won't be able to connect in the application.

Let us update the .env file as follows:

MYSQL_DB_HOST = myinstance.123456789012.us-east-1.rds.amazonaws.com
MYSQL_DB_USER = odongo
MYSQL_DB_PASSWORD = 123456
MYSQL_DB = blogs
Enter fullscreen mode Exit fullscreen mode

Once your back-end is running ensure you are connected to the database before running the front-end and testing the blog application.

Alt Text

For RDS pricing on AWS https://aws.amazon.com/rds/mysql/pricing/.

/app/models/index.js

Let us define the routes.

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  // declaring pool is optional
  // pool: {
//   max: dbConfig.pool.max,
//   min: dbConfig.pool.min,
//   acquire: dbConfig.pool.acquire,
//   idle: dbConfig.pool.idle
// }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.blog = require("./blog.model.js")(sequelize, Sequelize);

module.exports = db;
Enter fullscreen mode Exit fullscreen mode

index.js

Add the following to sync your database.

// connect to db
const db = require("./app/models");
db.sequelize.authenticate().then(() => {
      console.log("Connected to the database!");
    })
    .catch(err => {
      console.log("Cannot connect to the database!", err);
      process.exit();
    });

// sync
db.sequelize.sync()

// to force sync during development
//db.sequelize.sync({ force: true }).then(() => {
//console.log("Drop and re-sync db.");
//});
Enter fullscreen mode Exit fullscreen mode

Remove the mongoose connection from index.js

Alt Text

/app/models/blog.model.js

module.exports = (sequelize, Sequelize) => {
  const Blog = sequelize.define("blog", {
    author: {
      type: Sequelize.STRING
    },
    content: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Blog;
};
Enter fullscreen mode Exit fullscreen mode

This Sequelize Model represents the blog table in the MySQL database. These columns will be generated automatically: id, createdAt, updatedAt as well as the ones we have defined.

/app/controller/blog.controller.js

const db = require("../models");
const Blog = db.blog;
const Op = db.Sequelize.Op;

// Create and Save a new blog
exports.create = (req, res) => {
    // Validate request
  if (!req.body.content) {
    res.status(400).send({ message: "Content can not be empty!" });
    return;
  }

  // Create a Blog
  const blog = {
    author: req.body.author,
    content: req.body.content,
    published: req.body.published ? req.body.published : false
  }

  // Save blog in the database
  Blog
    .create(blog)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while creating the blog."
      });
    });

};

// Retrieve all blogs from the database.
exports.findAll = (req, res) => {
    const content = req.query.content;
    var condition = content ? { [Op.like]: `%${content}%` } : null;

    Blog.findAll({ where: condition })
      .then(data => {
        res.send(data);
      })
      .catch(err => {
        res.status(500).send({
          message:
            err.message || "Some error occurred while retrieving blogs."
        });
      });

};

// Find a single blog with an id
exports.findOne = (req, res) => {
    const id = req.params.id;

    Blog.findByPk(id)
      .then(data => {
        if (!data)
          res.status(404).send({ message: "Not found blog with id " + id });
        else res.send(data);
      })
      .catch(err => {
        res
          .status(500)
          .send({ message: "Error retrieving blog with id=" + id });
      });

};

// Update a blog by the id in the request
exports.update = (req, res) => {
    if (!req.body) {
        return res.status(400).send({
          message: "Data to update can not be empty!"
        });
      }

      const id = req.params.id;

      Blog.update(req.body, {
    where: { id: id }
  })
        .then(data => {
          if (!data) {
            res.status(404).send({
              message: `Cannot update Blog with id=${id}. Maybe Blog was not found!`
            });
          } else res.send({ message: "Blog was updated successfully." });
        })
        .catch(err => {
          res.status(500).send({
            message: "Error updating Blog with id=" + id
          });
        });

};

// Delete a blog with the specified id in the request
exports.delete = (req, res) => {
    const id = req.params.id;

    Blog.destroy({
    where: { id: id }
  })
      .then(data => {
        if (data === 1) {
          res.send({
            message: "Blog was deleted successfully!"
          });
        } else {
          res.status(404).send({
            message: `Cannot delete Blog with id=${id}. Maybe Blog was not found!`
          });
        }
      })
      .catch(err => {
        res.status(500).send({
          message: "Could not delete Tutorial with id=" + id
        });
      });

};

// Delete all blogs from the database.
exports.deleteAll = (req, res) => {
    Blog.destroy({
    where: {},
    truncate: false
  })
    .then(data => {
      res.send({
        message: `${data} Blogs were deleted successfully!`
      });
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while removing all blogs."
      });
    });
};

// Find all published blogs
exports.findAllPublished = (req, res) => {
  Blog.findAll({ where: { published: true }})
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving blogs."
      });
    });
};
Enter fullscreen mode Exit fullscreen mode

Remember our routes are currently not protected therefore you can not go to production like this. We will need to protect our routes and add authentication to our application. In the next article, we are going to handle this.

Open your front-end directory in a new window of vs-code or whatever text editor you are using and run the application.

$ cd .. && cd /blog-vue-front-end
$ npm run serve
// Your front-end will be running on PORT 8080 || PORT 8081
// Your back-end will be running on PORT 3000
Enter fullscreen mode Exit fullscreen mode

Once the application is running let us create the following file in the components folder:

/components/MySQL-express-script.js

This will hold all our requests to the backend for mongo-express-script.js.

Install axios in the front-end

$ yarn add axios
Enter fullscreen mode Exit fullscreen mode

In the MySQL-express-script.js file add the following:

import axios from "axios";

// create new blog
export const createnewblog = async item => {
  let data = {
    author: JSON.stringify({
      name: item.author.name,
      email: item.author.email,
      about: item.author.about
    }), // replace with auth user
    content: JSON.stringify({
      title: item.content.title,
      src: item.content.src,
      text: item.content.text
    })
  };
  let request = {
    url: "http://localhost:3000/api/blog", // should be replaced after going to production with domain url
    method: "post",
    headers: {
      "Content-type": "application/json"
    },
    data: JSON.stringify(data)
  };

  const response = await axios(request);
  return response;
};

// delete blog
export const deleteblog = async item => {
  let request = {
    url: "http://localhost:3000/api/blog/" + item, // should be replaced after going to production with domain url
    method: "delete",
    headers: {
      "Content-type": "application/json"
    }
  };

  const response = await axios(request);
  return response;
};

// update blog
export const updateblog = async item => {
  let data = {
    author: JSON.stringify({
      name: item.author.name,
      email: item.author.email,
      about: item.author.about
    }), // replace with auth user
    content: JSON.stringify({
      title: item.content.title,
      src: item.content.src,
      text: item.content.text
    }),
    published: item.published
  };
  let request = {
    url: "http://localhost:3000/api/blog/" + item._id, // should be replaced after going to production with domain url
    method: "put",
    headers: {
      "Content-type": "application/json"
    },
    data: JSON.stringify(data)
  };

  const response = await axios(request);
  return response;
};

// get all blog
export const retriveallblog = async () => {
  let request = {
    url: "http://localhost:3000/api/blog", // should be replaced after going to production with domain url
    method: "get",
    headers: {
      "Content-type": "application/json"
    }
  };

  const response = await axios(request);
  return response;
};


Enter fullscreen mode Exit fullscreen mode

Here is how the blog should be working currently with your backend setup. We have completed the integration. Ensure your backend and front end are running concurrently.

What's next?. We need to protect our routes, add authentication and go to production.

Here is a repo for the backend https://github.com/kevinodongo/tutorial-blog-backend.git.

CONCLUSION

This approach has already fulfilled the Server approach. We currently have a server that we have to manage. If we had used Aurora then we would still be covering the serverless approach.

Thank you

Top comments (0)