Vue js Node js Express + MySQL CRUD

Vue Js node express js + MySQL + CRUD; Through this tutorial, i am going to show you how to build a simple crud app in VUE JS using node js +  Express Rest API as backend + MySQL DB.

VUE JS + Node.js + Express + MySQL CRUD Example Tutorial

Use the below given steps to create vue js crud with node js backend + MySQL database:

  • Step 1 – 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
  • Step 2 – 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

Step 1 – 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 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"
  }
}

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 routes
import Router from "./routes/routes.js";
 
// init express
const app = express();
 
// use express json
app.use(express.json());
 
// use cors
app.use(cors());
 
// 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

Step 2 – 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:

  • 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>

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

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

Leave a Comment