DEV Community

Muhammad Tayyab Sheikh
Muhammad Tayyab Sheikh

Posted on

Minimal User Management using Express and PostgreSQL

Often times when I start any new pet project, I get caught up in setting up the basics like setting up the directory structure, choosing libraries etc. So over the last weekend, I built a minimal API template in Node.js which when cloned for a new project is ready to build the actual project rather than spending time in setting up User Management. (Of course this is for projects that require User Management API)

Here is how to get there:

If you just want the code you can view it here:

GitHub logo cstayyab / express-psql-login-api

A simple authentication API using Express.js and PostgreSQL DB

Prerequisites

You would need a few things before you start:

  • Node and NPM installed
  • A Code Editor (I use and highly recommend VS Code)
  • A working instance of PostgreSQL (If you are using Windows and are familiar with WSL then install PostgreSQL there. I wasted quite some time trying to get it running on Windows 10 and finally moved to WSL instead)
  • Create an empty Database in PostgreSQL ( I will use the name logindb)
CREATE DATABASE logindb
Enter fullscreen mode Exit fullscreen mode

The Coding Part

Shall we?

Directory Structure

Create a new directory and initialize package.json

mkdir express-psql-login-api
cd express-psql-login-api
npm init -y
Enter fullscreen mode Exit fullscreen mode

This will create a package.json in express-psql-login-api with following information:

{
  "name": "express-psql-login-api",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}
Enter fullscreen mode Exit fullscreen mode

You can edit name, version and description etc. later. For now just update main script address to server.js

Now, Make directory structure to look like this(You can omit the LICENSE, .gitignore and README.md files):

    .
    ├── .gitignore
    ├── config
    │   ├── db.config.js
    │   └── jwt.config.js
    ├── controllers
    │   └── user.controller.js
    ├── LICENSE
    ├── middlewares.js
    ├── models
    │   ├── index.js
    │   └── user.model.js
    ├── package-lock.json
    ├── package.json
    ├── README.md
    ├── routes
    │   └── user.routes.js
    └── server.js

Installing Dependencies

Install necessary dependencies:

npm install pg, pg-hstore, sequelize, cors, crypto, express, jsonwebtoken
Enter fullscreen mode Exit fullscreen mode

or you can paste the following in the dependencies section of your package.json and then run npm install to install the exact same versions of packages I used:

"dependencies": {
    "cors": "^2.8.5",
    "crypto": "^1.0.1",
    "express": "^4.17.1",
    "jsonwebtoken": "^8.5.1",
    "pg": "^8.6.0",
    "pg-hstore": "^2.3.3",
    "sequelize": "^6.6.2"
  }
Enter fullscreen mode Exit fullscreen mode

Configuration

We have two configuration files in config directory:

  1. db.config.js (PostgreSQL and Sequelize related)
  2. jwt.config.js (To use JSON Web Tokens [JWT])

Database Configuration

Here's what it looks like:

module.exports = {
    HOST: "localhost", // Usually does not need updating
    USER: "postgres", // This is default username
    PASSWORD: "1234", // You might have to set password for this 
    DB: "logindb", // The DB we created in Prerequisites section
    dialect: "postgres", // to tell Sequelize that we are using PostgreSQL
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
  };
Enter fullscreen mode Exit fullscreen mode

JWT Configuration

This one just has one variable that is Secret String for signing JWT Tokens:

module.exports = {
    secret: 'T0P_S3CRet'
}
Enter fullscreen mode Exit fullscreen mode

Setting up the DB Models

We will use Sequelize to create DB Models. On every run it will check if table corresponding to model already exists, if not, it will be created.
As our system is just a User Management system, we have only one model: the User.
First let's connect to the database. Open models/index.js to write the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.connection = sequelize;

// Our `Users` Model, we will create it in next step
db.users = require('./user.model.js')(db.connection, db.Sequelize)

module.exports = db;
Enter fullscreen mode Exit fullscreen mode

The above code initializes DB connection using Sequelize and creates an instance of Users model which we are going to create. So, now in models/user.model.js:

Import crypto for encrypting passwords so we can securely store it in our database:

const crypto = require('crypto')
Enter fullscreen mode Exit fullscreen mode

Define User model using Sequelize:

module.exports = (sequelize, Sequelize) => {
  const User = sequelize.define("user", {
  // TODO Add Columns in Schema Here
  });
  // TODO Some Instance Methods and Password related methods

  return User;
}
Enter fullscreen mode Exit fullscreen mode

Add username and email columns:

username: {
      type: Sequelize.STRING,
      set: function (val) {
        this.setDataValue('username', val.toLowerCase());
      },
      notEmpty: true,
      notNull: true,
      is: /^[a-zA-Z0-9\._]{4,32}$/,
      unique: true
    },
    email: {
      type: Sequelize.STRING,
      set: function (val) {
        this.setDataValue('email', val.toLowerCase());
      },
      isEmail: true,
      notEmpty: true,
      notNull: true,
      unique: true
    },
Enter fullscreen mode Exit fullscreen mode

Both are of type String, both can neither be empty nor null and both must be unique.
The set function does preprocessing before data is stored in Database. Here we are converted username and email to lower case for consistency.

Tip: Always use this.setDataValue to set values instead of directly accessing the column.

We are validating our username by providing a Regular Expression to is attribute. You can test that RegEx here

For email however, we just have to set isEmail to true and Sequelize will take care of it.

Now for the password related fields:

    password: {
      type: Sequelize.STRING,
      get() {
        return () => this.getDataValue('password')
      }
    },
    salt: {
      type: Sequelize.STRING,
      notEmpty: true,
      notNull: true,
      get() {
        return () => this.getDataValue('salt')
      }
    }
Enter fullscreen mode Exit fullscreen mode

Here we are encrypting password with randomly generated salt value for each user, for which we will add other functions later. You might have noticed that we have used get method in both fields and each of them is returning a JavaScript function instead of a value. This tell Sequelize to not include the field in output of functions such as find and findAll hence providing a later of security.

Now add two more functions that are class functions generateSalt and encryptPassword which will be used next to SET and UPDATE the password and Salt field.

  User.generateSalt = function () {
    return crypto.randomBytes(16).toString('base64')
  }
  User.encryptPassword = function (plainText, salt) {
    return crypto
      .createHash('RSA-SHA256')
      .update(plainText)
      .update(salt)
      .digest('hex')
  }
Enter fullscreen mode Exit fullscreen mode

Write another local function setSaltAndPassword which will generate a random salt using generateSalt function and encrypt the password whenever password is updated.

const setSaltAndPassword = user => {
    if (user.changed('password')) {
      user.salt = User.generateSalt()
      user.password = User.encryptPassword(user.password(), user.salt())
    }
  }
Enter fullscreen mode Exit fullscreen mode

We also need to register the above function for every update and create event as follows:

 User.beforeCreate(setSaltAndPassword)
 User.beforeUpdate(setSaltAndPassword)
Enter fullscreen mode Exit fullscreen mode

Last but not the least, we need to add verfiyPassword instance method so we can verify user-entered password in-place.

  User.prototype.verifyPassword = function (enteredPassword) {
    return User.encryptPassword(enteredPassword, this.salt()) === this.password()
  }
Enter fullscreen mode Exit fullscreen mode

Here's complete user.model.js file for your reference
const crypto = require('crypto')

module.exports = (sequelize, Sequelize) => {
  const User = sequelize.define("user", {
    username: {
      type: Sequelize.STRING,
      set: function (val) {
        this.setDataValue('username', val.toLowerCase());
      },
      notEmpty: true,
      notNull: true,
      is: /^[a-zA-Z0-9\._]{4,32}$/,
      unique: true
    },
    email: {
      type: Sequelize.STRING,
      set: function (val) {
        this.setDataValue('email', val.toLowerCase());
      },
      isEmail: true,
      notEmpty: true,
      notNull: true,
      unique: true
    },
    password: {
      type: Sequelize.STRING,
      get() {
        return () => this.getDataValue('password')
      }
    },
    salt: {
      type: Sequelize.STRING,
      notEmpty: true,
      notNull: true,
      get() {
        return () => this.getDataValue('salt')
      }
    }
  });

  User.generateSalt = function () {
    return crypto.randomBytes(16).toString('base64')
  }
  User.encryptPassword = function (plainText, salt) {
    return crypto
      .createHash('RSA-SHA256')
      .update(plainText)
      .update(salt)
      .digest('hex')
  }

  const setSaltAndPassword = user => {
    if (user.changed('password')) {
      user.salt = User.generateSalt()
      user.password = User.encryptPassword(user.password(), user.salt())
    }
  }

  User.prototype.verifyPassword = function (enteredPassword) {
    return User.encryptPassword(enteredPassword, this.salt()) === this.password()
  }

  User.beforeCreate(setSaltAndPassword)
  User.beforeUpdate(setSaltAndPassword)

  return User;
};

Controller for the Model

We will now create controller for User model with following functions:

  1. findUserByUsername
  2. findUserByEmail
  3. signup
  4. login
  5. changepassword
  6. verifypassword

Create a file controllers/user.controller.js without following code:

const db = require("../models");
const User = db.users;
const Op = db.Sequelize.Op;
const where = db.Sequelize.where;
const jwt = require('jsonwebtoken');
const { secret } = require('../config/jwt.config');


async function findUserByUsername(username) {
    try {
        users = await User.findAll({ where: {username: username} })
        return (users instanceof Array) ? users[0] : null;
    }
    catch (ex) {
        throw ex;
    }
}

async function findUserByEamil(email) {
    try {
        users = await User.findAll({ where: {email: email} })
        return (users instanceof Array) ? users[0] : null;
    }
    catch (ex) {
        throw ex;
    }
}


exports.signup = (req, res) => {
    console.log(req.body)
    if(!req.body.username, !req.body.email, !req.body.password) {
        res.status(400).send({
            message: 'Please provide all the fields.'
        });
        return;
    }

    // Create the User Record
    const newUser = {
        username: req.body.username,
        email: req.body.email,
        password: req.body.password
    }

    User.create(newUser)
    .then(data => {
      res.send({
          message: "Signup Successful!"
      });
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while signing you up.",
        errObj: err
      });
    });
}

exports.login = async (req, res) => {
    console.log(req.body)

    if ((!req.body.username && !req.body.email) || (!req.body.password)) {
        res.status(400).send({
            message: 'Please provide username/email and password.'
        });
    }
    user = null;
    if(req.body.username) {
        user = await findUserByUsername(req.body.username);
    } else if (req.body.email) {
        user = await findUserByEamil(req.body.email);
    }
    if(user == null || !(user instanceof User)) {
        res.status(403).send({
            message: "Invalid Credentials!"
        });
    } else {
        if(user.verifyPassword(req.body.password)) {
            res.status(200).send({
                message: "Login Successful",
                token: jwt.sign({ username: user.username, email: user.email }, secret)
            })
        } else {
            res.status(403).send({
                message: "Invalid Credentails!"
            });
        }
    }
}

exports.changepassword = async (req, res) => {
    console.log(req.body)

    if (!req.body.oldpassword || !req.body.newpassword) {
        res.status(400).send({
            message: 'Please provide both old and new password.'
        });
    }
    user = await findUserByUsername(req.user.username);
    if(user == null || !(user instanceof User)) {
        res.status(403).send({
            message: "Invalid Credentials!"
        });
    } else {
        if(user.verifyPassword(req.body.oldpassword)) {
            user.update({password: req.body.newpassword}, {
                where: {id: user.id}
            });
            res.status(200).send({
                message: "Password Updated Successfully!"
            })
        } else {
            res.status(403).send({
                message: "Invalid Old Password! Please recheck."
            });
        }
    }
}

exports.verifypassword = async (req, res) => {
    console.log(req.body)

    if (!req.body.password) {
        res.status(400).send({
            message: 'Please provide your password to re-authenticate.'
        });
    }
    user = await findUserByUsername(req.user.username);
    if(user == null || !(user instanceof User)) {
        res.status(403).send({
            message: "Invalid Credentials!"
        });
    } else {
        if(user.verifyPassword(req.body.password)) {
            res.status(200).send({
                message: "Password Verification Successful!"
            })
        } else {
            res.status(403).send({
                message: "Invalid Password! Please recheck."
            });
        }
    }
}

module.exports = exports;
Enter fullscreen mode Exit fullscreen mode

In the above code you might have noticed the use of req.user which is not a normal variable in Express. This is being used to check for User Authentication. To know where it is coming from move to next section.

Introducing Middlewares

We are just write two middlewares in this application one is for basic logging (which you can of course extend) and other one is for authentication of each request on some specific routes which we will define in next section.

We will put our middlewares in middlewares.js in root directory.

Logging

This one just outputs a line on console telling details about received request:

const logger = (req, res, next) => {
    console.log(`Received: ${req.method} ${req.path} Body: ${req.body}`);
    next()
}
Enter fullscreen mode Exit fullscreen mode

AuthenticateJWT

In this we are going to look for Authorization header containing the JWT token returned to the user upon login. If it is invalid, it means user isn't logged in or the token has expired. In this case request will not proceed and an error will be returned.

const { secret } = require('./config/jwt.config');
const jwt = require('jsonwebtoken');

const authenticateJWT = (req, res, next) => {
    const authHeader = req.headers.authorization;

    if (authHeader) {
        const token = authHeader.split(' ')[1];

        jwt.verify(token, secret, (err, user) => {
            if (err) {
                return res.status(403).send({
                    message: 'Invalid Authorization Token.'
                });
            }

            req.user = user;
            next();
        });
    } else {
        res.status(401).send({
            message: 'You must provide Authorization header to use this route.'
        });
    }
}; 
Enter fullscreen mode Exit fullscreen mode

Now we have to export both of them so other files can use it:

module.exports = {
    logger: logger,
    auth: authenticateJWT
}
Enter fullscreen mode Exit fullscreen mode

Routing the Traffic

Now we are going to define all our endpoints and route them to respective functions. For that create a file routes/user.routes.js as follows:

module.exports = app => {
    const users = require("../controllers/user.controller.js");
    const {_, auth} = require('../middlewares');

    var router = require("express").Router();

    router.post("/signup", users.signup);

    router.post("/login", users.login);

    router.post("/changepassword", auth, users.changepassword);

    router.post("/verifypassword", auth, users.verifypassword);

    app.use('/user', router);
};
Enter fullscreen mode Exit fullscreen mode

Notice that we have used our auth middleware with routes that we wanted behind the Login Wall.

Bringing up the Server

In the very end we will put everything together in out entry file server.js in the root directory.

const express = require('express');
const cors = require('cors');
const db = require("./models");
const {logger, } = require('./middlewares');

const app = express();

var corsOptions = {
  origin: '*'
};

app.use(cors(corsOptions));

// parse requests of content-type - application/json
app.use(express.json());

// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));

// Use custom logging middleware
app.use(logger)

// Prepare DB
db.connection.sync();

// simple route
app.get('/', (req, res) => {
  res.json({ message: 'Welcome to Login System', developer: { name: 'Muhammad Tayyab Sheikh', alias: 'cstayyab'} });
});

require("./routes/user.routes")(app);

// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

Enter fullscreen mode Exit fullscreen mode

Let's Run

You are now ready to start the API and test it using cURL or Postman etc. Just run npm start and see the magic.

For sample output of the API, checkout the demo.

Conclusion

In this article, I have tried not to spoon feed each and every details and leave somethings for the developer to explore. But if you have any question or suggestion, feel free to pen it down in the comment section below.

Top comments (0)