Angular 7 CRUD with Nodejs and MySQL Example

Angular 7 CRUD with Nodejs and MySQL Example

Angular7, 8, 9 CRUD with Nodejs and Mysql example. 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.

Angular 7 CRUD with Nodejs and MySQL Example

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

Angular CRUD with Nodejs and MySQL Example

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.

================================================

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Top Node.js Development Companies and Expert NodeJS Developers

A thoroughly researched list of top NodeJS development companies with ratings & reviews to help hire the best Node.JS developers who provide development services and solutions across the world. List of Leading Node.js development Service Providers...

Создание сайта на Mongo DB, Express JS, Node JS и Angular

Видео курс по изучению стека MEAN. В курсе вы научитесь создавать сайты при помощи Node JS, Express JS, Angular JS и баз данных MongoDB. Вы ознакомитесь со всеми моментами разработки и в конце курса выгрузите сайт на удаленный сервер.

Angular 8 Node & Express JS File Upload

In this Angular 8 and Node.js tutorial, we are going to look at how to upload files on the Node server. To create Angular image upload component, we will be using Angular 8 front-end framework along with ng2-file-upload NPM package; It’s an easy to use Angular directives for uploading the files.