Minimal User Management using Express and PostgreSQL
Tags: javascript, postgres, codenewbie, 100daysofcode
Published: 2021-04-28 00:56:46

---
title: Minimal User Management using Express and PostgreSQL
description: Developing a simple Node.js template with User Management for your future projects
tags: JavaScript, Postgres, CodeNewbie, 100DaysOfCode
published: true
---
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:

>
{% spoiler If you just want the code you can view it here: %} {% github https://github.com/cstayyab/express-psql-login-api no-readme %} {% endspoiler %}

# Prerequisites
You would need a few things before you start:
* Node and NPM installed
* A Code Editor (I use and highly recommend [VS Code](https://github.com/microsoft/vscode))
* {% details A working instance of [PostgreSQL](https://www.postgresql.org/download/) %}
<small>(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)</small> {% enddetails %}
* Create an empty Database in PostgreSQL ( I will use the name `logindb`)
```sql
CREATE DATABASE logindb
```

# The Coding Part
Shall we?
## Directory Structure
Create a new directory and initialize `package.json`
```bash
mkdir express-psql-login-api
cd express-psql-login-api
npm init -y
```
This will create a `package.json` in `express-psql-login-api` with following information:
```json
{
  "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"
}
```
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):
<pre>
    .
    ├── .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
</pre>
## Installing Dependencies
Install necessary dependencies:
```bash
npm install pg, pg-hstore, sequelize, cors, crypto, express, jsonwebtoken
```
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:
```json
"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"
  }
```
## 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:
```javascript
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
    }
  };
```

### JWT Configuration
This one just has one variable that is Secret String for signing JWT Tokens:
```javascript
module.exports = {
    secret: 'T0P_S3CRet'
}
```

## 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:

```javascript
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;
```
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:
```javascript
const crypto = require('crypto')
```
Define `User` model using Sequelize:
```javascript
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;
}
```
Add `username` and `email` columns:
```javascript
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
    },
```
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 <a href="https://regex101.com/r/1ds0na/1" target="_blank">here</a>

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

Now for the password related fields:
```javascript
    password: {
      type: Sequelize.STRING,
      get() {
        return () => this.getDataValue('password')
      }
    },
    salt: {
      type: Sequelize.STRING,
      notEmpty: true,
      notNull: true,
      get() {
        return () => this.getDataValue('salt')
      }
    }
```
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.

```javascript
  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')
  }
```
Write another local function `setSaltAndPassword` which will generate a random salt using `generateSalt` function and encrypt the password whenever password is updated.

```javascript
const setSaltAndPassword = user => {
    if (user.changed('password')) {
      user.salt = User.generateSalt()
      user.password = User.encryptPassword(user.password(), user.salt())
    }
  }
```
We also need to register the above function for every update and create event as follows:

```javascript
 User.beforeCreate(setSaltAndPassword)
 User.beforeUpdate(setSaltAndPassword)
```

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

```javascript
  User.prototype.verifyPassword = function (enteredPassword) {
    return User.encryptPassword(enteredPassword, this.salt()) === this.password()
  }
```
{% details Here's complete <code>user.model.js</code> file for your reference %}
```javascript
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;
};
```
{% enddetails %}

## 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:

```javascript
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;
```

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:

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

### 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.

```javascript
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.'
        });
    }
}; 
```

Now we have to export both of them so other files can use it:
```javascript
module.exports = {
    logger: logger,
    auth: authenticateJWT
}
```

## 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:
```javascript
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);
};
```

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.

```javascript
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}.`);
});

```

# 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 <a href="https://github.com/cstayyab/express-psql-login-api#demo" target="_blank">demo</a>.


# 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.