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
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"
}
}
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 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
// }
};
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
Select MySQL:
Then update username and password:
Add a database name.
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 MySQL Workbench so we can test the connection of our database https://dev.mysql.com/downloads/workbench/
Go to parameters add your details and save.
Test connection and if successful 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:
MYSQL_DB_HOST = myinstance.123456789012.us-east-1.rds.amazonaws.com
MYSQL_DB_USER = odongo
MYSQL_DB_PASSWORD = 123456
MYSQL_DB = blogs
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/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;
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 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."
});
});
};
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/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
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;
};
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)