Sequelize is a promise-based ORM for Node.js v4 and later. In the tutorial, we will show how to GET/POST/PUT/DELETE requests from Angular 11 Client to PostgreSQL with NodeJs/Express RestAPIs using Sequelize ORM.
Related posts:
– Node.js/Express RestAPIs CRUD – Sequelize ORM – PostgreSQL
– Node.js/Express RestAPIs – Angular 11 HttpClient – Get/Post/Put/Delete requests + Bootstrap 4
Technologies
- Angular 11
- RxJS 6
- Bootstrap 4
- Visual Studio Code – version 1.24.0
- Nodejs – v8.11.3
- Sequelize
- PostgreSQL
Demo
Overview
Goal
We create 2 projects:
– Angular Client Project:
– Node.js RestAPIs project:
UserCase
Start Node.js server -> Logs:
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 , "firstname" VARCHAR(255), "lastname" VARCHAR(255), "age" INTEGER, "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): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Joe','Thomas',36,'2018-07-11 08:31:06.976 +00:00','2018-07-11 08:31:06.976 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Peter','Smith',18,'2018-07-11 08:31:06.977 +00:00','2018-07-11 08:31:06.977 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Lauren','Taylor',31,'2018-07-11 08:31:06.978 +00:00','2018-07-11 08:31:06.978 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Mary','Taylor',24,'2018-07-11 08:31:06.978 +00:00','2018-07-11 08:31:06.978 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'David','Moore',25,'2018-07-11 08:31:06.978 +00:00','2018-07-11 08:31:06.978 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Holly','Davies',27,'2018-07-11 08:31:06.978 +00:00','2018-07-11 08:31:06.978 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Michael','Brown',45,'2018-07-11 08:31:06.979 +00:00','2018-07-11 08:31:06.979 +00:00') RETURNING *;
-> PostgreSQL records:
– Angular client retrieve all customers from Node.js RestAPIs:
– Angular client update a customer -> Change the firstname of first customer: ‘Joe’ to ‘Robert’.
-> result:
– Delete ‘Peter’ customer:
– Add a new customer:
-> result:
– Check final customer’s list:
-> Sequelize Logs:
Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer";
Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer" WHERE "customer"."id" = '1';
Executing (default): UPDATE "customers" SET "id"=1,"firstname"='Robert',"lastname"='Thomas',"age"=36,"createdAt"='2018-07-11 08:31:06.976 +00:00',"updatedAt"='2018-07-11 08:32:42.344 +00:00' WHERE "id" = 1
Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer";
Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer" WHERE "customer"."id" = '2';
Executing (default): DELETE FROM "customers" WHERE "id" = '2'
Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer";
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Maria','Garcia',39,'2018-07-11 08:33:04.390 +00:00','2018-07-11 08:33:04.390 +00:00') RETURNING *;
-> PostgreSQL’s records:
Node.js/Express RestAPIs
Node.js exposes 5 RestAPIs as below:
router.post(‘/api/customers’, customers.create);router.get(‘/api/customers’, customers.findAll);router.get(‘/api/customers/:id’, customers.findOne);router.put(‘/api/customers’, customers.update);router.delete(‘/api/customers/:id’, customers.delete);
– Configure cross-origin for Angular-Client which running at port: 4200.
const cors = require('cors')
const corsOptions = {
origin: 'http://localhost:4200',
optionsSuccessStatus: 200
}
app.use(cors(corsOptions))
Angular 11 HttpClient
Use Angular HttpClient APIs to do Get/Post/Put/Delete requests to Node.js RestAPIs:
// 1. GET All Customers from remote SpringBoot API <code>@GetMapping(value="/api/customers")
getCustomers (): Observable<Customer[]> {
return this.http.get<Customer[]>(this.customersUrl)
}
// 2. GET a Customer from remote SpringBoot API <code>@GetMapping(value="/api/customers/{id}")
getCustomer(id: number): Observable<Customer> {
const url = `${this.customersUrl}/${id}`;
return this.http.get<Customer>(url);
}
// 3. POST a Customer to remote SpringBoot API <code>@PostMapping(value="/api/customers")
addCustomer (customer: Customer): Observable<Customer> {
return this.http.post<Customer>(this.customersUrl, customer, httpOptions);
}
// 4.DELETE a Customer from remote SpringBoot API <code>@DeleteMapping(value="/api/customers/{id}")
deleteCustomer (customer: Customer | number): Observable<Customer> {
const id = typeof customer === 'number' ? customer : customer.id;
const url = `${this.customersUrl}/${id}`;
return this.http.delete<Customer>(url, httpOptions);
}
// 5. PUT a Customer to remote SpringBoot API <code>@PutMapping(value="/api/customers")
updateCustomer (customer: Customer): Observable<any> {
return this.http.put(this.customersUrl, customer, httpOptions);
}
Practice
Node.js Express RestAPIs
Setting up NodeJs/Express project
Following the guide to create a NodeJS/Express project.
Install Express, Sequelize, PostgreSQL, and Cors:
$npm install express sequelize pg pg-hstore cors --save
– Express is one of the most popular web frameworks for NodeJs which is built on top of Node.js http module, and adds support for routing, middleware, view system etc.
– Cors is a mechanism that uses HTTP headers to tell a browser to let a web application running at one origin (domain) have permission to access selected resources from a server at a different origin.
– Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL …
-> package.json file:
{
"name": "nodejs-express-sequelizejs-postgresql",
"version": "1.0.0",
"description": "nodejs-express-sequelizejs-postgresql",
"main": "server.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [
"Angular-6-Client-NodeJs-Express-RestAPIs-SequelizeJs-CRUD-PostgreSQL"
],
"author": "JSA",
"license": "ISC",
"dependencies": {
"cors": "^2.8.4",
"express": "^4.16.3",
"pg": "^7.4.3",
"pg-hstore": "^2.3.2",
"sequelize": "^4.37.6"
}
}
Setting up Sequelize PostgreSQL connection
– Create ‘./app/config/env.js’ file:
const env = {
database: 'test',
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
Create file ./app/model/customer.model.js file:
module.exports = (sequelize, Sequelize) => {
const Customer = sequelize.define('customer', {
firstname: {
type: Sequelize.STRING
},
lastname: {
type: Sequelize.STRING
},
age: {
type: Sequelize.INTEGER
}
});
return Customer;
}
Express RestAPIs
Route ->
Define Customer’s routes in ‘./app/controller/customer.route.js’ file:
module.exports = function(app) {
const customers = require('../controller/customer.controller.js');
// Create a new Customer
app.post('/api/customers', customers.create);
// Retrieve all Customer
app.get('/api/customers', customers.findAll);
// Retrieve a single Customer by Id
app.get('/api/customers/:id', customers.findById);
// Update a Customer with Id
app.put('/api/customers', customers.update);
// Delete a Customer with Id
app.delete('/api/customers/:id', 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({
"firstname": req.body.firstname,
"lastname": req.body.lastname,
"age": req.body.age
}).then(customer => {
// Send created customer to client
res.json(customer);
}).catch(err => {
console.log(err);
res.status(500).json({msg: "error", details: err});
});
};
// FETCH All Customers
exports.findAll = (req, res) => {
Customer.findAll().then(customers => {
// Send All Customers to Client
res.json(customers.sort(function(c1, c2){return c1.id - c2.id}));
}).catch(err => {
console.log(err);
res.status(500).json({msg: "error", details: err});
});
};
// Find a Customer by Id
exports.findById = (req, res) => {
Customer.findById(req.params.id).then(customer => {
res.json(customer);
}).catch(err => {
console.log(err);
res.status(500).json({msg: "error", details: err});
});
};
// Update a Customer
exports.update = (req, res) => {
const id = req.body.id;
Customer.update( req.body,
{ where: {id: id} }).then(() => {
res.status(200).json( { mgs: "Updated Successfully -> Customer Id = " + id } );
}).catch(err => {
console.log(err);
res.status(500).json({msg: "error", details: err});
});
};
// Delete a Customer by Id
exports.delete = (req, res) => {
const id = req.params.id;
Customer.destroy({
where: { id: id }
}).then(() => {
res.status(200).json( { msg: 'Deleted Successfully -> Customer Id = ' + id } );
}).catch(err => {
console.log(err);
res.status(500).json({msg: "error", details: err});
});
};
Server.js
server.js ->
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 }');
initial();
});
require('./app/route/customer.route.js')(app);
// Create a Server
var server = app.listen(8080, function () {
let host = server.address().address
let port = server.address().port
console.log("App listening at http://%s:%s", host, port);
})
function initial(){
let customers = [
{
firstname: "Joe",
lastname: "Thomas",
age: 36
},
{
firstname: "Peter",
lastname: "Smith",
age: 18
},
{
firstname: "Lauren",
lastname: "Taylor",
age: 31
},
{
firstname: "Mary",
lastname: "Taylor",
age: 24
},
{
firstname: "David",
lastname: "Moore",
age: 25
},
{
firstname: "Holly",
lastname: "Davies",
age: 27
},
{
firstname: "Michael",
lastname: "Brown",
age: 45
}
]
// Init data -> save to PostgreSQL
const Customer = db.customers;
for (let i = 0; i < customers.length; i++) {
Customer.create(customers[i]);
}
}
Angular 11 Client
Data Model
customer.ts ->
export class Customer {
id: number;
firstname: string;
lastname: string;
age: number;
}
Configure AppModule
In the developed application, we use:
- Angular
Forms-> for building form HttpClient-> for httpGet/Post/Put/DeleterequestsAppRouting-> for app routing
-> Modify AppModule app.module.ts:
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { FormsModule } from '@angular/forms';
import { HttpClientModule } from '@angular/common/http';
import { AppRoutingModule } from './app-routing/app-routing.module';
import { AppComponent } from './app.component';
import { CustomerComponent } from './customer/customer.component';
import { CustomerDetailsComponent } from './customer-details/customer-details.component';
import { AddCustomerComponent } from './add-customer/add-customer.component';
@NgModule({
declarations: [
AppComponent,
CustomerComponent,
CustomerDetailsComponent,
AddCustomerComponent
],
imports: [
BrowserModule,
FormsModule,
AppRoutingModule,
HttpClientModule
],
providers: [],
bootstrap: [AppComponent]
})
export class AppModule { }
HttpClient DataService
Implement CustomerService customer.service.ts with HttpClient for Get/Post/Put/Delete:
import { Injectable } from '@angular/core';
import { HttpClient, HttpHeaders } from '@angular/common/http';
import { Observable } from 'rxjs';
import { Customer } from './customer';
const httpOptions = {
headers: new HttpHeaders({ 'Content-Type': 'application/json' })
};
@Injectable({
providedIn: 'root'
})
export class CustomerService {
private customersUrl = 'http://localhost:8080/api/customers'; // URL to web api
constructor(
private http: HttpClient
) { }
getCustomers (): Observable {
return this.http.get(this.customersUrl)
}
getCustomer(id: number): Observable {
const url = `${this.customersUrl}/${id}`;
return this.http.get(url);
}
addCustomer (customer: Customer): Observable {
return this.http.post(this.customersUrl, customer, httpOptions);
}
deleteCustomer (customer: Customer | number): Observable {
const id = typeof customer === 'number' ? customer : customer.id;
const url = `${this.customersUrl}/${id}`;
return this.http.delete(url, httpOptions);
}
updateCustomer (customer: Customer): Observable {
return this.http.put(this.customersUrl, customer, httpOptions);
}
}
Angular Router
Implement App-Routing module app-routing.module.ts:
import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';
import { CustomerComponent } from '../customer/customer.component';
import { AddCustomerComponent } from '../add-customer/add-customer.component';
import { CustomerDetailsComponent } from '../customer-details/customer-details.component';
const routes: Routes = [
{
path: 'customers',
component: CustomerComponent
},
{
path: 'customer/add',
component: AddCustomerComponent
},
{
path: 'customers/:id',
component: CustomerDetailsComponent
},
{
path: '',
redirectTo: 'customers',
pathMatch: 'full'
},
];
@NgModule({
imports: [ RouterModule.forRoot(routes) ],
exports: [ RouterModule ]
})
export class AppRoutingModule {}
Router Outlet & Router Links
-> Questions:
- How to show Componenets with Angular Routers? -> Solution: using
Router Outlet - How to handle the routing that comes from user’s actions? (like clicks on anchor tag) -> Solution: using
Router Link
-> We can achieve above functions by using Angular’s router-outlet and routerLink.
Modify the template file app.component.html of AppComponenet component as below:
<div class="container">
<div class="row">
<div class="col-sm-4">
<h1>Angular HttpClient</h1>
<ul class="nav justify-content-center">
<li class="nav-item">
<a routerLink="customers" class="btn btn-light btn-sm" role="button" routerLinkActive="active">Retrieve</a>
</li>
<li class="nav-item">
<a routerLink="customer/add" class="btn btn-light btn-sm" role="button" routerLinkActive="active">Create</a>
</li>
</ul>
<hr>
<router-outlet></router-outlet>
</div>
</div>
</div>
Customer Component
Customer Component ->
– Implement CustomerComponent class customer.component.ts:
import { Component, OnInit } from '@angular/core';
import { Customer } from '../customer';
import { CustomerService } from '../customer.service';
@Component({
selector: 'app-customer',
templateUrl: './customer.component.html',
styleUrls: ['./customer.component.css']
})
export class CustomerComponent implements OnInit {
customers: Customer[];
constructor(private customerService: CustomerService) {}
ngOnInit(): void {
this.getCustomers();
}
getCustomers() {
return this.customerService.getCustomers()
.subscribe(
customers => {
console.log(customers);
this.customers = customers
}
);
}
}
– Implement the template customer.component.html :
<h5>All Customers</h5>
<div *ngFor="let cust of customers">
<a [routerLink]="['/customers', cust.id]" style="color:black"><span class="badge badge-dark">{{cust.id}}</span> -> {{ cust.firstname }}</a>
</div>
Customer Detail Component
Customer Detail ->
-> results:
– Implement CustomerDetails class customer-details.component.ts:
import { Component, OnInit } from '@angular/core';
import { Customer } from '../customer';
import { CustomerService } from '../customer.service';
import { ActivatedRoute, Params } from '@angular/router';
import { Location } from '@angular/common';
@Component({
selector: 'app-customer-details',
templateUrl: './customer-details.component.html',
styleUrls: ['./customer-details.component.css']
})
export class CustomerDetailsComponent implements OnInit {
customer = new Customer() ;
submitted = false;
message: string;
constructor(
private customerService: CustomerService,
private route: ActivatedRoute,
private location: Location
) {}
ngOnInit(): void {
const id = +this.route.snapshot.paramMap.get('id');
this.customerService.getCustomer(id)
.subscribe(customer => this.customer = customer);
}
update(): void {
this.submitted = true;
this.customerService.updateCustomer(this.customer)
.subscribe(() => this.message = "Customer Updated Successfully!");
}
delete(): void {
this.submitted = true;
this.customerService.deleteCustomer(this.customer.id)
.subscribe(()=> this.message = "Customer Deleted Successfully!");
}
goBack(): void {
this.location.back();
}
}
– Implement CustomerDetailsComponent template customer-details.component.html :
<h4><span class="badge badge-light ">{{customer.id}}</span> -> {{customer.firstname}}</h4>
<div [hidden]="submitted">
<form #detailCustomerForm="ngForm">
<div class="form-group">
<label for="firstname">First Name</label>
<input type="text" class="form-control" id="firstname" required
[(ngModel)]="customer.firstname" name="firstname" #firstname="ngModel">
<div [hidden]="firstname.valid || firstname.pristine"
class="alert alert-danger">
First Name is required
</div>
</div>
<div class="form-group">
<label for="lastname">Last Name</label>
<input type="text" class="form-control" id="lastname" required
[(ngModel)]="customer.lastname" name="lastname" #lastname="ngModel">
<div [hidden]="lastname.valid || lastname.pristine"
class="alert alert-danger">
Last Name is required
</div>
</div>
<div class="form-group">
<label for="age">Age</label>
<input type="number" class="form-control" id="age" required
[(ngModel)]="customer.age" name="age" #age="ngModel">
<div [hidden]="age.valid || age.pristine"
class="alert alert-danger">
Age is required
</div>
</div>
<div class="btn-group btn-group-sm">
<button type="button" class="btn btn-dark" (click)="goBack()">Back</button>
<button type="button" class="btn btn-dark" (click)="update()" [disabled]="!detailCustomerForm.form.valid">Update</button>
<button type="button" class="btn btn-dark" (click)="delete()">Delete</button>
</div>
</form>
</div>
<div [hidden]="!submitted">
<p>{{message}}</p>
<div class="btn-group btn-group-sm">
<button type="button" class="btn btn-dark" (click)="goBack()">Back</button>
</div>
</div>
We can change the value of ng-valid & ng-invalid for more visual feedback,
-> Create ./assets/forms.css file:
.ng-valid[required], .ng-valid.required {
border-left: 5px solid rgba(32, 77, 32, 0.623);
}
.ng-invalid:not(form) {
border-left: 5px solid rgb(148, 27, 27);
}
Add ./assets/forms.css file to index.html :
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>Angular6Httpclient</title> <base href="/"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="icon" type="image/x-icon" href="favicon.ico"> <link rel="stylesheet" href="assets/forms.css"> </head> <body> <app-root></app-root> </body> </html>
Add-Customer Component
AddCustomer Component ->
-> result:
– Implement AddCustomerComponent class add-customer.component.ts:
import { Component, OnInit } from '@angular/core';
import { Customer } from '../customer';
import { CustomerService } from '../customer.service';
import { Location } from '@angular/common';
@Component({
selector: 'app-add-customer',
templateUrl: './add-customer.component.html',
styleUrls: ['./add-customer.component.css']
})
export class AddCustomerComponent{
customer = new Customer();
submitted = false;
constructor(
private customerService: CustomerService,
private location: Location
) { }
newCustomer(): void {
this.submitted = false;
this.customer = new Customer();
}
addCustomer() {
this.submitted = true;
this.save();
}
goBack(): void {
this.location.back();
}
private save(): void {
this.customerService.addCustomer(this.customer)
.subscribe();
}
}
– Implement the template add-customer.component.html:
<h3>Add Customer</h3>
<div [hidden]="submitted">
<form #addCustomerForm="ngForm">
<div class="form-group">
<label for="firstname">First Name</label>
<input type="text" class="form-control" id="firstname" placeholder="Give Customer's FirstName"
required
[(ngModel)]="customer.firstname" name="firstname" #firstname="ngModel">
<div [hidden]="firstname.valid || firstname.pristine"
class="alert alert-danger">
First Name is required
</div>
</div>
<div class="form-group">
<label for="lastname">Last Name</label>
<input type="text" class="form-control" id="lastname" placeholder="Give Customer's LastName"
required
[(ngModel)]="customer.lastname" name="lastname" #lastname="ngModel">
<div [hidden]="lastname.valid || lastname.pristine"
class="alert alert-danger">
Last Name is required
</div>
</div>
<div class="form-group">
<label for="age">Age</label>
<input type="number" class="form-control" id="age"
placeholder="Give Customer's Age"
required
[(ngModel)]="customer.age" name="age" #age="ngModel">
<div [hidden]="age.valid || age.pristine"
class="alert alert-danger">
Age is required
</div>
</div>
<div class="btn-group btn-group-sm">
<button type="button" class="btn btn-dark" (click)="goBack()">Back</button>
<button type="button" class="btn btn-dark" (click)="addCustomer()" [disabled]="!addCustomerForm.form.valid">Add</button>
</div>
</form>
</div>
<div [hidden]="!submitted">
<p>Submitted Successfully! -> <span class="badge badge-light">{{customer.firstname}} {{customer.lastname}}</span></p>
<div class="btn-group btn-group-sm">
<button type="button" class="btn btn-dark" (click)="goBack()">Back</button>
<button type="button" class="btn btn-dark" (click)="newCustomer(); addCustomerForm.reset()">Continue</button>
</div>
</div>
SourceCode
- Angular-6-Http-Client
- Nodejs-Express-Sequelizejs-PostgreSQL
