Create RESTful Api with Node.js , Express, MySQL

Create RESTful Api with Node.js , Express, MySQL

If you want to create a RESTful API with Node Js framework Express js? Here is the rest api crud tutorial, by reading this tutorial you can create our own REST API application server.

Create RESTful api in Node js – Today we will learn how to create node js apis using node js framework express with mysql database.

If you want to create a RESTful API with Node Js framework Express js? Here is the rest api crud tutorial, by reading this tutorial you can create our own REST API application server.

We are going to use a very popular web node js framework called Express to create RESTful APIs using mysql database.

Create Project Folder

Use the below command and create your express project with name expressfirst

 mkdir node-rest-crud-api
 cd node-rest-crud-api

After successfully created node-rest-crud-api folder in your system. Next follow the below commands and install node js express in your project :

npm init --yes

npm install 

Now install express js framework and MySQL driver with NPM. go to terminal and use the below commands :

 npm install express --save
 npm install mysql --save
 npm install body-parser --save

Create Database and table

Next we need to create database and table for perform crud operation of node js restful api.

-- Table structure for users
  CREATE TABLE IF NOT EXISTS users (
    id int(11) NOT NULL,
    name varchar(200) NOT NULL,
    email varchar(200) NOT NULL,
    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  ALTER TABLE users ADD PRIMARY KEY (id);
  ALTER TABLE users MODIFY id int(11) NOT NULL AUTO_INCREMENT;
Insert data into database :
INSERT INTO users (id, name, email, created_at) VALUES
  (1, 'Test', '[email protected]', '2019-02-28 13:20:20'),
  (2, 'john', '[email protected]', '2019-02-28 13:20:20'),
  (3, 'tutsmake', '[email protected]', '2019-02-28 13:20:20'),
  (4, 'tut', '[email protected]', '2019-02-28 13:20:20'),
  (5, 'mhd', '[email protected]', '2019-02-28 13:20:20');

Create server.js

Next we need to create server.js file inside of node-rest-crud-api folder and put the connection. server.js is entry point.

var express = require('express');
 var app = express();
 var bodyParser = require('body-parser');

 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({
     extended: true
 }));
 // default route
 app.get('/', function (req, res) {
     return res.send({ error: true, message: 'hello' })
 });
 // set port
 app.listen(3000, function () {
     console.log('Node app is running on port 3000');
 });
 module.exports = app;

Create database connection

We need to required database connection for fetching or update data into database :

// connection configurations
 var dbConn = mysql.createConnection({
     host: 'localhost',
     user: 'root',
     password: '',
     database: 'node_js_api'
 });
 // connect to database
 dbConn.connect(); 
We will implement following apis with methods name

| Method | Url | Action |
| GET | /users | fetch all users |
| GET
| user/1 | fetch user with id ==1 |
| POST | user | add new user |
| PUT | user | update user by id == 1 |
| DELETE | user | delete user by id == 1 |

Create users list api

Now we will first implement route of users list api with we will Fetch all users into database.

// Retrieve all users 
 app.get('/users', function (req, res) {
     dbConn.query('SELECT * FROM users', function (error, results, fields) {
         if (error) throw error;
         return res.send({ error: false, data: results, message: 'users list.' });
     });
 });

This function simply return all users information as you can see in this query, to call this API use this URL http://127.0.0.1:3000/users.

Get Single User Api

Now we will implement single user api. This api will return single user record.

// Retrieve user with id 
 app.get('/user/:id', function (req, res) {
     let user_id = req.params.id;
     if (!user_id) {
      return res.status(400).send({ error: true, message: 'Please provide user_id' });
     }
     dbConn.query('SELECT * FROM users where id=?', user_id, function (error, results, fields) {
      if (error) throw error;
       return res.send({ error: false, data: results[0], message: 'users list.' });
     });
 });

Call this API use this URL http://127.0.0.1/user/1.

Add User Api

Now We will implement add new user route so that when a user sends a post request with required data, app will add a new record to the database.

// Add a new user  
 app.post('/user', function (req, res) {
     let user = req.body.user;
     if (!user) {
       return res.status(400).send({ error:true, message: 'Please provide user' });
     }
    dbConn.query("INSERT INTO users SET ? ", { user: user }, function (error, results, fields) {
   if (error) throw error;
     return res.send({ error: false, data: results, message: 'New user has been created successfully.' });
     });
 });

This API function accepts post request and insert record in your database. To call this API use this URL http://127.0.0.1:3000/add

Update User Api

Now We will implement update user route so that when a user sends a post request with required data, app will update record to the database.

//  Update user with id
 app.put('/user', function (req, res) {
 let user_id = req.body.user_id;
 let user = req.body.user;
 if (!user_id || !user) {
   return res.status(400).send({ error: user, message: 'Please provide user and user_id' });
 }
 dbConn.query("UPDATE users SET user = ? WHERE id = ?", [user, user_id], function (error, results, fields) {
   if (error) throw error;
   return res.send({ error: false, data: results, message: 'user has been updated successfully.' });
  });
 });

This API accept put request and updates record in your database. To call this API use this URL http://127.0.0.1/user/{id}

Delete User Api

We are going to create a new route so that when a user sends a delete request, the app will delete a record from the database.

//  Delete user
 app.delete('/user', function (req, res) {
 let user_id = req.body.user_id;
 if (!user_id) {
     return res.status(400).send({ error: true, message: 'Please provide user_id' });
 }
 dbConn.query('DELETE FROM users WHERE id = ?', [user_id], function (error, results, fields) {
     if (error) throw error;
     return res.send({ error: false, data: results, message: 'User has been updated successfully.' });
 });
 }); 

Here is the complete server.js file.

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');
  
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));
  
  
// default route
app.get('/', function (req, res) {
    return res.send({ error: true, message: 'hello' })
});
// connection configurations
var dbConn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_js_api'
});
  
// connect to database
dbConn.connect(); 
 
 
// Retrieve all users 
app.get('/users', function (req, res) {
    dbConn.query('SELECT * FROM users', function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'users list.' });
    });
});
 
 
// Retrieve user with id 
app.get('/user/:id', function (req, res) {
  
    let user_id = req.params.id;
  
    if (!user_id) {
        return res.status(400).send({ error: true, message: 'Please provide user_id' });
    }
  
    dbConn.query('SELECT * FROM users where id=?', user_id, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'users list.' });
    });
  
});
 
 
// Add a new user  
app.post('/user', function (req, res) {
  
    let user = req.body.user;
  
    if (!user) {
        return res.status(400).send({ error:true, message: 'Please provide user' });
    }
  
    dbConn.query("INSERT INTO users SET ? ", { user: user }, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'New user has been created successfully.' });
    });
});
 
 
//  Update user with id
app.put('/user', function (req, res) {
  
    let user_id = req.body.user_id;
    let user = req.body.user;
  
    if (!user_id || !user) {
        return res.status(400).send({ error: user, message: 'Please provide user and user_id' });
    }
  
    dbConn.query("UPDATE users SET user = ? WHERE id = ?", [user, user_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'user has been updated successfully.' });
    });
});
 
 
//  Delete user
app.delete('/user', function (req, res) {
  
    let user_id = req.body.user_id;
  
    if (!user_id) {
        return res.status(400).send({ error: true, message: 'Please provide user_id' });
    }
    dbConn.query('DELETE FROM users WHERE id = ?', [user_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'User has been updated successfully.' });
    });
}); 
 
// set port
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});
 
module.exports = app;

We have successfully implemented node js crud api using express js.

NodeJS API Development with Express MongoDB and Mongoose

NodeJS API Development with Express MongoDB and Mongoose

NodeJS API Development with Express MongoDB and Mongoose. MongoDB is a NoSQL document-oriented database. It’s popular in the Node.js community and a viable database solution for building real-world applications.

MongoDB is different from traditional, SQL databases like MySQL and PostgreSQL in that data is stored in binary JSON-like objects called BSON). This structure lends itself well to building Javascript applications that communicate with JSON. Additionally, MongoDB has flexible schema. This means there aren’t database migrations to worry about and data models can grow and change.

In this tutorial, we’re going to set up a Node.js server application, connect it to MongoDB and demonstrate how relationships work between MongoDB Collections. In the table below (provided by MongoDB) you’ll see how traditional aspects of SQL databases stack up against their MongoDB equivalents. You can find the whole source code for this tutorial in this GitHub repo.

In SQL databases, we get database relationships using joins. For example, if we had a SQL database with two tables, books and authors, we could get all the books that belong to an author like so:

SELECT b.id AS ‘Post ID’, 
b.title AS ‘Book Title’, 
a.name AS ‘Author Name`, 
a.id AS ‘Author ID’
FROM books b
JOIN authors ON b.author_id = a.id
WHERE a.id = 1234;

This will grab information from both tables and display the results in a single dataset for us. Frameworks like Ruby On Rails and Laravel have abstracted this functionality for developers, making it possible to write PHP or Ruby to grab related information.

In Ruby On Rails, using Active Record finding, an author and related posts could look like:

authorWithBooks = Author.find 1234, :include => [:books]

In Laravel, using Eloquent, we could do:

$authorWithBooks = Author::find(1234)->books();

These results would give us the author with id 1234 and all the books that they've written. On the books table, we'd store an author_id, setting up the relationship between authors and books in the SQL world. MongoDB doesn't use joins though, so how do we achieve this functionality?

There is a helper npm package for working with MongoDB called mongoose that we’re going to use for illustrative purposes in this tutorial. Mongoose is an ORM (stands for Object Relationship Mapper) that is a helper for MongoDB kind of like how ActiveRecord and Eloquent are helpers for working with relational data.

Create Database Models with Mongoosejs

The first thing to do is set up our models in Mongoose. These schemas are flexible but help us define what we want our data to look like.

For the author model, we define a model schema that can reference documents in another collection:

const mongoose = require('mongoose');
const authorModel = mongoose.Schema({
  name: { 
   type: String, 
   required: '{PATH} is required!'
  },
  bio: {
   type: String
  },
  website: {
   type: String
  },
  books: [
    { type: mongoose.Schema.Types.ObjectId, ref: 'Book' }
  ]
}, {
  timestamps: true
});
module.exports = mongoose.model('Author', authorModel);

In the above model, we define that in the authors MongoDB collection authors have name, bio, website and an array of books. Each element in the books array will reference the book id on the books collection. We’ll define that below. The second argument, saying timestamps = true will include "updated at" and "created at" fields when we create author records.

The Books schema models what our book data will look like. The schema has a reference to find the id of an associated author. In this example, I’m saying that a book is written by only one author, though in the real world that’s not always the case! Here’s what a belongs-to relationship could look like using Mongoose.js:

const mongoose = require('mongoose');
const bookModel = mongoose.Schema({
  title: { 
    type: String, 
    required: '{PATH} is required!'
  },
  subtitle: {
    type: String
  },
  author: { 
    type: mongoose.Schema.Types.ObjectId, 
    ref: 'Author' 
  }
}, {
  timestamps: true
});
module.exports = mongoose.model('Book', bookModel);

Instead of an array of authors, the book references one author id as the author of the book. We’re using timestamps again for the “updated at” and “created at fields”.

In the root models directory, I added an index to register the models:

module.exports = {
  'Author': require('./Author'),
  'Book': require('./Book'),
};
Register Routes to Return JSON From Express 4

Now that we have the authors and book models defined, it’s time to return and show the data via a JSON API. For that, I set up a controllers for Authors called AuthorsController and one for Books called BooksController. The controllers are responsible for handling the request after the router determines which route to use. Below, we'll define a method for rendering a JSON response of all authors and the JSON of one author based on an id.

The authors controller looks like this:

const { Author } = require('../models');
const AuthorsController = {
  async index(req, res){
    const authors = await Author
       .find()
       .populate('books');
    res.send(authors);
  },
  async show(req, res){
    const author = await Author
       .findById(req.params.id)
       .populate(‘books’);
    res.send(author);
  }
};
module.exports = AuthorsController;

Here, I’m importing the author model, grabbing all of them and populating the query result with the related books. To use the async-await functionality with Express 4, I pulled in a package called express-async-errors and registered it in like so: require('express-async-errors');.

Following that Express 4 requires some server boilerplate setup:

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(cors());
app.use(methodOverride());
app.use(cookieParser());
app.use(express.static(__dirname + '/public'));
require('./server/routes')(app);

In the **server/routes.js** file, I register API routes for showing all the authors with their books and individual author with their books:

const express = require('express'),
  path = require('path'),
  rootPath = path.normalize(__dirname + '/../'),
  router = express.Router(),
  { AuthorsController, 
    BooksController } = require('./controllers');
module.exports = function(app){
  router.get('/authors', AuthorsController.index);
  router.get('/authors/:id', AuthorsController.show);
  app.use('/api', router);
};

Now we have a working API that returns authors with the books that they’ve written. The only problem is that there are no authors or books stored in MongoDB yet! To fix that, we’ll need to set up code to seed the database with records. If you visit /api/authors now all you'll see is an empty array.

Seed Records Into MongoDB

We need to make sure that the Express 4 server connects properly to MongoDB. For that, we can connect via an URL and listen for successful connection events like so:

const mongoose = require('mongoose'),
  env = process.env.NODE_ENV = process.env.NODE_ENV || 'development',
  envConfig = require('../server/env')[env];
mongoose.Promise = require('bluebird');
mongoose.connect(envConfig.db, { useMongoClient: true, });
mongoose.connection.on('connected', function () {  
  console.log(`Database connection open to ${mongoose.connection.host} ${mongoose.connection.name}`);
});
mongoose.connection.on('error',function (err) {  
  console.log('Mongoose default connection error: ' + err);
});
mongoose.connection.on('disconnected', function () {  
  console.log('Mongoose default connection disconnected'); 
});

With the environment config file defined like so:

var path = require('path'),
  rootPath = path.normalize(__dirname + '/../../');
  
module.exports = {
  development: {
    rootPath: rootPath,
    db: 'mongodb://localhost/mongodb-relationships',
    port: process.env.PORT || 3000
  },
  production: {
    rootPath: rootPath,
    db: process.env.MONGOLAB_URI || 'you can add a mongolab uri here ($ heroku config | grep MONGOLAB_URI)',
    port: process.env.PORT || 80
  }
};

The seeder itself we’re going to run from the command line. It’s a bit verbose but goes through the process of creating and updating records in MongoDB with Mongoose.js.

require('./index');
const mongoose = require('mongoose');
const { Author, Book } = require('../server/models');
async function seedAuthors() {
  console.log('Seeding authors to ' + mongoose.connection.name + '...');
  const authors = [
    { name: 'JK Rowling', bio: 'J.K. Rowling is the author of the much-loved series of seven Harry Potter novels, originally published between 1997 and 2007.' },
    { name: 'Tony Robbins', bio: 'Tony Robbins is an entrepreneur, best-selling author, philanthropist and the nation\'s #1 Life and Business Strategist.' },
  ];
for (author of authors) {
    var newAuthor = new Author(author);
    await newAuthor.save();
  }
const a = await Author.find();
  console.log('authors: ', a);
}
async function seedBooks() {
  console.log('Seeding books to ' + mongoose.connection.name + '...');
const jkRowling = await Author.findOne({ name: 'JK Rowling' });
  const tonyRobbins = await Author.findOne({ name: 'Tony Robbins' });
let harryPotter = new Book({ title: 'Harry Potter', author: jkRowling._id });
  let awakenGiant = new Book({ title: 'Awaken the Giant Within', author: tonyRobbins._id });
await harryPotter.save();
  await awakenGiant.save();
jkRowling.books.push(harryPotter);
  tonyRobbins.books.push(awakenGiant);
await jkRowling.save();
  await tonyRobbins.save();
}
seedAuthors();
seedBooks();

This will create a new connection to the MongoDB database and then convert a normal array of JavaScript objects into data we can persistently access. The author will have an array of books with one book for each author in the array. To add more books, we can push to the books array and save the changes. Each book will have one author. MongoDB stores these relationships via the id. Using the populate method in our controller above, we'll be able to view the entire object.

After running the seeder, you should be able to see your records in MongoDB Compass, as shown below. Compass is a GUI for viewing, creating, deleting, querying and editing MongoDB data.

Test The API

Now, to view this data from MongoDB via the API, start the Node server with npm run start and visit localhost:3000/api/authors in the web browser.

The final data will look something like:

[ 
   { 
      "_id":"5d51ea23acaf6f3380bcab56",
      "updatedAt":"2019-08-12T22:38:46.925Z",
      "createdAt":"2019-08-12T22:37:23.430Z",
      "name":"JK Rowling",
      "bio":"J.K. Rowling is the author of the much-loved series of seven Harry Potter novels, originally published between 1997 and 2007.",
      "__v":1,
      "books":[ 
         { 
            "_id":"5d51ea76f607f9339d5a76f6",
            "updatedAt":"2019-08-12T22:38:46.919Z",
            "createdAt":"2019-08-12T22:38:46.919Z",
            "title":"Harry Potter",
            "author":"5d51ea23acaf6f3380bcab56",
            "__v":0
         }
      ]
   },
   { 
      "_id":"5d51ea23acaf6f3380bcab57",
      "updatedAt":"2019-08-12T22:38:46.937Z",
      "createdAt":"2019-08-12T22:37:23.475Z",
      "name":"Tony Robbins",
      "bio":"Tony Robbins is an entrepreneur, best-selling author, philanthropist and the nation's #1 Life and Business Strategist.",
      "__v":1,
      "books":[ 
         { 
            "_id":"5d51ea76f607f9339d5a76f7",
            "updatedAt":"2019-08-12T22:38:46.921Z",
            "createdAt":"2019-08-12T22:38:46.921Z",
            "title":"Awaken the Giant Within",
            "author":"5d51ea23acaf6f3380bcab57",
            "__v":0
         }
      ]
   }
]

Congratulations, you’ve built an API with Node.js, Express 4 and MongoDB!

Lastly, a word from the Jscrambler team — before shipping your web apps, make sure you are protecting their JavaScript source code against reverse-engineering, abuse, and tampering. 2 minutes is all it takes to begin your free Jscrambler trial and start protecting JavaScript.

First CRUD Node Express Js Mysql Example

First CRUD Node Express Js Mysql Example

In this node express js tutorial, we would love to share with you how to create a crud application in node express js with mysql

In this node express js tutorial, we would love to share with you how to create a crud application in node express js with mysql

Hello developers, Today we will discuss how to install node js framework express js and how to create crud (create, update, read, delete) application in node js using express js framework with mysql database. Today we would love to show you how to create crud application in node js.

We will create crud application in node express js framework with mysql database. we will do each thing step by step and easy.

In this node express js crud application. We will use ejs templating engine. It is very simple and easy to understand for everyone.

Let’s start create CRUD with Node.Js , Express, MySQL

Create Express js Project

Use the below command and create your express project with name expressfirst

express --view=ejs expressfirst

After successfully created expressfirst folder in your system. Next follow the below commands and install node js in your project :

cd expressfirst

npm install  

Next we need to install some required pacakges, go to terminal and use the below commands :

 npm install express-flash --save
 npm install express-session --save
 npm install express-validator --save
 npm install method-override --save
 npm install mysql --save

Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.

In this node js mysql crud tutorial express flash is used to display a warning, error and information message

Express-session is used to made a session as like in PHP. In this node js mysql crud tutorial, session is needed as the express requirement of express-flash.

Express validator is used to validate form data it is easy to use. express-validator highly effective and efficient way to accelerate the creation of applications.

NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.

Driver to connect node.js with MySQL

Database Connection with Mysql

Next we need to create one folder name lib and create a new file name db.js inside this folder. We will connect node js to mysql using this file

lib/db.js
 var mysql=require('mysql');
 var connection=mysql.createConnection({
   host:'localhost',
   user:'your username',
   password:'your password',
   database:'your database name'
 });
connection.connect(function(error){
   if(!!error){
     console.log(error);
   }else{
     console.log('Connected!:)');
   }
 });  
module.exports = connection; 

Changes in app.js

We need to some changes in app.js file. go to app.js file and put some code here :

 var createError = require('http-errors');
 var express = require('express');
 var path = require('path');
 var cookieParser = require('cookie-parser');
 var logger = require('morgan');
 var expressValidator = require('express-validator');
 var flash = require('express-flash');
 var session = require('express-session');
 var bodyParser = require('body-parser');

 var mysql = require('mysql');
 var connection  = require('./lib/db');

 var indexRouter = require('./routes/index');
 var usersRouter = require('./routes/users');
 var customersRouter = require('./routes/customers');

 var app = express();

// view engine setup
 app.set('views', path.join(__dirname, 'views'));
 app.set('view engine', 'ejs');

 app.use(logger('dev'));
 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({ extended: true }));
 app.use(cookieParser());
 app.use(express.static(path.join(__dirname, 'public')));

 app.use(session({ 
     secret: '123456cat',
     resave: false,
     saveUninitialized: true,
     cookie: { maxAge: 60000 }
 }))

 app.use(flash());
 app.use(expressValidator());

 app.use('/', indexRouter);
 app.use('/users', usersRouter);
 app.use('/customers', customersRouter);

 // catch 404 and forward to error handler
 app.use(function(req, res, next) {
   next(createError(404));
 });

 // error handler
 app.use(function(err, req, res, next) {
   // set locals, only providing error in development
   res.locals.message = err.message;
   res.locals.error = req.app.get('env') === 'development' ? err : {};
 // render the error page
   res.status(err.status || 500);
   res.render('error');
 });
 module.exports = app;

Create Route

Next We need to create one route file name customers.js inside routes folder. After created this file, We will implement crud logic in this file. Go to routes/customers.js and use the below code :

var express = require('express');
var router = express.Router();
var connection  = require('../lib/db');
 
 
/* GET home page. */
router.get('/', function(req, res, next) {
      
 connection.query('SELECT * FROM customers ORDER BY id desc',function(err,rows)     {
 
        if(err){
         req.flash('error', err); 
         res.render('customers',{page_title:"Customers - Node.js",data:''});   
        }else{
            
            res.render('customers',{page_title:"Customers - Node.js",data:rows});
        }
                            
         });
        
    });
 
 
// SHOW ADD USER FORM
router.get('/add', function(req, res, next){    
    // render to views/user/add.ejs
    res.render('customers/add', {
        title: 'Add New Customers',
        name: '',
        email: ''        
    })
})
 
// ADD NEW USER POST ACTION
router.post('/add', function(req, res, next){    
    req.assert('name', 'Name is required').notEmpty()           //Validate name
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
  
    var errors = req.validationErrors()
     
    if( !errors ) {   //No errors were found.  Passed Validation!
         
     
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
         
     connection.query('INSERT INTO customers SET ?', user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                     
                    // render to views/user/add.ejs
                    res.render('customers/add', {
                        title: 'Add New Customer',
                        name: user.name,
                        email: user.email                    
                    })
                } else {                
                    req.flash('success', 'Data added successfully!');
                    res.redirect('/customers');
                }
            })
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })                
        req.flash('error', error_msg)        
         
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/add', { 
            title: 'Add New Customer',
            name: req.body.name,
            email: req.body.email
        })
    }
})
 
// SHOW EDIT USER FORM
router.get('/edit/(:id)', function(req, res, next){
   
connection.query('SELECT * FROM customers WHERE id = ' + req.params.id, function(err, rows, fields) {
            if(err) throw err
             
            // if user not found
            if (rows.length <= 0) {
                req.flash('error', 'Customers not found with id = ' + req.params.id)
                res.redirect('/customers')
            }
            else { // if user found
                // render to views/user/edit.ejs template file
                res.render('customers/edit', {
                    title: 'Edit Customer', 
                    //data: rows[0],
                    id: rows[0].id,
                    name: rows[0].name,
                    email: rows[0].email                    
                })
            }            
        })
  
})
 
// EDIT USER POST ACTION
router.post('/update/:id', function(req, res, next) {
    req.assert('name', 'Name is required').notEmpty()           //Validate nam           //Validate age
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
  
    var errors = req.validationErrors()
     
    if( !errors ) {   
 
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
         
connection.query('UPDATE customers SET ? WHERE id = ' + req.params.id, user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                     
                    // render to views/user/add.ejs
                    res.render('customers/edit', {
                        title: 'Edit Customer',
                        id: req.params.id,
                        name: req.body.name,
                        email: req.body.email
                    })
                } else {
                    req.flash('success', 'Data updated successfully!');
                    res.redirect('/customers');
                }
            })
         
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })
        req.flash('error', error_msg)
         
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/edit', { 
            title: 'Edit Customer',            
            id: req.params.id, 
            name: req.body.name,
            email: req.body.email
        })
    }
})
       
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
    var user = { id: req.params.id }
     
connection.query('DELETE FROM customers WHERE id = ' + req.params.id, user, function(err, result) {
            //if(err) throw err
            if (err) {
                req.flash('error', err)
                // redirect to users list page
                res.redirect('/customers')
            } else {
                req.flash('success', 'Customer deleted successfully! id = ' + req.params.id)
                // redirect to users list page
                res.redirect('/customers')
            }
        })
   })
 
 
module.exports = router;

Create views

First we will create one foleder name customers inside the views folder.

Next we need to create three views file name add.ejs, edit.ejs and index.ejs. We will create three view files inside the views/customers folder.

Create first file index.ejs

Index.ejs file, we will display the list of customers.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <div>
    <a href="/" class="btn btn-primary ml-3">Home</a>  
    <a href="/customers/add" class="btn btn-secondary ml-3">New Customer</a> 
    <a href="/customers" class="btn btn-info ml-3">Customer List</a>
</div>    
<!--   <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %> -->
  
<% if (messages.success) { %>
    <p class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>  
<br>
  <table class="table">
<thead>
  <tr>
    <th scope="col">#</th>
    <th scope="col">Name</th>
    <th scope="col">Email</th>
    <th width="200px">Action</th>
 
  </tr>
</thead>
<tbody>
  <% if(data.length){
 
  for(var i = 0; i< data.length; i++) {%>  
  <tr>
    <th scope="row"><%= (i+1) %></th>
    <td><%= data[i].name%></td>
    <td><%= data[i].email%></td>
    <td>
    <a class="btn btn-success edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
    <a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
   </td>
  </tr>
  <% }
           
   }else{ %>
       <tr>
          <td colspan="3">No user</td>
       </tr>
    <% } %>    
  
</tbody>
</table>
</body>
</html>

Create second file name add.ejs

Add.ejs file, we will create form for sending to data in database.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %>
  
<% if (messages.success) { %>
    <p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/customers/add" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
</div>
 
<input type="submit" class="btn btn-primary" value="Add">
</form>
</body>
</html>

Create third file name edit.ejs

Next create last file edit.ejs, we will to edit data in this form.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<form action="/customers/update/<%= id %>" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="<%= name %>" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" class="form-control" name="email" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="<%= email %>">
  
</div>
 
<button type="submit" class="btn btn-info">Update</button>
</form>
</body>
</html>

Test Node js Crud app

run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/customers

Conclusion

In this node express js crud tutorial – We have created node js crud (create, read, update, delete) application with mysql database. We have also successfully install node js framework express with use ejs templating engine.

Learn More

MySQL Databases With Python Tutorial

Build a Basic CRUD App with Node and React

Build a Simple CRUD App with Python, Flask, and React

Build a Basic CRUD App with Laravel and Vue

Build a Simple CRUD App with Spring Boot and Vue.js

Build a Basic CRUD App with Laravel and Angular

Build a Basic CRUD App with Laravel and React

Express.js & Node.js Course for Beginners - Full Tutorial

Build a CRUD App with Angular and Firebase

Angular 7 + Spring Boot CRUD Example

The Complete Node.js Developer Course (3rd Edition)

Angular & NodeJS - The MEAN Stack Guide

NodeJS - The Complete Guide (incl. MVC, REST APIs, GraphQL)

Node.js: The Complete Guide to Build RESTful APIs (2018)

How to get started Internationalization in JavaScript with NodeJS

How to get started Internationalization in JavaScript with NodeJS

Tutorial showing how to use the Intl JS API in NodeJS (i18n). We'll install a module to unlock the Intl API languages for Node and test out RelativeTimeFormat to translate and localise relative times in JavaScript.

Tutorial showing how to use the Intl JS API in NodeJS (i18n). We'll install a module to unlock the Intl API languages for Node and test out RelativeTimeFormat to translate and localise relative times in JavaScript. I'll tell you how to get started with the built-in internationalization library in JS for Node 12 and higher. We'll change the locale to see how the translation works and test different BCP 47 language tags.

Internationalization is a difficult undertaking but using the Intl API is an easy way to get started, it's great to see this new API in the JS language and available for use. Soon, you'll be able to have confidence using it in the browser as modern browsers support the major Intl features. Have a look at the browser compatibility charts to see which browsers and versions of node are supported.

Use Intl.RelativeTimeFormat for language-sensitive relative time formatting.
#javascript #nodejs #webdevelopment

MDN Documentation:

https://developer.mozilla.org/en-US/d...

Full ICU NPM package:

https://www.npmjs.com/package/full-icu