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

Hey Dev's

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

AWS PostgreSQL Database

Let us begin by making the changes:

We need to install the following packages and remove mongodb and mongoose

$ yarn remove mongodb mongoose
$ yarn add sequelize pg pg-hstore

pg for PostgreSQL and pg-hstore for converting data into the PostgreSQL hstore format.

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": [
    "PostgreSQL",
    "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",
    "pg": "^8.5.1",
    "pg-hstore": "^2.3.3",
    "sequelize": "^6.5.0"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

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

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 PostgreSQL Database on AWS.

module.exports = {
  HOST: process.env.POSTGRESQL_DB_HOST,
  USER: process.env.POSTGRESQL_DB_USER,
  PASSWORD: process.env.POSTGRESQL_DB_PASSWORD,
  DB: process.env.POSTGRESQL_DB,
  dialect: "postgres",
  // declaring pool is optional
  // pool: {
//   max: 5,
//   min: 0,
//   acquire: 30000,
//   idle: 10000
// }
};

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

To get a PostgreSQL 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 PostgreSQL on an EC2.
Search for RDS and create

Select PostgreSQL:

Then update username and password:

For this tutorial enable public access:

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

Ensure you have selected the free tier:

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

Download pgadim so we can test the connection of our database https://www.pgadmin.org/download/pgadmin-4-windows/

Go to connections add your details and save.

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:

POSTGRESQL_DB_HOST = myinstance.123456789012.us-east-1.rds.amazonaws.com
POSTGRESQL_DB_USER = odongo
POSTGRESQL_DB_PASSWORD = 123456
POSTGRESQL_DB = postgres

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

For RDS pricing on AWS https://aws.amazon.com/rds/postgresql/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;

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.");
//});

Remove the mongoose connection from index.js

/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;
};

This Sequelize Model represents the blog table in the PostgreSQL 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;

// 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.iLike]: `%${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."
      });
    });
};

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

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

/components/postgreSQL-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

In the postgreSQL-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;
};

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