How to Build VUE JS CRUD Using Node js Express and MySQL?

26-Apr-2023

Admin

How to Build VUE JS CRUD Using Node js Express and MySQL?

Hello Friends,

How to build a Vue.js CRUD application using Node.js, Express, and MySQL" or "Vue.js CRUD with Node.js, Express, and MySQL Tutorial" would be better titled.

In this tutorial, you'll learn how to create a full-stack web application with a Node.js Express backend, MySQL database, and Vue.js frontend using CRUD functionality. Specifically, you'll learn how to create a CRUD application in Vue.js with a Node.js Express REST API and MySQL database.

Build RESTful API with Node js Express + 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:


{
    "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"
    }
}

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;

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

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;

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

Step 3: Crearte CRUD Component

Create CRUD components files; So, visit “frontend/src/components” directory and create the following files:

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>

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>

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>

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')

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

#Node.js Express

✌️ Like this article? Follow me on Twitter and Facebook. You can also subscribe to RSS Feed.

How to Build VUE JS CRUD Using Node js Express and MySQL?

You might also like...

Related Posts

Category

Latest Posts