Angular7 CRUD with nodejs and mysql example
Nov 25, 2018 by Pankaj Kumar
Hey there, Today we will proceed to create a demo for CRUD with Mysql, Express, Angular7(MEAN) and Nodejs from scratch using Angular CLI.
Below are the requirements for creating the CRUD on MEAN
- Node.js
- Angular CLI
- Angular 7
- Mysql
- IDE or Text Editor
We assume that you have already available the above tools/frameworks and you are familiar with all the above that what individually actually does.
So now we will proceed step by step to achieve the task.
1. Update Angular CLI and Create Angular 7 Application
At first, We have to update the Angular CLI to the latest version. Open the terminal then go to the project folder and then type the below command to update the Angular CLI
sudo npm install -g @angular/cli
Once the above task finishes, Next task is to create new angular application with below command. So go to your project folder and then type below command:
ng new angular7-crud
then go to the newly created folder of angular application with cd /angular7-crud and type ng serve. Now, open the browser then go to http://localhost:4200 you should see this page.
2. Create a server with node.js express and Mysql for REST APIs
create a separate folder named server for server-side stuff, Then move inside folder and create server.js by typing touch server.js
Let's have a look on the server.js file
let app = require('express')(),
server = require('http').Server(app),
bodyParser = require('body-parser')
express = require('express'),
cors = require('cors'),
http = require('http'),
path = require('path');
let articleRoute = require('./Routes/article'),
util = require('./Utilities/util');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: false }));
app.use(cors());
app.use(function(err, req, res, next) {
return res.send({ "statusCode": util.statusCode.ONE, "statusMessage": util.statusMessage.SOMETHING_WENT_WRONG });
});
app.use('/article', articleRoute);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next();
});
/*first API to check if server is running*/
app.get('*', (req, res) => {
res.sendFile(path.join(__dirname, '../server/client/dist/index.html'));
})
server.listen(3000,function(){
console.log('app listening on port: 3000');
});
In the above file we can see, at the top, there are required packages for the app. Below that body parsing, middleware and routing is done.
The next task is to create routes and create a file article.js . So creating a folder name 'Routes' and adding article.js within it.
Add the below code for routing in article.js inside routing folder
let express = require('express'),
router = express.Router(),
util = require('../Utilities/util'),
articleService = require('../Services/article');
/**Api to create article */
router.post('/create-article', (req, res) => {
articleService.createArticle(req.body, (data) => {
res.send(data);
});
});
// /**Api to update article */
router.put('/update-article', (req, res) => {
articleService.updateArticle(req.body, (data) => {
res.send(data);
});
});
// /**Api to delete the article */
router.delete('/delete-article', (req, res) => {
articleService.deleteArticle(req.query, (data) => {
res.send(data);
});
});
/**Api to get the list of article */
router.get('/get-article', (req, res) => {
documentService.getArticle(req.query, (data) => {
res.send(data);
});
});
// /**API to get the article by id... */
router.get('/get-article-by-id', (req, res) => {
articleService.getArticleById(req.query, (data) => {
res.send(data);
});
});
module.exports = router;
Now create a folder named Utilities for all config, common methods and mysql connection config.
Now I am adding config values in a file named config.js
let environment = "dev";
let serverURLs = {
"dev": {
"NODE_SERVER": "http://localhost",
"NODE_SERVER_PORT": "3000",
"MYSQL_HOST": 'localhost',
"MYSQL_USER": 'root',
"MYSQL_PASSWORD": 'password',
'MYSQL_DATABASE': 'demo_angular7_crud',
}
}
let config = {
"DB_URL_MYSQL": {
"host": `${serverURLs[environment].MYSQL_HOST}`,
"user": `${serverURLs[environment].MYSQL_USER}`,
"password": `${serverURLs[environment].MYSQL_PASSWORD}`,
"database": `${serverURLs[environment].MYSQL_DATABASE}`
},
"NODE_SERVER_PORT": {
"port": `${serverURLs[environment].NODE_SERVER_PORT}`
},
"NODE_SERVER_URL": {
"url": `${serverURLs[environment].NODE_SERVER}`
}
};
module.exports = {
config: config
};
Now configure mysql connection. So I am writing the connection with database in a separate file. So creating a file named mysqkConfig.js under Utilities folder and adding the below line of code for mysql connection:
var config = require("../Utilities/config").config;
var mysql = require('mysql');
var connection = mysql.createConnection({
host: config.DB_URL_MYSQL.host,
user: config.DB_URL_MYSQL.user,
password: config.DB_URL_MYSQL.password,
database: config.DB_URL_MYSQL.database,
});
connection.connect(() => {
require('../Models/Article').initialize();
});
let getDB = () => {
return connection;
}
module.exports = {
getDB: getDB
}
Now I am creating separate file name util.js to save common methods and common status code/message:
// Define Error Codes
let statusCode = {
OK: 200,
FOUR_ZERO_FOUR: 404,
FOUR_ZERO_THREE: 403,
FOUR_ZERO_ONE: 401,
FIVE_ZERO_ZERO: 500
};
// Define Error Messages
let statusMessage = {
SERVER_BUSY : 'Our Servers are busy. Please try again later.',
DATA_UPDATED: 'Data updated successfully.',
DELETE_DATA : 'Delete data successfully',
};
module.exports = {
statusCode: statusCode,
statusMessage: statusMessage
}
Now the next part is model, So create a folder named Models and create a file Article.js and add the below code in it:
let mysqlConfig = require("../Utilities/mysqlConfig");
let initialize = () => {
mysqlConfig.getDB().query("create table IF NOT EXISTS article (id INT auto_increment primary key, category VARCHAR(30), title VARCHAR(24))");
}
module.exports = {
initialize: initialize
}
Now create DAO folder and add a file articleDAO.js for writting the mysql queries common functions:
let dbConfig = require("../Utilities/mysqlConfig");
let getArticle = (criteria, callback) => {
//criteria.aricle_id ? conditions += ` and aricle_id = '${criteria.aricle_id}'` : true;
dbConfig.getDB().query(`select * from article where 1`,criteria, callback);
}
let getArticleDetail = (criteria, callback) => {
let conditions = "";
criteria.id ? conditions += ` and id = '${criteria.id}'` : true;
dbConfig.getDB().query(`select * from article where 1 ${conditions}`, callback);
}
let createArticle = (dataToSet, callback) => {
console.log("insert into article set ? ", dataToSet,'pankaj')
dbConfig.getDB().query("insert into article set ? ", dataToSet, callback);
}
let deleteArticle = (criteria, callback) => {
let conditions = "";
criteria.id ? conditions += ` and id = '${criteria.id}'` : true;
console.log(`delete from article where 1 ${conditions}`);
dbConfig.getDB().query(`delete from article where 1 ${conditions}`, callback);
}
let updateArticle = (criteria,dataToSet,callback) => {
let conditions = "";
let setData = "";
criteria.id ? conditions += ` and id = '${criteria.id}'` : true;
dataToSet.category ? setData += `category = '${dataToSet.category}'` : true;
dataToSet.title ? setData += `, title = '${dataToSet.title}'` : true;
console.log(`UPDATE article SET ${setData} where 1 ${conditions}`);
dbConfig.getDB().query(`UPDATE article SET ${setData} where 1 ${conditions}`, callback);
}
module.exports = {
getArticle : getArticle,
createArticle : createArticle,
deleteArticle : deleteArticle,
updateArticle : updateArticle,
getArticleDetail : getArticleDetail
}
Now one create Services folder and add a file article.js for all the logic of API
let async = require('async'),
parseString = require('xml2js').parseString;
let util = require('../Utilities/util'),
articleDAO = require('../DAO/articleDAO');
//config = require("../Utilities/config").config;
/**API to create the atricle */
let createArticle = (data, callback) => {
async.auto({
article: (cb) => {
var dataToSet = {
"category":data.category?data.category:'',
"title":data.title,
}
console.log(dataToSet);
articleDAO.createArticle(dataToSet, (err, dbData) => {
if (err) {
cb(null, { "statusCode": util.statusCode.FOUR_ZERO_ONE, "statusMessage": util.statusMessage.SERVER_BUSY });
return;
}
cb(null, { "statusCode": util.statusCode.OK, "statusMessage": util.statusMessage.DATA_UPDATED,"result":dataToSet });
});
}
//]
}, (err, response) => {
callback(response.article);
});
}
/**API to update the article */
let updateArticle = (data,callback) => {
async.auto({
articleUpdate :(cb) =>{
if (!data.id) {
cb(null, { "statusCode": util.statusCode.FOUR_ZERO_ONE, "statusMessage": util.statusMessage.PARAMS_MISSING })
return;
}
console.log('phase 1');
var criteria = {
id : data.id,
}
var dataToSet={
"category": data.category,
"title":data.title,
}
console.log(criteria,'test',dataToSet);
articleDAO.updateArticle(criteria, dataToSet, (err, dbData)=>{
if(err){
cb(null,{"statusCode":util.statusCode.FOUR_ZERO_ONE,"statusMessage":util.statusMessage.SERVER_BUSY});
return;
}
else{
cb(null, { "statusCode": util.statusCode.OK, "statusMessage": util.statusMessage.DATA_UPDATED,"result":dataToSet });
}
});
}
}, (err,response) => {
callback(response.articleUpdate);
});
}
/**API to delete the subject */
let deleteArticle = (data,callback) => {
console.log(data,'data to set')
async.auto({
removeArticle :(cb) =>{
if (!data.id) {
cb(null, { "statusCode": util.statusCode.FOUR_ZERO_ONE, "statusMessage": util.statusMessage.PARAMS_MISSING })
return;
}
var criteria = {
id : data.id,
}
articleDAO.deleteArticle(criteria,(err,dbData) => {
if (err) {
console.log(err);
cb(null, { "statusCode": util.statusCode.FOUR_ZERO_ONE, "statusMessage": util.statusMessage.SERVER_BUSY });
return;
}
cb(null, { "statusCode": util.statusCode.OK, "statusMessage": util.statusMessage.DELETE_DATA });
});
}
}, (err,response) => {
callback(response.removeArticle);
});
}
/***API to get the article list */
let getArticle = (data, callback) => {
async.auto({
article: (cb) => {
articleDAO.getArticle({},(err, data) => {
if (err) {
cb(null, {"errorCode": util.statusCode.INTERNAL_SERVER_ERROR,"statusMessage": util.statusMessage.SERVER_BUSY});
return;
}
cb(null, data);
return;
});
}
}, (err, response) => {
callback(response.article);
})
}
/***API to get the article detail by id */
let getArticleById = (data, callback) => {
async.auto({
article: (cb) => {
let criteria = {
"id":data.id
}
articleDAO.getArticleDetail(criteria,(err, data) => {
if (err) {
console.log(err,'error----');
cb(null, {"errorCode": util.statusCode.INTERNAL_SERVER_ERROR,"statusMessage": util.statusMessage.SERVER_BUSY});
return;
}
cb(null, data[0]);
return;
});
}
}, (err, response) => {
callback(response.article);
})
}
module.exports = {
createArticle : createArticle,
updateArticle : updateArticle,
deleteArticle : deleteArticle,
getArticle : getArticle,
getArticleById : getArticleById
};
3. Create angular component for performing CRUD task of article
ng g component article
Above command will generate all required files for build article component and also automatically added this component to app.module.ts.
create src/app/article/article.component.css (0 bytes)
create src/app/article/article.component.html (23 bytes)
create src/app/article/article.component.spec.ts (614 bytes)
create src/app/article/article.component.ts (321 bytes)
update src/app/app.module.ts (390 bytes)
Now we need to add `HttpClientModule` to `app.module.ts`. Open and edit `src/app/app.module.ts` then add this import. And add it to `@NgModule` imports after `BrowserModule`. Now our app.module.ts will have following code:
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { ReactiveFormsModule } from '@angular/forms';
import { HttpModule } from '@angular/http';
import { AppComponent } from './app.component';
import { ArticleComponent } from './article.component';
import { ArticleService } from './article.service';
@NgModule({
imports: [
BrowserModule,
HttpModule,
ReactiveFormsModule
],
declarations: [
AppComponent,
ArticleComponent
],
providers: [
ArticleService
],
bootstrap: [
AppComponent
]
})
export class AppModule { }
Now create a service file where we will make all the request to the server for CRUD operation. Command for creating service is ng g service artcle , for now I have just created a file named it article.service.ts. Let's have a look in the code inside this file.
import { Injectable } from '@angular/core';
import { Http, Response, Headers, URLSearchParams, RequestOptions } from '@angular/http';
import { Observable } from 'rxjs';
import 'rxjs/add/operator/map';
import 'rxjs/add/operator/catch';
import { Article } from './article';
@Injectable()
export class ArticleService {
//URL for CRUD operations
articleUrl = "http://localhost:3000/article";
//Create constructor to get Http instance
constructor(private http:Http) {
}
//Fetch all articles
getAllArticles(): Observable<Article[]> {
return this.http.get(this.articleUrl+"/get-article")
.map(this.extractData)
.catch(this.handleError);
}
//Create article
createArticle(article: Article):Observable<number> {
let cpHeaders = new Headers({ 'Content-Type': 'application/json' });
let options = new RequestOptions({ headers: cpHeaders });
return this.http.post(this.articleUrl+"/create-article", article, options)
.map(success => success.status)
.catch(this.handleError);
}
//Fetch article by id
getArticleById(articleId: string): Observable<Article> {
let cpHeaders = new Headers({ 'Content-Type': 'application/json' });
let options = new RequestOptions({ headers: cpHeaders });
console.log(this.articleUrl +"/get-article-by-id?id="+ articleId);
return this.http.get(this.articleUrl +"/get-article-by-id?id="+ articleId)
.map(this.extractData)
.catch(this.handleError);
}
//Update article
updateArticle(article: Article):Observable<number> {
let cpHeaders = new Headers({ 'Content-Type': 'application/json' });
let options = new RequestOptions({ headers: cpHeaders });
return this.http.put(this.articleUrl +"/update-article", article, options)
.map(success => success.status)
.catch(this.handleError);
}
//Delete article
deleteArticleById(articleId: string): Observable<number> {
let cpHeaders = new Headers({ 'Content-Type': 'application/json' });
let options = new RequestOptions({ headers: cpHeaders });
return this.http.delete(this.articleUrl +"/delete-article?id="+ articleId)
.map(success => success.status)
.catch(this.handleError);
}
private extractData(res: Response) {
let body = res.json();
return body;
}
private handleError (error: Response | any) {
console.error(error.message || error);
return Observable.throw(error.status);
}
}
In the above file we have made all the http request for the CRUD operation. Observables of rxjs library has been used to handle the data fetching from http request.
Now let's move to the next file, article.component.ts. Here we have all the login part of the app. Let's have a look code inside this file:
import { Component, OnInit } from '@angular/core';
import { FormControl, FormGroup, Validators } from '@angular/forms';
import { ArticleService } from './article.service';
import { Article } from './article';
@Component({
selector: 'app-article',
templateUrl: './article.component.html',
styleUrls: ['./article.component.css']
})
export class ArticleComponent implements OnInit {
//Component properties
allArticles: Article[];
statusCode: number;
requestProcessing = false;
articleIdToUpdate = null;
processValidation = false;
//Create form
articleForm = new FormGroup({
title: new FormControl('', Validators.required),
category: new FormControl('', Validators.required)
});
//Create constructor to get service instance
constructor(private articleService: ArticleService) {
}
//Create ngOnInit() and and load articles
ngOnInit(): void {
this.getAllArticles();
}
//Fetch all articles
getAllArticles() {
this.articleService.getAllArticles()
.subscribe(
data => this.allArticles = data,
errorCode => this.statusCode = errorCode);
}
//Handle create and update article
onArticleFormSubmit() {
this.processValidation = true;
if (this.articleForm.invalid) {
return; //Validation failed, exit from method.
}
//Form is valid, now perform create or update
this.preProcessConfigurations();
let article = this.articleForm.value;
if (this.articleIdToUpdate === null) {
//Generate article id then create article
this.articleService.getAllArticles()
.subscribe(articles => {
//Generate article id
let maxIndex = articles.length - 1;
let articleWithMaxIndex = articles[maxIndex];
let articleId = articleWithMaxIndex.id + 1;
article.id = articleId;
console.log(article,'this is form data---');
//Create article
this.articleService.createArticle(article)
.subscribe(successCode => {
this.statusCode = successCode;
this.getAllArticles();
this.backToCreateArticle();
},
errorCode => this.statusCode = errorCode
);
});
} else {
//Handle update article
article.id = this.articleIdToUpdate;
this.articleService.updateArticle(article)
.subscribe(successCode => {
this.statusCode = successCode;
this.getAllArticles();
this.backToCreateArticle();
},
errorCode => this.statusCode = errorCode);
}
}
//Load article by id to edit
loadArticleToEdit(articleId: string) {
this.preProcessConfigurations();
this.articleService.getArticleById(articleId)
.subscribe(article => {
console.log(article,'poiuytre');
this.articleIdToUpdate = article.id;
this.articleForm.setValue({ title: article.title, category: article.category });
this.processValidation = true;
this.requestProcessing = false;
},
errorCode => this.statusCode = errorCode);
}
//Delete article
deleteArticle(articleId: string) {
this.preProcessConfigurations();
this.articleService.deleteArticleById(articleId)
.subscribe(successCode => {
//this.statusCode = successCode;
//Expecting success code 204 from server
this.statusCode = 204;
this.getAllArticles();
this.backToCreateArticle();
},
errorCode => this.statusCode = errorCode);
}
//Perform preliminary processing configurations
preProcessConfigurations() {
this.statusCode = null;
this.requestProcessing = true;
}
//Go back from update to create
backToCreateArticle() {
this.articleIdToUpdate = null;
this.articleForm.reset();
this.processValidation = false;
}
}
Now we have to show the task over browser, So lets have a look inside article.component.html file.
<h1 class="text-center">Angular 7 CRUD Demo App</h1>
<h3 class="text-center" *ngIf="articleIdToUpdate; else create">
Update Article for Id: {{articleIdToUpdate}}
</h3>
<ng-template #create>
<h3 class="text-center"> Create New Article </h3>
</ng-template>
<div>
<form [formGroup]="articleForm" (ngSubmit)="onArticleFormSubmit()">
<table class="table-striped" style="margin:0 auto;">
<tr><td>Enter Title</td><td><input formControlName="title">
<label *ngIf="articleForm.get('title').invalid && processValidation" [ngClass] = "'error'"> Title is required. </label>
</td></tr>
<tr><td>Enter Category</td><td><input formControlName="category">
<label *ngIf="articleForm.get('category').invalid && processValidation" [ngClass] = "'error'"> Category is required. </label>
</td></tr>
<tr><td colspan="2">
<button class="btn btn-default" *ngIf="!articleIdToUpdate">CREATE</button>
<button class="btn btn-default" *ngIf="articleIdToUpdate">UPDATE</button>
<button (click)="backToCreateArticle()" *ngIf="articleIdToUpdate">Go Back</button>
</td></tr>
</table>
</form>
<br/>
<div class="text-center" *ngIf="statusCode; else processing">
<div *ngIf="statusCode === 201" [ngClass] = "'success'">
Article added successfully.
</div>
<div *ngIf="statusCode === 409" [ngClass] = "'success'">
Article already exists.
</div>
<div *ngIf="statusCode === 200" [ngClass] = "'success'">
Article updated successfully.
</div>
<div *ngIf="statusCode === 204" [ngClass] = "'success'">
Article deleted successfully.
</div>
<div *ngIf="statusCode === 500" [ngClass] = "'error'">
Internal Server Error.
</div>
</div>
<ng-template #processing>
<img *ngIf="requestProcessing" src="assets/images/loading.gif">
</ng-template>
</div>
<h3 class="text-center">Article List</h3>
<table class="table-striped" style="margin:0 auto;" *ngIf="allArticles">
<tr><th> Id</th> <th>Title</th><th>Category</th><th></th><th></th></tr>
<tr *ngFor="let article of allArticles" >
<td>{{article.id}}</td> <td>{{article.title}}</td> <td>{{article.category}}</td>
<td><button class="btn btn-default" type="button" (click)="loadArticleToEdit(article.id)">Edit</button> </td>
<td><button class="btn btn-default" type="button" (click)="deleteArticle(article.id)">Delete</button></td>
</tr>
</table>
Now since I have created server and client two separate folder for nodejs and angular task. So will run both the apps with npm start over two tabs of terminal.
On the browser, over link http://localhost:4200. App will look like below
That’s all for now. Thank you for reading and I hope this post will be very helpful for creating CRUD operations with angular7,node.js & mysql.
Let me know your thoughts over the email demo.jsonworld@gmail.com. I would love to hear them and If you like this article, share with your friends.
Find complete source code over GitHub
