Vue.js + Node.js + MySQL example: CRUD Application Tutorial

1 Comment on Vue.js + Node.js + MySQL example: CRUD Application Tutorial

In this tutorial, you will learn how to create a simple crud app in VUE JS with node js  Express + Rest API + MySQL DB.

How to Build Simple CRUD App with Node js and Vue JS with and MySQL

Here are the steps for a full-stack (Vue.js + Node.js + Express + MySQL) web example with a CRUD application:

  • 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 create restFul APIs with node js express and MySQL:

Step 1- Create a Database and Table

Execute the following command to create a new database for the Node js Express + MySQL app:

CREATE DATABASE demo_db;

Then execute the following command to create a 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 cmd to create a directory 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);
        }
    });
}
Recommended:-Node js Send Email Through Gmail with Attachment Example

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 the “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 cmd 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 cmd 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 cmd to visit the “frontend” directory:

ngcd frontend

Then install vue-router, Axios, and Bulma by typing the following commands in the cmd:

npm install vue-router axios bulma
Recommended:-VUE JS Get Selected Value of Dropdown Example

Step 3 – Crearte CRUD Component

Create CRUD components files, 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 the “frontend/src” directory; open the 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 the “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 the cmd to run the vue js application:

npm run serve

Conclusion

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 Resize Image Before Upload using Multer Sharp Example
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 – Add Class To Element On Click Tutorial Example
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
Filed Under: MySQL, Node js, REST API, Vue

AuthorDevendra Dode

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

One reply to Vue.js + Node.js + MySQL example: CRUD Application Tutorial

  1. Thanks for one more wonderful informative post, I’m a regular reader of your blog. I genuinely appreciate your hard work. Expect more posts in the future.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *