Web applications enable us to interact with dynamic content, perform complex tasks, and provide seamless user experiences. Angular, a popular JavaScript framework, offers a powerful platform for building robust and scalable web applications. When combined with a reliable database management system like MySQL, Angular becomes even more versatile, allowing developers to create data-driven applications with ease.
This tutorial will guide you through the process of developing an Angular application with MySQL. Here we will not use the traditional older LAMP stack, instead, we will employ a variation of the MEAN stack, where the "M" represents MySQL as the database, Express.js as the backend framework, and Angular.js for the client side.
Our application aims to create a basic timeline where users can add and edit events, leveraging a simple CRUD (Create, Read, Update, Delete) API.
Build Angular App with MySQL
To build Angular App with MySQL, you need to:
Set up MySQL Database
Set up CRUD Express Server
Run Your Express App and then Connect to MySQL Database
Authentication with JWT
Timeline Angular Client where you will Implement Application
Run the Application
1. Setting up MySQL
Follow the below steps to set up the MySQL database:
STEP 1: Install MySQL
If MySQL is not installed on your system, then Install MySQL from the original website.
STEP 2: Log into MySQL
Once MySQL is installed, open the terminal or command prompt and login to the MySQL server using the MySQL client. Use the login details of the database administrator. Enter the following command and provide the password when prompted:
$ mysql -u root -p
Note: The -p option indicates that a password is required to connect to the MySQL server. If you don't need a password, you can omit this option.
After successful authentication, you should see the MySQL command line prompt:
mysql>
STEP 3: Create the Database
Create a new database for this tutorial by executing the following commands in the MySQL prompt:
CREATE DATABASE timeline;
USE timeline;
These commands create a database named "timeline" and switch to using that database.
STEP 4: Create a User and Grant Permissions
It's recommended to have separate users for each database on the system. Create a new user associated with the "timeline" database using the following commands:
CREATE USER'timeline'@'localhost' IDENTIFIED BY'password';
GRANT ALL ON timeline.* TO 'timeline'@'localhost';
Replace 'password' with a more secure password of your choice. These commands create a user named "timeline" with the specified password and grant them all permissions on the "timeline" database.
STEP 5: Create the Events Table
Now, let's create the "events" table in the "timeline" database with the desired data schema. Execute the following SQL statement:
CREATE TABLE events (
id INT AUTO_INCREMENT,
owner VARCHAR(255) NOTNULL,
name VARCHAR(255) NOTNULL,
description TEXT,
dateDATE,
PRIMARY KEY (id),
INDEX (owner, date)
);
This statement creates a table named "events" with columns such as id, owner, name, description, and date. The "id" column serves as the primary key, allowing you to access individual rows. An index is added to the "owner" and "date" columns to improve lookup performance.
STEP 6: Exit MySQL Client
Once you have executed the above command, you can exit the MySQL client by typing:
quit
2. Set up CRUD Express Server
To set up a simple CRUD Node Express server with MySQL, follow the steps below:
Step 1: Create Project Directory and Initialize Node Project
Create a new directory for your project and navigate to it in the terminal. Then, initialize a new Node.js project by running the following command and providing the required information:
npm init
Set the entry point to src/index.js when prompted.
Step 2: Install Required Packages
Install the necessary packages using the following command:
npm install --save-exact express@4.17.1 cors@2.8.5 mysql@2.17.1
Step 3: Create Server File
Create a new file named src/index.js and paste the following code into it:
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(events(connection));
app.listen(port, () => {
console.log(`Express server listening on port ${port}`);
});
At the top of the file, you can see several imports. express contains the Express framework for creating REST APIs, cors is a middleware responsible for allowing cross-origin requests, and body-parser makes sure that the bodies of the incoming requests are read and attached to the request object.
The MySql object allows us to connect to your MySQL database and is seen in the code immediately below the required statements. In the options to createConnection, you will need to replace the password with the password that you have stored in your MySQL server above. In the bottom part of src/index.js, the Express server is configured with the middleware and the events router and then started.
Step 4: Create Events Router
Create a new file named src/events.js and paste the following code into it:
const express = require('express');
functioncreateRouter(db) {
const router = express.Router();
const owner = '';
router.post('/event', (req, res, next) => {
db.query(
'INSERT INTO events (owner, name, description, date) VALUES (?,?,?,?)',
[owner, req.body.name, req.body.description, newDate(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', (req, res, next) => {
db.query(
'SELECT id, name, description, date FROM events WHERE owner=? ORDER BY date LIMIT 10 OFFSET ?',
[owner, 10 * (req.query.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', (req, res, next) => {
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', (req, res, next) => {
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;
Step 5: Run the Server
You can now start the server by running the following command in the terminal:
node src/index.js
Note: Make sure you have a MySQL server running with the necessary database and user credentials mentioned in the code. Adjust the host, user, password, and database values in src/index.js according to your MySQL configuration.
With these steps, you have set up a simple CRUD Node Express server with MySQL integration. You can now make HTTP requests to the server to perform CRUD operations on the "events" table in your MySQL database.
3. Run Your Express App and Connect to MySQL
To run your Express app and connect to MySQL, follow these steps:
Step 1: Open Terminal
Open the terminal or command prompt in the project directory.
Step 2: Run the Express App
Run the following command in the terminal to start your Express app:
node src/index.js
Step 3: Fix MySQL Authentication Error
If you encounter the "ER_NOT_SUPPORTED_AUTH_MODE" error, you need to update the authentication mode for the MySQL user. Follow these instructions:
a) Open a new terminal or command prompt window.
b) Log into your MySQL server using the following command:
mysql -u root -p
You will be prompted to enter the password for the root user. Provide the password and press Enter.
c) Once logged in, execute the following SQL query to update the authentication mode for the 'timeline' user (replace 'password' with the actual password you used in your code):
ALTER USER 'timeline'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
This query updates the authentication mode for the 'timeline' user to use the 'mysql_native_password' method.
d) After executing the query, exit the MySQL client by typing:
quit;
You have now successfully run your Express app and fixed the MySQL authentication error. Your app should be connected to the MySQL database and ready to handle requests.
4. Authentication With JWT
To implement authentication with JWT and Okta, follow these steps:
Step 1: Sign up for Okta
Sign up for a free developer account with Okta and access your Okta dashboard.
Step 2: Create a new application in Okta a)
In the Okta dashboard,
select the "Applications" link in the top menu.
Click the "Add Application" button and choose "Single-Page App" as the application type.
Edit the application settings by setting the base URI to http://localhost:4200/ and the Login Redirect URI to http://localhost:4200/implicit/callback.
After saving the settings, note down the client ID displayed on the screen.
Step 3: Install required libraries
In your project's terminal, run the following command to install the necessary libraries:
npm install --save-exact express-bearer-token@2.4.0 @okta/jwt-verifier@1.0.0
Step 4: Create src/auth.js file
Create a new file called src/auth.js and paste the following code into it:
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;
Replace {yourClientId} with the client ID obtained from the Okta application settings, and {yourOktaDomain} with your Okta domain.
Step 5: Update src/index.js
Open src/index.js and add the following required statements at the top of the file:
const bearerToken = require('express-bearer-token');
const oktaAuth = require('./auth');
Then modify the Express application configuration to include the bearerToken and oktaAuth middleware:
const app = express()
.use(cors())
.use(bodyParser.json())
.use(bearerToken())
.use(oktaAuth)
.use(events(connection));
Step 6: Update src/events.js
In src/events.js, remove the line const owner = ''; and add const owner = req.user.email; as the first line in each router block:
router.post('/event', (req, res, next) => {
const owner = req.user.email;
// db.query() code
});
router.get('/event', function (req, res, next) {
const owner = req.user.email;
// db.query() code
});
router.put('/event/:id', function (req, res, next) {
const owner = req.user.email;
// db.query() code
});
router.delete('/event/:id', function (req, res, next) {
const owner = req.user.email;
// db.query() code
});
These steps implement JWT authentication with Okta in your application. User authentication is now handled securely, and events are stored separately for each user, ensuring that each user can only see their own events.
5. The Timeline Angular Client
Now it’s time to implement the client application, based on Angular 8 with the ngx-bootstrap library for responsive layout and standard components. You will also use a free icon set called Line Awesome, a variant of the well-known Font Awesome that replaces the standard icon designs with some stylish line icons. You will also use the ngx-timeline library to make it easy to create beautiful vertical timelines.
Step 1: Install Angular CLI
Open a terminal and run the following command to install the latest version of the Angular CLI:
npm install -g @angular/cli@8.1.2
Depending on your system, you might have to run this command using sudo to allow modification of system resources.
Step 2: Create a new Angular application
Navigate to the directory where you want to create your project and run the following command to generate a new Angular application:
ng new timeline-client
When prompted, select "Y" to add Angular routing and choose the default CSS option for stylesheet format.
Step 3: Install additional packages
Change into the newly created "timeline-client" directory:
cd timeline-client
Install the ngx-bootstrap library for responsive layout and standard components:
ng add ngx-bootstrap@5.1.0
Install the ngx-timeline library for creating vertical timelines:
npm install --save-exact ngx-timeline@5.0.0
Install Okta's Angular SDK for authentication (optional):
npm install --save-exact @okta/okta-angular@1.2.1
Step 4: Configure external CSS files
Open the file src/index.html in your IDE and add the following external CSS files inside the <head> tags:
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://maxcdn.icons8.com/fonts/line-awesome/1.1/css/line-awesome-font-awesome.min.css">
Step 5: Update src/app/app/components.ts
Open src/app/app.component.ts and replace the contents with the following code:
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('/');
}
}
The AppComponent handles the authentication through Okta. OktaAuthService.isAuthenticated() initializes the isAuthenticated field of the component. The field is kept up to date by subscribing to the OktaAuthService.$authenticationState observable. OktaAuthService.loginRedirect() will trigger a browser redirect to the Okta login page and OktaAuthService.logout('/') will log out the user and navigate to the '/' route.
Step 6: Update src/app/app.component.html
Open src/app/app.component.html and replace the existing code with the following:
<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>
The bootstrap navigation bar contains a menu with links to the home page and a timeline route. It also contains a login button and a logout button that appear depending on the authentication state.
Step 7: Update src/app/app.module.ts
Open src/app/app.module.ts and update the imports and declarations as shown below:
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 { OktaAuthModule } from '@okta/okta-angular';
@NgModule({
declarations: [
AppComponent,
HomeComponent,
TimelineComponent
],
imports: [
BrowserModule,
HttpClientModule,
AppRoutingModule,
BrowserAnimationsModule,
FormsModule,
ReactiveFormsModule,
BsDatepickerModule.forRoot(),
NgxTimelineModule,
ModalModule.forRoot(),
OktaAuthModule.initAuth({
issuer: 'https://{yourOktaDomain}/oauth2/default',
redirectUri: 'http://localhost:4200/implicit/callback',
clientId: '{yourClientId}'
})
],
providers: [],
bootstrap: [AppComponent]
})
export class AppModule { }
Make sure to replace {yourOktaDomain} with your Okta domain and {yourClientId} with your client ID.
STEP 8: Create components
Create two components for the home page and the timeline page, as well as a service to connect to the server.
ng generate component home
ng generate component timeline
ng generate service server
The home page will simply contain a heading and no other functionality.
STEP 9: Update src/app/home/home.component.html
Open src/app/home/home.component.html and replace the contents with the following.
<div class="container">
<div class="row">
<div class="col-sm">
<h1>Angular MySQL Timeline</h1>
</div>
</div>
</div>
STEP 10: Add the style
Now open src/app/home/home.component.css and add the following styles.
h1 {
margin-top: 50px;
text-align: center;
}
STEP 11: Update src/app/server.service.ts
Now, open src/app/server.service.ts and replace the contents with the following code.
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}`);
}
}
The src/app/server.service.ts component is responsible for handling server requests. Within this component, there is a request method that sends a request to the server. To authenticate the user with the server, a bearer token obtained from the OktaAuthService is included in the request header. This method returns a Promise that resolves once a response is received from the server.
The remaining functionalities of the ServerService utilize the request method to interact with the server routes. The URL of the server is retrieved from the environment object, which is defined in src/environments/environment.ts.
Here's an example of the environment.ts file:
export const environment = {
production: false,
serverUrl: 'http://localhost:8080'
};
By accessing environment.serverUrl, you can obtain the server URL needed for server communication. Additionally, the comments within the src/environments/environment.ts file provide useful guidance on how to utilize environments effectively within the Angular framework.
STEP 12: Implement the timeline component
Now, you can proceed with implementing the timeline component. Open the file src/app/timeline/timeline.component.html and modify its contents to match the following code:
<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>
In this template, we utilize the ngx-timeline component to display an array of events in a vertical timeline format. Each event is rendered using the ng-template directive, which provides a custom template for the timeline body. The template includes buttons for editing and deleting events.
STEP 13: Create model window using ngx-bootstrap
ngx-bootstrap allows you to simply create a modal window from an ng-template component. Paste the following code into the bottom of the file.
<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">×</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>
This code defines a modal window template that includes a header, body, and form for capturing event details. The form is associated with a formGroup named form and contains input fields for event name, description, and date. The modal also includes buttons for submitting and canceling the form.
In addition, you can add the following styling in src/app/timeline/timeline.component.css to enhance the design:
.page-content {
margin-top: 2rem;
}
.button-row {
display: flex;
justify-content: space-between;
margin-top: 1rem;
}
These styles ensure proper spacing and alignment of elements within the timeline component.
The src/app/timeline/timeline.component.ts file contains the main application logic for the component.
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();
}
}
The addEvent(), editEvent(), and deleteEvent() methods serve as event handlers for the corresponding HTML buttons. When called, addEvent() and editEvent() open the modal window and set either createEvent() or updateEvent() as the callback function when the user submits the data. These methods utilize the ServerService to send the necessary requests to the server.
STEP 14: Link components to the routes
To complete the integration, you need to link the components to the routes. Open src/app/app-routing.module.ts and include the following import statements at the top of the file to access the required functionalities:
import { HomeComponent } from './home/home.component';
import { TimelineComponent } from './timeline/timeline.component';
import { OktaCallbackComponent, OktaAuthGuard } from '@okta/okta-angular';
STEP 15: Replace the routes
Next, replace the routes array with the following configuration:
const routes: Routes = [
{
path: '',
component: HomeComponent
},
{
path: 'timeline',
component: TimelineComponent,
canActivate: [OktaAuthGuard]
},
{ path: 'implicit/callback', component: OktaCallbackComponent }
];
First, it sets the base route and then associates it with the HomeComponent. After that, it links the timeline route to the TimelineComponent and ensures that only authenticated users can access it by utilizing the OktaAuthGuard.
When a user successfully authenticates via the Okta service, they will be automatically redirected to the implicit/callback route.
By configuring the routes in this manner, you establish the navigation paths within your application and define the corresponding components to be rendered when accessing those routes.
6. Run Your Angular Client Application
This completes the implementation of the client. You can start the client from the terminal by running this command.
ng serve
Make sure that you have also started the server as described in the previous section. Open a browser and navigate to http://localhost:4200. Log in, click on Timeline in the top menu, and add events to your heart's content."
Conclusion
We have learned the process of building a web application with Angular and MySQL, utilizing an Express-based server. By leveraging the ngx-timeline library, you were able to create visually appealing timelines. Authentication, when implemented from scratch, demands a strong understanding of current security issues. However, by utilizing Okta authentication and helper libraries, you can achieve authentication functionality with just a few lines of code, simplifying the process significantly.
Comments