Setting up Express JS REST API, Postgres, and Sequelize ORM with ES6+
Tags: node, sequelizejs, postgres, express
Published: 2021-02-02 10:58:22

Developing with express js (node js), Postgres and Sequelize ORM has been fun for me. I enjoy working with ORMs such as Sequelize as it aids me in building projects faster and efficiently. I want to share with you how I set up my Express js project with Postgres and Sequelize to code in ES6 and above.

This tutorial assumes you have a fair knowledge of the JavaScript language.

### Setup
To begin, install [Node JS](https://nodejs.org/en/) on your computer. It comes with a package manager which you'll use for command-line operations.

* Create a folder where your project will reside, you can give it any name you want. we'll call it node-project

* Open your command line and change directory into it.

`cd node-project`
* Initialize a NodeJS application by running the following command:

```
npm init -y
```
This will create a package.json file with a basic config. You can manually input the configuration by omitting the `-y` flag.

### Express JS Setup

To install express js, run `npm i express` on your command line within the project folder.

* Create an `index.js` file.

* Add the following codes in the newly created index.js file

```
const express = require('express');

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// Create a catch-all route for testing the installation.
app.get('*', (req, res) => res.status(200).send({
  message: 'Hello World!',
}));

const port = 5000;

app.listen(port, () => {
  console.log('App is now running at port ', port)
})
```

* Run `node ./index.js` on the terminal
* Visit `http://localhost:5000` on your browser to view your express API server. Any route will display the same welcome message because of the catch-all route we created.

When we make changes, we'll have to kill the process and restart to see the effect. So, we'll install a package that will save us that stress: Nodemon.

* Run `npm i -D nodemon`

* Edit scripts in package.json file to look like this:

```
"scripts": {
    "dev": "nodemon index.js"
  },
```
Kill your current process and run `npm run dev` to start the server. Going forward, when you start the server this way, you won't need to restart it to see changes.

The folder structure should look like this:

```
node-project
├── node_modules
├── index.js
├── package.json
├── package-lock.json
```

#### Babel Setup for ES6

To code in ES6 and above, you need to install babel packages,
Run the following command to install the necessary packages for our project:

```
npm i @babel/core @babel/node @babel/preset-env
```
* Create a .babelrc file in the folder and populate with the following:
```
{
  "presets": [
    [
      "@babel/preset-env", {
        "targets": {
          "node": "current"
        }
      }
    ]
  ]
}

```

* Edit the package.json file script command to use babel

```
 "scripts": {
    "dev": "nodemon --exec babel-node index.js"
  },
```

The package.json file should look like this:

```
{
  "name": "project",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon --exec babel-node index.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "@babel/core": "^7.12.10",
    "@babel/node": "^7.12.10",
    "@babel/preset-env": "^7.12.11"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

```

The index.js file should look like this:

```
import express from 'express';

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// Create a catch-all route for testing the installation.
app.get('*', (req, res) => res.status(200).send({
  message: 'Hello World!',
}));

const port = 5000;

app.listen(port, () => {
  console.log('App is now running at port ', port)
})

```

* Restart your server and you are ready to code your node js API with ES6 syntax

### Sequelize Setup in Express JS App

We will be using Postgres DB in this tutorial but you can use any DB you are comfortable with such as MySQL, SQLite, etc.

To get started with Sequelize ORM with Postgres, you need a Postgres DB which could be remote or on your local machine. Visit [this link](https://www.postgresql.org/download/) to install Postgres on your computer.

To use Sequelize in your node app with Postgres, you can install the Command Line Tool (CLI) package globally on your computer or in the app. You just need to know how to call it.

In this tutorial, we will install it globally and in the project as well.

Run the command to install it locally and globally

```
npm install -g sequelize-cli

npm install sequelize-cli
```

Depending on which approach you choose, if globally installed, you will always make the command as `sequelize do:something`, if local, it'll be `./node_modules/.bin/sequelize` inside the project folder.

* Install Sequelize and Postgres packages by running the command:

```
npm i sequelize pg
```

* Install babel to work with sequelize
```
npm i @babel/register
```

* Create `.sequelizerc` file and populate with the following configuration
```
require("@babel/register");

const path = require('path');

module.exports = {
  "config": path.resolve('./src/config', 'config.json'),
  "models-path": path.resolve('./src/models'),
  "seeders-path": path.resolve('./src/database/seeders'),
  "migrations-path": path.resolve('./src/database/migrations')
};
```

The `sequelizerc` file contains how the folders for Sequelize will be organized. If you don't have it, it'll still work but everything will be placed in the root folder. The configuration will be in src/config, Sequelize models will reside in src/models folder, while seeders file and migration will be in src/database folder.

* Next, we initialize Sequelize in the project.

```
sequelize init
```

The command creates the necessary folders and files for Sequelize ORM.

* If you look at `src/models/index.js`, it is not written in ES6. Let's refactor that and it'll become:

```
import fs from 'fs';
import path from 'path';
import Sequelize from 'sequelize';
import enVariables from '../config/config.json';

const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = enVariables[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(file => (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js'))
  .forEach(file => {
    // eslint-disable-next-line global-require,import/no-dynamic-require
    const model = require(path.join(__dirname, file)).default(sequelize, Sequelize.DataTypes);
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

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

export default db;
```

* Create a DB and update the config/config.json file accordingly:
```
{
  "development": {
    "username": "postgres",
    "password": "password",
    "database": "node-project",
    "port": "5434",
    "host": "127.0.0.1",
    "dialect": "postgres"

  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

```

#### All is now set to create models and migration.
Let's create a model and migration for users.

Run the command: 

```
sequelize model:generate --name User --attributes name:string,email:string,phone:string,password:string,status:string,last_login_at:date,last_ip_address:string
```
This command creates a User model and migration table in the corresponding folders. The attributes are the fields we want to have on the table.

The user model looks like this:
```
'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    phone: DataTypes.STRING,
    password: DataTypes.STRING,
    status: DataTypes.STRING,
    last_login_at: DataTypes.DATE,
    last_ip_address: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};
```
And migrations like this:
```
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      phone: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.STRING
      },
      last_login_at: {
        type: Sequelize.DATE
      },
      last_ip_address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```

Let's refactor the generated migration and model to:

```
import { Model } from 'sequelize';

export default (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    phone: DataTypes.STRING,
    password: DataTypes.STRING,
    status: DataTypes.STRING,
    last_login_at: DataTypes.DATE,
    last_ip_address: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};
```

and 

```
export default {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      phone: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.STRING
      },
      last_login_at: {
        type: Sequelize.DATE
      },
      last_ip_address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```
I like renaming the model name to use uppercase first and in some cases camel case.

Since it's a user model we created and we have some protected fields, I'll quickly add a method to hide the fields on JSON response.

Let's add some validations to the fields for user authentication with node js (express js).

The model and migration will now look like this:

```
import { Model } from 'sequelize';

const PROTECTED_ATTRIBUTES = ['password'];

export default (sequelize, DataTypes) => {
  class User extends Model {
    toJSON() {
      // hide protected fields
      const attributes = { ...this.get() };
      // eslint-disable-next-line no-restricted-syntax
      for (const a of PROTECTED_ATTRIBUTES) {
        delete attributes[a];
      }
      return attributes;
    }
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: {
      type: DataTypes.STRING,
      allowNull: {
        args: false,
        msg: 'Please enter your email address',
      },
      unique: {
        args: true,
        msg: 'Email already exists',
      },
      validate: {
        isEmail: {
          args: true,
          msg: 'Please enter a valid email address',
        },
      },
    },
    phone: {
      type: DataTypes.STRING,
      unique: true,
    },
    password: DataTypes.STRING,
    status: DataTypes.STRING,
    last_login_at: DataTypes.DATE,
    last_ip_address: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};
```

and 
```
export default {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        allowNull: false,
        unique: true,
        type: Sequelize.STRING,
      },
      phone: {
        type: Sequelize.STRING,
        unique: true,
      },
      password: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.STRING
      },
      last_login_at: {
        type: Sequelize.DATE
      },
      last_ip_address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```

We have to refactor any model and migration we create in the future to look like this.

* Next, we run the migration to create the DB tables:
```
sequelize db:migrate
```

Our database is now created.

#### Route and Controllers

Let's create route and controllers to be able to interact with our database.

* Create a `controllers` folder in `src` and add `AuthController.js` file to it.
That's where our user management logic will reside.

Add the following code to create user sign up controller logic:

```
import { Op } from 'sequelize';
import model from '../models';

const { User } = model;

export default {
  async signUp(req, res) {
    const {email, password, name, phone} = req.body;
    try {
      const user = await User.findOne({where: {[Op.or]: [ {phone}, {email} ]}});
      if(user) {
        return res.status(422)
        .send({message: 'User with that email or phone already exists'});
      }

      await User.create({
        name,
        email,
        password,
        phone,
      });
      return res.status(201).send({message: 'Account created successfully'});
    } catch(e) {
      console.log(e);
      return res.status(500)
      .send(
        {message: 'Could not perform operation at this time, kindly try again later.'});
    }
  }
}
```

Ideally, you will have to encrypt (hash) the user's password before storing it in the DB.

Create the route folder with index.js file inside and add the following code:

```
import AuthController from '../controllers/AuthController'

export default (app) => {
  app.post('/register', AuthController.signUp);

// Create a catch-all route for testing the installation.
app.all('*', (req, res) => res.status(200).send({
  message: 'Hello World!',
}));
};

```

Notice that we have transferred the first route we created here.
Our top level index.js file will now look like this:

```
import express from 'express';
import route from './src/routes'

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

route(app);

const port = 5000;

app.listen(port, () => {
  console.log('App is now running at port ', port)
})
```

#### Restart the server and use postman to test the API.

That's basically how I setup Express JS REST API, Postgres, and Sequelize ORM with ES6+.

See the code on github [here](https://github.com/richienabuk/express-js-postgres-sequelize).

If you have any concerns, kindly raise them in the comment section below.

> I wrote this tutorial as a foundation for an article I want to publish on [how to implement dynamic Role-based Access Control (RBAC) in Express JS](https://dev.to/richienabuk/how-to-implement-dynamic-role-based-access-control-rbac-in-express-js-rest-api-54fe) where an admin can assign multiple roles to a user and a role can have multiple permissions.

Update: [How to implement dynamic Role-based Access Control (RBAC) in Express JS REST API](https://dev.to/richienabuk/how-to-implement-dynamic-role-based-access-control-rbac-in-express-js-rest-api-54fe)