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

Vue-nodejs-express-restapi-sequelize-postgresql---feature-image

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

Related Posts:
Node.js/Express RestAPIs CRUD – Sequelize ORM – PostgreSQL
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

– PostgreSQL

Overview

This is full-stack Architecture:

Vue-nodejs-express-restapi-sequelize-postgresql---full-stack-architecture

1. Node.js Server

Vue-nodejs-express-restapi-sequelize-postgresql---backend-mysql-architecture

2. Vue.js Client

Vue-nodejs-express-restapi-sequelize-postgresql---front-vue.js

Practice

1. Node.js Backend

Project structure:

Vue-nodejs-express-restapi-sequelize-postgresql---nodejs-project-structure

Setting up Nodejs/Express project

Init package.json by cmd:

npm init

Install express, postgresql, sequelize & cors:

$npm install express cors sequelize pg pg-hstore --save

-> now package.json file:

{
  "name": "vue-nodejs-restapis-postgresql",
  "version": "1.0.0",
  "description": "Nodejs-Rest-APIs-Sequelize-PostgreSQL",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Nodejs",
    "RestAPIs",
    "Sequelize",
    "PostgreSQL",
    "Vue.js"
  ],
  "author": "grokonez.com",
  "license": "ISC",
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.16.4",
    "pg": "^7.7.1",
    "pg-hstore": "^2.3.2",
    "sequelize": "^4.42.0"
  }
}
Setting up Sequelize PostgreSQL connection

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

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

– Setup Sequelize-PostgreSQL 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 PostgreSQL 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

Vue-nodejs-express-restapi-sequelize-postgresql---vue.js-project-structure

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:

Vue-nodejs-express-restapi-sequelize-postgresql---cli

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:

%MINIFYHTMLcde4ebf61217e68591be3e2b787438b316%
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

%MINIFYHTMLcde4ebf61217e68591be3e2b787438b317%
Item Details

components/Customer.vue

%MINIFYHTMLcde4ebf61217e68591be3e2b787438b318%
Add Item

components/AddCustomer.vue

%MINIFYHTMLcde4ebf61217e68591be3e2b787438b319%
Search Items

components/SearchCustomers.vue

%MINIFYHTMLcde4ebf61217e68591be3e2b787438b320%
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

Vue-nodejs-express-restapi-sequelize-postgresql---add-customer

-> PostgreSQL’s records:

Vue-nodejs-express-restapi-sequelize-postgresql---select-all

Search Customers

Vue-nodejs-express-restapi-sequelize-postgresql---search-customer

Load All Customers

Vue-nodejs-express-restapi-sequelize-postgresql---load-all-customer

Vue-nodejs-express-restapi-sequelize-postgresql---load-customer-katherin

Update Customers

– Update Katherin customer from inactive to active ->

Vue-nodejs-express-restapi-sequelize-postgresql---update-customer

-> See PostgreSQL’s records:

Vue-nodejs-express-restapi-sequelize-postgresql---update-katherin-customer

Delete Customer

Delete Jack:
Vue-nodejs-express-restapi-sequelize-postgresql---delete-customer

-> PostgreSQL’s records:

Vue-nodejs-express-restapi-sequelize-postgresql---delete-jack-customer

Node.js Logs

-> Logs:

D:\gkz\article\Node.js-RestAPIs>npm start

> vue-nodejs-restapis-postgresql@1.0.0 start D:\gkz\article\Node.js-RestAPIs
> node server.js

App listening at http://:::8080
Executing (default): DROP TABLE IF EXISTS "customers" CASCADE;
Executing (default): DROP TABLE IF EXISTS "customers" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "customers" ("id"   SERIAL , "name" VARCHAR(255), "age" INTEGER, "active" BOOLEAN DEFAULT false, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'customers' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
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-25 08:41:32.570 +00:00','2018-12-25 08:41:32.570 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","name","age","active","createdAt","updatedAt") VALUES (DEFAULT,'Katherin','23',false,'2018-12-25 08:41:40.043 +00:00','2018-12-25 08:41:40.043 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","name","age","active","createdAt","updatedAt") VALUES (DEFAULT,'Adam','23',false,'2018-12-25 08:41:46.320 +00:00','2018-12-25 08:41:46.320 +00:00') RETURNING *;
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-25 08:45:04.404 +00:00' 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

Nodejs-RestAPIs
Vue.js-Client



By grokonez | December 26, 2018.


Related Posts


5 thoughts on “Vue.js + Nodejs/Express RestAPIs – Sequelize ORM + PostgreSQL CRUD example”

  1. Nicely written tutorial with flow charts, screen shots, source code, and table of contents. So many tutorials don’t even show the final result. Yours does, plus log files. Very nice!

  2. facing the issue in server side please help

    node:28201) [SEQUELIZE0004] DeprecationWarning: A boolean value was passed to options.operatorsAliases. This is a no-op with v5 and should be removed.
    events.js:183
    throw er; // Unhandled ‘error’ event
    ^

    Error: listen EADDRINUSE :::8080
    at Object._errnoException (util.js:1022:11)
    at _exceptionWithHostPort (util.js:1044:20)
    at Server.setupListenHandle [as _listen2] (net.js:1367:14)
    at listenInCluster (net.js:1408:12)
    at Server.listen (net.js:1492:7)
    at Function.listen (/home/fc/Nodejs-RestAPIs-1/Nodejs-RestAPIs/node_modules/express/lib/application.js:618:24)
    at Object. (/home/fc/Nodejs-RestAPIs-1/Nodejs-RestAPIs/server.js:23:18)
    at Module._compile (module.js:652:30)
    at Object.Module._extensions..js (module.js:663:10)
    at Module.load (module.js:565:32)
    npm ERR! code ELIFECYCLE
    npm ERR! errno 1
    npm ERR! vue-nodejs-restapis-postgresql@1.0.0 start: `node server.js`
    npm ERR! Exit status 1
    npm ERR!
    npm ERR! Failed at the vue-nodejs-restapis-postgresql@1.0.0 start script.
    npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

    npm ERR! A complete log of this run can be found in:
    npm ERR! /home/fc/.npm/_logs/2020-07-20T12_35_21_249Z-debug.log

Got Something To Say:

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

*