Vue3-MySQL(Basic)

backend/index.js

require('dotenv').config();

const mysql = require('mysql2/promise');

const express = require('express');

const cors = require('cors');

const app = express();

app.use(cors());

app.use(express.json());

//TEST 1

app.get('/', (req,res) => {

    res.json({

        message: 'GET Request success',

        author: process.env.AUTHOR

    });

});

//TEST 2

app.post('/', (req,res) => {

    const {body} = req;

    res.json({

        message: 'POST Request success',

        data: body,

        author: process.env.AUTHOR

    });

});

//TEST 3

app.get('/html', (req,res) => {

    res.send('Hello world .....')

});

//CONNECT TO DATABASE

const pool = mysql.createPool({

    host: 'localhost',

    user: 'root',

    database: 'db1',

    password: '',

});

//READ

app.get('/getall', async (req,res) => {

    try {

        const [rows, fields] = await pool.query('SELECT * FROM users');

        res.json({

            message: 'GET ALL Request success',

            data: rows,

            author: process.env.AUTHOR

        })

    } catch (err) {

        console.log(err);

    }

})

//CREATE  

app.post('/create', async (req,res) => {

    try {

        const { name, email, password, age } = req.body

        const [result] = await pool.query("INSERT INTO users (name, email, password, age) VALUES (?,?,?,?)",

        [name, email, password, age]);

        res.json({

            message: 'CREATE Request success',

            insertId: result.insertId,

            author: process.env.AUTHOR

        })

    } catch (err) {

        console.log(err);

    }

})

//UPDATE

app.put('/update', async (req,res) => {

    try {

        const { id, name, email, password, age } = req.body

        const [result] = await pool.query("UPDATE users SET name=?, password=?, age =? WHERE id=? ",

        [name, password, age, id]);

        res.json({

            message: 'UPDATE Request success',

            affectedRows: result.affectedRows,

            author: process.env.AUTHOR

        })

    } catch (err) {

        console.log(err);

    }

})

//DELETE

app.delete('/delete/:id', async (req,res) => {

    try {

        const id = req.params.id

        const [result] = await pool.query("DELETE FROM users WHERE id=? ",

        [id]);

        res.json({

            message: 'DELETE Request success',

            affectedRows: result.affectedRows,

            author: process.env.AUTHOR

        })

    } catch (err) {

        console.log(err);

    }

})

//LISTEN TO PORT

const PORT = process.env.PORT

app.listen(PORT, () => {

    console.log(`Running on http://localhost:${PORT}`)

})

backend/.env

PORT=4000

AUTHOR=Mohammad Nizam

frontend/src/views/HomeView.vue

<template>

  <main>

    <h1>Welcome !</h1>

    <button @click="addNew"> add New </button>

    <hr>

    <table class="table">

      <thead>

        <tr>

          <th>Bil</th>

          <th>Name</th>

          <th>Age</th>

        </tr>

      </thead>

      <tbody>

        <tr v-for="(item, idx) in display" :key="item.id">

          <td> {{ idx+1 }}</td>

          <td>{{ item.name }}</td>

          <td>{{ item.age }}</td>

          <td @click="goDetail(item)">detail</td>

        </tr>

      </tbody>

    </table>

    <!-- <pre> {{ display }}</pre> -->

  </main>

</template>

<script setup>

  import { onMounted, ref } from 'vue';

  import axios from 'axios';

  import router from '@/router';

  import { useCounterStore } from '../stores/counter';

  const store = useCounterStore();

  let display = ref('')

  onMounted(()=>getAll())

  async function getAll(){

    try {

      // const {data} = await axios.get('http://api-test.mlrguitm.com/getall');

      // console.log(data.data);

      const {data} = await axios.get('http://localhost:4000/getall');

      display.value = data.data;

    } catch (error) {

      console.error(error);

    }

  }

  function goDetail(item){

    store.detail = item

    router.push('/detail')

  }

  const addNew=()=>{router.push('/add')}

</script>

frontend/src/views/DetailView.vue

<template>

  <div class="about">

    <h2>This is a detail page</h2>

    <button @click="onBack">Back</button>

    <hr>

    <pre>{{ store.detail }} </pre>

  </div>

</template>

<script setup>  

  import router from "../router/index.js";

  import { useCounterStore } from '../stores/counter.js';

  const store = useCounterStore();

  const onBack=()=>{router.push('/')}

</script>

frontend/src/views/AddView.vue

<template>

  <div>

    <h2>This is a add user page</h2>

    <button @click="onBack">Back</button>

    <hr>

    <form>

        <div>

            <br>

            <label>Email</label><br>

            <input type="text" v-model="form.email">

        </div><br>

        <div>

            <label>Name</label><br>

            <input type="text" v-model="form.name">

        </div><br>

        <div>

            <label>Password</label><br>

            <input type="text" v-model="form.password">

        </div><br>

        <div>

            <label>Age</label><br>

            <input type="text" v-model="form.age">

        </div><br>

    </form>

    <button type="submit" @click="onSubmit">Submit</button><br><br>

    <pre>{{ form }} </pre>

  </div>

</template>

<script setup>  

  import {  reactive } from 'vue';

  import router from "../router/index.js";

  import axios from 'axios';

  import { useCounterStore } from '../stores/counter.js';

  const store = useCounterStore();

  const form = reactive({})

  const onBack=()=>{router.push('/')}

  async function onSubmit(){

    try {

      // await axios.post('https://api-test.mlrguitm.com/create', form );

      const token = await axios.post('http://localhost:4000/create', form );

      // console.log(data.data);

      //display.value = data.data;

    } catch (error) {

      console.error(error);

    } finally {

      router.push('/')

    }

  }

</script>

frontend/src/views/DeleteView.vue

<template>

  <div>

    <h2>This is a delete page</h2>

    <button @click="onBack">Back</button>

    <hr>

    <pre>{{ copyRemove }} </pre>

    <button @click="onDelete">Delete</button>

  </div>

</template>

<script setup>  

  import {  reactive } from 'vue';

  import router from "../router/index.js";

  import axios from 'axios';

  import { useCounterStore } from '../stores/counter.js';

  const store = useCounterStore();

  const onBack=()=>{router.push('/')}

  //COPY FROM PINIA TO LOCAL

  const copyRemove = JSON.parse(JSON.stringify(store.remove));

  const id = copyRemove.id

  async function onDelete(){

    try {

      const result = await axios.delete('https://mon27.mlrguitm.com/delete/' +id );

      //const result = await axios.delete('http://localhost:4000/delete/' +id );

      console.log(result);  

      router.push('/')  

    } catch (error) {

      console.error(error);

    }

  }

</script>

frontend/src/views/UpdateView.vue

<template>

  <div>

    <h2>Update user</h2>

    <button @click="onBack">Back</button>

    <hr>

    <form>

        <div>

            <br>

            <label>Email</label><br>

            <input type="text" v-model="update.email" disabled>

        </div><br>

        <div>

            <label>Name</label><br>

            <input type="text" v-model="update.name">

        </div><br>

        <div>

            <label>Password</label><br>

            <input type="text" v-model="update.password">

        </div><br>

        <div>

            <label>Age</label><br>

            <input type="text" v-model="update.age">

        </div><br>

    </form>

    <button type="submit" @click="onSubmit">Submit</button><br><br>

    <pre>{{ update }} </pre>

  </div>

</template>

<script setup>  

  import {  reactive } from 'vue';

  import router from "../router/index.js";

  import axios from 'axios';

  import { useCounterStore } from '../stores/counter.js';

  const store = useCounterStore();

  //COPY FROM PINIA TO LOCAL

  const copy = JSON.parse(JSON.stringify(store.update));

  const update = reactive(copy)

  const onBack=()=>{router.push('/')}

  async function onSubmit(){

    try {

      const result = await axios.put('https://mon27.mlrguitm.com/update', update );

      // const result = await axios.post('http://localhost:4000/update', update );

      console.log(result);    

    } catch (error) {

      console.error(error);

    } finally {

      router.push('/')

    }

  }

</script>

frontend/src/stores/Counter.js

import { ref, computed } from 'vue'

import { defineStore } from 'pinia'

export const useCounterStore = defineStore('counter',

() => {

  const detail = ref({})       //DetailView

  const update = ref({})       //UpdateView

  const remove = ref({})       //DeleteView

  return { detail, update, remove }

})

frontend/src/router/index.js

import { createRouter, createWebHistory } from 'vue-router'

import HomeView from '../views/HomeView.vue';

import DetailView from '../views/DetailView.vue';

import AddView from '../views/AddView.vue';

const router = createRouter({

  history: createWebHistory(import.meta.env.BASE_URL),

  routes: [

    {

      path: '/',

      name: 'home',

      component: HomeView

    },

    {

      path: '/detail',

      name: 'detail',

      component: DetailView

    },

    {

      path: '/add',

      name: 'add',

      component: AddView

    },

    {

      path: '/update',

      name: 'update',

      component: UpdateView

    },

    {

      path: '/delete',

      name: 'delete',

      component: DeleteView

    },    

  ]

})

export default router