Create a full-stack web application with a node express js + MySQL + Vue js CRUD example. In this tutorial, you will learn how to create a crud app in VUE JS using node js + Express Rest API + mysql DB.
This Full Stack Web Application Using Vue.Js + Express Js + MySQL tutorial will show you step by step how to create RESTful API with Node and Express.js + MySQL and how to use this restful APIs with VUE JS crud application.
VUE JS + Node.js + Express + MySQL CRUD Example Tutorial
- Build RESTful API with Node js Express + MySQL
- Step 1- Create Database and Table
- Step 2 – Install Express, MySQL2, and Cors
- Step 3 – Connect to Database
- Step 4 – Create Controller, Model and Route
- Step 5 – Update index.js
- Step 6 – Start Node JS Express + MySQL App
- Create VUE JS CRUD App
- Step 1 – Create New Vue App
- Step 2 – Install Axios Library
- Step 3 – Create CRUD Components
- Step 4 – Update Main.js
- Step 5 – Update App.js
- Step 6 – Start Vue JS App
Build RESTful API with Node js Express + MySQL
Now, use the following steps to cretae restFul apis with node js express and mysql:
Step 1- Create Database and Table
Execute the following command to create a new database for Node js Express + MySQL app:
CREATE DATABASE demo_db;
Then execute the following command to create new table:
CREATE TABLE product(
product_id INT(11) PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(200),
product_price DOUBLE
)ENGINE=INNODB;
Step 2 – Install Express, MySQL2, and Cors
Execute the following command on terminal to create directory, which name “backend“:
mkdir backend cd backend npm init –y npm install express body-parser mysql2 cors
Then add the following code to the “package.json” file:
"type": "module",
So that the “package.json” file looks like this:
{
"name": "backend",
"version": "1.0.0",
"description": "",
"type": "module",
"main": "index.js",
"scripts": {
"test": "echo "Error: no test specified" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"cors": "^2.8.5",
"express": "^4.17.1",
"mysql2": "^2.2.5"
}
}
Then create “config”, “controllers”, “models”, and “routes” directories inside the “backend” directory.
Step 3 – Connect to Database
Visit the “config” directory and create the “database.js” file; Then add the following code into it:
import mysql from "mysql2";
// create the connection to database
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'demo_db'
});
export default db;
Recommended:-Node JS Express Crud with Mysql Example
Step 4 – Create Controller, Model and Route
Create Product.js controller file; so visit “controllers” directory and create the “ Product.js” file. Then add the following code into it:
// Import function from Product Model
import { getProducts, getProductById, insertProduct, updateProductById, deleteProductById } from "../models/productModel.js";
// Get All Products
export const showProducts = (req, res) => {
getProducts((err, results) => {
if (err){
res.send(err);
}else{
res.json(results);
}
});
}
// Get Single Product
export const showProductById = (req, res) => {
getProductById(req.params.id, (err, results) => {
if (err){
res.send(err);
}else{
res.json(results);
}
});
}
// Create New Product
export const createProduct = (req, res) => {
const data = req.body;
insertProduct(data, (err, results) => {
if (err){
res.send(err);
}else{
res.json(results);
}
});
}
// Update Product
export const updateProduct = (req, res) => {
const data = req.body;
const id = req.params.id;
updateProductById(data, id, (err, results) => {
if (err){
res.send(err);
}else{
res.json(results);
}
});
}
// Delete Product
export const deleteProduct = (req, res) => {
const id = req.params.id;
deleteProductById(id, (err, results) => {
if (err){
res.send(err);
}else{
res.json(results);
}
});
}
Create productModel.js controller file; so visit “Models” directory and create the “ productModel.js” file. Then add the following code into it:
// import connection
import db from "../config/database.js";
// Get All Products
export const getProducts = (result) => {
db.query("SELECT * FROM product", (err, results) => {
if(err) {
console.log(err);
result(err, null);
} else {
result(null, results);
}
});
}
// Get Single Product
export const getProductById = (id, result) => {
db.query("SELECT * FROM product WHERE product_id = ?", [id], (err, results) => {
if(err) {
console.log(err);
result(err, null);
} else {
result(null, results[0]);
}
});
}
// Insert Product to Database
export const insertProduct = (data, result) => {
db.query("INSERT INTO product SET ?", [data], (err, results) => {
if(err) {
console.log(err);
result(err, null);
} else {
result(null, results);
}
});
}
// Update Product to Database
export const updateProductById = (data, id, result) => {
db.query("UPDATE product SET product_name = ?, product_price = ? WHERE product_id = ?", [data.product_name, data.product_price, id], (err, results) => {
if(err) {
console.log(err);
result(err, null);
} else {
result(null, results);
}
});
}
// Delete Product to Database
export const deleteProductById = (id, result) => {
db.query("DELETE FROM product WHERE product_id = ?", [id], (err, results) => {
if(err) {
console.log(err);
result(err, null);
} else {
result(null, results);
}
});
}
Recommended:-Node.js Express Login Example with MySQL
Create routes .js controller file; so visit “routes” directory and create the “ routes.js” file. Then add the following code into it:
// import express
import express from "express";
// import function from controller
import { showProducts, showProductById, createProduct, updateProduct, deleteProduct } from "../controllers/product.js";
// init express router
const router = express.Router();
// Get All Product
router.get('/products', showProducts);
// Get Single Product
router.get('/products/:id', showProductById);
// Create New Product
router.post('/products', createProduct);
// Update Product
router.put('/products/:id', updateProduct);
// Delete Product
router.delete('/products/:id', deleteProduct);
// export default router
export default router;
Recommended:-Node JS Facebook Login Example
Step 5 – Update index.js
In this step update the “index.js” file; which is located inside “backend” directory, then type the following code:
// import express
import express from "express";
// import cors
import cors from "cors";
import bodyParser from "body-parser";
// import routes
import Router from "./routes/routes.js";
// init express
const app = express();
// use express json
app.use(express.json());
// use cors
app.use(cors());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
// use router
app.use(Router);
app.listen(5000, () => console.log('Server running at http://localhost:5000'));
Step 6 – Start Node JS Express + MySQL App
Execute the following command in the terminal to start Node JS Express + MySQL App:
node index
Create VUE JS CRUD App
Now, use the following steps to create vue js crud app:
Step 1 – Create New Vue App
Open your terminal and execute the following command on it to install vue app:
npm install –g @vue/cli vue create frontend
Step 2 – Install Axios Library
Execute the following command on the terminal to visit the “frontend” directory:
ngcd frontend
Then install vue-router, axios, and bulma by typing the following commands in the terminal:
npm install vue-router axios bulma
Recommended:-VUE JS Get Selected Value of Dropdown Example
Step 3 – Crearte CRUD Component
Create CRUD components files; So, visit “frontend/src/components” directory and create the following files:
- ProductList.vue
- AddProduct.vue
- EditProduct.vue
Then open the file “ProductList.vue” and add the following code into it:
<template>
<div>
<router-link :to="{ name: 'Create' }" class="button is-success mt-5"
>Add New</router-link
>
<table class="table is-striped is-bordered mt-2 is-fullwidth">
<thead>
<tr>
<th>Product Name</th>
<th>Price</th>
<th class="has-text-centered">Actions</th>
</tr>
</thead>
<tbody>
<tr v-for="item in items" :key="item.product_id">
<td>{{ item.product_name }}</td>
<td>{{ item.product_price }}</td>
<td class="has-text-centered">
<router-link
:to="{ name: 'Edit', params: { id: item.product_id } }"
class="button is-info is-small"
>Edit</router-link
>
<a
class="button is-danger is-small"
@click="deleteProduct(item.product_id)"
>Delete</a
>
</td>
</tr>
</tbody>
</table>
</div>
</template>
<script>
// import axios
import axios from "axios";
export default {
name: "ProductList",
data() {
return {
items: [],
};
},
created() {
this.getProducts();
},
methods: {
// Get All Products
async getProducts() {
try {
const response = await axios.get("http://localhost:5000/products");
this.items = response.data;
} catch (err) {
console.log(err);
}
},
// Delete Product
async deleteProduct(id) {
try {
await axios.delete(`http://localhost:5000/products/${id}`);
this.getProducts();
} catch (err) {
console.log(err);
}
},
},
};
</script>
<style>
</style>
Recommended:-Vue JS Add Multiple Markers on Google Maps Example
Then open the file “AddProduct.vue” and add the following code into it:
<template>
<div>
<div class="field">
<label class="label">Product Name</label>
<div class="control">
<input
class="input"
type="text"
placeholder="Product Name"
v-model="productName"
/>
</div>
</div>
<div class="field">
<label class="label">Price</label>
<div class="control">
<input
class="input"
type="text"
placeholder="Price"
v-model="productPrice"
/>
</div>
</div>
<div class="control">
<button class="button is-success" @click="saveProduct">SAVE</button>
</div>
</div>
</template>
<script>
// import axios
import axios from "axios";
export default {
name: "AddProduct",
data() {
return {
productName: "",
productPrice: "",
};
},
methods: {
// Create New product
async saveProduct() {
try {
await axios.post("http://localhost:5000/products", {
product_name: this.productName,
product_price: this.productPrice,
});
this.productName = "";
this.productPrice = "";
this.$router.push("/");
} catch (err) {
console.log(err);
}
},
},
};
</script>
<style>
</style>
Recommended:-Vue Js Range Slider Example
Then open the file “EditProduct.vue” and add the following code into it:
<template>
<div>
<div class="field">
<label class="label">Product Name</label>
<div class="control">
<input
class="input"
type="text"
placeholder="Product Name"
v-model="productName"
/>
</div>
</div>
<div class="field">
<label class="label">Price</label>
<div class="control">
<input
class="input"
type="text"
placeholder="Price"
v-model="productPrice"
/>
</div>
</div>
<div class="control">
<button class="button is-success" @click="updateProduct">UPDATE</button>
</div>
</div>
</template>
<script>
// import axios
import axios from "axios";
export default {
name: "EditProduct",
data() {
return {
productName: "",
productPrice: "",
};
},
created: function () {
this.getProductById();
},
methods: {
// Get Product By Id
async getProductById() {
try {
const response = await axios.get(
`http://localhost:5000/products/${this.$route.params.id}`
);
this.productName = response.data.product_name;
this.productPrice = response.data.product_price;
} catch (err) {
console.log(err);
}
},
// Update product
async updateProduct() {
try {
await axios.put(
`http://localhost:5000/products/${this.$route.params.id}`,
{
product_name: this.productName,
product_price: this.productPrice,
}
);
this.productName = "";
this.productPrice = "";
this.$router.push("/");
} catch (err) {
console.log(err);
}
},
},
};
</script>
<style>
</style>
Recommended:-Vue Js – Google Area Chart Tutorial Example
Step 4 – Update Main.js
Visit “frontend/src” directory; open main.js file and add the following code into it:
import Vue from 'vue'
import VueRouter from 'vue-router'
import App from './App.vue'
import Create from './components/AddProduct.vue'
import Edit from './components/EditProduct.vue'
import Index from './components/ProductList.vue'
Vue.use(VueRouter)
Vue.config.productionTip = false
const routes = [
{
name: 'Create',
path: '/create',
component: Create
},
{
name: 'Edit',
path: '/edit/:id',
component: Edit
},
{
name: 'Index',
path: '/',
component: Index
},
];
const router = new VueRouter({ mode: 'history', routes: routes })
new Vue({
// init router
router,
render: h => h(App),
}).$mount('#app')
Recommended:-Vue Js Google Line Charts Tutorial Example
Step 5 – Update App.js
Visit “frontend/src” directory; open app.js file and add the following code into it:
<template>
<div id="app" class="container is-max-desktop">
<router-view />
</div>
</template>
<script>
export default {
name: "App",
};
</script>
<style>
/* import style bulma */
@import "~bulma/css/bulma.css";
</style>
Step 6 – Start Vue JS App
Execute the following command on terminal to run the vue js application:
npm run serve
Conclusion
VUE JS CRUD + Node.js + Express + MySQL. In this tutorial, you have learned how to create crud app in vue js using node js + express and MySQL DB with REST API.
Recommended Vue JS and NodeJS Tutorials
Recommended:-Node JS Google Authentication with Passport Example
Recommended:-Node JS Express Socket IO Chat Application Example
Recommended:-Node JS LinkedIn Login using Passport Example
Recommended:-Node.js Web Scraping Example
Recommended:-Node js Express Get Client IP Address Tutorial
Recommended:-Vue JS – Google Map Integration Example
Recommended:-Vue Js Login and Registration Example
Recommended:-Vue JS Checkbox Checked Event Example
Recommended:-Vue JS – How to Download File using Axios Tutorial
Recommended:-Vue JS Radio Button OnChange Event Example
Recommended:-Vue JS Get Query String Parameters Tutorial
Recommended:-Vue 3 jQuery DataTable Example Tutorial
