CRUD WebApp With Angular, Node.JS, MySQL

Motivations

Disclaimer & Note

  1. Do each part of the project piecemeal and test that everything works before moving on. This way you can isolate problems to small incremental changes in your project. For example, I first ensured I could achieve a working connection between the client browser and my linux server through HTTP, before then changing it to HTTPS, installing an SSL certificate and achieving the same over a secure connection. This way I could isolate my client and server to be working correctly, and then focus on the HTTPS pipe and the SSL certificate being set up correctly. If I did not do this, I wouldn’t know if the issue was with my SSL certificate, with my server not correctly listening to requests or with my client not correctly sending requests.
  2. Zone in on key terminology such as REST APIs, reverse proxying, CRUD, opening ports, SSL, CNAME, naked domains, etc. I have tried to list them in the tutorial but the more you pick up on the technical terms, the easier it will get when you are googling to find solutions for the problems you are facing.

Introduction

  1. Client-facing User Interface, displayed through the Web Browser: We will use Angular to create static webpages to achieve this.
  2. Web Host, for our website: Our static webpages and can be hosted by any web host. We will use Amazon S3 and CloudFront in our tutorial.
  3. Server-side database, to store all user information: We will use MySQL for this.
  4. Node Express server, to interface with our database and serve and receive information to/from our client-facing website.

Very important note before beginning

Part 1 — Setting up our Linux Virtual Server

Amazon EC2

Firewall & Ports

sudo tcpdump -i any port 443

Elastic IPs — Setting a permanent IP for your server

SSH & Remote Access

sudo chmod 400 ~/Downloads/test-launch-key-pair.pem
ssh -i ~/Downloads/test-launch-key-pair.pem ubuntu@46.137.255.53

Brief Detour — Basic Linux [Optional]

List all the folders and files in the current directory:
ls
Move to a subfolder (you can also enter the path of a folder to move to it, e.g. cd /etc/nginx):
cd folder_name
Move up one directory. Note that ".." represents the directory above while "." represents the current directory (see mv example at bottom of this list):
cd .. ##
Move to your home directory:
cd ~
Create a file:
touch file_name
Create a directory:
mkdir folder_name
Delete a file:
rm file_name
Delete a folder and all its files:
rm -R folder_name
Copy a file to another location:
cp original_file new_filename_or_directory_location
Copy multiple files (e.g. 3 files in example below) to another location:
cp file1 file2 file3 new_location
Copy all files in a folder to another location:
cp * new_location
Copy all files and subfolders to another location:
cp -R * new_location
Move files and folders up one folder (run this from parent folder). In general note that cp and mv operate in the same manner:
mv subfolder/* subfolder/.* .
Rename file (works for folders too):
mv file_name new_file_name
Admin/Super User access:
sudo now_enter_your_command
Check access settings for files in current folder:
ls -l file_name_optional
Only readable by you (need to do this to SSH files):
chmod 400 file_name
Give all users full read, write and execute access (don't do this):
chmod 777 file_name
Owner can read and write, others can read only:
chmod 644 file_name
Owner can read, write and execute, others can read and execute only:
chmod 775 file_name
Exit current process:
CTRL + C
sudo apt-get update
sudo apt-get upgrade
wget file_http_url
git init
git remote set-url origin git@github.com:user-name/repo-name.git
git pull origin
git add file_name
git commit -m "commit message"
git push -u origin master
sudo apt-get install emacs
sudo apt-get install htop

Part 2 — Creating a MySQL Database

sudo apt-get install mysql-server
sudo mysql -u root
create database timeline;
use timeline;
create user 'timeline'@'localhost' identified by 'password';
grant all on timeline.* to 'timeline'@'localhost';
ALTER USER 'timeline'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
create table events (
id INT AUTO_INCREMENT,
owner VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
date DATE,
PRIMARY KEY (id),
INDEX (owner, date)
);

Part 3 — Creating an Express Server

sudo apt-get install nodejs
sudo apt-get install npm
sudo apt-get install curl
sudo apt autoremove
curl -sL https://deb.nodesource.com/setup_12.x | sudo -E bash -
sudo apt-get install nodejs
mkdir timeline-server
cd timeline-server
npm install express cors mysql
mkdir src
cd src
touch index.js
touch events.js
touch auth.js
emacs index.js
const bearerToken = require('express-bearer-token');
const oktaAuth = require('./auth');
const express = require('express');
const cors = require('cors');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const events = require('./events');
const connection = mysql.createConnection({
host : 'localhost',
user : 'timeline',
password : 'password',
database : 'timeline'
});
connection.connect();const port = process.env.PORT || 8080;const app = express()
.use(cors())
.use(bodyParser.json())
.use(bearerToken())
.use(oktaAuth)
.use(events(connection));
app.listen(port, () => {
console.log(`Express server listening on port ${port}`);
});

Part 4 — Brief Detour — Adding User Authentication

Configuring Express Server with Okta

sudo npm install express-bearer-token @okta/jwt-verifier
const OktaJwtVerifier = require('@okta/jwt-verifier');const oktaJwtVerifier = new OktaJwtVerifier({
clientId: '{yourClientId}',
issuer: 'https://{yourOktaDomain}/oauth2/default'
});
async function oktaAuth(req, res, next) {
try {
const token = req.token;
if (!token) {
return res.status(401).send('Not Authorized');
}
const jwt = await oktaJwtVerifier.verifyAccessToken(token, ['api://default']);
req.user = {
uid: jwt.claims.uid,
email: jwt.claims.sub
};
next();
}
catch (err) {
console.log('AUTH ERROR: ', err);
return res.status(401).send(err.message);
}
}

module.exports = oktaAuth;

Part 5 — REST APIs & Connecting Express to MySQL

const express = require('express');function createRouter(db) {
const router = express.Router();
// the routes are defined hererouter.post('/event', (req, res, next) => {
const owner = req.user.email;
db.query(
'INSERT INTO events (owner, name, description, date) VALUES (?,?,?,?)',
[owner, req.body.name, req.body.description, new Date(req.body.date)],
(error) => {
if (error) {
console.error(error);
res.status(500).json({status: 'error'});
} else {
res.status(200).json({status: 'ok'});
}
}
);
});
router.get('/event', function (req, res, next) {
const owner = req.user.email;
db.query(
'SELECT id, name, description, date FROM events WHERE owner=? ORDER BY date LIMIT 10 OFFSET ?',
[owner, 10*(req.params.page || 0)],
(error, results) => {
if (error) {
console.log(error);
res.status(500).json({status: 'error'});
} else {
res.status(200).json(results);
}
}
);
});
router.put('/event/:id', function (req, res, next) {
const owner = req.user.email;
db.query(
'UPDATE events SET name=?, description=?, date=? WHERE id=? AND owner=?',
[req.body.name, req.body.description, new Date(req.body.date), req.params.id, owner],
(error) => {
if (error) {
res.status(500).json({status: 'error'});
} else {
res.status(200).json({status: 'ok'});
}
}
);
});
router.delete('/event/:id', function (req, res, next) {
const owner = req.user.email;
db.query(
'DELETE FROM events WHERE id=? AND owner=?',
[req.params.id, owner],
(error) => {
if (error) {
res.status(500).json({status: 'error'});
} else {
res.status(200).json({status: 'ok'});
}
}
);
});
return router;
}
module.exports = createRouter;
node index.js

Autolaunching your Express server in the background

sudo npm install pm2 -g
pm2 start index.js

Part 6 — Angular & Creating the Web App

Setup

sudo npm install -g @angular/cli
ng new timeline-client
ng add ngx-bootstrap
sudo npm install ngx-timeline @okta/okta-angular
ng generate component home
ng generate component timeline
ng generate service server

Adding Angular code to App Component

import { Component } from '@angular/core';
import { OktaAuthService } from '@okta/okta-angular';
@Component({
selector: 'app-root',
templateUrl: './app.component.html',
styleUrls: ['./app.component.css']
})
export class AppComponent {
title = 'timeline-client';
isAuthenticated: boolean;
constructor(public oktaAuth: OktaAuthService) {
this.oktaAuth.$authenticationState.subscribe(
(isAuthenticated: boolean) => this.isAuthenticated = isAuthenticated
);
}
ngOnInit() {
this.oktaAuth.isAuthenticated().then((auth) => {this.isAuthenticated = auth});
}
login() {
this.oktaAuth.loginRedirect();
}
logout() {
this.oktaAuth.logout('/');
}
}
<nav class="navbar navbar-expand navbar-light bg-light">
<a class="navbar-brand" [routerLink]="['']">
<i class="fa fa-clock-o"></i>
</a>
<ul class="navbar-nav mr-auto">
<li class="nav-item">
<a class="nav-link" [routerLink]="['']">
Home
</a>
</li>
<li class="nav-item">
<a class="nav-link" [routerLink]="['timeline']">
Timeline
</a>
</li>
</ul>
<span>
<button class="btn btn-primary" *ngIf="!isAuthenticated" (click)="login()"> Login </button>
<button class="btn btn-primary" *ngIf="isAuthenticated" (click)="logout()"> Logout </button>
</span>
</nav>
<router-outlet></router-outlet>
import { BrowserModule } from '@angular/platform-browser';
import { HttpClientModule } from '@angular/common/http';
import { NgModule } from '@angular/core';
import { BsDatepickerModule } from 'ngx-bootstrap/datepicker';
import { NgxTimelineModule } from 'ngx-timeline';
import { FormsModule, ReactiveFormsModule } from '@angular/forms';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { BrowserAnimationsModule } from '@angular/platform-browser/animations';
import { ModalModule } from 'ngx-bootstrap/modal';
import { HomeComponent } from './home/home.component';
import { TimelineComponent } from './timeline/timeline.component';
import { OKTA_CONFIG, OktaAuthModule } from '@okta/okta-angular';const oktaConfig = {
issuer: 'https://{yourOktaDomain}/oauth2/default',
redirectUri: 'http://localhost:4200/implicit/callback',
clientId: '{yourClientId}',
pkce: true
}
@NgModule({
declarations: [
AppComponent,
HomeComponent,
TimelineComponent
],
imports: [
BrowserModule,
HttpClientModule,
AppRoutingModule,
BrowserAnimationsModule,
FormsModule,
ReactiveFormsModule,
BsDatepickerModule.forRoot(),
NgxTimelineModule,
ModalModule.forRoot(),
OktaAuthModule
],
providers: [{ provide: OKTA_CONFIG, useValue: oktaConfig } ],
bootstrap: [AppComponent]
})
export class AppModule { }
import { HomeComponent } from './home/home.component';
import { TimelineComponent } from './timeline/timeline.component';
import { OktaCallbackComponent, OktaAuthGuard } from '@okta/okta-angular';
import { NgModule } from '@angular/core';
import { Routes, RouterModule } from '@angular/router';
const routes: Routes = [
{
path: '',
component: HomeComponent
},
{
path: 'timeline',
component: TimelineComponent,
canActivate: [OktaAuthGuard]
},
{ path: 'implicit/callback', component: OktaCallbackComponent }
];
@NgModule({
imports: [RouterModule.forRoot(routes)],
exports: [RouterModule]
})
export class AppRoutingModule { }

Adding Angular code for Home Component

<div class="container">
<div class="row">
<div class="col-sm">
<h1>Angular MySQL Timeline</h1>
</div>
</div>
</div>
h1 {
margin-top: 50px;
text-align: center;
}
<div class="container page-content">
<div class="row">
<div class="col-sm-12 col-md">
<ngx-timeline [events]="events">
<ng-template let-event let-index="rowIndex" timelineBody>
<div>{{event.body}}</div>
<div class="button-row">
<button type="button" class="btn btn-primary" (click)="editEvent(index, eventmodal)"><i class="fa fa-edit"></i></button>
<button type="button" class="btn btn-danger" (click)="deleteEvent(index)"><i class="fa fa-trash"></i></button>
</div>
</ng-template>
</ngx-timeline>
</div>
<div class="col-md-2">
<button type="button" class="btn btn-primary" (click)="addEvent(eventmodal)"><i class="fa fa-plus"></i> Add</button>
</div>
</div>
</div>
<ng-template #eventmodal>
<div class="modal-header">
<h4 class="modal-title pull-left">Event</h4>
<button type="button" class="close pull-right" aria-label="Close" (click)="modalRef.hide()">
<span aria-hidden="true">&times;</span>
</button>
</div>
<div class="modal-body">
<form [formGroup]="form" (ngSubmit)="onSubmit()">
<div class="form-group full-width-input">
<label>Name</label>
<input class="form-control" placeholder="Event Name" formControlName="name" required>
</div>
<div class="form-group full-width-input">
<label>Description</label>
<input class="form-control" formControlName="description">
</div>
<div class="form-group full-width-input">
<label>Date</label>
<input class="form-control" formControlName="date" bsDatepicker>
</div>
<div class="button-row">
<button type="button" class="btn btn-primary" (click)="modalCallback()">Submit</button>
<button type="button" class="btn btn-light" (click)="onCancel()">Cancel</button>
</div>
</form>
</div>
</ng-template>
.page-content {
margin-top: 2rem;
}
.button-row {
display: flex;
justify-content: space-between;
margin-top: 1rem;
}
import { Component, OnInit, TemplateRef } from '@angular/core';
import { BsModalService, BsModalRef } from 'ngx-bootstrap/modal';
import { FormGroup, FormBuilder, Validators, AbstractControl, ValidatorFn } from '@angular/forms';
import { ServerService } from '../server.service';
@Component({
selector: 'app-timeline',
templateUrl: './timeline.component.html',
styleUrls: ['./timeline.component.css']
})
export class TimelineComponent implements OnInit {
form: FormGroup;
modalRef: BsModalRef;
events: any[] = [];
currentEvent: any = {id: null, name: '', description: '', date: new Date()};
modalCallback: () => void;
constructor(private fb: FormBuilder,
private modalService: BsModalService,
private server: ServerService) { }
ngOnInit() {
this.form = this.fb.group({
name: [this.currentEvent.name, Validators.required],
description: this.currentEvent.description,
date: [this.currentEvent.date, Validators.required],
});
this.getEvents();
}
private updateForm() {
this.form.setValue({
name: this.currentEvent.name,
description: this.currentEvent.description,
date: new Date(this.currentEvent.date)
});
}
private getEvents() {
this.server.getEvents().then((response: any) => {
console.log('Response', response);
this.events = response.map((ev) => {
ev.body = ev.description;
ev.header = ev.name;
ev.icon = 'fa-clock-o';
return ev;
});
});
}
addEvent(template) {
this.currentEvent = {id: null, name: '', description: '', date: new Date()};
this.updateForm();
this.modalCallback = this.createEvent.bind(this);
this.modalRef = this.modalService.show(template);
}
createEvent() {
const newEvent = {
name: this.form.get('name').value,
description: this.form.get('description').value,
date: this.form.get('date').value,
};
this.modalRef.hide();
this.server.createEvent(newEvent).then(() => {
this.getEvents();
});
}
editEvent(index, template) {
this.currentEvent = this.events[index];
this.updateForm();
this.modalCallback = this.updateEvent.bind(this);
this.modalRef = this.modalService.show(template);
}
updateEvent() {
const eventData = {
id: this.currentEvent.id,
name: this.form.get('name').value,
description: this.form.get('description').value,
date: this.form.get('date').value,
};
this.modalRef.hide();
this.server.updateEvent(eventData).then(() => {
this.getEvents();
});
}
deleteEvent(index) {
this.server.deleteEvent(this.events[index]).then(() => {
this.getEvents();
});
}
onCancel() {
this.modalRef.hide();
}
}

Adding Angular code for Server Service

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { OktaAuthService } from '@okta/okta-angular';
import { environment } from '../environments/environment';
@Injectable({
providedIn: 'root'
})
export class ServerService {
constructor(private http: HttpClient, public oktaAuth: OktaAuthService) {
}
private async request(method: string, url: string, data?: any) {
const token = await this.oktaAuth.getAccessToken();
const result = this.http.request(method, url, {
body: data,
responseType: 'json',
observe: 'body',
headers: {
Authorization: `Bearer ${token}`
}
});
return new Promise((resolve, reject) => {
result.subscribe(resolve, reject);
});
}
getEvents() {
return this.request('GET', `${environment.serverUrl}/event`);
}
createEvent(event) {
return this.request('POST', `${environment.serverUrl}/event`, event);
}
updateEvent(event) {
return this.request('PUT', `${environment.serverUrl}/event/${event.id}`, event);
}
deleteEvent(event) {
return this.request('DELETE', `${environment.serverUrl}/event/${event.id}`);
}
}
export const environment = {
production: false,
serverUrl: 'http://localhost:8080'
};

Running your Angular app locally

ng serve

Part 7 — Hosting the Web App

export const environment = {
production: true,
serverUrl: 'http://localhost:8080/'
};
ng build --prod

Setting up S3 Bucket

Setting up CloudFront

Some more work…

Final note on this section

Part 8 — NGINX & Reverse Proxying

Reverse proxying allows us to connect our static Angular website to our dynamic linux server that is hosting our database
sudo apt-get install nginx
sudo rm /etc/nginx/sites-enabled/default
sudo emacs /etc/nginx/sites-available/node
server {
listen 80;
server_name example.com;
location / {
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header Host $http_host;
proxy_pass http://127.0.0.1:8080;
}
}
sudo ln -s /etc/nginx/sites-available/node /etc/nginx/sites-enabled/node
sudo service nginx restart

Some minor notes

Part 9 — Domains & DNS

Linking your main website to a domain

Linking your linux server to a domain

Part 10 — Security & SSL

Okta Authentication

SSL Certificates

Adding an SSL certificate to our main website

Note — your page will look slightly different, as it will have the option ‘Default CloudFront Certificate’ selected

Adding an SSL certificate to your linux server

sudo apt-get update
sudo apt-get install software-properties-common
sudo apt-get-repository universe
sudo apt-get update
sudo apt-get install certbot python3-certbot-nginx
server {
listen 80;
server_name www.yourlinuxdomain.com yourlinuxdomain.com;
location / {
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header Host $http_host;
proxy_pass “http://127.0.0.1:8080”;
}
}
sudo certbot --nginx
server {
server_name www.yourlinuxdomain.com yourdomain.com;
location / {
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header Host $http_host;
proxy_pass "http://127.0.0.1:8080";
}
listen 443 ssl; # managed by Certbot
ssl_certificate /etc/letsencrypt/live/yourlinuxdomain.com/fullchain.pem; # managed b
y Certbot
ssl_certificate_key /etc/letsencrypt/live/yourlinuxdomain.com/privkey.pem; # managed by Certbot
include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot
}server {
if ($host = www.yourlinuxdomain.com) {
return 301 https://$host$request_uri;
} # managed by Certbot
if ($host = yourdomain.com) {
return 301 https://$host$request_uri;
} # managed by Certbot
listen 80;
server_name www.yourlinuxdomain.com yourlinuxdomain.com;
return 404; # managed by Certbot
}
sudo service nginx restart
  1. Checking the JavaScript Console in my client side web browser to see what the error message (e.g. at first it said the certificate was invalid, and later it said it could not connect — for the first issue I had to fix my SSL configurations and for the second issue I had to start up my Express Server as it was not running at the time)
  2. Deleting CertBot history via sudo certbot delete and then re-running sudo certbot --nginx
  3. Checking that there were no errors in my nginx file via sudo nginx -t
  4. Killing all my nginx servers via sudo killall nginx as I had 2 servers accidentally running at the same time and causing havoc
  5. Monitoring activity on my 443 port via sudo tcpdump -i any port 443

Update your Angular app to point to your domain

export const environment = {
production: true,
serverUrl: 'https://www.yourlinuxdomain.com'
};

Next Steps

--

--

--

Masters in Quantitative Finance. Writing Computer Science articles and notes on topics that interest me, with a tendency towards writing about Lisp & Swift

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

JavaScript core concepts — part 1

Auto-capitalise sentence browser extension/add-on

Summarizing YDKJS : UP & GOING: Chapter -1

How to Send data through routing paths in Angular

Working with Storybook and Drupal (Part 2)

Working with Storybook and Drupal (Part 1)

Animation in Unity and React Native

How to build ionic app on android

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ashok Khanna

Ashok Khanna

Masters in Quantitative Finance. Writing Computer Science articles and notes on topics that interest me, with a tendency towards writing about Lisp & Swift

More from Medium

Authentication using the Amazon Cognito to an Angular application

Dockerizing an Angular Application with Nginx and hosting in AWS - ec2

Express.js Server for Angular Project

Express.js

Implement FB Conversion API on Angular