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 12 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 12 HttpClient – Get/Post/Put/Delete requests + Bootstrap 4
Technologies
- Angular 12
- 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 12 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 12 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/Delete
requestsAppRouting
-> 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