CODOPLEX BLOG

Tag: expressjs

  • API Development Using NodeJS, ExpressJS and MySQL

    In this tutorial guide, we will build a simple NodeJS API using ExpressJS and MySQL with basic CRUD (create, read, update, delete) operations. NodeJS is a JavaScript run-time environment used to execute JavaScript code outside browsers i.e on the servers. Visit our other blog post, to learn more about what is NodeJS? NodeJS along with ExpressJS (web framework for NodeJS) is a widely used combination to build RESTful API’s with a database query language like MySQL or MongoDB.

    Setup NodeJS Project

    First of all, we need to create a new NodeJS app with ExpressJS and MySQL installed as dependencies using NPM (Node Package Manager). If you are new to NodeJS then following guides are recommended before getting started.

    //App dependencies
const express = require('express');
const app = express(); // creates an express application
const bodyParser = require('body-parser');
var cors = require('cors');

//App middle-wares
app.use(cors());
app.use(bodyParser.json());
app.use((req, res, next) => {
    res.header('Access-Control-Allow-Origin', '*');
    res.header('Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content-Type, Accept, Authorization');
    next();
});

//Routes
const users = express.Router();
app.use('/users', users);

//App server creation
app.listen(3000, function(){
    console.log('Server is running at port : ' + 3000);
});

    Database Connection

    We need to use mysql node package to create and use MySQL database with NodeJS. Following code can be used to connect to the MySQL database.

    const mysql = require('mysql'); //adding the mysql module

const db = mysql.createConnection({
  host: "localhost",
  user: "db username",
  password: "db password",
  database: "db name"
});

    CRUD Operations

    For demo we will create CRUD operations for managing users. We are assuming that a table named ‘users’ exist with necessary columns (fname, lname, username, email, password, role, createdAt). We will configure following routes/endpoints for our NodeJS API.

    • GET: http://localhost:3000/users/ – To read all users
    • GET: http://localhost:3000/users/:id – To read single user with specific ID
    • POST: http://localhost:3000/users/ – To create new user
    • POST: http://localhost:3000/users/:id – To update single user with specific ID
    • DELETE: http://localhost:3000/users/:id – To delete single user with specific ID

    Create New User

    //POST:  http://localhost:3000/users/
users.post('/', (req, res, next) => {
  const sql = "SELECT * FROM users WHERE email = ?";
  conn.query(sql, [req.body.email], function (err, result) {
      if (err) throw err;
      if(!result[0]){
        const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
        const sql = "INSERT INTO users SET ?";
        conn.query(sql, [user], function (err, result) {
            if (err) throw err;
            res.status(200).send(JSON.stringify({message: "User is successfuly added!"}));
        });
      }else{
        res.status(400).send(JSON.stringify({message: "User already exists with this email address!"}));
      }
  });
});

    Read All Users

    //GET:  http://localhost:3000/users/
users.get('/', (req, res, next) => {
  const sql = "SELECT * FROM users";
  conn.query(sql, function (err, result) {
      if (err) throw err;
      res.status(200).send(result);
  });
});

    Read Single User With ID

    //GET:  http://localhost:3000/users/:id
users.get('/:id', authFunc.checkAdmin, (req, res, next) => {
  const sql = "SELECT * FROM users WHERE id = ?";
  conn.query(sql, [req.params.id], function (err, result) {
      if (err) throw err;
      res.status(200).send(result[0]);
  });
});

    Update User With ID

    //POST:  http://localhost:3000/users/:id
users.post('/:id', (req, res, next) => {
  const sql = "UPDATE users SET ? WHERE id = ?";
  const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
  conn.query(sql, [user, req.params.id], function (err, result) {
      if (err) throw err;
      res.status(200).send(JSON.stringify({message: "User information is successfuly updated!"}));
  });
});

    Delete User With ID

    //DELETE:  http://localhost:3000/users/:id
users.delete('/:id', (req, res) => {
  const sql = "SELECT * FROM users WHERE id = ?";
  conn.query(sql, [req.params.id], function (err, result) {
      if (err) throw err;
      if(!result[0]){
        res.status(404).send(JSON.stringify({message: "No user exists with this ID!"}));
      }else{
          const sql = "DELETE FROM users WHERE id = ?";
          conn.query(sql, [req.params.id], function (err, result) {
              if (err) throw err;
              res.status(200).send(JSON.stringify({message: "User is successfuly deleted!"}));
          });
      }
  });
});

    Complete Code

    //app dependencies
const express = require('express');
const app = express(); // creates an express application
const bodyParser = require('body-parser');
var cors = require('cors');
const mysql = require('mysql'); //adding the mysql module

//app middle-wares
app.use(cors());
app.use(bodyParser.json());
app.use((req, res, next) => {
    res.header('Access-Control-Allow-Origin', '*');
    res.header('Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content-Type, Accept, Authorization');
    next();
});

//database connection
const db = mysql.createConnection({
  host: "localhost",
  user: "db username",
  password: "db password",
  database: "db name"
});

//Routes
const users = express.Router();
app.use('/users', users);

//POST:  http://localhost:3000/users/
users.post('/', (req, res, next) => {
  const sql = "SELECT * FROM users WHERE email = ?";
  conn.query(sql, [req.body.email], function (err, result) {
      if (err) throw err;
      if(!result[0]){
        const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
        const sql = "INSERT INTO users SET ?";
        conn.query(sql, [user], function (err, result) {
            if (err) throw err;
            res.status(200).send(JSON.stringify({message: "User is successfuly added!"}));
        });
      }else{
        res.status(400).send(JSON.stringify({message: "User already exists with this email address!"}));
      }
  });
});

//GET:  http://localhost:3000/users/
users.get('/', (req, res, next) => {
  const sql = "SELECT * FROM users";
  conn.query(sql, function (err, result) {
      if (err) throw err;
      res.status(200).send(result);
  });
});

//GET:  http://localhost:3000/users/:id
users.get('/:id', authFunc.checkAdmin, (req, res, next) => {
  const sql = "SELECT * FROM users WHERE id = ?";
  conn.query(sql, [req.params.id], function (err, result) {
      if (err) throw err;
      res.status(200).send(result[0]);
  });
});

//POST:  http://localhost:3000/users/:id
users.post('/:id', (req, res, next) => {
  const sql = "UPDATE users SET ? WHERE id = ?";
  const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
  conn.query(sql, [user, req.params.id], function (err, result) {
      if (err) throw err;
      res.status(200).send(JSON.stringify({message: "User information is successfuly updated!"}));
  });
});

//DELETE:  http://localhost:3000/users/:id
users.delete('/:id', (req, res) => {
  const sql = "SELECT * FROM users WHERE id = ?";
  conn.query(sql, [req.params.id], function (err, result) {
      if (err) throw err;
      if(!result[0]){
        res.status(404).send(JSON.stringify({message: "No user exists with this ID!"}));
      }else{
          const sql = "DELETE FROM users WHERE id = ?";
          conn.query(sql, [req.params.id], function (err, result) {
              if (err) throw err;
              res.status(200).send(JSON.stringify({message: "User is successfuly deleted!"}));
          });
      }
  });
});

//App server creation
app.listen(3000, function(){
    console.log('Server is running at port : ' + 3000);
});

    I hope, this guide will be helpful for you to get started with NodeJS API development using ExpressJS and MySQL. Feel free to comment your suggestions and how the guide can be further improved.

  • 7 Steps To Create Hello World App in NodeJS using ExpressJS Framework

    1. Download and install node.js
    2. Create a folder and CD into that folder using using node.js command prompt
    3. run the command npm init
    4. To install and use expressjs ( Fast, unopinionated, minimalist web framework for Node.js ), run the command npm install express –save
    5. Create a new file and name it server.js (code is attached at the end of this post). Add the code (Server.JS File Code), in this file.
    6. To run the node.js app, run the command node server.js
    7. Visit http://localhost:3000/ to see the results

    Extra Packages:

    1. To automatically restart the server, install supervisor package (nodemon can also be used) by running the command npm install npm install supervisor and then run the app using the command supervisor index.js
    2. To get body response from post request, we can use body-parser package. To install it we can use run the command: npm install –save body-parser
    3. To generate json web tokens, we can use jsonwebtoken package. To install it we can run the command: npm install jsonwebtoken –save

    Server.JS File Code

    var express = require('express');
var app = express(); // creates an express application

//routes
app.get('/', (req, res) => {
    res.send('Hello World');
});

app.listen(3000, function(){
    console.log('Server is running at port : ' + 3000);
});