Vue.js + Nodejs/Express RestAPIs – Sequelize ORM + MySQL CRUD example

In this tutorial, we show you Vue.js Http Client & Node.js Server example that uses Sequelize ORM to do CRUD with MySQL and Vue.js as a front-end technology to make request and receive response.

Related Posts:
– Sequelize ORM – Build CRUD RestAPIs with NodeJs/Express, Sequelize, MySQL
– Vue Router example – with Nav Bar, Dynamic Route & Nested Routes

Technologies

– Node.js/Express
– Sequelize

– Vue 2.5.17
– Vue Router 3
– Axios 0.18.0

– MySQL

Overview

This is full-stack Architecture:

1. Node.js Server

2. Vue.js Client

Practice

1. Node.js Backend

Project structure:

Setting up Nodejs/Express project

Init package.json by cmd:

npm init

Install express, mysql, sequelize & cors:

$npm install express cors sequelize mysql2 --save

-> now package.json file:

{
  "name": "vue-nodejs-restapis-mysql",
  "version": "1.0.0",
  "description": "Nodejs-Rest-APIs-Sequelize-MySQL",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Nodejs",
    "RestAPIs",
    "Sequelize",
    "MySQL",
    "Vue.js"
  ],
  "author": "ozenero.com",
  "license": "ISC",
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.16.4",
    "mysql2": "^1.6.4",
    "sequelize": "^4.42.0"
  }
}

Setting up Sequelize MySQL connection

– Create ./app/config/env.js file:

const env = {
  database: 'testdb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
	  max: 5,
	  min: 0,
	  acquire: 30000,
	  idle: 10000
  }
};
 
module.exports = env;

– Setup Sequelize-MySQL connection in ./app/config/db.config.js file:

const env = require('./env.js');
 
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,
 
  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});
 
const db = {};
 
db.Sequelize = Sequelize;
db.sequelize = sequelize;
 
//Models/tables
db.customers = require('../model/customer.model.js')(sequelize, Sequelize);
 
 
module.exports = db;

Create Sequelize model

– ./app/model/customer.model.js file:

module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {
	  name: {
		type: Sequelize.STRING
	  },
	  age: {
		  type: Sequelize.INTEGER
	  },
	  active: {
		type: Sequelize.BOOLEAN, defaultValue: false
	  },
	});
	
	return Customer;
}

Express RestAPIs

– Route
-> Define Customer’s routes in ‘./app/route/customer.route.js’ file:

module.exports = function(app) {
 
    const customers = require('../controller/customer.controller.js');
 
    // Create a new Customer
    app.post('/api/customer', customers.create);
 
    // Retrieve all Customer
    app.get('/api/customers', customers.findAll);
 
    // Retrieve a single Customer by Id
    app.get('/api/customer/:customerId', customers.findById);
	
	// Retrieve Customers Age
    app.get('/api/customers/age/:age', customers.findByAge);
 
    // Update a Customer with Id
    app.put('/api/customer/:customerId', customers.update);
 
    // Delete a Customer with Id
    app.delete('/api/customer/:customerId', customers.delete);
}

– Controller :
-> Implement Customer’s controller in ./app/controller/customer.controller.js file:

const db = require('../config/db.config.js');
const Customer = db.customers;
 
// Post a Customer
exports.create = (req, res) => {	
	// Save to MySQL database
	Customer.create({  
		name: req.body.name,
		age: req.body.age
	}).then(customer => {		
		// Send created customer to client
		res.send(customer);
	}).catch(err => {
		res.status(500).send("Error -> " + err);
	})
};
 
// FETCH all Customers
exports.findAll = (req, res) => {
	Customer.findAll().then(customers => {
		// Send all customers to Client
		res.send(customers);
	}).catch(err => {
		res.status(500).send("Error -> " + err);
	})
};
 
// Find a Customer by Id
exports.findById = (req, res) => {	
	Customer.findById(req.params.customerId).then(customer => {
		res.send(customer);
	}).catch(err => {
		res.status(500).send("Error -> " + err);
	})
};

// Find Customers by Age
exports.findByAge = (req, res) => {
	Customer.findAll({
		where: {
			age: req.params.age
		}
	}).then(
		customers => {
			res.send(customers)
		}
	).catch(err => {
		res.status(500).send("Error -> " + err);
	})
};
 
// Update a Customer
exports.update = (req, res) => {
	var customer = req.body;
	const id = req.params.customerId;
	Customer.update( { name: req.body.name, age: req.body.age, active: req.body.active }, 
						{ where: {id: req.params.customerId} }
				   ).then(() => {
						res.status(200).send(customer);
				   }).catch(err => {
						res.status(500).send("Error -> " + err);
				   })
};
 
// Delete a Customer by Id
exports.delete = (req, res) => {
	const id = req.params.customerId;
	Customer.destroy({
	  where: { id: id }
	}).then(() => {
		res.status(200).send('Customer has been deleted!');
	}).catch(err => {
		res.status(500).send("Error -> " + err);
	});
};

Server.js

– server.js file:

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
app.use(bodyParser.json())

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions))
 
const db = require('./app/config/db.config.js');
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
});
 
require('./app/route/customer.route.js')(app);
 
// Create a Server
var server = app.listen(8080, function () {
 
  var host = server.address().address
  var port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port)
})

2. Vue Client

– package.json with 3 main modules: vue, vue-router, axios.
– 4 components: CustomersList, Customer, AddCustomer, SearchCustomer.
– router.js defines routes, each route has a path and maps to a component.
– http-common.js initializes HTTP Client with baseUrl and headers for axios HTTP methods.
– vue.config.js configures port for Vue App.

For more details about how to use Vue Router in this example, please visit:
Vue Router example – with Nav Bar, Dynamic Route & Nested Routes

2.0 Setup Vue Project & Router

Init Project

Point cmd to the folder you want to save Project folder, run command:
vue create vue.js-client

You will see 2 options, choose default:

Add Vue Router to Project

– Run command: npm install vue-router.
– Import router to src/main.js:

import Vue from "vue";
import App from "./App.vue";
import router from './router'

Vue.config.productionTip = false;

new Vue({
  router, // inject the router to make whole app router-aware
  render: h => h(App)
}).$mount("#app");

Define Routes

src/router.js:

import Vue from "vue";
import Router from "vue-router";
import CustomersList from "./components/CustomersList.vue";
import AddCustomer from "./components/AddCustomer.vue";
import SearchCustomers from "./components/SearchCustomers.vue";
import Customer from "./components/Customer.vue";

Vue.use(Router);

export default new Router({
  mode: "history",
  routes: [
    {
      path: "/",
      name: "customers",
      alias: "/customer",
      component: CustomersList,
      children: [
        {
          path: "/customer/:id",
          name: "customer-details",
          component: Customer,
          props: true
        }
      ]
    },
    {
      path: "/add",
      name: "add",
      component: AddCustomer
    },
    {
      path: "/search",
      name: "search",
      component: SearchCustomers
    }
  ]
});

App template with Navbar and router-view

src/App.vue:

2.1 Initialize HTTP Client

Install axios with command: npm install axios.
Then create http-common.js file:

import axios from "axios";

export default axios.create({
  baseURL: "http://localhost:8080/api",
  headers: {
    "Content-type": "application/json",
  }
});

2.2 Components

List of Items

components/CustomersList.vue

Item Details

components/Customer.vue

Add Item

components/AddCustomer.vue

Search Items

components/SearchCustomers.vue

2.3 Configure Port for Vue App

vue.config.js

module.exports = {
  devServer: {
    port: 4200
  }
}

Run

– Node.js Server: npm start.
– Vue.js Client: npm run serve.

Open Browser with Url: http://localhost:4200/.

Add Customers

-> MySQL’s records:

Search Customers

Load All Customers

Update Customers

– Update Katherin customer from inactive to active ->

Delete Customer

Delete Jack:

-> MySQL’s records:

Node.js Logs

-> Logs:

$npm start

> vue-nodejs-restapis-mysql@1.0.0 start D:\gkz\article\Vue-Nodejs-RestAPIs-MySQL
> node server.js

App listening at http://:::8080
Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): CREATE TABLE IF NOT EXISTS `customers` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `age` INTEGER, `active` TINYINT(1) DEFAULT false, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `customers`
Drop and Resync with { force: true }
Executing (default): SELECT `id`, `name`, `age`, `active`, `createdAt`, `updatedAt` FROM `customers` AS `customer`;
Executing (default): INSERT INTO `customers` (`id`,`name`,`age`,`active`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Jack','20',false,'2018-12-24 06:32:36','2018-12-24 06:32:36');
Executing (default): INSERT INTO `customers` (`id`,`name`,`age`,`active`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Katherin','23',false,'2018-12-24 06:33:24','2018-12-24 06:33:24');
Executing (default): INSERT INTO `customers` (`id`,`name`,`age`,`active`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Adam','23',false,'2018-12-24 06:33:35','2018-12-24 06:33:35');
Executing (default): SELECT `id`, `name`, `age`, `active`, `createdAt`, `updatedAt` FROM `customers` AS `customer` WHERE `customer`.`age` = '23';
Executing (default): SELECT `id`, `name`, `age`, `active`, `createdAt`, `updatedAt` FROM `customers` AS `customer`;
Executing (default): UPDATE `customers` SET `name`='Katherin',`age`=23,`active`=true,`updatedAt`='2018-12-24 06:35:53' WHERE `id` = '2'
Executing (default): DELETE FROM `customers` WHERE `id` = '1'
Executing (default): SELECT `id`, `name`, `age`, `active`, `createdAt`, `updatedAt` FROM `customers` AS `customer`;

SourceCode

– Vue.js-Client
– Nodejs-RestAPIs