NodeJS Rest API with Express & PostgreSQL

Héla Ben Khalfallah

5 min readMay 9, 2020

How to build a simple Rest API with NodeJS and Express (JSON) ?

Introduction

In my last article, we had seen :

express nodejs http server
some important middlewares like passport for Authentication
adding JWT to passport
communicating with a mongo database using mongoose

In this article, we will use the same socle but we will add communication with a PostgreSQL using sequelize.

NodeJS Rest API with Express, Passport, JWT and MongoDB

How to do a NodeJS Json Rest API ?

medium.com

Add PostgreSQL router middleware

In server.js add :

import PsqlUserRouter from '../app/psql/routes/PsqlUserRouter';if (process.env.PSQL_ENABLED === 'true') {
  // psql routes
  AppLogger.debug('server PSQL_ENABLED');
  app.use(process.env.PSQL_USER_BASE_PATH, PsqlUserRouter);
}

.env :

// psql const
PSQL_DB_PORT = 5432 
PSQL_DB_HOST = 127.0.0.1 
PSQL_DB_USER =  admin
PSQL_DB_PASSWORD =  admin
PSQL_DB_DIALECT =  postgres
PSQL_DB_NAME =  appsqlizepsqldb

psql UserRouter

import express from 'express';
import UserController from '../controllers/UserController';

const {
  Router,
} = express;

// express router
const PsqlUserRouter = Router();

// retrieve all users
PsqlUserRouter.get(process.env.USER_LIST_PATH,
    (request, response) => {
      UserController.find(request, response);
    });

// get user by id
PsqlUserRouter.post(process.env.USER_PROFILE_ID_PATH,
    (request, response) => {
      UserController.findById(request, response);
    });

// get user by email
PsqlUserRouter.post(process.env.USER_PROFILE_EMAIL_PATH,
    (request, response) => {
      UserController.findByEmail(request, response);
    });

// add a user
PsqlUserRouter.post(process.env.USER_ADD_PATH,
    (request, response) => {
      UserController.addIfNotExist(request, response);
    });

// update a user by email key
PsqlUserRouter.post(process.env.USER_UPDATE_PATH,
    (request, response) => {
      UserController.updateIfExist(request, response);
    });

// delete a user by email key
PsqlUserRouter.post(process.env.USER_DELETE_PATH,
    (request, response) => {
      UserController.deleteIfExist(request, response);
    });

export default PsqlUserRouter;

psql UserEntity

module.exports = (sequelize, DataTypes) => {
  const UserEntity = sequelize.define('UserEntity', {
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
      required: true,
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false,
      required: true,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      required: true,
      validate: {
        isEmail: true,
      },
    },
    birthday: {
      type: DataTypes.DATE,
      allowNull: true,
    },
    job: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    created_at: {
      type: DataTypes.DATE,
      field: 'createdAt',
    },
    updated_at: {
      type: DataTypes.DATE,
      field: 'updatedAt',
    },
  }, {});
  UserEntity.associate = function(models) {
    // associations can be defined here
  };
  return UserEntity;
};

psql connection

import Sequelize from 'Sequelize';

// configure psql db
const dbHost = process.env.PSQL_DB_HOST;
const dbName = process.env.PSQL_DB_NAME;
const dbPort = process.env.PSQL_DB_PORT;
const dbUser = process.env.PSQL_DB_USER;
const dbPassword = process.env.PSQL_DB_PASSWORD;
const dbDialect = process.env.PSQL_DB_DIALECT;// psql connection
const sequelize = new Sequelize(dbName, dbUser, dbPassword, {
  host: dbHost,
  port: dbPort,
  dialect: dbDialect,
  operatorsAliases: false,
  define: {
    underscored: true,
  },
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
});

// Connect all the models/tables in
// the database to a db object,
// so everything is accessible via one object
const PsqlDB = {};
PsqlDB.Sequelize = Sequelize;
PsqlDB.sequelize = sequelize;

// Models/tables
PsqlDB.users = require('./UserEntity')(sequelize, Sequelize);

export default PsqlDB;

psql UserController

find all users

// retrieve all user
const find = (request, response) => {
  PsqlDB.users.findAll().then((users) => {
    // retrieve all users
    response
        .status(200)
        .send({
          success: true,
          users: users,
        });
  }).catch((error) => {
    // return error
    AppLogger.info('PsqlRouter findAll error : ' + error);
    response
        .status(401)
        .send({
          success: false,
          message: error.message,
        });
  });
};

find user by id

// find user by id
const findById = (request, response) => {
  const userId = request.body.id || '';
  PsqlDB.users.findById(userId)
      .then((user) => {
      // retrieve all users
        AppLogger.info('PsqlRouter find by id user : ' + user);
        if (user) {
          response
              .status(200)
              .send({
                success: true,
                user: user,
              });
        } else {
          response
              .status(401)
              .send({
                success: false,
                message: MesssageProvider
                    .messageByKey(Messages.KEYS.USER_ID_NOT_FOUND),
              });
        }
      }).catch((error) => {
      // return error
        AppLogger.info('PsqlRouter find by id error : ' + error);
        response
            .status(401)
            .send({
              success: false,
              message: error.message,
            });
      });
};

find user by email

// find user by email
const findByEmail = (request, response) => {
  const email = request.body.email || '';
  PsqlDB.users.findOne({
    where: {
      email: email,
    },
  })
      .then((user) => {
      // retrieve all users
        AppLogger.info('PsqlRouter find by email user : ' + user);
        if (user) {
          response
              .status(200)
              .send({
                success: true,
                user: user,
              });
        } else {
          response
              .status(401)
              .send({
                success: false,
                message: MesssageProvider
                    .messageByKey(Messages.KEYS.USER_EMAIL_NOT_FOUND),
              });
        }
      }).catch((error) => {
      // return error
        AppLogger.info('PsqlRouter find by email error : ' + error);
        response
            .status(401)
            .send({
              success: false,
              message: error.message,
            });
      });
};

Add user if not exist

// add user if not exist
const addIfNotExist = (request, response) => {
  // insert only if user not exist
  // date format should been 1984-09-28
  const firstName = request.body.firstName || '';
  const lastName = request.body.lastName || '';
  const email = request.body.email || '';
  const birthday = request.body.birthday || '';
  const job = request.body.job || '';
  PsqlDB.users.findAll({
    where: {
      email: email,
    },
  }).then((users) => {
    // retrieve all users
    AppLogger.info('PsqlRouter created users : ' + users);
    if (isEmpty(users)) {
      PsqlDB.users.create({
        firstName: firstName,
        lastName: lastName,
        email: email,
        birthday: birthday,
        job: job,
      }).then((user) => {
        // send created customer to client
        AppLogger.info('PsqlRouter created user : ' + user);
        response
            .status(200)
            .send({
              success: true,
              user: user,
            });
      }).catch((error) => {
        // return error
        AppLogger.info('PsqlRouter created error : ' + error);
        response
            .status(401)
            .send({
              success: false,
              message: error.message,
            });
      });
    } else {
      AppLogger.info('PsqlRouter create user already exist  ');
      response
          .status(401)
          .send({
            success: false,
            message: MesssageProvider
                .messageByKey(Messages.KEYS.USER_ALREADY_EXIST),
          });
    }
  }).catch((error) => {
    // return error
    AppLogger.info('PsqlRouter create error : ' + error);
    response
        .status(401)
        .send({
          success: false,
          message: error.message,
        });
  });
};

Update user if exist

// update user if exist
const updateIfExist = (request, response) => {
  // to do
  // edit only if user exist
  // date format should been 1984-09-28
  const firstName = request.body.firstName || '';
  const lastName = request.body.lastName || '';
  const email = request.body.email || '';
  const birthday = request.body.birthday || '';
  const job = request.body.job || '';
  PsqlDB.users.findOne({
    where: {
      email: email,
    },
  }).then((user) => {
    // user to edit
    AppLogger.info('PsqlRouter user to edit  : ' + user);
    if (!isEmpty(user)) {
      PsqlDB.users.update(
          {
            firstName: firstName,
            lastName: lastName,
            birthday: birthday,
            job: job,
          },
          {
            where: {
              email: email,
            },
          },
      ).then(() => {
        // send created customer to client
        AppLogger.info('PsqlRouter edit check user email : ' + email);
        PsqlDB.users.findOne({
          where: {
            email: email,
          },
        })
            .then((user) => {
            // retrieve all users
              AppLogger.info('PsqlRouter edited user result : ' + user);
              response
                  .status(200)
                  .send({
                    success: true,
                    user: user,
                  });
            }).catch((error) => {
            // return error
              AppLogger.info('PsqlRouter edited user error: ' + error);
              response
                  .status(401)
                  .send({
                    success: false,
                    message: error.message,
                  });
            });
      }).catch((error) => {
        // return error
        AppLogger.info('PsqlRouter edit error : ' + error);
        response
            .status(401)
            .send({
              success: false,
              message: error.message,
            });
      });
    } else {
      AppLogger.info('PsqlRouter edit user not exist  ');
      response
          .status(401)
          .send({
            success: false,
            message: MesssageProvider
                .messageByKey(Messages.KEYS.USER_EMAIL_NOT_FOUND),
          });
    }
  }).catch((error) => {
    // return error
    AppLogger.info('PsqlRouter edit error : ' + error);
    response
        .status(401)
        .send({
          success: false,
          message: error.message,
        });
  });
};

Delete user if exist

// delete user if exist
const deleteIfExist = (request, response) => {
  const email = request.body.email || '';
  PsqlDB.users.findOne({
    where: {
      email: email,
    },
  })
      .then((user) => {
      // user to delete
        AppLogger.info('PsqlRouter delete by email user : ' + user);
        if (user) {
          PsqlDB.users.destroy({
            where: {
              email: email,
            },
          }).then(() => {
            AppLogger.info('PsqlRouter delete user by email success : ' + email);
            response
                .status(200)
                .send({
                  success: true,
                  user: user,
                });
          }).catch((error) => {
          // return error
            AppLogger.info('PsqlRouter delete user by email error : ' + error);
            response
                .status(401)
                .send({
                  success: false,
                  message: error.message,
                });
          });
        } else {
          response
              .status(401)
              .send({
                success: false,
                message: MesssageProvider
                    .messageByKey(Messages.KEYS.USER_EMAIL_NOT_FOUND),
              });
        }
      }).catch((error) => {
      // return error
        AppLogger.info('PsqlRouter delete by email error : ' + error);
        response
            .status(401)
            .send({
              success: false,
              message: error.message,
            });
      });
};

Complete code

helabenkhalfallah/node-express-mongoose-sqlize

node, express, mongoose, sqlize, passport, jwt. Contribute to helabenkhalfallah/node-express-mongoose-sqlize…

github.com

Thank you for reading my story.

You can find me at :

Twitter : https://twitter.com/b_k_hela

Github : https://github.com/helabenkhalfallah